If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > Access SQL problems using MS Jet OLEDB with ADO+VB6

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 07-14-04, 05:57
mhall84 mhall84 is offline
Registered User
 
Join Date: Jul 2004
Posts: 3
Exclamation 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
File Type: zip orders.zip (10.3 KB, 89 views)
Reply With Quote
  #2 (permalink)  
Old 07-14-04, 07:42
r123456 r123456 is offline
Registered User
 
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.
__________________
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Last edited by r123456; 07-14-04 at 07:50.
Reply With Quote
  #3 (permalink)  
Old 07-14-04, 07:51
mhall84 mhall84 is offline
Registered User
 
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?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On