Date Formats in VBA

[kt1978]

Hi all

I have been having a problem trying to enter a date in a table using vba. I have fixed it now, but can anyone shed any light as to why my two examples use the date format differently but both work???

I know that vba uses the american date format but surely, it should remain the same no matter what type of procedure you are running.

Please see my examples.

Using the db.Execute method I have had to specify the date format as "mm/dd/yyyy" and also use the #

Sub dateEntry1()

  Dim db As DAO.Database
  Set db = CurrentDb
  
  ' date is entered in the format dd/mm/yyyy
  myDate = InputBox("Please enter date", "Date required")
  
  db.Execute "Update tblDate Set runDate = #" & Format(myDate, "mm/dd/yyyy") & "#", dbFailOnError
  
End Sub

Using a recordset I just have to use the expected format of "dd/mm/yyyy" and no #

Sub dateEntry2()

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("tblDate")
    
  ' date is entered in the format dd/mm/yyyy
  myDate = InputBox("Please enter date", "Date required")
  
  rs.Edit
  rs(0) = Format(myDate, "dd/mm/yyyy")
  rs.Update
  
  rs.Close
  db.Close

End Sub

I thought this may be different for just recordsets, so I tested using a SELECT statement. I had to use the "mm/dd/yyyy" format and #... Confused!

Sub DateFind()

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  
  Set db = CurrentDb
  
  ' date is entered in the format dd/mm/yyyy
  myDate = InputBox("Please enter date", "Date required")
  
  Set rs = _
  db.OpenRecordset("SELECT * FROM tblDate WHERE runDate = #" & Format(myDate, "mm/dd/yyyy") & "#")
   
  MsgBox rs.RecordCount
      
  rs.Close
  db.Close
  
End Sub

Why does it do this or is there something I need to do so that I can just use the same format throughout my code.

Thanks

 

[MarkK]

Check your Windows Regional Settings, as Access honors those in some cases. The settings there may provide some insight into why Access is treating dates the way it does.

 

[JANR]

I have been having a problem trying to enter a date in a table using vba. I have fixed it now, but can anyone shed any light as to why my two examples use the date format differently but both work???

It is not VBA that require you to format a date to US dates, but JET/ACE engine.

db.execute is SQL and needs a reformat, the same goes for
db.OpenRecordset("Select......")

db.OpenRecordset("tblCustomer") does not involve JET/ACE and you don't have to anyting with the dates.

That's my understanding of it.
JR

 

[gemma-the-husky]

I am sure Jan is correct

Any time you use SQL you have to coerce the date to a regional format, or it will treat it as a US date, if its a valid US date

so 14/11/10 will be 14th Nov, but 12/11/10 will be December 11th.

 

[smig]

as I use non US formats I had this situation before.

when you use dates in SQL you should format them as US format (cause this is what the engine like )

 

[ted.martin]

You may find this useful

Public Function MakeUSDate(myUSdate As Variant)
If Not IsDate(myUSdate) Then Exit Function
MakeUSDate = "#" & Month(myUSdate) & "/" & Day(myUSdate) & "/" & Year(myUSdate) & "#"
End Function

 

[Rabbie]

You may find this useful

Public Function MakeUSDate(myUSdate As Variant)
If Not IsDate(myUSdate) Then Exit Function
MakeUSDate = "#" & Month(myUSdate) & "/" & Day(myUSdate) & "/" & Year(myUSdate) & "#"
End Function

I have just tested your function with the following results

