Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Yes, it's strange. I have reverted back to verson of DB before running the query and just ran it again and table is now correct with correct (195662) number of records.

    Not sure what happened, possibly a bug as it was running first time but it seems to be working now thanks.

    Just conscious next time I run it with more data, is it going to reconvert the converted ItemIDs by adding an extra 'EZ' in front of them (so I then get EZEZ1001 for instance)? What would I need to do to stop this?



    Sorry for so many questions!!

  2. #17
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    No it won't. If you're actually updating the ItemID field, when you re-run the query it won't find a '2' there because it's not '2' anymore so it won't do anything.
    If you're creating a new field, it will simply replace the entire 'EZ9999' again.

    In either case, no problem.

  3. #18
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Hi Davegri,

    Just tried it with new data and it's doubled up on the EZ so now getting EZEZ1001 etc.

    Here is the code:
    IIf([itemID]='2',(IIf((([Date Paid Date]-[Date of birth])/12)<6,'EZ1001',(IIf((([Date Paid Date]-[Date of birth])/12)<12,'EZ1002',(IIf((([Date Paid Date]-[Date of birth])/12)<36,'EZ1003',(IIf((([Date Paid Date]-[Date of birth])/12)<132,'EZ1004','EZ1005')))))))),'EZ' & [itemID])

    Could it be the last bit which is what I need as the data that doesn't contain EZ yet needs to be converted to EZ prefix.

    Do i need a new IFF statement to start off whereby it checks if ItemID already has prefix 'EZ' and if so it does nothing otherwise it runs the above ?
    Sorry, not sure how I'd do that if that's what I need.

    Really sorry to be a pain!!

  4. #19
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    sorted, just put a Not Like "EZ*" in the criteria section and seems to be doing the job.

    Thanks again for all your assistance - really appreciated.

  5. #20
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Great job finding that last EZ at the end of the update! Good luck with the rest of your project.

  6. #21
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Thanks Davegri, can't thank you enough!!

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Conditional Formatting - Using Eval function with wildcards
    By mlichtenstein in forum Programming
    Replies: 2
    Last Post: 07-02-2015, 08:09 AM
  2. Replies: 19
    Last Post: 09-05-2014, 06:13 AM
  3. Replies: 7
    Last Post: 10-04-2013, 11:45 AM
  4. Conditional Sum Function
    By samthomasny in forum Access
    Replies: 4
    Last Post: 11-21-2011, 10:00 AM
  5. Using conditional COUNT function
    By Laura WW in forum Reports
    Replies: 3
    Last Post: 11-08-2011, 11:59 PM

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