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

    Exclamation Unanswered: Access expression(s) from excel formula(s)...for conditional formatting

    Are these proposed access expression(s) correctly coded?
    See attached screenshot for the desired intention fro access conditionl formatting:

    =D11>74% (Orginal Excel Formula)
    =[3_INPUT_REVIEW]>74%(Prposed Access expression

    =$B$6> =IF(TYPE(CODE(B14))=1,E14,$B$6+100) (Orginal Excel Formula)
    =DATE()>=IIF(ISNULL([1_ID_REFERENCE]),NULL,4_PLAN],Date()+100) (Proposed New Access expresion)
    =$B$6>(AO11-$I$3) Note that excel formulas are :- AO11=IF(TYPE(CODE(B15))=1,E15,$B$6+100) $B$6=NOW() or Date(); $IS3=number of Package Pending days...enter number (Orginal Excel Formula)
    =DATE()>=IIF(ISNULL([1_ID_REFERENCE]),]))=1,[4_PLAN],Date()+100)-([ Package_Pending_Days]) Proposed Access Expression?
    = is this needed in the above access expression(s)?
    Attached Thumbnails Attached Thumbnails Screenshot 21.jpg   Screenshot 20.jpg  

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would get out of the habit of using ISNULL([x]) and use [x] IS NULL. Apart from VBA it can be used in the same way and has the advantage of being ANSI SQL, more efficient and won't confuse the heck out of you if you upgrade to SQL Server.

    I would use DATEADD() rather than Date() + 100: although the latter works it is not explicit and frankly makes little sense. "Monday 3rd of November + 29 equals Tuesday 2nd December? WTF?"

    Looks ok, but I still don't know what CODE() and TYPE() do. Are you getting the same results?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Is the conditional formatting for rows on a continuous form? If so, you may well experience (known) issues;

    If you do have problems, search the forum for 'conditional formatting continuous'.

    Alternatively, Dev Ashish of The Access Web has an article above which may help:

    http://www.mvps.org/access/forms/frm0024.htm
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    Oct 2008
    Posts
    8
    Pootle flump: The access expression posted still doesn't work, when used to set conditional formating? I'll amend to suit your suggestion like DateAdd etc

    TYPE(value) Returns an integer representing the data of a value:numbr = 1; text = 2; logical value = 4; error value = 16; array = 64.

    CODE(text) Returns a numeric code for the first character in a string, in the character st used by your compter

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - in Access, CODE() is ASC() - as in ASCII.
    There is no direct equivalent of TYPE() but for numbers you can use the LIKE operator. I'll need to throw together the expression.

    Which of the expressions work and which don't?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - stolen\ adapted from here:
    http://weblogs.sqlteam.com/jeffs/arc...nstraints.aspx
    Code:
    column NOT LIKE "*[^ -~A-Za-z]*"
    Note that ISNUMERIC() is not reliable, in case you have heard of it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2008
    Posts
    8
    Pootle Flump: thanks for your comments


    This works for the form field STATUS [6_STATUS] the following code is place in the access Property sheet, data, control source:-

    =IIf(IsNull([1_ID_REFERENCE]),Null,IIf([5_ACTUAL]=0,([4_PLAN]-Date())/7,([4_PLAN]-[5_ACTUAL])/7))

    the format is set to #,##0.0 and decimal place set to 1

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Apologies - I'm still not clear. The posted expression does not return the correct data? If not, what does it return (wrong data, nothing....)?

    If it does return data and it is correct, then you mean you can't get the conditional formatting working correctly for it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2008
    Posts
    8
    These expressions get "the expression you entered contain invalid syntax , you may have entered a comma withut a preceding value or identifier!"

    =([3_INPUT_REVIEW])>”74%”))
    =DATE()>=IIF(ISNULL([1_ID_REFERENCE]),NULL,4_PLAN],Date()+100)
    =DATE()>=IIF(ISNULL([1_ID_REFERENCE]),]))=1,[4_PLAN],Date()+100)-([ Package_Pending_Days])

    The followoing expression is working correctly:-

    =IIf(IsNull([1_ID_REFERENCE]),Null,IIf([5_ACTUAL]=0,([4_PLAN]-Date())/7,([4_PLAN]-[5_ACTUAL])/7))

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Try -
    Code:
    =[3_INPUT_REVIEW]>"74%"
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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