Database writing

[joolzz]

Hi,

I am having problems with my vb code finding records in an access 2000 database by date.

does the date format have to be the same in the data base as the one i a using in my code ie

my sql is :-
"SELECT * From LaborDB WHERE Date = #" & strDate & "#"
the date format in strDate is 2/12/2002

in the database the date is 02/12/2002 but the above sql returns that it cannot find it.

I am using a adodb.connection

Thanks for any help

Joolz

[PsudoLogical]

Try changing the data type of your date variable to date... as opposed to string.

[joolzz]

Hi,

I tried changing them but I still can not get a match, If I search using other fields it works such as integers so that means it must be the date. any thoughts as this is driving me mad!!!!


Thanks

Joolz

[swoozie]

SELECT * From LaborDB WHERE Date = #" & strDate & "#"
the date format in strDate is 2/12/2002

change it to


SELECT * From LaborDB WHERE Date = "& chr(39) & strDate & Chr(39) & ";"

the date format in strDate is 2/12/2002

When pasing a SQL statement from code, I have found that using the # sign doesnt work.

[joolzz]

Hi,

I have found a daft solution, my computers date is set to
day/month/year and appears this way in access

If i search using #day/month/year# if does not find anything


but

If i search using #month/day/year# it works, i smell the americans buggering me about.

Can anyone tell me why this is, a bag of chips to the winner

Thanks

Joolz

[PilgrimPete]

It's just the way Access is designed. Each database is different (to keep you on your toes!).

One way I've found to overcome the problem is to do this:

"SELECT * From LaborDB WHERE Date = #" & format(strDate, "dd mmm yyyy") & "#"

Not elegant, but it seems to do the trick..!

[joolzz]

Thanks

Your the daddy,


Joolz

[johny p]

another way :


"SELECT * From LaborDB WHERE Date = #" & cdate( 2/12/2002 )& "#"


johan

[SkiNLaB]

format(dateVar, "dd-mmm-yyyy") is my best friend