Results 1 to 10 of 10
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Conditional Memo (2002 SP-2)

    I have a report e-mailed to trucking companies with the information necessary to pick up a load. There is a release number provided by the shipper to assure the driver gets the correct load. For as far back as I can recall (20 years) this has always been a number and never text. The number may be something like 456789; or if there are several loads under the same "root" number they have always been 456789.1 through 456789.10 Now, for some weird reason (or lack of reason) I have a new customer who is using 456789-01 through 456789-10. The "dash" is causing a problem for me when I e-mail the order because I cannot use a " - " in the release number field (nor would I want to). What I would like to do is add a memo field to the report detail that would appear only if the order has been placed by "AJAX" (txtShipperConsignee1) that would instruct the driver to treat 456789.1 as 456789-01. If "AJAX" is not the shipper, then the memo would not appear.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Memo (2002 SP-2)

    Use a text box without label. Set its Can Shrink and Can Grow properties to Yes. Set its Control Source to

    =IIf([txtShipperConsignee1]="AJAX","Treat " & [txtReleaseNumber] & " as " & Left([txtReleaseNumber],InStr([txtReleaseNumber],".")-1) & "-" & Format(Mid([txtReleaseNumber],InStr([txtReleaseNumber],".")+1),"00"),Null)

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Conditional Memo (2002 SP-2)

    Hans:

    You know, next time I have a question, the least you could do is delay the answer long enough for me to think it was more difficult. How do you get this stuff off the top of your head like that? Pure genius! An excellent solution (and far better than I had considered). Many, many, thanks. (You are, however, getting a bit scary) <img src=/S/witch.gif border=0 alt=witch width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Conditional Memo (2002 SP-2)

    Hans:

    The plot thickens. First, due to your brilliant "code", I decided to modify it just slightly and then place it directly on top of txtRelease1No:
    =IIf([txtShipperConsignee1]="AJAX",Left([txtRelease1No],InStr([txtRelease1No],".")-1) & "-" & Format(Mid([txtRelease1No],InStr([txtRelease1No],".")+1),"00"),Null)
    This way there is no need to take up additional space as a "memo", and the driver isn't even aware of the "special" circumstance. I just took an order from "AJAX" shipping from another facility and the release number was 4567893! That's a complete departure from the other series of numbers. I called them and they said when an order ships from a facility they own, it has six digits (plus the "-01" etc.) but if it ships from one of their venders it has seven digits and no "-01". Not wanting to get trapped again, they assured me there was never a seven digit number used with "-01" and a six digit number would always have "-01" etc. Might there be some way to address this in the statement?
    4567893 = 4567893
    456789.1 = 456789-01
    In the current statement a seven digit number (without ".1"), of course, returns Error#
    <img src=/S/please.gif border=0 alt=please width=31 height=23>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Memo (2002 SP-2)

    Teehee!

    =IIf([txtShipperConsignee1]="AJAX",If(Len([txtRelease1No])=7,[txtRelease1No],Left([txtRelease1No],InStr([txtRelease1No],".")-1) & "-" & Format(Mid([txtRelease1No],InStr([txtRelease1No],".")+1),"00"),Null))

    Note: by now, this probably is displayed in two or more lines. It is one expression, though.

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Conditional Memo (2002 SP-2)

    Hmm. . .
    Getting this when opening the report:
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Memo (2002 SP-2)

    There's a nested IIF in the expression that is missing the first "I". Even Hans' nimble fingers slip now and then. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Since "If" isn't recognized anywhere but in VBA code, you get a parameter dialog.
    Charlotte

  8. #8
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Conditional Memo (2002 SP-2)

    Charlotte:

    I actually tried that, but got:
    (Shame on me for questioning HansV)
    Edit:<font color=blue> "Shame on me for questioning HansV" is not an actual error msg in Access (although it probably should be)</font color=blue> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Memo (2002 SP-2)

    Not only did I write If instead of IIf, I also placed a closing bracket ) in the wrong place. That's what you get when you try to do write the expression in the post itself instead of in Access. Sorry about the confusion. Try this version:

    =IIf([txtShipperConsignee1]='AJAX',IIf(Len([txtRelease1No])=7,[txtRelease1No],Left([txtRelease1No],InStr([txtRelease1No],'.')-1) & '-' & Format(Mid([txtRelease1No],InStr([txtRelease1No],'.')+1),'00')),Null)

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Conditional Memo (2002 SP-2)

    Hans:
    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15>. . .works perfectly!
    P.S. Thanks for slowing the reply time (intentionally or otherwise). <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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