Help with SQL query error

[jeremypaule]

I’m reposting my previous thread because it is pretty much a different question now. I am getting the error, too few parameters expected 1. I’ve looked through it 1000 times, hopefully someone else’s eyes can catch what I’ve missed.

Set rst = CurrentDb.OpenRecordset("SELECT [MANAGER TABLE].[ACCT_NAME_FS AS Account], [PY CY TABLE].[MONTH], " & _
"Sum([CY VOL]-[PY VOL]) AS VAR, [PY CY TABLE].[ONRMP_CITY], [PY CY TABLE].[OFFRMP_CITY] " & _
"FROM [PY CY TABLE] INNER JOIN [MANAGER TABLE] ON [PY CY TABLE].[SHIPPER] = [MANAGER TABLE].[CUST_NAME_GS] " & _
" GROUP BY [MANAGER TABLE].[ACCT_NAME_FS], [PY CY TABLE].[MONTH], [PY CY TABLE].[ONRMP_CITY], " & _
"[PY CY TABLE].[OFFRMP_CITY] HAVING [PY CY TABLE].[MONTH]= " & [Forms]![Lane Input Form]![MONTH] & " AND" & _
"[PY CY TABLE].[ONRMP_CITY]='" & [Forms]![Lane Input Form]![ORIGIN] & "' AND [PY CY TABLE].[OFFRMP_CITY]='" & [Forms]![Lane Input Form]![DESTINATION] & "' ;")

 

[KeithG]

Do you have your form open?

 

[jeremypaule]

yes, I made the forms cbo boxes values to see if it was reading them and infact the query is. it now looks like

Dim rst As Recordset
Dim strSQL As String 'SQL for query
strMonth = Me.MONTH.Value
strOrigin = Me.ORIGIN.Value
strDestination = Me.DESTINATION.Value

strSQL = ("SELECT [MANAGER TABLE].[ACCT_NAME_FS AS Account], [PY CY TABLE].[MONTH], " & _
"Sum([CY VOL]-[PY VOL]) AS VAR, [PY CY TABLE].[ONRMP_CITY], [PY CY TABLE].[OFFRMP_CITY] " & _
"FROM [PY CY TABLE] INNER JOIN [MANAGER TABLE] ON [PY CY TABLE].[SHIPPER] = [MANAGER TABLE].[CUST_NAME_GS] " & _
" GROUP BY [MANAGER TABLE].[ACCT_NAME_FS], [PY CY TABLE].[MONTH], [PY CY TABLE].[ONRMP_CITY], " & _
"[PY CY TABLE].[OFFRMP_CITY] HAVING [PY CY TABLE].[MONTH]= " strMonth " AND" & _
"[PY CY TABLE].[ONRMP_CITY]='" & strOrigin & "' AND [PY CY TABLE].[OFFRMP_CITY]='" & strDestination & "' ;")

Set rst = CurrentDb.OpenRecordset(strSQL)

same error, expected 1.

 

[jeremypaule]

wow i got it to work thanks. instead of making it HAVING i remade it to WHERE. THANKS for the help everyone!