Syntax (missing operator) error

G

Guest

It could be because I have looked at this too long but I just can't see what
is wrong with the following:

rst31 = "SELECT [LoadTable Payroll Clear].FISCAL_YR, " & _
"[LoadTable Payroll Clear].AGENCY_NBR," & _
"[LoadTable Payroll Clear].FUND_CD," & _
"[LoadTable Payroll Clear].APPROP_UNIT_NBR," & _
"[LoadTable Payroll Clear].[APPROP_ ORGN_ NBR]," & _
"[LoadTable Payroll Clear].AGENCY_DIV_NBR," & _
"[LoadTable Payroll Clear].APPROP_NM," & _
"[LoadTable Payroll Clear].APPROP_SHORT_NM," & _
"[LoadTable Payroll Clear].DEDC_RCPT_CAP_AMT," & _
"[LoadTable Payroll Clear].LEGAL_CITATION_TXT," & _
"[LoadTable Payroll Clear].SECT_SEQ_CD," & _
"[LoadTable Payroll Clear].BDGT_AUTH_OPT_IND," & _
"[LoadTable Payroll Clear].APPROP_TYPE_CD," & _
"[LoadTable Payroll Clear].APPROP_APPROV_AMT," & _
"[LoadTable Payroll Clear].APPROP_PROJ_GRANT_CD" & _
"FROM [LoadTable Payroll Clear]" & _
"WHERE ((([LoadTable Payroll Clear].[AGENCY_NBR]) = " & Chr(34) &
rst10![AGENCY_NBR] & Chr(34) & Chr(41) & Chr(41) & Chr(59)

When I use the Immediate window I find that rst31 is as follows:

SELECT [LoadTable Payroll Clear].FISCAL_YR, [LoadTable Payroll
Clear].AGENCY_NBR,[LoadTable Payroll Clear].FUND_CD,[LoadTable Payroll
Clear].APPROP_UNIT_NBR,[LoadTable Payroll Clear].[APPROP_ ORGN_
NBR],[LoadTable Payroll Clear].AGENCY_DIV_NBR,[LoadTable Payroll
Clear].APPROP_NM,[LoadTable Payroll Clear].APPROP_SHORT_NM,[LoadTable Payroll
Clear].DEDC_RCPT_CAP_AMT,[LoadTable Payroll
Clear].LEGAL_CITATION_TXT,[LoadTable Payroll Clear].SECT_SEQ_CD,[LoadTable
Payroll Clear].BDGT_AUTH_OPT_IND,[LoadTable Payroll
Clear].APPROP_TYPE_CD,[LoadTable Payroll Clear].APPROP_APPROV_AMT,[LoadTable
Payroll Clear].APPROP_PROJ_GRANT_CDFROM [LoadTable Payroll Clear]WHERE
((([LoadTable Payroll Clear].[AGENCY_NBR]) = "B04"));

Yet I get the runtime error 3075, Syntax (Missing Operator) when I try to
use it. Can someone with better eyes spot the problem? Thanks for your
help!!!!
 
D

Dirk Goldgar

In
vtj said:
It could be because I have looked at this too long but I just can't
see what is wrong with the following:

rst31 = "SELECT [LoadTable Payroll Clear].FISCAL_YR, " & _
"[LoadTable Payroll Clear].AGENCY_NBR," & _
"[LoadTable Payroll Clear].FUND_CD," & _
"[LoadTable Payroll Clear].APPROP_UNIT_NBR," & _
"[LoadTable Payroll Clear].[APPROP_ ORGN_ NBR]," & _
"[LoadTable Payroll Clear].AGENCY_DIV_NBR," & _
"[LoadTable Payroll Clear].APPROP_NM," & _
"[LoadTable Payroll Clear].APPROP_SHORT_NM," & _
"[LoadTable Payroll Clear].DEDC_RCPT_CAP_AMT," & _
"[LoadTable Payroll Clear].LEGAL_CITATION_TXT," & _
"[LoadTable Payroll Clear].SECT_SEQ_CD," & _
"[LoadTable Payroll Clear].BDGT_AUTH_OPT_IND," & _
"[LoadTable Payroll Clear].APPROP_TYPE_CD," & _
"[LoadTable Payroll Clear].APPROP_APPROV_AMT," & _
"[LoadTable Payroll Clear].APPROP_PROJ_GRANT_CD" & _
"FROM [LoadTable Payroll Clear]" & _
"WHERE ((([LoadTable Payroll Clear].[AGENCY_NBR]) = " & Chr(34) &
rst10![AGENCY_NBR] & Chr(34) & Chr(41) & Chr(41) & Chr(59)

