Access SQL problems using MS Jet OLEDB with ADO+VB6

Hi there. I am having trouble with querying a record set by date range using the Between operator. Im using VB 6 with ADO.

Referenced: Microsoft ActiveX Data Objects 2.8 Library

Connection String: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\localdata\orders.mdb;Persist Security Info=False"

SQL Query used: "SELECT * FROM tbl_orders WHERE (InsertDate BETWEEN #12/07/2004# AND #14/07/2004#)"

Code:

QueryString is passed as "(InsertDate BETWEEN #12/07/2004# AND #14/07/2004#)"

Public Function GetOrders(ByRef OrderArray() As OrderDetails, QueryString As String) As Boolean

On Error GoTo errorHandler

'\\ Adds a new customer to the local database.

Dim dbConnection As ADODB.Connection
Dim dbRecordSet As ADODB.Recordset
Dim intLoopIndex As Integer

If Len(Application.LocalDBConnection) < 1 Then

MsgBox "No local database connection parameter provided. Please run the configuration utility. Unable to get orders.", 48, "Warning"

Exit Function

End If

Set dbConnection = New ADODB.Connection
Set dbRecordSet = New ADODB.Recordset

'Connect to the database
dbConnection.ConnectionString = Application.LocalDBConnection
dbConnection.Open

'Build SQL command
With dbRecordSet

.Open "SELECT * FROM tbl_orders WHERE " & QueryString, dbConnection, adOpenStatic, adLockReadOnly

If Not (.RecordCount < 1) Then

ReDim OrderArray(.RecordCount - 1)

intLoopIndex = 0

.MoveFirst

Do

OrderArray(intLoopIndex).Customer = .Fields("Customer").Value
OrderArray(intLoopIndex).CustOrdNo = .Fields("CustOrdNo").Value
OrderArray(intLoopIndex).Description = .Fields("Description").Value
OrderArray(intLoopIndex).User = .Fields("User").Value
OrderArray(intLoopIndex).Total = .Fields("Total").Value
OrderArray(intLoopIndex).InsertDate = .Fields("InsertDate").Value

.MoveNext

intLoopIndex = (intLoopIndex + 1)

Loop Until (.EOF)

GetOrders = True

End If

End With

'Close recordset and connection
If (dbRecordSet.State = adStateOpen) Then

dbRecordSet.Close

End If

If (dbConnection.State = adStateOpen) Then

dbConnection.Close

End If

'Dispose of DB objects
Set dbConnection = Nothing
Set dbRecordSet = Nothing

Exit Function

errorHandler:

'Close recordset and connection
If (dbRecordSet.State = adStateOpen) Then

dbRecordSet.Close

End If

If (dbConnection.State = adStateOpen) Then

dbConnection.Close

End If

'Dispose of DB objects
Set dbConnection = Nothing
Set dbRecordSet = Nothing

ShowError Err, "modFunctions.GetOrders()"

End Function


Problem:

The recordset returns a record that has the field 'InsertDate' equal to 15/07/2004. This is not within my date range specified.

The actual orders.mdb file is attached to this post.

Any help much appreciated!

Regards,
-Matthew Hall.Hi,

The following query will solve your requirement.

SELECT * FROM tbl_orders WHERE (InsertDate BETWEEN #7/12/2004# AND #7/14/2004#)

I believe Access evaluates date values as mm/dd/yyyy by default. If a supplied value is not valid in this format, Access will attempt to convert it. Therefore, the query you supplied would be equal to ... BETWEEN #12/07/2004# AND #7/14/2004#, which would indeed return 7/15/2004.|||Thank you for your response.

Is this not dependent on the systems regional settings?

Is there an option I can set in the actual Access file that will set the date to be in the correct format, or am I forced to use American format dates?