Solved

#Error Conversions

Posted on 2014-10-28
6
155 Views
Last Modified: 2014-10-28
Experts,

I have an access query that we use for inspections and the value is a string. My problem lies is that 90% of the time my field data are numbers and I convert them to a double in another query column so we can perform calculations to that field i.e. CDbl[Result1]. My problem is I have it as a string because my customers require me to occasionally type "Pass" or "Fail" due to the fact that they are visual inspections and do not have a calculated result. So in my converted column I get the #Error for all the places it has a "Pass" or "Fail" results. Is there where away to create another column maybe using the IIf statement to convert the #Error back to the "Pass" or "Fail"? I tried this but the computer won't recognize the #Error as a value.

Actual 1: IIf([Convert 1]=#Error,[Result1],[Convert 1])

Any help is greatly appreciated.
0
Comment
Question by:NuclearOil
  • 3
  • 2
6 Comments
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40408538
How would pass or fail be "counted" in the calculation?

I would probably go with two columns.  One for the value and one for the grade.  You can calculate the grade based on the value so grade can be required.  Value seems to need to be optional.

You can't check for #Error, you would need to prevent it by testing for a not numeric value prior to doing the calculation.

IIf(IsNumeric(SomeField), ...calc here..., SomeField) As Result

The expression above will result in a string so again, you will have trouble down the line if you need to sum or create an average.  Just go with two columns and stop the confusion.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40408544
Try:

Actual 1: IIf(iserror([Convert 1]),[Result1],[Convert 1])
0
 

Author Comment

by:NuclearOil
ID: 40408606
Pat,

I would like to go with two columns but I have too much data stored that way. The way it was set up, pre dates me. Is there a way to convert the "#Error" to just appear as nulls?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:NuclearOil
ID: 40408610
Phillip,

Still comes up as #Error.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40408635
Then try:

IIf([Convert 1]="#Error",[Result1],[Convert 1])
0
 

Author Closing Comment

by:NuclearOil
ID: 40408639
Pat,

The is numeric function will get me buy for what i need to do.

Thank you!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question