?
Solved

Date Format

Posted on 2011-09-08
25
Medium Priority
?
409 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 40

Expert Comment

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

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
 
LVL 49

Expert Comment

by:Dale Fye
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 49

Expert Comment

by:Dale Fye
ID: 36510584
Have you tried:

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


0
 
LVL 52

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 49

Expert Comment

by:Dale Fye
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 52

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 49

Expert Comment

by:Dale Fye
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 52

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 52

Accepted Solution

by:
Gustav Brock earned 2000 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 52

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 52

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

755 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