Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.
Course Of The Month
8 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
ColdFusion Update Number Type Field in Access
Posted on 2012-12-20
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?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
5 Comments
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
Active today
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
Active 3 days ago
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.yourVaria
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.yourVariabl
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 22 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.