Date Format Problems!

[Cavman]

Morning All,

I'm having a nightmare with dates in Access VBA! Here is what's happening:

The date is entered on a form using the date picker. Let's say I select 07 April 2013. This is then then transfered to a date variable (dtNewDate). The value in the variable is then inserted into a date field in a table (DoCmd.RunSQL "UPDATE tbl_AccountBase SET NextCallDate = #" & dtNewDate & "# WHERE AccountRef = '" & CurrentAccount & "'")

The field from the table is then displayed in long date format on another form, but it's showing as 4th July 2013, not 7th April as I originally selected!

Access seems to be switching the format all by itself without any help! Can anyone tell me where this switch occurs? And what's the best way to handle it?

Thanks in advance for any help,

Simon

 

[pr2-eugin]

Simon, Dates in Access is a bit tricky.. Allen Browne discusses more on this issue.. Try this..

DoCmd.RunSQL "UPDATE tbl_AccountBase SET NextCallDate = " & Format(dtNewDate,"\#mm\/dd\/yyyy\#") " & _
                " WHERE AccountRef = '" & CurrentAccount & "'"

 

[Cavman]

Thanks Paul, that's brilliant, I've bookmarked Allen Browne's page. I've been looking for an explanation that made sense for quite a while. Access seemed to be so inconsistent with dates, now I understand why!

I guess it's probably best to force the date into American format as soon as it's input and assigned to a variable. I have another section of code where I need to add a certain number of days to the date entered before it's finally inserted into the table. So I'll need to make sure it's formatted correctly before doing that.

Thanks again for your help,

Simon

 

[gemma-the-husky]

you aren't quite forcing it into US format. it's just that a direct SQL regards any date as a US date.


so 5/12/12 gets treated as May 12th (US), not 5th December(UK)

but 13/12/12 can only be treated as 13th December, as there is no 13th month.

 

[Cavman]

Ok, so what about functions such as DateAdd, do they also regard the date as US format? So would I need to use something like the following:

dtCurrentCallDate = Format(Me.txtDateEntered,"mm/dd/yyyy")
dtNewCallDate = DateAdd("d", intInterval, dtCurrentCallDate)

Thanks,

Simon

 

[pr2-eugin]

Ok, so what about functions such as DateAdd, do they also regard the date as US format?

Technically NOT.. Normally this Formatting would apply for when you are coding.. As the link suggests that the VBA Coding like a SQL statement, or Domain function (which is a simplified SQL statement) would require the Format to be in mm/dd/yyyy.. But nothing about VBA Functions, they normally use the Regional settings.. I have used several Date functions and they do not seem to be a problem so far..

If you have declared the Variables as Date or used the Bound Fields.. There will be absolutely no problem when you use the functions..

 

[Brianwarnock]

The problem only occurs with hard coded dates in SQL and VBA, if you use controls then the work is done on the Dble that the date is actually stored as, the confusion arises because run SQL and domain functions actually interpret, not sure what the correct word is, the date, if you use the immediate window to look at the SQL string you will see the hard code date not the dtenewdate , thus it has to be in US format.

Brian

 

[DocWomet]

If I could solicit further... having a similar likely rookie mistake that I've tried writing so many different ways I can't remember when I began.

All I'm trying to do is update a table with a Short Time value from a textbox (TRW.Value) so that where my table MASTERShell has the same Short Time as the one inputted, the Update clause changes the associated location to "TRW". Any pointers how to make this work? I'm planning to do this for about 15 other variables besides "TRW" so if I'm going about this wrong from the get go, any advice would be very appreciated.

VBA:

TRWvar = Format(Me.TRW.Value, "Short Time")

DoCmd.RunSQL " UPDATE MASTERShell" _
& " SET Location = '" & TRW & "' WHERE Time = " & #TRWvar# ";"


Thank you,
Nate

 

[pr2-eugin]

Hello DocWomet, Welcome to AWF..

Try this..

DoCmd.RunSQL " UPDATE MASTERShell" _
  & " SET Location = '" & TRW & "' WHERE Time = [COLOR=Red][B]#[/B][/COLOR]" & TRWvar "[COLOR=Red][B]#[/B][/COLOR];"

PS: In future please start a New thread under the specific Section, so that many others would be able to help you. If you wish to ask follow ups, you can post a link to the specific thread you are talking about to get a clearer picture.. Good luck..

 

[DocWomet]

Sweet, blessed success....! Thank you so much. WILCO on your advice.

Hello DocWomet, Welcome to AWF..

Try this..

DoCmd.RunSQL " UPDATE MASTERShell" _
  & " SET Location = '" & TRW & "' WHERE Time = [COLOR=red][B]#[/B][/COLOR]" & TRWvar "[COLOR=red][B]#[/B][/COLOR];"

PS: In future please start a New thread under the specific Section, so that many others would be able to help you. If you wish to ask follow ups, you can post a link to the specific thread you are talking about to get a clearer picture.. Good luck..