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:
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?
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
Last Comment
DJPr0
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
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.
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
<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
ASKER CERTIFIED SOLUTION
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.
ASKER
Worked perfectly, thanks _agx_!