VBA help with executing procedure

Question

Private Sub CmdPharmacy_Click()

Dim myM(11) As String

Set mydb = CurrentDb

If PMNM = "" Then
    PMNM = UCase(Left(cboMonth.Value, 3))
End If

sQ = "SELECT mN FROM PharDate WHERE mT = '" & PMNM & "';"
Set myrec = mydb.OpenRecordset(sQ, dbOpenDynaset)
myrec.MoveFirst
myC = myrec.Fields("mN")

For i = 0 To myC - 1
    myM(i) = "M" & i + 1
Next i

For i = myC To 11
    myM(i) = "NDATA"
Next i

'mydb.TableDefs.Delete ("PHAR_REPORT")

sQ = "SELECT HistoryData2.PR, HistoryData2.CC, " & _
       "HistoryData2." & myM(0) & " as U1, HistoryData2." & myM(1) & " as U2, HistoryData2." & myM(2) & " as U3, HistoryData2." & myM(3) & " as U4, " & _
       "HistoryData2." & myM(4) & " as U5, HistoryData2." & myM(5) & " as U6, HistoryData2." & myM(6) & " as U7, HistoryData2." & myM(7) & " as U8, " & _
       "HistoryData2." & myM(8) & " as U9, HistoryData2." & myM(9) & " as U10, HistoryData2." & myM(10) & " as U11, HistoryData2." & myM(11) & " as U12," & _
       "Revenue." & myM(0) & " as R1, Revenue." & myM(1) & " as R2, Revenue." & myM(2) & " as R3, Revenue." & myM(3) & " as R4, Revenue." & myM(4) & " as R5, " & _
       "Revenue." & myM(5) & " as R6, Revenue." & myM(6) & " as R7, Revenue." & myM(7) & " as R8, Revenue." & myM(8) & " as R9, Revenue." & myM(9) & " as R10, " & _
       "Revenue." & myM(10) & " as R11, Revenue." & myM(11) & " as R12, " & _
       "INTO PHAR_REPORT FROM HistoryData2, Revenue WHERE (((HistoryData2.PR) = Revenue.PR) And " & _
       "((Revenue.PR)>=76700000 And (Revenue.PR)<=76900000)) ORDER BY HistoryData2.PR;"

When I try and execute sQ, I receive the run-time error that..... The SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect.

Can anyone help me figure this error out?

SELECT HistoryData2.PR, HistoryData2.CC, HistoryData2.M1 as U1, HistoryData2.M2 as U2, HistoryData2.M3 as U3, HistoryData2.M4 as U4, HistoryData2.M5 as U5, HistoryData2.M6 as U6, HistoryData2.M7 as U7, HistoryData2.M8 as U8, HistoryData2.NDATA as U9, HistoryData2.NDATA as U10, HistoryData2.NDATA as U11, HistoryData2.NDATA as U12 Revenue.M1 as R1, Revenue.M2 as R2, Revenue.M3 as R3, Revenue.M4 as R4, Revenue.M5 as R5, Revenue.M6 as R6, Revenue.M7 as R7, Revenue.M8 as R8, Revenue.NDATA as R9, Revenue.NDATA as R10, Revenue.NDATA as R11, Revenue.NDATA as R12 
   INTO PHAR_REPORT 
FROM HistoryData2, Revenue 
WHERE (((HistoryData2.PR) = Revenue.PR) And ((Revenue.PR)>=76700000 And (Revenue.PR)<=76900000)) 
ORDER BY HistoryData2.PR;

Answer 1

You need to see the finished SQL statement you're attempting to execute. Add a line before mydb.Execute like this:

Debug.Print sQ
mydb.Execute sQ

Debug.Print will print the statement in the Immediate Window. (You can use CTRL+g to get there.) Copy the statement, then open a new query, switch to SQL View and paste it in there, and try it. Hopefully that effort will help you identify the problem. If you can't resolve it, edit your question to include that statement so we can see it, too.

Update: The latest revision of your question includes a comma after the last field in your field list:

"Revenue." & myM(10) & " as R11, Revenue." & myM(11) & " as R12, " & _

Eliminate that comma after R12.

Update2: David Fenton spotted the missing comma in the SQL statement you included. I would expect the missing comma to cause error 3075, "Syntax error (missing operator) in query expression ...". However, your last report indicated the error message you're receiving is "Data type mismatch in criteria expression". I suspect we have been dealing with a moving target. You've reported at least 3 different error messages. And the SQL statement you posted was not consistent with the VBA code you showed us.

If your error message is still "Data type mismatch in criteria expression", try this query and show us what it gives you:

SELECT "HistoryData2" AS table_name, TypeName(PR) AS pr_data_type
FROM HistoryData2
UNION ALL
SELECT "Revenue" AS table_name, TypeName(PR) AS pr_data_type
FROM Revenue;

Answer 2

In your SQL statement's SELECT clause you have this:

HistoryData2.NDATA as U12 Revenue.M1 as R1

It should be this:

HistoryData2.NDATA as U12, Revenue.M1 as R1

That is, a missing comma.

However, I see in the code that concatenates the string, the trailing comma is there. I can't explain the discrepancy, unless you've changed the code, or did not derive the SQL from the correct source.