Why does UPDATE query change data type?

[deko]

I have an unbound text box on a form that is set to:

Format = Short Date (with the corresponding input mask)

When I run this:

DoCmd.RunSQL ("UPDATE tblOutput SET ApptStart = " & Me!txtApptStart)

the value in the table is only a time value, or some other incorrect date.

The data type and format of the table field matches that of the textbox
field.
I've tried messing around with DateValue and removing the input mask, but
keep getting screwy results.
There must be some way to get the date value into a table with a query...

Am I missing something obvious?

[Allen Browne]

You need to explicitly format the literal date in the SQL string, and use
the # delimiter:

DoCmd.RunSQL ("UPDATE tblOutput SET ApptStart = " & _
Format(Me!txtApptStart, "\#mm\/dd\/yyyy\#"))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <dj****@hotmail.com> wrote in message
news:Hc******************@newssvr25.news.prodigy.c om...

I have an unbound text box on a form that is set to:

Format = Short Date (with the corresponding input mask)

When I run this:

DoCmd.RunSQL ("UPDATE tblOutput SET ApptStart = " & Me!txtApptStart)

the value in the table is only a time value, or some other incorrect date.

The data type and format of the table field matches that of the textbox
field.
I've tried messing around with DateValue and removing the input mask, but
keep getting screwy results.
There must be some way to get the date value into a table with a query...

Am I missing something obvious?

[deko]

thanks for the tip.

this was driveing me crazy...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

You need to explicitly format the literal date in the SQL string, and use
the # delimiter:

DoCmd.RunSQL ("UPDATE tblOutput SET ApptStart = " & _
Format(Me!txtApptStart, "\#mm\/dd\/yyyy\#"))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <dj****@hotmail.com> wrote in message
news:Hc******************@newssvr25.news.prodigy.c om...

I have an unbound text box on a form that is set to:

Format = Short Date (with the corresponding input mask)

When I run this:

DoCmd.RunSQL ("UPDATE tblOutput SET ApptStart = " & Me!txtApptStart)

the value in the table is only a time value, or some other incorrect date.
The data type and format of the table field matches that of the textbox
field.
I've tried messing around with DateValue and removing the input mask, but keep getting screwy results.
There must be some way to get the date value into a table with a query...
Am I missing something obvious?