Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Exclamation Unanswered: 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#)"


    Code:
    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.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Last edited by r123456; 07-14-04 at 08:50.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jul 2004
    Posts
    3
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •