SQL UPDATE DD/MM/YYYY query

[Mr Clevver]

Hey again,

I'm having issue with an unbound textbox, which sits in an unbound form. Upon clicking the 'Save & Exit' button it is supposed to update the date field in a table (tEvents) with the SQL UPDATE query:

stSQL1 = "UPDATE tEvents SET tEvents.Username = " & "'" & Me.txtUsername & "'" & ", tEvents.EventDescription = " & "'" & Me.txtEventDescription & "'" & ", tEvents.EventDate = " & "#" & Format(Me.txtEventDate, "DD/MM/YYYY") & "#" & " WHERE (((tEvents.EventID)=" & Me.txtEventID & "));"

This has been working since it rolled out a week ago, but this week if I try to save the record for any event that has been tagged for next month it changes the date from, DD/MM/YYYY to MM/DD/YYYY; so 01/07/2009 becomes 07/01/2009. I added the Format(Me.txtEventDate, "DD/MM/YYYY") to the query to see if this would help, but it doesn't seem to be.

Client and server machines are all set to DD/MM/YYYY format.

Any ideas what I could do to force the query into ensuring that the dates are added in DD/MM/YYYY format? Or is this something I could be doing at form level?

Thanks.

PS. Oddly enough, the INSERT query that creates the record, on the same form, seems to work perfectly, though it fetches the date from the exact same textbox.

 

[namliam]

Access requires dates to be in MM/DD/YYYY for it to work properly... I am somewhat sceptical to your statement:
"This has been working since it rolled out a week ago,"

Access simply interperts 02-06-2009 as beeing Feb 06 not June 2, period! it doesnt depend on anything... the only time it does work is 13-06-2009 as month 13 doesnt excist it will be forced to make this June 13.

Now to solve it, in order of preverence I think:
1) Use a date picker/control
2) Use the DateSerial function in conjuction with left,right and Mid functions.
3) force the MM/DD/YYYY format using Left, Right and Mid functions
4) Have the user input MM/DD/YYYY

 

[Mr Clevver]

Thanks for the response.

Access requires dates to be in MM/DD/YYYY for it to work properly... I am somewhat sceptical to your statement:
"This has been working since it rolled out a week ago,"

Access simply interperts 02-06-2009 as beeing Feb 06 not June 2, period! it doesnt depend on anything... the only time it does work is 13-06-2009 as month 13 doesnt excist it will be forced to make this June 13.

I thought this was probably the case.
As the system has only been used in anger for a week, this issue hasn't yet come up (so far the only dates edited have had a DD portion of the date >13).

Now to solve it, in order of preverence I think:
1) Use a date picker/control

The inbuilt date picker in Access2007 seems to still be producing the same error. I guess this is because the SQL is reading it from an unbound text box, which is displaying in DD/MM/YYYY, but is being interpreted by the SQL query as MM/DD/YYYY.

2) Use the DateSerial function in conjuction with left,right and Mid functions.
3) force the MM/DD/YYYY format using Left, Right and Mid functions

I shall research these. Never used either.

4) Have the user input MM/DD/YYYY

Not really practical. Would produce far too many user errors.


Thanks, you've given me some stuff to look into. Now I just have to run through the system and see if there are any other areas which are likely to encounter the same error in a few days time.

 

[namliam]

Any datepicker control will have the "real" date of MM/DD/YYYY, infact any date is really a number of days since 1/1/1900 (or 1904) so unless you force it using Format(..., "DD/MM/YYYY") -which is obviously wrong- then yes it breaks.
If you use MM/DD/YYYY or nothing it should both work properly.

Not really practical. Would produce far too many user errors.

Which is why I put it as the last option its a bad one but an option non the less.

Another option is input the date in 3 boxes instead of one...
Day Month Year in seperate boxes and use DateSerial to "fix" the date.

 

[Mr Clevver]

I think what I'm going to do is simply make a separate form for each process and have them as bound forms, with the text box bound to the relevant date field. Currently the form uses if statements to figure out which particular field the date should be written to, and then uses the SQL query to update that field in the record.
If I change it so that it is instead a bound form, one for each process, it works correctly again. (I've tested it).

Trying to be clever, you see.

Thanks for you assistance anyway, you've been most helpful. And I've still learned something (which is the important part).

 

[namliam]

Good: you learned something

Bad: You feel you need to go with a workaround solution

 

[Mr Clevver]

-Double Edit, both these issues have been fixed with liberal use of DoCmd.RunCommand acCmdSaveRecord
Even worse: The new bound forms are giving me

"The Data has been changed.

Another user edited this record and saved the changes before you attempted to save your changes.
Re-edit the record"

...as soon as I type in the bound fields.

-Edit I resolved the issue below
And if I do re-edit it gives me the typical:

"This record has been changed by another user" etc. etc. dialogue when I try to save.

 

[namliam]

OK So no issue then?

 

[Mr Clevver]

OK So no issue then?

No, all issues seem to be resolved (touch wood). I had simply got a little panicked when the work around hadn't worked initially.

Thanks for all you help.