sql string syntax

A

Abrm

Hello,
I am to use an sql string to create a recordset.
If I use this one I get all teh right articles
strsql = ""
strsql = "SELECT * " _
& "FROM table " _
& "WHERE table.Article =" & Article

But if I use this one, it doesn't work
strsql = ""
strsql = "SELECT * " _
& "FROM table " _
& "WHERE table.date =" & Vdate

I made the following chagnes.
dim vdate as date. doesn't make a difference
in stead of = It shows all records in the table
Click to expand...
< in stead of = no records
Can you someone tell me why it works with the article number but not with a
date?
 
A

Alex White MCDBA MCSE

with dates in access they have to have # either side of the value e.g. where
table.date = #" & vdate & "#"

but a couple of things to note, in your example your date field is called
date this is a reserved word, so in your case you should use the
table.[date] the next problem that I suffer with is dates switching between
english and US, so to get the right result for me every time

where day(table.[date])= day(vdate) and month(table.[date])=month(vdate) and
year(table.[date])=year(vdate) otherwise I have to check the workstations to
make sure they are on the right date format.

Hope that answers your question. (the above applies to querying an access
database)
 
T

Tim Ferguson

with dates in access they have to have # either side of the value e.g.
where table.date = #" & vdate & "#"
Click to expand...

Shame on you Alex, especially with a .co.uk address..!

You really do have to force the date into USA or ISO format, because a
d/m/y ordering will create problems. Jet is not sensitive to locality
settings.

"WHERE MyTable.MyDate = " & Format(MyDateValue, "\#yyyy\-mm\-dd\#")

will do what you want.

All the best


Tim F
 
A

Alex White MCDBA MCSE

Hi Tim,

thanks for the tip, allways willing to learn only being doing programming 20
years, and I learn a new thing every day...
 
A

Abrm

I will try the format of the date.
Thanks for the answer.
B.

Alex White MCDBA MCSE said:
with dates in access they have to have # either side of the value e.g. where
table.date = #" & vdate & "#"

but a couple of things to note, in your example your date field is called
date this is a reserved word, so in your case you should use the
table.[date] the next problem that I suffer with is dates switching between
english and US, so to get the right result for me every time

where day(table.[date])= day(vdate) and month(table.[date])=month(vdate) and
year(table.[date])=year(vdate) otherwise I have to check the workstations to
make sure they are on the right date format.

Hope that answers your question. (the above applies to querying an access
database)

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Abrm said:
Hello,
I am to use an sql string to create a recordset.
If I use this one I get all teh right articles
strsql = ""
strsql = "SELECT * " _
& "FROM table " _
& "WHERE table.Article =" & Article

But if I use this one, it doesn't work
strsql = ""
strsql = "SELECT * " _
& "FROM table " _
& "WHERE table.date =" & Vdate

I made the following chagnes.
dim vdate as date. doesn't make a difference
< in stead of = no records
Can you someone tell me why it works with the article number but not with
a
date?
Click to expand...
Click to expand...
 
Top