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?

DJPr0Asked: 2012-12-20

Who is Participating?

LVL 53

_agx_Commented: 2012-12-20

I want to update the field to null or 0.

EDIT To set the value to 0, use the val() function. It automatically converts non-numeric values like an empty string to 0. (I'm assuming your column type is INTEGER. Change if needed ...)

UPDATE TableName
SET NumColumn = <cfqueryparam value="#val(form.yourVariable)#" cfsqltype="cf_sql_integer">
WHERE ID = ....

To set the value to null, use the NULL attribute

UPDATE TableName
SET NumColumn = <cfqueryparam value="#form.yourVariable#"
cfsqltype="cf_sql_integer"
null="#not IsNumeric(form.yourVariable)#" >
WHERE ID = ....

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?

All depends on your needs. Sometimes 0 is better, other times null is. When I want to indicate something has no value, like for a date field, I prefer NULL instead of entering a bogus date like 12/31/1899. For numbers it can go either way.

LVL 3

jdthedjCommented: 2012-12-20

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

DJPr0Author Commented: 2012-12-20

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.

LVL 3

jdthedjCommented: 2012-12-20

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