UtterAccess Discussion Forums > Incorrect date format

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

FireKat

Mar 4 2007, 07:16 PM

I have an odd little problem coming up that I can't seem to work out. I have a small database that is being used to keep track of the training of various people in my organisation. I have a form that allows the table to be updated that a particular type of trainning has been completed and the date on which it was completed.

Unfortunately the date is being entered into the table in the incorrect format and I can't work out why. My regional setting son the computer are set to the proper format (dd/mm/yyy) and when I set the text box to short date is shows up in that format. The change seems to be occurring when the SQL query runs. For reference the SQL query is:

CODE

strSql = "UPDATE tblFirefighters SET tblFirefighters.Competency = Yes, tblFirefighters.CompDate = #" & dtCompDate & "#"
strSql = strSql & " WHERE (((tblFirefighters.RegNo)= " & lngRegNo & "))"
DoCmd.RunSQL strSql

Can anyone tell why this is happening and what I can do to correct it?

Alan_G

Mar 4 2007, 08:37 PM

Hi

Try changing it to

CODE

strSql = "UPDATE tblFirefighters SET tblFirefighters.Competency = Yes, tblFirefighters.CompDate = #" & Format(dtCompDate,"mm\/dd\/yyyy") & "#"
strSql = strSql & " WHERE tblFirefighters.RegNo = " & lngRegNo

Currentdb.Execute strSql, dbFailOnerror

FireKat

Mar 4 2007, 09:06 PM

Ah, that works. Thank you!

Can I ask what difference the \/ in the format structure makes? I've never seen it used before and its got me curious.

Alan_G

Mar 4 2007, 09:43 PM

Glad it worked for you

Have a look here for info on dates in Access and an explanation of the \/

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.