Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013

    Unanswered: Link Tables: #Num! Due to Blank Cells

    Hi All - newbie alert so please be gentle!

    I've got fairly solid excel skills but am new to access so this is probably why i'm having difficulty with this! I'm on Access 2007-10.

    I've found numerous references online to #Num! in link tables but none that seem to match my situation and i don't know access well enough to expand the logic to solve my problems.

    I have link table in my db which has a two columns that primarily contain a date for each row (format in dd/mm/yyyy hh:mm). However, there are a number of rows which are intentionally blank but in reality contain a space (" "). When i open the link table these cells now contain #Num!.

    I am trying to replicate the excel formula which i use: =IF(a2=" ","SAF","Sold")

    In access i have therefore tried:

    = IIF([Payment Received Date]=" ","SAF","Sold")

    but because of the #Num! that just gives the correct "Sold" where there is a date but #Num! where there isn't one.

    I have also tried:

    = IIF(IsDate([Payment Received Date]),"Sold","SAF") and,

    = IIF(IsError([Payment Received Date]),"SAF","Sold")

    both get stuck on the #Num! still.

    Any ideas? Can i fix the link file or is there an alternative formula i can try?


  2. #2
    Join Date
    May 2011
    Omaha, NE
    I had a table recently that I thought had a space like that too but it was actually "". Try changing your if to double quotes without the space & see if it works.

  3. #3
    Join Date
    Feb 2013
    I've tried "" instead of " " in access but this makes no difference. The cells now contain "#Num!" but it doesn't seem to be recognised as an error by the IsError() function.

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    Rather than linking the data from Excel, try importing it into Access. That way you can manipulate it, and you don't need to rely on Access treating the data according to the data type that it thinks best.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts