Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2008
    Posts
    8

    Unanswered: Build an Access expression that will do the same as the following Excel formula?

    Question: In Access 2003/XP/2000/97, I'm trying to build an expression that will do the same as the following Excel formula with the result formatted as

    =IF(TYPE(CODE(B11))=1,IF(F11=0,(E11-$B$6)/7,(E11-F11)/7)," ")

    See attached Screen shots of Excel and Access Form

    i've tried this but it's not working??

    IIF(ISNULL([1_ID_REFERENCE),NULL,IIF([5_ACTUAL]=0,([4_PLAN]-Date())/7,([4_PLAN]-[5_ACTUAL])/7))

    The Access feild names are selection type text boxes on the form shown in the attached screen shot
    1_ID_Reference is a 'Text' datatype
    5_ACTUAL is a 'date/time' datatype
    4_PLAN is a 'Date/Time' datatype
    How can I do this?
    Attached Thumbnails Attached Thumbnails Screenshot 8.jpg   Screenshot 10.jpg   Screenshot 9.jpg   Screenshot 11.jpg  

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What does TYPE() do? What does CODE() do?
    Or you could try explaining in natural English what the formula does.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2008
    Posts
    8

    Exclamation VBA Code to Trigger an Event

    Original issue was: In Access 2003/XP/2000/97, I'm trying to build an expression that
    will do the same as the following Excel formula with the result formatted as

    =IF(TYPE(CODE(B11))=1,IF(F11=0,(E11-$B$6)/7,(E11-F11)/7)," ")

    I've tried this expression but it's not working??

    to count the number of days between two
    dates and divide the result by 7, in which case you need something
    along the lines of;


    I understand that Access cannot
    ADD amd SUBTRACT directly (+/-) ...DateDiff , DateAdd VBA code that
    triggered by an event i would certainly try it


    IIf(IsNull([1_ID_REFERENCE),Null,IIf([5_ACTUAL]=0,DateDiff("d", [4_Plan],
    Date)/7,DateDiff("d", [4_PLAN], [5_ACTUAL])/7)))
    [/SIZE]

    See the attached screen shots for view of access form and origianl excel spreadsheet

    I need help with the following VBA Function


    Public Function fnTestThis(dat4Plan As Date, dat5Actual As Date) As Long

    fnTestThis = CLng(DateDiff("d", dat4Plan, dat5Actual))

    End Function

    ------------------------------------------
    I need help to write this to determine what to pass to that function to execute on whatever event you need it to gve result as original excel formula:------------------------------------------
    I need help to sort out the data validation and error handling.

    dim dat_4Plan as date
    dim dat_5Actual as date
    dim lngDelta as long 'datediff number that will be retd from function

    dat_4Plan = me.4_Plan

    if me.5_Actual=0 then
    dat_5Actual = date()
    else
    dat5_Actual=me.5_Actual
    endif

    lngDelta = fnTestThis(dat_4Plan, dat_5Actual)
    ------------------------------------------
    Attached Thumbnails Attached Thumbnails Screenshot 7.jpg   Screenshot 8.jpg   Screenshot 10.jpg   Screenshot 11.jpg   Screenshot 14.jpg  


  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Threads merged.

    One question, one thread please Jon
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would avoid a VBA function where you can
    Code:
    IIf([1_ID_REFERENCE] Is Null,Null,DateDiff("d",[4_PLAN],IIf([5_ACTUAL]=0,Date(),NZ([5_ACTUAL],Date())))/7)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Oct 2008
    Posts
    8
    Pootle flump: Thanks for you help..

    Just to make sure i doing this right where should this code be placed, where within the properties of the text box?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    jon - just looked properly at your screen shots - that the most meticulously prepared question ever.

    I also thought this was in a query, not a form, so avoiding VBA is not a big deal and might be impossible.

    Anyhoo - I can't remember how best to do this in bound forms. Try in the Control Source of the text box. I think it might work - if not, we can play around with some After Update events.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The Control Source is correct. But I don't know if the expression is right. I think you'd have to use IsNull() rather than X Is Null.

    And I agree. Well done on the way you've prepared this question! :w00t:
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No need to use the evil ISNULL(). X Is Null works fine
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What's evil about IsNull() ??
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1) It is a proprietary function that duplicates the functionality of ANSI standard syntax ([x] IS NULL)
    2) Because it is a function, it is MUCH SLOWER than ANSI syntax when used in a query. Create a table with one column, throw in a few thousand rows including half a dozen or so NULLs. Index it and create two queries with WHERE predicates on the column to find the NULL entries - ISNULL() will perform much worse than [x] IS NULL.
    3) Even Microsoft can't decide what it should do, so IsNull() does something totally different in T-SQL, which confuses the bejesus out of people that upgrade. I use the ANSI COALESCE() exclusively in T-SQL
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in access / jet you can simply subtract date a from date b to give a difference in days. its a quirk of JET
    however as you are subtracting two dat values and divide by 7.. does the mean you are looking for the answer in weeks.. if so datediff is definately your man, but use the 'w'weeks styel rather than the 'd' day style
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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