strSQL formatting in Access

Question

I am having some trouble formatting an SQL string in Access, I can never seem to debug these syntax issues with SQL strings. I have this string:

strSQL = "SELECT * FROM FXData WHERE ShortCode=" & Forms!FXVolatility.cboCurve.Value & " AND MaxOfMarkAsOfDate=#" & MaxOfMarkAsofDate & "# ORDER BY MaxOfMarkAsOfDate "

debug.print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)

which prints

SELECT * FROM FXData WHERE ShortCode=USD.XS AND MaxOfMarkAsOfDate=#3/31/2016# ORDER BY MaxOfMarkAsOfDate

However this gives me a "Too Few Parameters, expected 1" error.

All the fields and their associated values that are referenced in strSQL exist in the referenced table. What could the error be?

Also if you've got any resources on how to debug/identify these specific access SQL formatting issues I'd be happy to hear them.

Community · Accepted Answer · 2017-05-23 11:52:30Z

3

In SQL, strings need to be put in single or double quotes. Thus, your output should look like this:

... WHERE ShortCode='USD.XS' ...

Thus, your code becomes:

strSQL = "SELECT * FROM FXData WHERE ShortCode='" & _
         Replace(Forms!FXVolatility.cboCurve.Value, "'", "''") & _
         "' AND MaxOfMarkAsOfDate=#" & MaxOfMarkAsofDate & _
         "# ORDER BY MaxOfMarkAsOfDate "

The Replace ensures that any single quotes occurring within cboCurve.Value are properly escaped.

Note that it is recommended to use parameters instead of string concatenation to "fill" values into an SQL statement. An example for how to do this in MS Access can be found in the answer to this question:

VBA OpenRecordset Producing Too few parameters. Expected 2. Error

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Jun 20, 2016 at 14:40

Heinzi

173k61 gold badges386 silver badges555 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

beeba Over a year ago

thank you that fixed the problem! I appreciate the resource as well

Collectives™ on Stack Overflow

strSQL formatting in Access

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related