Solved

#Error Conversions

Posted on 2014-10-28
6
148 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 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
Comment Utility
Try:

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

Author Comment

by:NuclearOil
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:NuclearOil
Comment Utility
Phillip,

Still comes up as #Error.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Then try:

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

Author Closing Comment

by:NuclearOil
Comment Utility
Pat,

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

Thank you!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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 views 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 Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now