When I use the Immediate window I find that rst31 is as follows:

SELECT [LoadTable Payroll Clear].FISCAL_YR, [LoadTable Payroll
Clear].AGENCY_NBR,[LoadTable Payroll Clear].FUND_CD,[LoadTable Payroll
Clear].APPROP_UNIT_NBR,[LoadTable Payroll Clear].[APPROP_ ORGN_
NBR],[LoadTable Payroll Clear].AGENCY_DIV_NBR,[LoadTable Payroll
Clear].APPROP_NM,[LoadTable Payroll Clear].APPROP_SHORT_NM,[LoadTable
Payroll Clear].DEDC_RCPT_CAP_AMT,[LoadTable Payroll
Clear].LEGAL_CITATION_TXT,[LoadTable Payroll
Clear].SECT_SEQ_CD,[LoadTable Payroll
Clear].BDGT_AUTH_OPT_IND,[LoadTable Payroll
Clear].APPROP_TYPE_CD,[LoadTable Payroll
Clear].APPROP_APPROV_AMT,[LoadTable Payroll
Clear].APPROP_PROJ_GRANT_CDFROM [LoadTable Payroll Clear]WHERE
((([LoadTable Payroll Clear].[AGENCY_NBR]) = "B04"));

Yet I get the runtime error 3075, Syntax (Missing Operator) when I
try to use it. Can someone with better eyes spot the problem?
Thanks for your help!!!!
Click to expand...

Look at this line from the constructed SQL:
Clear].APPROP_PROJ_GRANT_CDFROM [LoadTable Payroll Clear]WHERE
Click to expand...

You need a space before the keyword "FROM". You also ought to have a
space before the keyword "WHERE", but the SQL parser can probably figure
that out, because of the closing bracket just before it.
 
F

fredg

It could be because I have looked at this too long but I just can't see what
is wrong with the following:

rst31 = "SELECT [LoadTable Payroll Clear].FISCAL_YR, " & _
"[LoadTable Payroll Clear].AGENCY_NBR," & _
"[LoadTable Payroll Clear].FUND_CD," & _
"[LoadTable Payroll Clear].APPROP_UNIT_NBR," & _
"[LoadTable Payroll Clear].[APPROP_ ORGN_ NBR]," & _
"[LoadTable Payroll Clear].AGENCY_DIV_NBR," & _
"[LoadTable Payroll Clear].APPROP_NM," & _
"[LoadTable Payroll Clear].APPROP_SHORT_NM," & _
"[LoadTable Payroll Clear].DEDC_RCPT_CAP_AMT," & _
"[LoadTable Payroll Clear].LEGAL_CITATION_TXT," & _
"[LoadTable Payroll Clear].SECT_SEQ_CD," & _
"[LoadTable Payroll Clear].BDGT_AUTH_OPT_IND," & _
"[LoadTable Payroll Clear].APPROP_TYPE_CD," & _
"[LoadTable Payroll Clear].APPROP_APPROV_AMT," & _
"[LoadTable Payroll Clear].APPROP_PROJ_GRANT_CD" & _
"FROM [LoadTable Payroll Clear]" & _
"WHERE ((([LoadTable Payroll Clear].[AGENCY_NBR]) = " & Chr(34) &
rst10![AGENCY_NBR] & Chr(34) & Chr(41) & Chr(41) & Chr(59)

When I use the Immediate window I find that rst31 is as follows:

SELECT [LoadTable Payroll Clear].FISCAL_YR, [LoadTable Payroll
Clear].AGENCY_NBR,[LoadTable Payroll Clear].FUND_CD,[LoadTable Payroll
Clear].APPROP_UNIT_NBR,[LoadTable Payroll Clear].[APPROP_ ORGN_
NBR],[LoadTable Payroll Clear].AGENCY_DIV_NBR,[LoadTable Payroll
Clear].APPROP_NM,[LoadTable Payroll Clear].APPROP_SHORT_NM,[LoadTable Payroll
Clear].DEDC_RCPT_CAP_AMT,[LoadTable Payroll
Clear].LEGAL_CITATION_TXT,[LoadTable Payroll Clear].SECT_SEQ_CD,[LoadTable
Payroll Clear].BDGT_AUTH_OPT_IND,[LoadTable Payroll
Clear].APPROP_TYPE_CD,[LoadTable Payroll Clear].APPROP_APPROV_AMT,[LoadTable
Payroll Clear].APPROP_PROJ_GRANT_CDFROM [LoadTable Payroll Clear]WHERE
((([LoadTable Payroll Clear].[AGENCY_NBR]) = "B04"));

