problem with insert

[rkferri]

I'm using an access database and have the date fields set to datetime. I know if I change these to text I will not get an error but I want them defined as Datetime. Every time I do an insert I get the following error:

ODBC Error Code = 22005 (Error in assignment)


[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

Here is my insert statement:
Insert into Obits
(FirstName,
MiddleName,
LastName,
DOB,
DeathDate,
BirthCity,
BirthState,
Visitation,
FuneralMass,
Cemetery,
<cfif isDefined("Picture")><cfif Picture NEQ "">picture,</cfif></cfif>
Condolences
)
values
('#FirstName#',
'#MiddleName#',
'#LastName#',
'#DateFormat(DOB, "MM/DD/YYYY")#',
'#DateFormat(DeathDate, "MM/DD/YYYY")#',
'#BirthCity#',
'#BirthState#',
'#DateFormat(Visitation, "MM/DD/YYYY")#',
'#DateFormat(FuneralMass, "MM/DD/YYYY")#',
'#Cemetery#',
<cfif isDefined("Picture")><cfif Picture NEQ "">'#Picture#',</cfif></cfif>
'#Condolences#')

Can anyone see what I am doing wrong? Any help is greatly appreciated.

Thanks,
Kelly

 

[r937]

when you do this -- '#DateFormat(DOB, "MM/DD/YYYY")#'

then you get this -- '09/27/1995'

but for access you need to generate this -- #09/27/1995#

so you have to write this -- ###DateFormat(DOB, "MM/DD/YYYY")###






r937.com | rudy.ca

 

[rkferri]

If I do what you said I get the same error. This is now what my sql looks like.

Insert into Obits
(FirstName,
MiddleName,
LastName,
DOB,
DeathDate,
BirthCity,
BirthState,
Visitation,
FuneralMass,
Cemetery,
<cfif isDefined("Picture")><cfif Picture NEQ "">picture,</cfif></cfif>
PersonalInfo
)
values
('#FirstName#',
'#MiddleName#',
'#LastName#',
'###DateFormat(DOB, "MM/DD/YYYY")###',
'###DateFormat(DeathDate, "MM/DD/YYYY")###',
'#BirthCity#',
'#BirthState#',
'###DateFormat(Visitation, "MM/DD/YYYY")###',
'###DateFormat(FuneralMass, "MM/DD/YYYY")###',
'#Cemetery#',
<cfif isDefined("Picture")><cfif Picture NEQ "">'#Picture#',</cfif></cfif>
'#PersonalInfo#')

 

[r937]

i suggested that you write this -- ###DateFormat(DOB, "MM/DD/YYYY")###

but instead you wrote this -- '###DateFormat(DOB, "MM/DD/YYYY")###'

can you spot the difference? ;-)



r937.com | rudy.ca

 

[rkferri]

Yes I did spot the difference when I reposted the query I had put back in the tick marks. Sorry If I mislead you. When I try it without the tick marks like this:

###DateFormat(DOB, "MM/DD/YYYY")###

I get the following error:

Syntax error in date in query expression '##'.


SQL = "Insert into Obits (FirstName, MiddleName, LastName, DOB, DeathDate, BirthCity, BirthState, Visitation, FuneralMass, Cemetery, PersonalInfo ) values ('nnnnnnnn', 'nnnnnnnnn', 'nnnnnnnnnnn', ##, ##, '', 'IL', ##, ##, '', '')"

Any other thoughts or suggestions would be greatly appreciated. Again sorry for the confusion.
Thanks,
Kelly

 

[r937]

## is not a valid access date -- you have to actually put something in between those octothorps

if you don't know the DOB, use NULL instead -- just like that, the keyword NULL, no octothorps or quotes

r937.com | rudy.ca