VBA in Access ... sql statement with querydef
I am having a problem where the SQL source is removing /
treating an ampersand (&) as a concating one instead of a
litteral string.
Here is some of the code:
strSQL = strSQL & "((Format(Trim([fldShipMonth]) & '/01/'
& Trim([fldShipYear]),'Short Date')) >= #" & dteStartDate
& "#)"
if I do a msgbox on the strSQL it returns what I am
looking for:
Format(Trim([fldShipMonth]) & '/01/' & Trim
([fldShipYear]),'Short Date')) >= #1/01/01#)
but when I pass it to the query def (myQueryDef.SQL =
strSQL) it changes it to:
Format(Trim([fldShipMonth]) & '/01/' Trim
([fldShipYear]),'Short Date')) >= #1/01/01#)
Notice the lack of the second ampersand.
Has anyone else had this problem or know of a possible
solution. I have tried send the character set equivelents
chr(38) and chr(34) for the quotes and ampersands but it
gives the same results.
Here are my definitions:
This is declare at the form level
Dim dteStartDate As Date
These are declared in a public module
Public myQueryDef As QueryDef
Public strSQL As String
I have also included the Microsoft DAO 3.6 Object Library
