Date format error

  • Thread starter Thread starter Rose B
  • Start date Start date
R

Rose B

I am inserting a record into a table via VBA using the date field of a record
on a form. The insert works fine EXCEPT that instead of entering the date
into the table inn the format of dd/mm/yyyy it puts it in as mm/dd/yyyy. My
SQL INSERT statement is ......

"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency) values
(" & Format(Me.Date_of_Trip, "\#dd\/mm\/yyyy\#") & "," ...etc

The dates are defined as Short Date. I have displyed the values of
Me.Date_of_Trip and also of my SQL statement via MsgBox and they show the
date correctly in dd/mm/yyyy, but when I look at the record in the new table
it has inserted it incorrectly.....UNLESS the value of "dd" is higher than
"12" - in which case it inserts it correctly!

Can anyone help?
 
Rose

Import distinction -- Access has a Date/Time data type that stores dates
(and times) as numbers (and decimal fractions).

YOU decide which format to use when you display the data.

If you are trying to insert a text string that's been formatted to look like
a date, why?! Why not just use the Access Date/Time data type?

P.S. There are differences in how systems in the UK and in the US display
day, month and year of dates. Is this a factor for you?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
In queries, Access doesn't respect the Short Date format chosen through
Regional Settings. You must use a format Access will recognize, like
Format(Me.Date_of_Trip, "\#mm\/dd\/yyyy\#") or Format(Me.Date_of_Trip,
"\#yyyy\-mm\-dd\#")

For more on this, see what Allen Browne has at
http://www.allenbrowne.com/ser-36.html or what I had in my September, 2003
"Access Answers" column in Pinnacle Publication's "Smart Access". You can
download the column (and sample database) for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html
 
Hi Rose,
try in this way
"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency) values
#" & mid(Date_of_Trip,4,2) & left(Date_of_Trip,2) & right(Date_of_Trip,4) &
"# ," ...etc
 
I am not clear on where you mean for this formatting to take place....

- In the table? (Both tables currently have the field defined as Short Date.
In on eall entries are OK, in the other (which is being inserted into) it is
only OK if the day is greater than 12, otherwise it switches the month and
day around when saving ti that table
- in the INSERT statement? I am doing that I think - as in my orginal post.
Is there something wrong here?
- in the VBA behind the form?

Thanks,

Douglas J. Steele said:
In queries, Access doesn't respect the Short Date format chosen through
Regional Settings. You must use a format Access will recognize, like
Format(Me.Date_of_Trip, "\#mm\/dd\/yyyy\#") or Format(Me.Date_of_Trip,
"\#yyyy\-mm\-dd\#")

For more on this, see what Allen Browne has at
http://www.allenbrowne.com/ser-36.html or what I had in my September, 2003
"Access Answers" column in Pinnacle Publication's "Smart Access". You can
download the column (and sample database) for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rose B said:
I am inserting a record into a table via VBA using the date field of a
record
on a form. The insert works fine EXCEPT that instead of entering the date
into the table inn the format of dd/mm/yyyy it puts it in as mm/dd/yyyy.
My
SQL INSERT statement is ......

"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency) values
(" & Format(Me.Date_of_Trip, "\#dd\/mm\/yyyy\#") & "," ...etc

The dates are defined as Short Date. I have displyed the values of
Me.Date_of_Trip and also of my SQL statement via MsgBox and they show the
date correctly in dd/mm/yyyy, but when I look at the record in the new
table
it has inserted it incorrectly.....UNLESS the value of "dd" is higher than
"12" - in which case it inserts it correctly!

Can anyone help?
Click to expand...
Click to expand...
 
The US/UK thing might be a factor - but I am not sure how. Both fields are
defined as short date. My source table is OK all of the time, the table I am
inserting into is only OK when the day is greater than 12, other than that it
switched the day and month around - even though I am formatting the string in
my SQL statement. Appreciate any further thoughts.

Jeff Boyce said:
Rose

Import distinction -- Access has a Date/Time data type that stores dates
(and times) as numbers (and decimal fractions).

YOU decide which format to use when you display the data.

If you are trying to insert a text string that's been formatted to look like
a date, why?! Why not just use the Access Date/Time data type?

P.S. There are differences in how systems in the UK and in the US display
day, month and year of dates. Is this a factor for you?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Rose B said:
I am inserting a record into a table via VBA using the date field of a record
on a form. The insert works fine EXCEPT that instead of entering the date
into the table inn the format of dd/mm/yyyy it puts it in as mm/dd/yyyy. My
SQL INSERT statement is ......

"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency) values
(" & Format(Me.Date_of_Trip, "\#dd\/mm\/yyyy\#") & "," ...etc

The dates are defined as Short Date. I have displyed the values of
Me.Date_of_Trip and also of my SQL statement via MsgBox and they show the
date correctly in dd/mm/yyyy, but when I look at the record in the new table
it has inserted it incorrectly.....UNLESS the value of "dd" is higher than
"12" - in which case it inserts it correctly!

Can anyone help?
Click to expand...
Click to expand...
 
Thanks for the suggestion Paolo - I am still 'playing' with it buut no luck
so far. I am trying to get the format right (seems to object to wherever I
put # at the moment and if I leave it out then nothing gets inserted into the
field. Will come back if it works!

Paolo said:
Hi Rose,
try in this way
"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency) values
#" & mid(Date_of_Trip,4,2) & left(Date_of_Trip,2) & right(Date_of_Trip,4) &
"# ," ...etc


Rose B said:
I am inserting a record into a table via VBA using the date field of a record
on a form. The insert works fine EXCEPT that instead of entering the date
into the table inn the format of dd/mm/yyyy it puts it in as mm/dd/yyyy. My
SQL INSERT statement is ......

"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency) values
(" & Format(Me.Date_of_Trip, "\#dd\/mm\/yyyy\#") & "," ...etc

The dates are defined as Short Date. I have displyed the values of
Me.Date_of_Trip and also of my SQL statement via MsgBox and they show the
date correctly in dd/mm/yyyy, but when I look at the record in the new table
it has inserted it incorrectly.....UNLESS the value of "dd" is higher than
"12" - in which case it inserts it correctly!

Can anyone help?
Click to expand...
Click to expand...
 
It's in the INSERT statement. Yes, you have a Format statement there, but
you cannot format the date as dd/mm/yyyy and expect it to work reliably.
Access will ALWAYS treat nn/nn/nnnn as mm/dd/yyyy if it can. It's only when
the day is greater than 12 that it will correctly treat it as dd/mm/yyyy
(since there are only 12 months)

Read the articles I cited.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rose B said:
I am not clear on where you mean for this formatting to take place....

- In the table? (Both tables currently have the field defined as Short
Date.
In on eall entries are OK, in the other (which is being inserted into) it
is
only OK if the day is greater than 12, otherwise it switches the month and
day around when saving ti that table
- in the INSERT statement? I am doing that I think - as in my orginal
post.
Is there something wrong here?
- in the VBA behind the form?

Thanks,

Douglas J. Steele said:
In queries, Access doesn't respect the Short Date format chosen through
Regional Settings. You must use a format Access will recognize, like
Format(Me.Date_of_Trip, "\#mm\/dd\/yyyy\#") or Format(Me.Date_of_Trip,
"\#yyyy\-mm\-dd\#")

For more on this, see what Allen Browne has at
http://www.allenbrowne.com/ser-36.html or what I had in my September,
2003
"Access Answers" column in Pinnacle Publication's "Smart Access". You can
download the column (and sample database) for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rose B said:
I am inserting a record into a table via VBA using the date field of a
record
on a form. The insert works fine EXCEPT that instead of entering the
date
into the table inn the format of dd/mm/yyyy it puts it in as
mm/dd/yyyy.
My
SQL INSERT statement is ......

"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency)
values
(" & Format(Me.Date_of_Trip, "\#dd\/mm\/yyyy\#") & "," ...etc

The dates are defined as Short Date. I have displyed the values of
Me.Date_of_Trip and also of my SQL statement via MsgBox and they show
the
date correctly in dd/mm/yyyy, but when I look at the record in the new
table
it has inserted it incorrectly.....UNLESS the value of "dd" is higher
than
"12" - in which case it inserts it correctly!

Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Another suggestion Rose,

In the insert into statement use select instead of values so your statement
will look in this way

"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency) select
#" & mid(Date_of_Trip,4,2) & left(Date_of_Trip,2) & right(Date_of_Trip,4) &
"# ," ...etc

Cheers Paolo

Rose B said:
Thanks for the suggestion Paolo - I am still 'playing' with it buut no luck
so far. I am trying to get the format right (seems to object to wherever I
put # at the moment and if I leave it out then nothing gets inserted into the
field. Will come back if it works!

Paolo said:
Hi Rose,
try in this way
"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency) values
#" & mid(Date_of_Trip,4,2) & left(Date_of_Trip,2) & right(Date_of_Trip,4) &
"# ," ...etc


Rose B said:
I am inserting a record into a table via VBA using the date field of a record
on a form. The insert works fine EXCEPT that instead of entering the date
into the table inn the format of dd/mm/yyyy it puts it in as mm/dd/yyyy. My
SQL INSERT statement is ......

"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency) values
(" & Format(Me.Date_of_Trip, "\#dd\/mm\/yyyy\#") & "," ...etc

The dates are defined as Short Date. I have displyed the values of
Me.Date_of_Trip and also of my SQL statement via MsgBox and they show the
date correctly in dd/mm/yyyy, but when I look at the record in the new table
it has inserted it incorrectly.....UNLESS the value of "dd" is higher than
"12" - in which case it inserts it correctly!

Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
What Douglas Steele is saying is to change your statement to

"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency) values
(" & Format(Me.Date_of_Trip, "\#yyyy\/mm\/dd\#") & "," ...etc

Doing this will ensure that Access correctly interprets the date
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Rose B said:
I am not clear on where you mean for this formatting to take place....

- In the table? (Both tables currently have the field defined as Short
Date.
In on eall entries are OK, in the other (which is being inserted into) it
is
only OK if the day is greater than 12, otherwise it switches the month and
day around when saving ti that table
- in the INSERT statement? I am doing that I think - as in my orginal
post.
Is there something wrong here?
- in the VBA behind the form?

Thanks,

Douglas J. Steele said:
In queries, Access doesn't respect the Short Date format chosen through
Regional Settings. You must use a format Access will recognize, like
Format(Me.Date_of_Trip, "\#mm\/dd\/yyyy\#") or Format(Me.Date_of_Trip,
"\#yyyy\-mm\-dd\#")

For more on this, see what Allen Browne has at
http://www.allenbrowne.com/ser-36.html or what I had in my September,
2003
"Access Answers" column in Pinnacle Publication's "Smart Access". You can
download the column (and sample database) for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rose B said:
I am inserting a record into a table via VBA using the date field of a
record
on a form. The insert works fine EXCEPT that instead of entering the
date
into the table inn the format of dd/mm/yyyy it puts it in as
mm/dd/yyyy.
My
SQL INSERT statement is ......

"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency)
values
(" & Format(Me.Date_of_Trip, "\#dd\/mm\/yyyy\#") & "," ...etc

The dates are defined as Short Date. I have displyed the values of
Me.Date_of_Trip and also of my SQL statement via MsgBox and they show
the
date correctly in dd/mm/yyyy, but when I look at the record in the new
table
it has inserted it incorrectly.....UNLESS the value of "dd" is higher
than
"12" - in which case it inserts it correctly!

Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Thanks - that is great!! (What a minefield!)

John Spencer said:
What Douglas Steele is saying is to change your statement to

"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency) values
(" & Format(Me.Date_of_Trip, "\#yyyy\/mm\/dd\#") & "," ...etc

Doing this will ensure that Access correctly interprets the date
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Rose B said:
I am not clear on where you mean for this formatting to take place....

- In the table? (Both tables currently have the field defined as Short
Date.
In on eall entries are OK, in the other (which is being inserted into) it
is
only OK if the day is greater than 12, otherwise it switches the month and
day around when saving ti that table
- in the INSERT statement? I am doing that I think - as in my orginal
post.
Is there something wrong here?
- in the VBA behind the form?

Thanks,

Douglas J. Steele said:
In queries, Access doesn't respect the Short Date format chosen through
Regional Settings. You must use a format Access will recognize, like
Format(Me.Date_of_Trip, "\#mm\/dd\/yyyy\#") or Format(Me.Date_of_Trip,
"\#yyyy\-mm\-dd\#")

For more on this, see what Allen Browne has at
http://www.allenbrowne.com/ser-36.html or what I had in my September,
2003
"Access Answers" column in Pinnacle Publication's "Smart Access". You can
download the column (and sample database) for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am inserting a record into a table via VBA using the date field of a
record
on a form. The insert works fine EXCEPT that instead of entering the
date
into the table inn the format of dd/mm/yyyy it puts it in as
mm/dd/yyyy.
My
SQL INSERT statement is ......

"INSERT into RepeatJob ([LastTrip], Client, Destination, Frequency)
values
(" & Format(Me.Date_of_Trip, "\#dd\/mm\/yyyy\#") & "," ...etc

The dates are defined as Short Date. I have displyed the values of
Me.Date_of_Trip and also of my SQL statement via MsgBox and they show
the
date correctly in dd/mm/yyyy, but when I look at the record in the new
table
it has inserted it incorrectly.....UNLESS the value of "dd" is higher
than
"12" - in which case it inserts it correctly!

Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top