VBA/SQL handling of dates

[Klara]

Hi, I have the following code:

curve = Forms!frm_YldCurve!YCType.Value
yc_date = Forms!frm_YldCurve!YCDate.Value
...
With CurrentDb.OpenRecordset("SELECT AllYldCrvs.YCType, AllYldCrvs.YCDate FROM AllYldCrvs WHERE (((AllYldCrvs.YCType)=""&curve&"&quot AND ((AllYldCrvs.YCDate)=""& yc_date &"&quot)", dbOpenDynaset)

curve is a text variable
yc_date holds a date value in the MM/DD/YYYY format
my code substitutes yc_date as one of the parameters in the SQL query above

This code doesn't work - I get a "type mismatch" error for yc_date. I suspect that I need to add a few more quotation marks or parentheses or pound signs or something else around the variable name, but can't find any references telling me exactly what to do. If anyone knows how I should change the syntax, I would really appreciate any tips you could provide.

 

[GingerR]

it looks like there are too many quotation marks? also if YCType is text, then there needs to be SINGLE quotes around it (embedded in the double-quotes that designated the sql statement) and for the date, try putting # around the date (that designates a date type). not sure if this is perfect so you might have to still goof around with it:

With CurrentDb.OpenRecordset("SELECT AllYldCrvs.YCType, AllYldCrvs.YCDate FROM AllYldCrvs
WHERE AllYldCrvs.YCType='" & curve & "' AND AllYldCrvs.YCDate = #" & yc_date &"#", dbOpenDynaset)

so it ends up:

WHERE AllYldCrvs.YCType = 'XType' AND AllYldCrvs.YCDate = #07/23/2001#

hope this helps.
g