Avatar of DJPr0
DJPr0
 asked on

ColdFusion Update Number Type Field in Access

My website accepts number values and I receive an error if no value is inputted. (only works when a value is present)

Problem:
I would like to delete this value if needed without receiving this error:
Data type mismatch in criteria expression

My Access database has a number type field and using cf_sql_varchar in my update statement.
I've tried using other cf_sql types: number, integer, etc without success.

Would the solution be to insert a default value of 0 if no data is present or to change the cf_sql statement that would accept a null value for the number type field in Access?
ColdFusion LanguageMicrosoft Access

Avatar of undefined
Last Comment
DJPr0

8/22/2022 - Mon
APS NZ

You could use the IsNumeric function to check the value before adding to your database.  It is not a CF_SQL_VARCHAR which is for text.  I assume you are using <CFQUERYPARAM>  If you are (and you should be) you don't specify any CFSQLTYPE
DJPr0

ASKER
You could use the IsNumeric function to check the value before adding to your database.

How will this help to delete the value?

I want to update the field to null or 0.
APS NZ

Try this

<CFFUNCTION NAME="NullVal" RETURNTYPE="Numeric">
  <CFARGUMENT NAME="Amt" DATATYPE="String" REQUIRED="True">
  <CFIF IsNumeric(Amt)>
    <CFRETURN Amt>
  <CFELSE>
    <CFRETURN 0>
  </CFIF>
</CFFUNCTION>

The function will change any null to a zero
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
_agx_

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
DJPr0

ASKER
Worked perfectly, thanks _agx_!