Read and Insert a Date Field

[Guest]

Hi All,

I have an access application that will run by some clients in different
countries. Because I donot know how regional date is set on clients'
computers, for each text box which receives a date from a calendar click I
set nothing for the Format and Data. Also, in the code when I get a date
value, I donot need to format it before I insert into a table which has field
date type.

That is what I plan for date value. Any suggestion?

Thank you.

 

[Allen Browne]

In your fields (table design) and controls (forms and reports), it's find to
leave the Format property blank, or use one of the named formats such as
General Date or Short Date.

But in SQL statements and in VBA code, you must format any literal dates!

For details, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Guest]

Hi Allen,

In the code, I perform a SQL statement,
INSERT INTO table (dateField) VALUES (FORMAT(txtDate,"mm") & "/" &
FORMAT(txtDate(),"dd") &"/" & FORMAT(txtDate(),"yyyy"))
or
INSERT INTO table (dateField) VALUES (txtDate)

Which one is correct?

Thank you.

 

[Allen Browne]

Assuming txtDate is an unbound text box, I would format the date as a
literal in the right format and with the right delimiters, and concatenate
it into the string like this:
If IsDate(Me.txtDate) Then
strSql = "INSERT INTO table (dateField) VALUES (" & _
Format(Me.txtDate, "\#mm\/dd\/yyyy\#") & ");"
End If

Your 2nd option probably won't fly, as it lacks the context for txtDate, and
Execute can't call the Expression Service to evaluate the txtDate, so I
would expect it to give a "Parameters expected" error.

 

[Guest]

Hi Allen,

Your suggestion is simple than my first. My second also works on my PC, but
I am not sure if it works on other PCs. I will follow your suggestion.

Again, thanks for your replies.