Can anybody please tell me what's wrong...

[Porsche996GT2]

..with my SQL statement? I'm doing this in VBA and am having problems with the punctuations:


SQL = "SELECT qry2006RenewalsFlagged.PID, qry2006RenewalsFlagged.CID, qry2006RenewalsFlagged.SetID, qry2006RenewalsFlagged.[Group Name], Format(Format([anniversarymonth],""00"") & ""/01/"" & [contractyear],""mm/dd/yyyy"") AS [Renewal Date], Null AS AM, qry2006RenewalsFlagged.underwriter, qry2006RenewalsFlagged.[rate version], qry2006RenewalsFlagged.[ARK quote ID], qry2006RenewalsFlagged.[total members], qry2006RenewalsFlagged.[Target PMPM], qry2006RenewalsFlagged.[Final PMPM], ([Final PMPM]/[Target PMPM])-1 AS [Percent Change], IIf([LoadVariance]=0 And [CreditVariance]=0,0,IIf([LoadVariance]=0 And [CreditVariance]<>0,[CreditVariance],IIf([LoadVariance]<>0 And [CreditVariance]=0,[LoadVariance]))) AS Variance"
SQL = SQL & "FROM qry2006RenewalsFlagged"
SQL = SQL & "WHERE (((qry2006RenewalsFlagged.FDRflag)=" & FDR & ") AND ((qry2006RenewalsFlagged.anniversarymonth) Between [Forms].[frmExcelReports].[cboBegMonth] And [forms].[frmExcelReports].[cboEndMonth]) AND ((qry2006RenewalsFlagged.contractyear) Between [Forms].[frmExcelReports].[cboBegYear] And [forms].[frmExcelReports].[cboEndYear]))"
SQL = SQL & "ORDER BY qry2006RenewalsFlagged.PID, qry2006RenewalsFlagged.CID, qry2006RenewalsFlagged.SetID;"


I keep getting a Run Time Error 3141, saying that there is a reserved word or an argument name that is misspelled or missing, or that the punctuation is incorrect. Any help would be appreciated. Thanks!

 

[ZmrAbdulla]

SQL is a reserved word in Access see more here
List of reserved words in Access 2002 and Access 2003
List of reserved words in Access 2000
Reserved words in Microsoft Access 97

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[KenReay]

Hi

Is SQL a reserved word using:

Dim strSQL as String
strSQL = "SELECT ...blah"

would be better...

Also I think you are missing spaces to seperate your keywords from the previous/next bit of the statement:

SQL = "SELECT qry2006RenewalsFlagged.PID, qry2006RenewalsFlagged.CID, qry2006RenewalsFlagged.SetID, qry2006RenewalsFlagged.[Group Name], Format(Format([anniversarymonth],""00"") & ""/01/"" & [contractyear],""mm/dd/yyyy"") AS [Renewal Date], Null AS AM, qry2006RenewalsFlagged.underwriter, qry2006RenewalsFlagged.[rate version], qry2006RenewalsFlagged.[ARK quote ID], qry2006RenewalsFlagged.[total members], qry2006RenewalsFlagged.[Target PMPM], qry2006RenewalsFlagged.[Final PMPM], ([Final PMPM]/[Target PMPM])-1 AS [Percent Change], IIf([LoadVariance]=0 And [CreditVariance]=0,0,IIf([LoadVariance]=0 And [CreditVariance]<>0,[CreditVariance],IIf([LoadVariance]<>0 And [CreditVariance]=0,[LoadVariance]))) AS Variance"
SQL = SQL & " FROM qry2006RenewalsFlagged"
SQL = SQL & " WHERE (((qry2006RenewalsFlagged.FDRflag)=" & FDR & ") AND ((qry2006RenewalsFlagged.anniversarymonth) Between [Forms].[frmExcelReports].[cboBegMonth] And [forms].[frmExcelReports].[cboEndMonth]) AND ((qry2006RenewalsFlagged.contractyear) Between [Forms].[frmExcelReports].[cboBegYear] And [forms].[frmExcelReports].[cboEndYear]))"
SQL = SQL & " ORDER BY qry2006RenewalsFlagged.PID, qry2006RenewalsFlagged.CID, qry2006RenewalsFlagged.SetID;"

A more general point, for future reference, the easiest way to debug this tupe of problem is to put a breakpoint on the line following the strSQL = statement, run the code to the breakpoint, in the immediate window type ? strSQL

copy and paste the output to the SQL view of the query designer and try to run it, the query designer will give you more user freindly error messages and will often highlight the point at which the error occurs.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Porsche996GT2]

Hello Ken,

Thanks for your reply. I pretty much just copied the SQL statement from the SQL view of my query, thus the continues line before the FROM statement. I think this is the line that's causing the error:

Format(Format([anniversarymonth],""00"") & ""/01/"" & [contractyear],""mm/dd/yyyy"") AS [Renewal Date]

Any other suggestions...

Thanks,

Porsche

 

[KenReay]

Hi

You mean in addition to the fact that SQL is a reserved word, and you have missing spaces?

did you try the trick of pasting it back into the SQL view of the query builder?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Porsche996GT2]

Hello Ken,

Thanks for your help. I was able to make it work by creating a nother query based on that query. It's probably inefficient but it works now!

Thanks,

Porsche