Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    4

    Lightbulb Unanswered: IIf Formula Help

    I'm trying to set up a database with a sequental numbering system that I can control.

    Within that database, I need the sequential number for each entry to look like "20110001234". Each entry into the db will recieve this unique number increasing by "1" after each entry is made.

    However, I need the number "4" to be added within the sequential number and replace the first "0" after "2011" when a specific report is entered (eg: Arrest). Here's an example of what I'm talking about:

    Seq# Date Incident
    20110001234 07-19-11 Damage to Property
    20110001235 07-19-11 Assault
    20114001236 07-19-11 Arrest
    20110001237 07-19-11 Crash Report
    20114001238 07-19-11 Arrest
    20110001239 07-20-11 Breaking & Entering
    20114001240 07-20-11 Arrest

    I've tried to do something like this: (IIf[Incident]="Arrest",[Seq_#]+4000000)

    Your help and suggestions is greatly appreciated! Let me know if you have any questions or if I can give you any other information. THANKS AGAIN!

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    You don't need an "IIf" function.
    It's enough an Update query.
    Look at "DemoIIfFormulaA2000.mdb" (attachment, zip).
    Look at Table1, Query1Update.
    Run query and look in the table.
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2010
    Posts
    84
    For full functionality required by your inquiry is yet another condition, which is to verify that figure after 2011 is equal to 0 using the Mid function.
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2011
    Posts
    4
    Thanks for the information, but I'm not able to open the zip files that are posted here. I get a "corrupt folder" error when I try to open/save/unzip them.

    Try again?? THANKS!

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I've tried to do something like this: (IIf[Incident]="Arrest",[Seq_#]+4000000)
    Actually, your IIF() function isn't that bad. You just need to fix your syntax. Instead of
    Code:
    (IIf[Incident]="Arrest",[Seq_#]+4000000)
    you need to do something like
    Code:
    [Seq_#] = (IIf[Incident]="Arrest",[Seq_#]+4000000, [Seq_#])
    Sam

Tags for this Thread

Posting Permissions

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