count() not returning correct result in vba but does in normal Query

[Mr_Si]

Hi all,

I'm getting this return value of 0 in my message box which is supposed to return the number of records matching the date entered by user:

Any ideas on where I should change my code?

Initially, I was trying to use RecordCount, but I couldn't get it to work. I was wondering if I'm getting the "date" field into the query properly.


My code is:

Option Compare Database

Private Sub dtmVisit_Start_Date_LostFocus()
    
'This will warn you if there are too many jobs for a particular date and allow you to continue if you want
    
    Dim rst As dao.Recordset
    Dim BeginningSQL As String
    Dim WhereSQL As String
    Dim FinalSQL As String
    
    If IsNull(Me.dtmVisit_Start_Date) Then
        MsgBox "No Date Entered.", vbInformation
        Exit Sub
    
    Else
                
        'the following SQL is copied direct from the query builder in access
        BeginningSQL = "SELECT Count(*) As NumRecords FROM tblVisit WHERE "
               
        'where the date field is equal to the one entered by the user AND where the enquiry is a live project
        WhereSQL = "tblVisit.dtmVisit_Start_Date" & " = #" & Me.dtmVisit_Start_Date & "#;"
        FinalSQL = BeginningSQL & WhereSQL
        MsgBox FinalSQL
        
              
        Set rst = CurrentDb.OpenRecordset(FinalSQL)
            
            'If (rst.RecordCount > 2) Then
                If vbYes = MsgBox("You currently have " & (rst("NumRecords")) & " entries for this (" _
                    & Me.dtmVisit_Start_Date & ") date, are you sure you wish to continue?", vbYesNo) Then
                                
                    'MsgBox "Please continue as you were!", vbOKOnly
                    Me.dtmVisitTime.SetFocus
                
                Else
                    MsgBox "Please choose another date", vbOKOnly
                    Me.dtmVisit_Start_Date = Date
                    Me.chrVisit_Type.SetFocus
                
                End If
                
            'Else
                'MsgBox "This should only come up if less than or equal to 2", vbOKOnly
                'Me.dtmVisitTime.SetFocus
                
            'End If
            
        'End With
        
        
        
    End If
    
End Sub

 

[gemma-the-husky]

try this

WhereSQL = "tblVisit.dtmVisit_Start_Date" & " = #" & format(Me.dtmVisit_Start_Date,"long date") & "#;"


SQL seems to go to US dates as standard, so if you are testing for a UK format date 10/9/08, you actually search for the US date 9/10/08

using long date forces it to be treated correctly

 

[Mr_Si]

Gemma, that fixed it! thank you very much. I spent the whole working day yesterday to try and sort it, then tried some more via remote desktop last night until 1am this morning.

Grrrr for SQL.

Many thanks

 

[gemma-the-husky]

its very hard to pick up, until you have experienced it - then its always at the back of your mind.

 

[Mr_Si]

Yep, I'll sure never forget now!

 

[namliam]

WhereSQL = "tblVisit.dtmVisit_Start_Date" & " = #" & Me.dtmVisit_Start_Date & "#;"

This is an implicit conversion, implicit conversions are BAD!

In sql access requires dates to be in US format, so your 10/09/2008 (DD/MM/YYYY) beeing 10 sept 2008 to access is beeing translated into 9 oct 2008 (MM/DD/YYYY).

Be carefull with long date format tho!!!
Long date is dependant upon your regional settings...
i.e. Changing my regional settings to dutch, it will return
donderdag 11 september 2008
Instead of...
Thursday, September 11, 2008

Access SQL (not 100% sure) will probably fail on the dutch version...
if you want to have something that will work allways guaranteed 100%, use:
"Dateserial ( " & format(Me.dtmVisit_Start_Date,"yyyy,mm,dd") & ")"
this is guaranteed because you allow for access to do the compiling which should allways go right even if they deside to go with a different format.

Assuming that M$ is not going to change their date formatting ...
" #" & format(Me.dtmVisit_Start_Date,"MM/DD/YYYY") & "#"
Will work always... untill they do!

 

[gemma-the-husky]

Thanks for the update, namliam

That makes sense actually - i had a client where the regional settings were changed to Caribbean, and that caused the "long date" format I used to not work.

 

[namliam]

I have been burned by this darned date to so many times.... I still have the wounds to prove it.

Dates are a nightmare in access if you are not 100% proof on your coding. More so since it seems to work i.e. dont give an error. But it returns wrong information, or atleast different than what the user expects / should expect.

I once had a customer who had a problem like this, but didnt know. They had a "problem" somewhere because they could not make the bookkeeping match up. It turned out their reports from access were running on a dutch regional settings causing the "Short date" to be "DD/MM/YYYY".
Luckely I didnt make this database , and it did run perfect for a while (while the regional setting were US) so they just trusted the figures... untill a new user started using the computer and changed the regional settings.
Once I found this .... the problem was easily fixed by replacing "Short date" by "MM/DD/YYYY", a word to the wize Gemma and Mr_Si, be carefull with dates and dependant and/or implicit conversions in dates or actually anywhere.