Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.
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 5 days ago
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 today
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
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks.
Specify a start-up form through options:
Specify an Autoexec macro:
Us…
Suggested Courses
Course of the Month11 days, 17 hours left to enroll
730 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.