Link to home
Start Free TrialLog in
Avatar of APD Toronto
APD TorontoFlag for Canada

asked on

Date Format

Hello Experts,

I have a really wierd scenario...

I'm using the fowwing query.

strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & Me.lblDate.Caption & "#"

When my system date is formatted dd/MM/yyyy, my recordcount returns 0 (incorrect), but if the date format is MM/dd/yyyy. reccount=1 (correct)

how can i fix this?
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Try this:


strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & CDate(Me.lblDate.Caption) & "#"

mx
Avatar of APD Toronto

ASKER

mx, i added CDate as you have it, same results/

can i force mm/dd/yyyy somehow  with FormatDateTime?
maybe this:

strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & Format(Me.lblDate.Caption, "mm/dd/yyyy") & "#"

OR

strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & FormatDateTime(Me.lblDate.Caption, 2) & "#"

OR

strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & FormatDateTime(Me.lblDate.Caption, 3) & "#"
i will try, but FYI, i'm using MS Date Time Picker and the code is
Private Sub dtpDate_Change()
   lblMSG.Caption = "Comments previously entered for " + dtpToDate(dtpDate)
   lblDate.Caption = Format$(dtpToDate(dtpDate), "mm/dd/yyyy")
   
   Dim rs As Recordset
   Dim strSQL As String
   
   strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & CDate(Me.lblDate.Caption) & "#"
   
   Set rs = CurrentDb.OpenRecordset(strSQL)
   
   If Not rs.EOF Then
    rs.MoveFirst
    Me.txtNiagara = rs!fldCSSNcomments
    Me.txtToronto = rs!fldCSSNcomments
   Else
    rs.AddNew
    rs!fldDate = Me.lblDate.Caption
    rs.Update
   End If
   rs.Close
End Sub

Public Function dtpToDate(strDate As String) As String

    Dim intMO, intDAY, intYR As Integer
    
    intMO = CInt(Left(strDate, InStr(1, strDate, "/") - 1))
    strDate = (Mid(strDate, InStr(1, strDate, "/") + 1, Len(strDate) - InStr(1, strDate, "/") + 1))
    
    intDAY = CInt(Left(strDate, InStr(1, strDate, "/") - 1))
    strDate = (Mid(strDate, InStr(1, strDate, "/") + 1, Len(strDate) - InStr(1, strDate, "/") + 1))
    
    intYR = CInt(Left(strDate, 4))
    
    dtpToDate = intMO & "/" & intDAY & "/" & intYR
    
End Function

Open in new window

I don't know if you can grab a label's "caption" (which is text) and use it like that..
(besides, ...what if the date is invalid?, remember a label's  Caption cannot validate dates like a textbox can...)

Why not put this "date" into a textbox where this should be easy:

strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate=" & "#" & Me.txtDate & "#"
I tried your 3 suggestions on line 3,  no luck
by the way, last year i found a huge issue with the date time picker, that's why wrote that function.
heading home ... back later ....

mx
sorry, based on your post, i tried doing a text box, but same thing.

Assuming that no validationb needed, could you please whip a sample and test it in both formats?

Perhaps, 1 singlke field table with a short date, a form with a text box and a button with msgbox "found = " rs.RecordCount.

Try also:
strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = Datevalue(" & Me.lblDate.Caption & ")"
DateValue converts a string expression for a date as to your local settings. Thus:

strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = Datevalue('" & Me.lblDate.Caption & "')"

/gustav
It sounds like your regional settings have the date formatted as "dd/mm/yyyy", is that correct?

What is the format of the date in the caption?

Have you tried:

strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & Format(me.lblDate.Caption,"dd/mmm/yyyy") & "#"


"dd/mmm/yyyy"

That format should _never_ be used in VB(A) to create SQL code as the result created by "mmm" is localized.

/gustav
Gustav,

Can you explain what you mean by "localized"?

ADP_Toronto, You posted both to Access Database and SQL Query Syntax.  Are you actually using SQL server, or just Access?
> Can you explain what you mean by "localized"?

Yes, it means that, for example, Mar here will be mar (OK) but Oct will be okt (fail).
With a French locale it will fail for nearly all months.

/gustav
Gustav,

All my work is done in the U.S. but I have seen this syntax in many other multi-national posts

Would the same be the case for?

strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = " & CDate(Format(me.lblDate.Caption,"dd/mmm/yyyy"))

where the CDATE function would do the date conversion instead of JET?
You can easily check it out by writing in SQL view a hard coded string with, say, okt for October.

You could rewrite it using CDate as you mention because that would be read in VBA which understands and respects the regional settings.

Thus, here, with a caption of 8-10-2011:

? Format(Me.lblDate.Caption,"dd/mmm/yyyy")
08-okt-2011

? CDate(Format(Me.lblDate.Caption,"dd/mmm/yyyy"))
08-10-2011

However, now Format is moot because CDate can convert the caption as is:

? CDate(Me.lblDate.Caption)
08-10-2011

and so does DateValue which guarantees a date-only value:

? DateValue(Me.lblDate.Caption)
08-10-2011

which is why I suggested that method. And when DateValue is placed within the SQL code, it returns a value of data type DateTime which needs (must not) no conversion and formatting to text.

/gustav
ok, I just created the attached sample, and to replicate this issue:

1- Go to Form1
2- Click on the calendar icon and select Sep 8
3- Click the command button.

You'll see that when you change your regional settings to dd/MM/yyyy you get rec count =0, but with MM/dd/yyyy count = 1.

Any assistance is much appreciated New-Microsoft-Office-Access-2007.accdb
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have learned that any date surrounded by hashes may very well be interpreted as US format
Format changes how you see a date, not how it is stored or used
/gustav is the acknowledge master of dates--which can be very buggery things.

The only sure way to get dates doing what you expect them to is to build an ISO 8601 compliant string --->YYYY-MM-DD ---> 2011-09-09

If your dates are a sane format for your regional settings, you may be able to skip the ISO 8601 compliancy
Lets say your label has a caption of "9-Sep-11"

Dim myDate as Date
MyDate = CDate(Me.lblDate.Caption)
strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = " & MyDate

Should work correctly
If it decides it needs the hashes then only bulletproof way to get what you want is

Dim myDate as Date
Dim MyDateString as String
MyDate = CDate(Me.lblDate.Caption)
MyDateString = Year(myDate) & "-" & Month(myDate) & "-" & Day(myDate)
strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & MyDateString & "#"

Only an ISO 8601-compliant string is immune from being misinterpreted
The Date functions (CDate, DateVale ect) in Access use the regional settings.
SQL Server only accepts string literals as US format.

Dates.
Buggery things.
> strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = " & MyDate
> Should work correctly

Only in a US environment, because VB(A) will cast MyDate to a string expression as to the regional settings. To place safe, force the ISO format - and don't forget the hashes for Access SQL:

strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & Format(MyDate, "yyyy-mm-dd") & "#"

> SQL Server only accepts string literals as US format.
That was the old versions. The newer understand the ISO format very well, and for SQL Server 2008 it is even the default format.

/gustav
The hashes can play merry hell with things though.
Today being 09/09/2011 is a bad day to test things but this code (when the date is advance to tommorow) is not consistent.
I have short date as dd/mm/yyyy.
 
Function SeeTheDate()
Dim MyDate As Date
Dim MyDateString As String
Dim rs As Recordset
MyDate = CDate(Now())
MyDateString = Year(MyDate) & "-" & Month(MyDate) & "-" & Day(MyDate)

MsgBox MyDateString '<----This is good

MsgBox MyDate '<----This is good

Set rs = CurrentDb.OpenRecordset("select  #" & MyDate & "# as myNow from tblInsdetails where jobID = 1", dbOpenDynaset, dbSeeChanges)
MsgBox rs!myNow '<----This is baaaaaaaaaaaaaaad

rs.Close
Set rs = CurrentDb.OpenRecordset("select  #" & Format(MyDate, "yyyy-mm-dd") & "# as myNow from tblInsdetails where jobID = 1", dbOpenDynaset, dbSeeChanges)
MsgBox rs!myNow '<---This is good

rs.Close
Set rs = CurrentDb.OpenRecordset("select  #" & MyDateString & "# as myNow from tblInsdetails where jobID = 1", dbOpenDynaset, dbSeeChanges)
MsgBox rs!myNow '<---This is good

End Function

Open in new window

The moral of the story is you had better use an Access function to manipulate your date between your hash marks--or something unexpected may occur.

Play with the date on your computer.  You'll find that the baaaaaaaaaaaaaaad line only gets it wrong the first 12 days of the month .
So WHEN you test your date code matters

In the English speaking chunk of N. America, medium date formats of dd-MMM-yyyy work well, and don't get fouled up.
If for any reason you don't use that, ISO 8601 formats are the only way to go
You are making this more complicated than necessary ...

> The hashes can play merry hell with things though.
No, they just differentiate between text and date/time, and they must be used in Access SQL to indicate that a string is a date expression (which is not the same as a date value; note the difference)

In standard SQL however, as used by SQL Server, single quotes are used in place of hashes.

> Dim MyDate As Date
> MyDate = CDate(Now())

This should read:
Dim MyDate As Date
MyDate = Now

or rather, as you don't use the time part:
MyDate = Date

> The moral of the story is you had better use an Access function to manipulate
> your date between your hash marks--or something unexpected may occur.

It is true that the construct #" & MyDate & "# should be avoided at all times as it will only work in default US environments. Just use Format(MyDate, "yyyy-mm-dd") and you are safe.

/gustav