Solved

Date Format

Posted on 2011-09-08
25
351 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:APD_Toronto
  • 7
  • 6
  • 4
  • +4
25 Comments
 
LVL 75
ID: 36507365
Try this:


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

mx
0
 

Author Comment

by:APD_Toronto
ID: 36507390
mx, i added CDate as you have it, same results/

can i force mm/dd/yyyy somehow  with FormatDateTime?
0
 
LVL 75
ID: 36507406
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) & "#"
0
 

Author Comment

by:APD_Toronto
ID: 36507452
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

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36507466
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 & "#"
0
 

Author Comment

by:APD_Toronto
ID: 36507472
I tried your 3 suggestions on line 3,  no luck
0
 

Author Comment

by:APD_Toronto
ID: 36507489
by the way, last year i found a huge issue with the date time picker, that's why wrote that function.
0
 
LVL 75
ID: 36507496
heading home ... back later ....

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36507699
0
 

Author Comment

by:APD_Toronto
ID: 36507724
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.

0
 
LVL 39

Expert Comment

by:als315
ID: 36508470
Try also:
strSQL = "SELECT * FROM tblDailyCollections WHERE fldDate = Datevalue(" & Me.lblDate.Caption & ")"
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36509453
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36510373
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?

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36510584
Have you tried:

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


0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36510699
"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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36510800
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?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36510879
> 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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36511051
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?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36511274
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
0
 

Author Comment

by:APD_Toronto
ID: 36511869
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
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 36511993
Of course, because you haven't applied any of the suggestions mentioned here.

Adjust the SQL to:

strSQL = "SELECT * FROM tbl WHERE fldDate = DateValue('" & Me.txtDate & "')"

/gustav
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36512888
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36514180
> 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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36514309
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36515987
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



0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Splitting out Data 14 28
Pass Form Control to VBA Function 2 27
DSum between dates 5 15
Filtered index 5 0
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now