APD Toronto
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?
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?
ASKER
mx, i added CDate as you have it, same results/
can i force mm/dd/yyyy somehow with FormatDateTime?
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) & "#"
strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & Format(Me.lblDate.Caption,
OR
strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & FormatDateTime(Me.lblDate.
OR
strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & FormatDateTime(Me.lblDate.
ASKER
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
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 & "#"
(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 & "#"
ASKER
I tried your 3 suggestions on line 3, no luck
ASKER
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
mx
APD_Toronto,
I made a post:
https://www.experts-exchange.com/questions/27298978/Date-Format.html?cid=1573&anchorAnswerId=36507466#a36507466
I made a post:
https://www.experts-exchange.com/questions/27298978/Date-Format.html?cid=1573&anchorAnswerId=36507466#a36507466
ASKER
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.
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 & ")"
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
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?
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/yy yy") & "#"
strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & Format(me.lblDate.Caption,
"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
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"?
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
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.Ca ption,"dd/ mmm/yyyy") )
where the CDATE function would do the date conversion instead of JET?
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.Ca
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/yy yy")
08-okt-2011
? CDate(Format(Me.lblDate.Ca ption,"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.Capti on)
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
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,
08-okt-2011
? CDate(Format(Me.lblDate.Ca
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.Capti
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
> 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.
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
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
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
> 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
strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = #" & CDate(Me.lblDate.Caption) & "#"
mx