Solved

#Error Conversions

Posted on 2014-10-28
6
158 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
[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
  • 3
  • 2
6 Comments
 
LVL 37

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

729 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