Yet I get the runtime error 3075, Syntax (Missing Operator) when I try to
use it. Can someone with better eyes spot the problem? Thanks for your
help!!!!
Click to expand...

Try placing the where clause variable in single quotes instead of
double (assuming [Agency_NBR] is Text datatype).

WHERE [LoadTable Payroll Clear].[AGENCY_NBR] = '" & rst10![AGENCY_NBR]
& "';"

which should then return:
WHERE [LoadTable Payroll Clear].[AGENCY_NBR] = 'B04';

Note: The Query SQL editor throws in lots of parenthesis, which often
gets in the way of clarity. You don't need them unless the criteria
grouping requires it (which yours doesn't).
 
G

Guest

Thanks so much. That immediately solved the problem.

Dirk Goldgar said:
In
vtj said:
It could be because I have looked at this too long but I just can't
see what is wrong with the following:

rst31 = "SELECT [LoadTable Payroll Clear].FISCAL_YR, " & _
"[LoadTable Payroll Clear].AGENCY_NBR," & _
"[LoadTable Payroll Clear].FUND_CD," & _
"[LoadTable Payroll Clear].APPROP_UNIT_NBR," & _
"[LoadTable Payroll Clear].[APPROP_ ORGN_ NBR]," & _
"[LoadTable Payroll Clear].AGENCY_DIV_NBR," & _
"[LoadTable Payroll Clear].APPROP_NM," & _
"[LoadTable Payroll Clear].APPROP_SHORT_NM," & _
"[LoadTable Payroll Clear].DEDC_RCPT_CAP_AMT," & _
"[LoadTable Payroll Clear].LEGAL_CITATION_TXT," & _
"[LoadTable Payroll Clear].SECT_SEQ_CD," & _
"[LoadTable Payroll Clear].BDGT_AUTH_OPT_IND," & _
"[LoadTable Payroll Clear].APPROP_TYPE_CD," & _
"[LoadTable Payroll Clear].APPROP_APPROV_AMT," & _
"[LoadTable Payroll Clear].APPROP_PROJ_GRANT_CD" & _
"FROM [LoadTable Payroll Clear]" & _
"WHERE ((([LoadTable Payroll Clear].[AGENCY_NBR]) = " & Chr(34) &
rst10![AGENCY_NBR] & Chr(34) & Chr(41) & Chr(41) & Chr(59)

When I use the Immediate window I find that rst31 is as follows:

SELECT [LoadTable Payroll Clear].FISCAL_YR, [LoadTable Payroll
Clear].AGENCY_NBR,[LoadTable Payroll Clear].FUND_CD,[LoadTable Payroll
Clear].APPROP_UNIT_NBR,[LoadTable Payroll Clear].[APPROP_ ORGN_
NBR],[LoadTable Payroll Clear].AGENCY_DIV_NBR,[LoadTable Payroll
Clear].APPROP_NM,[LoadTable Payroll Clear].APPROP_SHORT_NM,[LoadTable
Payroll Clear].DEDC_RCPT_CAP_AMT,[LoadTable Payroll
Clear].LEGAL_CITATION_TXT,[LoadTable Payroll
Clear].SECT_SEQ_CD,[LoadTable Payroll
Clear].BDGT_AUTH_OPT_IND,[LoadTable Payroll
Clear].APPROP_TYPE_CD,[LoadTable Payroll
Clear].APPROP_APPROV_AMT,[LoadTable Payroll
Clear].APPROP_PROJ_GRANT_CDFROM [LoadTable Payroll Clear]WHERE
((([LoadTable Payroll Clear].[AGENCY_NBR]) = "B04"));

Yet I get the runtime error 3075, Syntax (Missing Operator) when I
try to use it. Can someone with better eyes spot the problem?
Thanks for your help!!!!
Click to expand...

Look at this line from the constructed SQL:
Clear].APPROP_PROJ_GRANT_CDFROM [LoadTable Payroll Clear]WHERE
Click to expand...

You need a space before the keyword "FROM". You also ought to have a
space before the keyword "WHERE", but the SQL parser can probably figure
that out, because of the closing bracket just before it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Top