SQL code and " "

[rodich]

Hi, guys,
how I can to write condition in SQL statement

I have
DoCmd.RunSQL SELECT * INTO SomeTable FROM Logs WHERE (((Logs.Date) = " & Date & "));
and Access return me message "syntax error"
Why?

 

[Banana]

Probably because you're 1) not delimiting the SQL as a string, and 2) passing in a number and comparing it to a date. You need to delimit it properly.

CurrentDb.Execute "SELECT * INTO SomeTable FROM Logs WHERE Logs.Date = Date();", dbFailOnError

If you want to pass in a different date for instance:

CurrentDb.Execute "SELECT * INTO SomeTable FROM Logs WHERE Logs.Date = #" & MyDate & "#;", dbFailOnError

I use CurrentDb.Execute because one advantage of Execute over DoCmd.RunSQL is that you don't get that annoying messages and you don't have to turn on/off the SetWarnings.

 

[JANR]

Here is a typical where-clause dealing with dates:

WHERE DonationDate > #" & Format(Me.StartDate, "mm\/dd\/yyyy") & "#;"

this is the way to go to be independent of regionalsettings.

JR

 

[MStef]

Look at "DemoRunSQLA2000.mdb" (attachment, zip).
Open form and try.
Look at tables, VBA.

 

[rodich]

Thank you, very much!


Banana, I try this but is not working too.
But, I use JANR's resolution and I have success with Format(Date, "mm\/dd\/yyyy")

 

[k.rowinski]

Hi,

I wanted to ask for help in my first post on this forum but maybe I can leave it for second.

When I access the Access data from Word I replace " with ' in the VBA code, e.g.:

Word VBA:
Set rstSample = dbSample.OpenRecordset( _
"SELECT * FROM tblDossierChapters WHERE [Module]='1.5' ORDER BY [SequenceNumber]")

Access SQL:
SELECT * FROM tblDossierChapters WHERE [Module]="1.5" ORDER BY [SequenceNumber]

Hope it helps!
KR

 

[rodich]

ok, MStef and k.rowinski, thank you, I knew this
I was problem with Date and now I resolved it

Thank you people!