Selecting by Date in VB6/Access

[monoman]

Hi,

I have a visual basic 6 program that reads an access table. I need to select records by date. I thought this would be simple, but it seems not.

I'm using DAO, erm...i've no idea what that means so i'll leave that there so i don't embarrass myself further.

Here's my vb code to read the table -

Qry = "SELECT * FROM OrderHeader WHERE [UserEntered] = " & Staff & _
            " AND FORMAT(DateEntered,'MM/DD/YY') = #" & Format(WorkDate, "MM/DD/YY") & "# ORDER BY TimeEntered"

The format of the date in the access table is dd.mm.yyyy. I change the format in the code above because, as far as I can tell, it needs to be in mm/dd/yy format to work with SQL.

(1) Is this right? Does it really have to be in that format?
(2) It doesn't work anyway, no records are found.

Any help would be much appreciated. Cheers.

 

[a_unique_person]

Dates are a dark art in access. They always give me the **** because by the time I have to use them again, I've forgotten what I did last time, and it varies from database to database.

 

[69dodge]

Maybe something like this?

Qry = "SELECT * FROM OrderHeader WHERE [UserEntered] = " & Staff & " AND [DateEntered] = #" & Format(WorkDate, "yyyy-MM-dd") & "# ORDER BY TimeEntered"

(I assume DateEntered is a database field and WorkDate is a VB variable.)

 

[monoman]

Maybe something like this?

Qry = "SELECT * FROM OrderHeader WHERE [UserEntered] = " & Staff & " AND [DateEntered] = #" & Format(WorkDate, "yyyy-MM-dd") & "# ORDER BY TimeEntered"

(I assume DateEntered is a database field and WorkDate is a VB variable.)

Eh! That worked! Cheers mate but i'd like to know why.

You assumed right that DateEntered is a database field and WorkDate is a VB variable (date type).

The thing is, DateEntered is in the format dd.mm.yyyy so i'd previously tried this :-

Qry = "SELECT * FROM OrderHeader WHERE [UserEntered] = " & Staff & " AND [DateEntered] = #" & Format(WorkDate, "dd.mm.yyyy") & "# ORDER BY TimeEntered"

This comes back with "Syntax error in date in query expression....."

I then tried (for a laugh):-

Qry = "SELECT * FROM OrderHeader WHERE [UserEntered] = " & Staff & " AND [DateEntered] = #" & Format(WorkDate, "dd-mm-yyyy") & "# ORDER BY TimeEntered"

Which didn't produce an error, but picked up nothing.

Why on earth does your format work (AUP, pay attention)?

ps: My system date setting is dd.mm.yyyy

 

[JonnyFive]

Out of curiosity, does it work if you omit the Format command on Workdate?

 

[69dodge]

Eh! That worked! Cheers mate but i'd like to know why.

I think this is the basic idea:

The "dd.mm.yyyy" format for DateEntered is only for display purposes. DateEntered is of type DATE, so it's not stored in the database as a string in one format or another. Think of it as just being a date.

So a SQL query can contain things like

[DateEntered] = #some particular date in a format that Access understands in SQL queries#​

Access understands the ISO 8601 format for dates (yyyy-mm-dd), and it's unambiguous, so it's a good choice. I think the US format (mm/dd/yy or mm/dd/yyyy) would also work.

 

[monoman]

Out of curiosity, does it work if you omit the Format command on Workdate?

Hi,

No, I get the "syntax error in date in query..." message

 

[monoman]

I think this is the basic idea:

The "dd.mm.yyyy" format for DateEntered is only for display purposes. DateEntered is of type DATE, so it's not stored in the database as a string in one format or another. Think of it as just being a date.

So a SQL query can contain things like

[DateEntered] = #some particular date in a format that Access understands in SQL queries#​

Access understands the ISO 8601 format for dates (yyyy-mm-dd), and it's unambiguous, so it's a good choice. I think the US format (mm/dd/yy or mm/dd/yyyy) would also work.

Yes, i'd read that the US format (mm/dd/yy) would work. This didn't produce the syntax error but it also didn't pick up any records. Maybe because i'm in Bulgaria

Anyway, i'm just happy that it works now and I can refer to this for any other date selections.

 

[JonnyFive]

Hi,

No, I get the "syntax error in date in query..." message

Ah, I was just wondering if VB 6 was smart enough to pass the properly parsed date through the date type if you didn't convert it manually. Guess not.

 

[monoman]

Ah, I was just wondering if VB 6 was smart enough to pass the properly parsed date through the date type if you didn't convert it manually. Guess not.

Naaah, I assume that'd take a serious amount of programming that even the Genius's at Microsoft would struggle to tackle!

 

[JonnyFive]

Naaah, I assume that'd take a serious amount of programming that even the Genius's at Microsoft would struggle to tackle!

Totally!

Just like it would take soooo much work for them to not have used goddamn banker's rounding (round down on a five - e.g. 4.5 = 4 and not 5) in VBA and possibly VB 6.

I had to write custom rounding code for the underwriting software I developed for my company using an Access DB with VBA code on top. It works, but why banker's rounding, of all things?