Quotes Problem

[GLT]

Hi,

I am having an issue with string and quotes, can anyone advise why the
following wont work and what I need to do to fix it?

strSQL = "DELETE tbl01_FullCompare.ImpDate FROM tbl01_FullCompare WHERE
(((tbl01_FullCompare.ImpDate) =< " & dateLimit & "));"

Second Question: I often use the Query design grid to build my queries, and
when I get the query working correctly, I copy the SQL into VBA. Becuase I
am copying the SQL into a string (with quotes), I often run into issues
trying to work out how the quotes (or how many quotes to use). Is there any
function in access, say the SQL view where I can get it to automatically
ready the SQL to be used in a VBA string? Probably not but I thought it was
worth a shot...

Cheers,
GLT.

 

[Allen Browne]

Re your Q1:
You must use # as the delimiter around date values, so:
"... WHERE tbl01_FullCompare.ImpDate =< #" & dateLimit & "#;"

That will still fail if the user lives in a region that uses a non-US date
format. To solve that issue also, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Re your 2nd question, this explains how to get the quotes right:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Since I often copy a SQL statement into code, I created a little form to
help me do it:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

Hope that helps.

 

[GLT]

Thanks Allen.

You're a champion and a life saver.

Thanks

 

[GLT]

Hi Allen,

Your converter works great, however I am have an issue tring to reference
dates in the Query Design Grid. In the Grid (this is an update query), in
the 'update to' field I have the following:

# "" & dateStamp & "" #

dateStamp is a varible in my code.

How do I reference the dateStamp varible in the query design grid, so I can
then pop it into your converter? Every time I try and do anything in the
design grid, it says the expression you entered has an invalid date value...

 

[GLT]

Hi All,

Does not seem to matter where I put quotes, or how many I use, all I keep
getting is Syntax errors, if anyone can adivise how to correct this line I
would be greatful, this is driving me nuts:

strSQL1 = "UPDATE tbl01_FullCompare SET tbl01_FullCompare.ImpDate = # """"""
& dateLimit & """"#;"""

Cheers

 

[Allen Browne]

You don't need extra quote marks to delimit dates:

strSQL1 = "UPDATE tbl01_FullCompare SET tbl01_FullCompare.ImpDate = #" &
dateLimit & "#;"
Debug.Print strSQL1