Results 1 to 4 of 4
  1. #1
    lansreef is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2016
    Posts
    2

    Converting Formula from Excel to Access

    Prior to a week ago, I had very little knowledge of Access and it hasn't improved any. I created a formula in Excel and wanted to see if I can convert it over to Access. These are all date fields if it makes a difference. I'm attempting to do this in queries. If there is a better place for this, please let me know.

    My Excel formula:
    =IF(BH2="",IF(AA2="","NA",TODAY()-AA2+1),IF(AA2="","NA",BH2-AA2+1))

    My attempt at Access:


    Expr1: IIf([Offer]="IS NULL",IIf([DateContacted]="IS Null",0,Date()-[DateContacted]+1),IIf([DateContacted]="IS NULL","0",[Offer]-[DateContacted]+1))

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Since "Offer" and "DateContacted" are fields, the fields cannot have a value of "IS NULL". So [Offer]="IS NULL" is an invalid test. If you wanted to test if a field is null, you would use the IsNull function: "ISNULL([FieldName])".

    Because the two fields are date/time fields, I would try the IsDate() function.

    For use in a query:
    (using negative logic ---> Not IsDate([Offer]) )
    Code:
    Expr1: IIf(Not IsDate([Offer]),IIf(Not IsDate([DateContacted]),0,Date()-[DateContacted]+1),IIf(Not IsDate([DateContacted]),0,[Offer]-[DateContacted]+1))
    I don't like to use negative logic, so here is positive the positive logic version:
    (using negative logic ---> IsDate([Offer]) )
    Code:
    Expr2: IIf(IsDate([Offer]),IIf(IsDate([DateContacted]),[Offer]-[DateContacted]+1,0),IIf(IsDate([DateContacted]),Date()-[DateContacted]+1,0))

  3. #3
    lansreef is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2016
    Posts
    2
    thank you!

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help.....

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

Similar Threads

  1. Replies: 19
    Last Post: 07-13-2015, 10:15 AM
  2. Replies: 41
    Last Post: 05-29-2015, 02:58 PM
  3. Converting Excel Formula to Access Query Format
    By Guy Winfield in forum Queries
    Replies: 2
    Last Post: 05-11-2015, 06:21 AM
  4. Replies: 7
    Last Post: 11-06-2014, 10:55 AM
  5. Converting this formula to an access database
    By prabha_friend in forum Queries
    Replies: 1
    Last Post: 07-23-2014, 10:11 AM

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