Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

#Error Conversions

Posted on 2014-10-28
6
Medium Priority
?
162 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 40

Accepted Solution

by:
PatHartman earned 2000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 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