Update and insert a Date into a field in sql - correctly

[Billp]

Hi,

I have the following sql statement.

'now set follow up
strsql1 = "UPDATE tblCustomerContacts " & _
" SET [Follow_Up] = " & Me![Follow_Up] & _
" WHERE [LineID] = " & Me!Customer_Contact_LineID 'Quotes this
way as feild is numeric
Debug.Print strsql


Where Me![Follow_Up] is a date - short date
The field that is being updated is also a date feild

In the current format 25/06/2010 (25 June 2010) results in 30/12/1899

ummmmmmmmmmmmmmmmmmm??????????????
What is the correct syntax to get the date to insert into the other field
and show correctly.

Thanx

 

[fredg]

Hi,

I have the following sql statement.

'now set follow up
strsql1 = "UPDATE tblCustomerContacts " & _
" SET [Follow_Up] = " & Me![Follow_Up] & _
" WHERE [LineID] = " & Me!Customer_Contact_LineID 'Quotes this
way as feild is numeric
Debug.Print strsql

Where Me![Follow_Up] is a date - short date
The field that is being updated is also a date feild

In the current format 25/06/2010 (25 June 2010) results in 30/12/1899

ummmmmmmmmmmmmmmmmmm??????????????
What is the correct syntax to get the date to insert into the other field
and show correctly.

Thanx

Access is correct ... for the expression as written.
It is processing the [Follow_Up] value as a date value of Zero (0)
which as a date value is 30 Dec 1899..

Date datatype variables ( Me![Follow_Up] ) need to be enclosed within
the date delimiter symbol (#).

Try:

rsql1 = "UPDATE tblCustomerContacts " & _
" SET [Follow_Up] = #" & Me![Follow_Up] & _
"# WHERE [LineID] = " & Me!Customer_Contact_LineID
Debug.Print strsql

 

[Allen Browne]

There are several issues here:
1. A literal date in a SQL statement must have # delimiters round it.

2. JET expects the literal date in the SQL statement in US format,
regardless of your local date format, so you need to coerce it like that.

3. You need to ensure the Follow_Up text box contains a valid date, and that
Access understands it that way. If Follow_Up is an unbound text box, set its
Format property to General Date. (If it's bound to a Date/Time field in a
table, you don't need to do this.)

If IsNull(Me.Follow_Up) Then
MsgBox "No valid date"
Else
strsql1 = "UPDATE tblCustomerContacts " & _
"SET [Follow_Up] = #" & Format(Me![Follow_Up], "mm\/dd\/yyyy") & _
"# WHERE [LineID] = " & Me!Customer_Contact_LineID
End If

More info in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

To explain the 1899 date, that is day zero of the Access date system.
Without the # delimiter, your expression is interpreted as 25 divided by 6
divided by 2010, which is less than one (and so day zero.)

 

[John W. Vinson]

Hi,

I have the following sql statement.

'now set follow up
strsql1 = "UPDATE tblCustomerContacts " & _
" SET [Follow_Up] = " & Me![Follow_Up] & _
" WHERE [LineID] = " & Me!Customer_Contact_LineID 'Quotes this
way as feild is numeric
Debug.Print strsql


Where Me![Follow_Up] is a date - short date
The field that is being updated is also a date feild

In the current format 25/06/2010 (25 June 2010) results in 30/12/1899

ummmmmmmmmmmmmmmmmmm??????????????
What is the correct syntax to get the date to insert into the other field
and show correctly.

Thanx

Delimit dates with #, and be sure they're in American or ISO format:

" SET [Follow_Up] = #" & Format(Me![Follow_Up], "yyyy-mm-dd") & "#" & _

Curious that you're not using a bound form... any reason to go to all this
extra work?

 

[Billp]

Thank you to all for your valued assistance.
Time and dates and comma etc - I can never get right and when to use what
and where is always going to be a night mare.

I am using a bound form and the field is date/time in the backing table.
When the form is completed and closes the selected fields are then used to
update a customer table where the contacts are updated with the new dates for
sales refrence.