1. MakeUSDate(#24/1/2011#) gives #1/24/2011#

2. MakeUSDate(#4/1/2011#) gives #4/1/2011#

1 seems to be correct

2 assumes you meant 1st April and not 4th January. This means the function will not convert dates in dd/mm/yyyy format to mm/dd/yyyy correctly if dd is less than 13.

 

[smig]

this will make sure your dates are in the correct format:

db.OpenRecordset("SELECT * FROM [tblDate] WHERE format([runDate], 'mm/dd/yyyy') = #" & Format(myDate, "mm/dd/yyyy") & "#")

 

[vbaInet]

Just a slight adjustment:

db.OpenRecordset("SELECT * FROM [tblDate] WHERE format([runDate],  'mm/dd/yyyy') = " & Format(myDate, "[COLOR=Red][B]\#[/B][/COLOR]mm/dd/yyyy[B][COLOR=Red]\#[/COLOR][/B]"))

Better if it's (i.e. #) parsed inside the Format function.

 

[kt1978]

Hi all

JanR, Thanks for your simple explanation around why different formats are required. Makes sense.

Gemma, that was the most annoying part. I was like, if its US format or UK format then fine, but don't change you mind half way through. lol

Thanks for everyone's input in this. Like I said, I had fixed the problem but I didn't understand why!!! Now I do...

Don't want drag this one on, but just one more question.... around formatting again..

vbaInet, What are the backslash's for "\#mm/dd/yyyy\#". I have also seen it entered like "mm\/dd\/yyyy", (or something like that).

I amended it to smig's one at first. Is there a right or wrong, or just preference.


Cheers again.

 

[gemma-the-husky]

Hi all

JanR, Thanks for your simple explanation around why different formats are required. Makes sense.

Gemma, that was the most annoying part. I was like, if its US format or UK format then fine, but don't change you mind half way through. lol

Thanks for everyone's input in this. Like I said, I had fixed the problem but I didn't understand why!!! Now I do...

Don't want drag this one on, but just one more question.... around formatting again..

vbaInet, What are the backslash's for "\#mm/dd/yyyy\#". I have also seen it entered like "mm\/dd\/yyyy", (or something like that).

I amended it to smig's one at first. Is there a right or wrong, or just preference.


Cheers again.

No - it doesn't change its mind

it ALWAYS FIRST tries to format the data as a US date - if it isn't a valid US date, then it tries your active domain.

so #1/4/10# is BY DEFAULT Jan 4th 2010
and #14/4/10# is then 14 April 2010 (as there is no month 14 in the US

so to force the first (and for safety, every date) to be treated correctly you have to tell it that the date is to be read in a different way

Hence format(#1/4/2010# ,"DD/MM/YY")
--------------
I actually generally use "long date"

format(#1/4/2010# ,"long date") and have never had an issue, although some experts explain that in some locales, it may not be accurate.

 

[ChrisO]

mm/dd/yyyy is incorrect.

mm\/dd\/yyyy is correct.

The Format function does not use mm/dd/yyyy to return mm/dd/yyyy.
The Format function uses mm/dd/yyyy to return mm ‘whatever the date separator happens to be in regional settings’ dd ‘whatever the date separator happens to be in regional settings’ yyyy.

The Format function uses mm\/dd\/yyyy to return mm/dd/yyyy because the backslash forces the forward slash to be taken literally and not go to regional settings and get the date separator.

Chris.

 

[smig]

as he only want to compare the dates I guess he can remove slashes alltogether, keeping the US formatqorder (as it's in query)
db.OpenRecordset("SELECT * FROM [tblDate] WHERE format([runDate], 'mmddyyyy') = " & Format(myDate, "\#mmddyyyy\#"))

when I sort dates or compare Higher/Lower I keep them formated as yyyymmdd.
I found that the normal regional format might make it wrong.

 

[JANR]

as he only want to compare the dates I guess he can remove slashes alltogether, keeping the US formatqorder (as it's in query)
db.OpenRecordset("SELECT * FROM [tblDate] WHERE format([runDate], 'mmddyyyy') = " & Format(myDate, "\#mmddyyyy\#"))

if you are going to convert the datefield then it would be better to convert to the integer part of the datefield. Provided they are typecast as Dates and not strings.

db.OpenRecordset("Select * From tblDate Where CLng(runDate) = CLng(MyDate)")

JR

 

[kt1978]

Hi all

Well, I'm glad I started this thread now, really starting to understand why and how dates are used so thank you all for your input, and once again for a couple more alternative solutions.

Thanks ChrisO for the explanation around the mm\/dd\/yyyy.

Gemma

No - it doesn't change its mind

I meant that a bit tounge in cheek. I knew it didn't change its mind but logically it doesn't seem very clever. I think an error flag around an incorrect date or something would be better (irrelevant in a way coz if I was using the methods you guys suggested I wouldn't have had the problem).

Anyway, now I have learnt so much around how to use the dates correctly it wont be a problem again.

Thank you all