Results 1 to 5 of 5
  1. #1
    mws5872 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2009
    Posts
    11

    #error handling

    Should be easy for you guys.

    do you have any ideas on how to replicate an Iferror in access that you do an excel? the problem is some of the fields have text (N/A) when an N/A appears when I do any sort of dividing or multiplying it gives a #error. I wan't to default these that have N/As to zero (or blank doesn't matter)
    Is there a way to do this? I have the sheet linked in.

  2. #2
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Why is the N/A appearing? It shouldn't be there unless the field is an incorrect data type (in this case, not a number). If the field was formatted as the correct type, it should show up as a number (like 0).

    Once we know why the N/A is appearing, we can figure out how to handle it, but one possibility might be an IIf statement like the following.

    Code:
    =IIf(Field="N/A",IfTrue,IfFalse)
    Be sure to replace Field, IfTrue, and IfFalse with the appropriate code. :P

  3. #3
    mws5872 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2009
    Posts
    11
    The reason the N/A is appearing is because it is defaulted in the data excel spreadsheet. If there is nothing put in the cell we default it to N/A ( and we need to keep an eye on these) that is why there are N/A's.

  4. #4
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Then, assuming you're importing the data straight into Access via VB Code (or similar) and not just retyping the whole thing, I'd go with one of the two possible solutions below:

    First one is the IIf statement listed in my previous Post. Since the text is being imported from Excel, looking for the string "N/A" should allow you to do exactly what you want.

    The other option (and probably the better of the two), would be to change your import Code so that, every time it encounters a field with "N/A" in it, it replaces it with either a Null value or a 0 (zero). Then you won't have to worry about "N/A" fields popping up when you least expect them.

  5. #5
    mws5872 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2009
    Posts
    11
    Rawb that worked !! thanks for the help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error Handling for newbie
    By smikkelsen in forum Access
    Replies: 4
    Last Post: 04-08-2010, 05:17 PM
  2. Replies: 2
    Last Post: 02-26-2010, 08:53 AM
  3. Handling dates in queries
    By mrk68 in forum Access
    Replies: 4
    Last Post: 03-23-2009, 06:35 AM
  4. Replies: 0
    Last Post: 03-05-2009, 12:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums