sql string syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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)
 
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
 
Hi Tim,

thanks for the tip, allways willing to learn only being doing programming 20
years, and I learn a new thing every day...
 
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...
 
Back
Top