[Resolved] MS Access Date/Time Problem

[Syberius]

Hi there,

I am working my way through teaching myself VB an have come quite a long way. Anyway, what I am trying to do is run an ADO SQL query on an access database table that will only return an ID from the SELECT query. However the conditions are that a date field is equal to a specific date and a time field is between 2 times.

BookingDay is a date picked up from a form (and is in the format of dd/mm/yyyy).
BookingTime is also picked up from a form (and is in the format of hh:mm:ss).
BookingEndTime is calculated by adding the designated number of hours (taken from the form) to the BookingTime.

The stuff above is working perfectly well, have run various tests on the values and they are fine.

visual basic code:

Dim CheckQuery As String
Dim CheckRecordset As ADODB.Recordset

CheckQuery = "SELECT BookingID FROM Bookings WHERE (BookingDate=#" & BookingDate & "#) " _
& "AND (BookingTime > #" & BookingTime & "# " _
& "AND BookingTime < #" & BookingEndTime & "#)"

Set CheckRecordset = adoConnection.Execute(CheckQuery)

Now the query is executing without an errors, but it is returning 0 rows, an empty recordset. When I go and run a query in the database using the same information, the records show up fine.

Am I missing something obvious here or am I trying to be a bit too complex for my own good?

Oh, the query originally used a BETWEEN statement but I took it out to test the query and so far have not put it back in.

Any help would be greatly appreciated.

Syberius.

[Ecniv]

debug.print the sql statement before you send it to the rst... then post that here (or fix it if you see something wrong).

I guess that it may be the date field as Sql statements are in us format, and certain date cause probelms.

One fix would be to put this in :

WHERE (BookingDate=#" & format(BookingDate,"dd mmm yyyy") & "#) " _

[Syberius]

Well, I was sending the query to a msgbox before sending it to ADO and the query is returning:

SELECT BookingID FROM Bookings WHERE (BookingDate=#04/02/2005#) AND (BookingTime > #09:00:00# AND BookingTime < #12:00:00#)

(Forgot to post that earlier).

Syberius,

[Ecniv]

Is that the 2nd of April or the 4th of Feb??

The Sql parser (being american) would go for the 2nd of April - but your tables are likely to be formatted to local display which would be 4th of Feb.. so no records are returned...

As to the query builder returning... not sure.. perhaps it picks up the local time for some reason whilst converting to sql statements..

[Syberius]

This is why I love UNIX Timestamps (used to working in PHP and MySQL and timestamps are the way).

The dates are in the form of dd/mm/yyyy and the DB is configured for that but as you say, because the SQL parser is american the thing reads that as mm/dd/yyyy or worse yyyy/mm/dd if it really wants to be ackward.

Do you have any suggestions on how I can get round this problem?

Thanks.

Syberius,

[Syberius]

*Bashes head of desk repeatidly*

Well, I fixed it after tossing your suggestion around lots in my head. The problem was and was not the program. What I had to do was run an update query on each record in the database (I had hand entered them) using VB/SQL and set the date using FormateDateTime(BookingDate, vbShortDate) and the time using FormatDateTime(BookingTime, vbLongTime).

This fixed the problem.

What was happening was the date that was being stored by the BookingTime field was set to 12/30/1899 and when the query ran a check agasint the time field, the dates did not match (even though the date is not displayed as part of the field, it is stored as a full date). So this has now been corrected.

Feel a bit stupid now for not noticing this earlier. Oh well, I guess that is programming for you.

Syberius,