VBA where clause with a date between two dates

[InspectorHound]

Hi,

I am building an SQL query string and I need to add a part that restricts field value to be between a start and an end date. Is this right:
If Not (IsNull(Me.txtStart) Or IsNull(Me.txtEnd)) Then
strSQL = strSQL & " AND [Authorized Travel Begin Date]>=" & Format (Me.txtStart, "dd/mm/yyyy") & _
"[Authorized Travel Begin Date]<=" & Format(Me.txtEnd, "dd/mm/yyyy")

End If

 

[SkipVought]

Hi,

Hi,

You were missing an AND, and your Format()s, returning ambiguous strings needed to be converted to DATEs, hence the unambiguous string and the conversion ## delimiters.

strSQL = strSQL & " AND [Authorized Travel Begin Date]>=#" & Format (Me.txtStart, "yyyy/mm/dd") & _
"# AND [Authorized Travel Begin Date]<=#" & Format(Me.txtEnd, "yyyy/mm/dd") &"#"

 

[Andrzejek]

You may also try this:

strSQL = strSQL & " AND [Authorized Travel Begin Date] BEETWEN #" & Format (Me.txtStart, "yyyy/mm/dd") & _
"# AND #" & Format(Me.txtEnd, "yyyy/mm/dd") & "#"  

Debug.Print strSQL

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

I prefer Andy's method with a small correction to the spelling of BEETWEN

strSQL = strSQL & " AND [Authorized Travel Begin Date] BETWEEN #" & Format (Me.txtStart, "yyyy/mm/dd") & _
"# AND #" & Format(Me.txtEnd, "yyyy/mm/dd") & "#"  

Debug.Print strSQL

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Hi,

Yes, this works -- thanks. But no data is returned on my form. What could be the problem -- do I have to format the [Authorized Travel Begin Date] variable?

 

[Andrzejek]

Is your field [tt]Authorized Travel Begin Date[/tt] declared as Date in your data base? Or is it a text / string?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[InspectorHound]

It is data type "Short Text" in the database table[highlight #8AE234][/highlight]

 

[dhookom]

Why would you store a date in a text field? You need to convert it to a date using a function like CDate() to make it into a date so it has some value.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

When you have this ‘Short Text’ field and any Date field, the data in them may look the same to you, but it is treated totally different. So when Duane says: “convert it to a date” he means ‘convert your Short Text data field to a Date type in your data base and then convert your data to a Date’

You wouldn’t keep LastName or FirstName data in the field defined as Number, right?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[InspectorHound]

Of course, I understand that. I think my problem is a space issue. I don't think it is doing the conversion. I will keep playing around with it.

 

[Andrzejek]

Space issue? Dates are just simple numbers, text takes a lot more space. Computers are a lot better with numbers than with text.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[InspectorHound]

When I tried to convert it, I got such a message. I started from the beginning, re-imported the table, this time setting the field type, and now it works, using the same code as before. It never worked when I was trying to convert the date at any other point.

Thanks for your help.

 

[Andrzejek]

setting the field type" to Date?
"now it works, using the same code as before" which is....?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[InspectorHound]

Initially, I was trying to use CDate. I first used it in the select part of the query that was the recordsource for the form. When I later used that variable to test the date range, nothing would happen.
The only way I for the code to work was when I re-imported this very huge table and set the field type to "Date/Time" when I did the import.

 

[SkipVought]

If your "date" strings are DMY, I seriously doubt that CDate() will work!