date from form

[bindurajeesh]

I have the following code on a button on a form:

DoCmd.RunSQL "update tblMember set HealProjectFormSigned = " &
chbHealProjectFormSigned & ", DateProjectFormSigned = " &
CDate(txtDateProjectFormSigned) & _
" where membernumber = " & cmbMemberNumber.Value

When the button is run the data that is entered in the table is 12/12/1889
even though the data entered on the form was 9/12/08. I have changed the
field data type to text and then the data is entered as a scientific number,
I have changed the field back to date/time and directily entered data in the
field while in the table and it stays at 9/12/08. so what is the problem
with the form? the text box in question has a format of short date. Thank
you in advance

 

[bindurajeesh]

also when I ?txtDateProjectFormSigned in the immediate window it shows
9/12/08 and ?cdate(txtdateProjectFormSigned) shows 9/12/08 in the immediate
window and so does ?format(txtdateprojectformsigned,"m/dd/yyyy")

 

[Marshall Barton]

I have the following code on a button on a form:

DoCmd.RunSQL "update tblMember set HealProjectFormSigned = " &
chbHealProjectFormSigned & ", DateProjectFormSigned = " &
CDate(txtDateProjectFormSigned) & _
" where membernumber = " & cmbMemberNumber.Value

When the button is run the data that is entered in the table is 12/12/1889
even though the data entered on the form was 9/12/08. I have changed the
field data type to text and then the data is entered as a scientific number,
I have changed the field back to date/time and directily entered data in the
field while in the table and it stays at 9/12/08. so what is the problem
with the form? the text box in question has a format of short date.

After the concatenations your SQL looks like:

.... Set ..., DateProjectFormSigned = 9/12/08 ...

BUT the 9/12/08 is an expression (9 divided by 12 divided by
8) which is clearly not what you want.

You need the SQL to end up looking like:

.... Set ..., DateProjectFormSigned = #9/12/08# ...

Because your Windpws settings are used when a date is
converted to a string (because of the concatenation) you
need to explocotly specify the separator characters. The
way I would do it is:

.... & ", DateProjectFormSigned = " &
Format(txtDateProjectFormSigned, "\#yyyy-m-d\#")) & _

 

[bindurajeesh]

Thanks dumb mistake on my part. Sometimes I wonder why I call myself a
programmer. Thanks again