Dates and Updating in an Access database 1

[Sherylj]

Here's the deal. I need to update a form. No big task right? Well I keep getting this error message and I suspect it's the date field giving me grief but can't figure it out. Please check my UPDATE query for syntax problems or anything else you might know. I'm using the following to populate a date field in the form. The pasing it on to the update query. Thanks.

<cfoutput query=&quot;Ticket_Detail&quot;>
<CFIF Ticket_Detail.UpdateDate IS &quot;&quot;>
<cfinput name=&quot;UpdateDate&quot; size=&quot;20&quot; maxlength=&quot;20&quot; value=&quot;#DateFormat(Now(),&quot;mmmm d, yyyy&quot#&quot;>
<cfelse>
<cfinput name=&quot;UpdateDate&quot; size=&quot;20&quot; maxlength=&quot;20&quot; value=&quot;#DateFormat(Ticket_Detail.UpdateDate)#&quot;>
</cfif>
</cfoutput>

Here's my query.... Is SQL different from Access? That's what I suspect.

<CFQUERY NAME=&quot;Update_Tickets&quot; DATASOURCE=&quot;HelpTest&quot;>
UPDATE tblTicketInformation
SET
AssignTo = '#FORM.AssignTo#',
TakenBy = '#FORM.TakenBy#',
Department = '#FORM.Department#',
Category = '#FORM.Category#',
Status = '#FORM.Status#',
Product = '#FORM.Product#',
Priority = '#FORM.Priority#',
UpdateDate = '#FORM.UpdateDate#',
UserLocation = '#FORM.UserLocation#',
UserName = '#FORM.UserName#',
UserPhone = '#FORM.UserPhone#',
UserEmail = '#FORM.UserEmail#',
UserStatus = '#FORM.UserStatus#',
Symptom = '#FORM.Symptom#',
Problem = '#FORM.Problem#',
Resolution = '#FORM.Resolution#',
Comments = '#FORM.Comments#'
Where TicketNumber = '#TicketNumber#'
</CFQUERY>

<!--- Use CFLOCATION to return to getopentickets.cfm --->
<CFLOCATION URL=&quot;getopentickets.cfm&quot;>

Thanks!
Sheryl

 

[tlhawkins]

Hey Sheryl,

I just took a quick look because this was a battle of mine once. You're doing the same thing I was. You need to change the date into an ODBCDATETIME variable like this:

#CreateODBCDateTime(Form.UpdateDate)#
you could probably get away with using createODBCDate but you might as well do the whole thing just in case.

so that Query line would look like this:

UpdateDate = '#CreateODBCDateTime(Form.UpdateDate)#'

I would also look closer at which fields need ' ' and which don't sometimes that can have problems. But if it is getting an error on the Date field this should fix it.

Hope it helps.

 

[Sherylj]

You are the MAN! Thanks again!! Worked like a charm!

Sheryl

 

[tlhawkins]

Glad to be of help.