Date format

E

Elaine

Hi guys,
I'm having some problems with date formatting.

Background:
- My system regional settings is set to UK system
- Location is set to United Kingdom
- All my dates in my Access tables are formatted as "dd/mm/yyyy"
- MS Windows XP SP2
- MS Access 2003

I have a table called tblSemester with the following information:
Field names: Semester, semStartDate, semEndDate
Sample data: S10506,01/10/2005,27/01/2006
S20506,28/01/2006,09/06/2006
Summer0506,10/06/2006,30/09/2006
S10607,01/10/2006,29/01/2007
S20607,30/01/07,02/06/2007

I have the following function:
************************
Public Function setSemester() As String
Dim varCriteria As String
varCriteria = "[semStart] <= #" & Format(Date, "dd/mm/yyyy") & "# AND
[semEnd] >= #" & Format(Date, "dd/mm/yyyy") & "#"
varSemester = DLookup("[Semester]", "tblSemester", varCriteria)
setSemester = varSemester
End Function
***************************

And finally, I have a form with one control, txtSemester, where the Default
value is set to "setSemester"

My problem is:
the result of the function is always wrong as it assumes the date is in the
American format.
For example, if today's date is 09/05/2006 (9 May 2006), the function will
return "Summer0506"(as would be expected if today's date were 05/Sept/2006),
instead of "S20506" as it should.
Could you please let me know where else in the system I should set the
location to UK, or any other way to work around this problem.

Let me know if you need other details.

Many many thanks,

Elaine
 
D

Douglas J Steele

Despite the fact that your regional settings may have your Short Date format
set to dd/mm/yyyy, you must use mm/dd/yyyy in queries.

Change your code to use

varCriteria = "[semStart] <= " & Format(Date, "\#mm\/dd\/yyyy\#") & "
AND [semEnd] >= " & Format(Date, "\#mm\/dd\/yyyy\#")

(Actually, dd/mm/yyyy format will work, but only if the day is 13 or higher:
in that case, Access will realize that there is no 13th month and interpret
it correctly. Also, it's not strictly true that you must use mm/dd/yyyy: you
can use an unambiguous format such as yyyy-mm-dd or dd mmm yyyy.)

You might find it interesting to read Allen Browne's "International Dates in
Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html or what I
had in my September 2003 Access Answers column for Pinnacle Publication's
"Smart Access" newsletter. The column and accompanying database can be
downloaded at http://members.rogers.com/douglas.j.steele/SmartAccess.html
 
K

Ken Sheridan

Elaine:

Date literals in Access must be in US or an otherwise internationally
unambiguous format. While dates are stored as a 64 bit floating point number
regardless of the local system date format, they are expressed by default in
the local short date format. Change your function to the following and it
should be fine:

************************
Public Function setSemester() As String

Dim varCriteria As String
varCriteria = "semStart <= #" & Format(Date, "mm/dd/yyyy") & _
"# AND semEnd >= #" & Format(Date, "mm/dd/yyyy") & "#"

setSemester = DLookup("Semester", "tblSemester", varCriteria)

End Function
***************************

As this is a common requirement I have the following function in a standard
module:

Function USDate(varDate)

If Not IsNull(varDate) Then
USDate = "#" & Format(varDate, "mm/dd/yyyy") & "#"
End If

End Function

It can then be called when necessary, e.g. in your case:

varCriteria = "semStart <= " & USDate(Date) " & _
"AND semEnd >= " & USDate(Date)

Ken Sheridan
Stafford, England
 
E

Elaine

Dear Doug,

This is absolutely perfect, it works a treat! Many many thanks!
Also thanks for the links for more info on date issues.

Kind Regards,
Elaine


Douglas J Steele said:
Despite the fact that your regional settings may have your Short Date
format
set to dd/mm/yyyy, you must use mm/dd/yyyy in queries.

Change your code to use

varCriteria = "[semStart] <= " & Format(Date, "\#mm\/dd\/yyyy\#") & "
AND [semEnd] >= " & Format(Date, "\#mm\/dd\/yyyy\#")

(Actually, dd/mm/yyyy format will work, but only if the day is 13 or
higher:
in that case, Access will realize that there is no 13th month and
interpret
it correctly. Also, it's not strictly true that you must use mm/dd/yyyy:
you
can use an unambiguous format such as yyyy-mm-dd or dd mmm yyyy.)

You might find it interesting to read Allen Browne's "International Dates
in
Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html or what I
had in my September 2003 Access Answers column for Pinnacle Publication's
"Smart Access" newsletter. The column and accompanying database can be
downloaded at http://members.rogers.com/douglas.j.steele/SmartAccess.html


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Elaine said:
Hi guys,
I'm having some problems with date formatting.

Background:
- My system regional settings is set to UK system
- Location is set to United Kingdom
- All my dates in my Access tables are formatted as "dd/mm/yyyy"
- MS Windows XP SP2
- MS Access 2003

I have a table called tblSemester with the following information:
Field names: Semester, semStartDate, semEndDate
Sample data: S10506,01/10/2005,27/01/2006
S20506,28/01/2006,09/06/2006
Summer0506,10/06/2006,30/09/2006
S10607,01/10/2006,29/01/2007
S20607,30/01/07,02/06/2007

I have the following function:
************************
Public Function setSemester() As String
Dim varCriteria As String
varCriteria = "[semStart] <= #" & Format(Date, "dd/mm/yyyy") & "# AND
[semEnd] >= #" & Format(Date, "dd/mm/yyyy") & "#"
varSemester = DLookup("[Semester]", "tblSemester", varCriteria)
setSemester = varSemester
End Function
***************************

And finally, I have a form with one control, txtSemester, where the Default
value is set to "setSemester"

My problem is:
the result of the function is always wrong as it assumes the date is in the
American format.
For example, if today's date is 09/05/2006 (9 May 2006), the function
will
return "Summer0506"(as would be expected if today's date were 05/Sept/2006),
instead of "S20506" as it should.
Could you please let me know where else in the system I should set the
location to UK, or any other way to work around this problem.

Let me know if you need other details.

Many many thanks,

Elaine
Click to expand...
Click to expand...
 
E

Elaine

Hi Ken,

many thanks for this. I shall start using your Usdate function from now on
Cheers
Elaine
Ken Sheridan said:
Elaine:

Date literals in Access must be in US or an otherwise internationally
unambiguous format. While dates are stored as a 64 bit floating point
number
regardless of the local system date format, they are expressed by default
in
the local short date format. Change your function to the following and it
should be fine:

************************
Public Function setSemester() As String

Dim varCriteria As String
varCriteria = "semStart <= #" & Format(Date, "mm/dd/yyyy") & _
"# AND semEnd >= #" & Format(Date, "mm/dd/yyyy") & "#"

setSemester = DLookup("Semester", "tblSemester", varCriteria)

End Function
***************************

As this is a common requirement I have the following function in a
standard
module:

Function USDate(varDate)

If Not IsNull(varDate) Then
USDate = "#" & Format(varDate, "mm/dd/yyyy") & "#"
End If

End Function

It can then be called when necessary, e.g. in your case:

varCriteria = "semStart <= " & USDate(Date) " & _
"AND semEnd >= " & USDate(Date)

Ken Sheridan
Stafford, England

Elaine said:
Hi guys,
I'm having some problems with date formatting.

Background:
- My system regional settings is set to UK system
- Location is set to United Kingdom
- All my dates in my Access tables are formatted as "dd/mm/yyyy"
- MS Windows XP SP2
- MS Access 2003

I have a table called tblSemester with the following information:
Field names: Semester, semStartDate, semEndDate
Sample data: S10506,01/10/2005,27/01/2006
S20506,28/01/2006,09/06/2006
Summer0506,10/06/2006,30/09/2006
S10607,01/10/2006,29/01/2007
S20607,30/01/07,02/06/2007

I have the following function:
************************
Public Function setSemester() As String
Dim varCriteria As String
varCriteria = "[semStart] <= #" & Format(Date, "dd/mm/yyyy") & "# AND
[semEnd] >= #" & Format(Date, "dd/mm/yyyy") & "#"
varSemester = DLookup("[Semester]", "tblSemester", varCriteria)
setSemester = varSemester
End Function
***************************

And finally, I have a form with one control, txtSemester, where the
Default
value is set to "setSemester"

My problem is:
the result of the function is always wrong as it assumes the date is in
the
American format.
For example, if today's date is 09/05/2006 (9 May 2006), the function
will
return "Summer0506"(as would be expected if today's date were
05/Sept/2006),
instead of "S20506" as it should.
Could you please let me know where else in the system I should set the
location to UK, or any other way to work around this problem.

Let me know if you need other details.

Many many thanks,

Elaine
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to add day to PWA 2016 date format? 0
textbox, strange behaviour..... 2
Date Formats 4
Date Formatting 1
Date Format 10
Changing Data format 3
Forcing a date entry format of dd/mm/yyyy 1
Date Format when Exporting 5

Top