Results 1 to 13 of 13
  1. #1
    DEWPCs is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2020
    Posts
    5

    Customer Database date comparison and validation

    We have a large customer DB with customer contract information containing data points such as contract term (in months), start date and end date of contract. I need to generate a Query that determines what the end date should be from the start date + term, then compares the existing end date and flags any inconsistencies. Below is a sample of the info.
    This is my first post in a long time as I have not been practicing IT for over 5 years and am getting back into it so please let me know if I am doing it correct.
    Date Status Company Notes to Rep Rep ID UoM Usage Fee Yr 1 Payout Date Paid CID Supplier Term Start End Rate Commodity Utility Acct Service Addy City State Zip Contact Business T Phone # Cust Email
    07-Aug-20 Yes test Co 1 PAID 3223 x 97,749.00 0.007 $684.24 2020-08-21 26289 EH 12 01-Sep-20 30-Aug-21 $0.06 E OH 212121 4280 Shem TEST 1 OH 11111 CUSTOMER 1 Owner 330
    17-Aug-20 No Test co 2 9/1 Rejected 3223 x


    0

    26457 EH 12 01-Sep-20 30-Aug-21 $0.06 E CEI 545441 30584 Century TEST 2 OH 22222 CUSTOMER 2 Owner 330
    26-Aug-20 Yes Test co 3 PAID 3223 x 30,090.00 0.007 $212.63 2020-09-11 26620 EH 12 01-Oct-20 30-Sep-21 $0.06 E OE 445454 4131 W slvb TEST 3 OH 33333 CUSTOMER 3 Owner 330

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I assume Term is months?

    You haven't said what constitutes an inconsistent end date but suspect it is the first two rows in your example data. (12 months - 2 days rather than 1)

    use the dateadd function

    WHERE dateadd("m",[Term],[Start])-1<>[End]

    Note that Date and End are reserved words, using them as field names can result in inconsistent results and/or misleading errors

  3. #3
    DEWPCs is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2020
    Posts
    5
    Ajax, this looks to be a great answer to the question I've asked and I thank you for the speedy response. I was able to partially address the issue but everything went else went on delay for the holidays so i apologize for the late response however I'm back on this project and the solution has brought me to an issue with the data itself. Some of the term dates are in months and some are in years. I don't care if its one of the other, but it needs to be only one duration.
    Some have a term of years which can look anywhere from 1 to 1.5 up to 5.
    The months are less consistent and can look like any of "12mo", "12 mo", OR "12 months".
    what is the simplest way to make these uniform in terms of months OR years, it makes no difference.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would standarddise on months rather than years

    to convert you might have a formula along these lines

    termInMonths:iif(isnumeric(term),term*12,val(term) )

    which should cope with the variety you have stated

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If I understand you correctly you might have in the term field (which seems to be short text as it looks left justified) values such as "12", "12mnth", "12 months","1","1.5","1.5 years", etc. If that is the case I suggest you redesign the table by making the Term field numeric (double) and add a new field called TermInterval or something like this. In the data entry form use a combo for the new fielld with "m", "yyyy" and any other available intervals in the DateAdd function (and limit it to list). Then you can simply base your validation on the two fields using the DateAdd function shown by Ajax.

    To transfer the data from the existing short text field to the two updated fields I suggest you do it in batches with update queries populating the new fields from the existing one for each of the individual patterns. For example use Like "* months" to isolate all the records with space months ("5 months", "12 months",etc.) and get the numeric part using Left([Term],Instr([Term]," ")).

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    DEWPCs is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2020
    Posts
    5
    Thank you both for your responses. I was able to use your suggestion Ajax to create a consistently accurate uniform term: "UniformTerm: IIf(IsNumeric([Term]),[Term]*12,Val([Term]))
    And the Formula WHERE: DateAdd("m",[uniformTerm],[Start date])<>[Expiration] seems to flag any inaccuracy's. However I need it to need more tolerant of +or - 1 month as depending on the date of insertion this is a normal factor and I am just not really understanding these formulas enough to where I can modify them with any success. I've tried inserting what I would understand as variables and every time I do I blow out all the correct responses. I also welcome any suggested training videos as rather asking so many questions I really want a better grasp of Access myself.
    I am so grateful of all the help you guys offer, and only wish at this point I can have a better understanding so I can help others like you have helped me.
    Example below

    Proper variable need to not flag
    Status Company Name Account # Start Date Expiration UniformTerm WHERE
    ACCEPTED test co3 33333333333333333 2/1/2017 1/31/2022 60 -1

    Error flagged properly
    Status Company Name Account # Start Date Expiration UniformTerm WHERE
    ACCEPTED test co2 222222222222222 4/1/2017 2/1/2022 48 -1

    Perfect expected result
    Status Company Name Account # Start Date Expiration UniformTerm WHERE
    ACCEPTED test co1 111111111111111111 4/1/2017 4/1/2021 48 0
    Last edited by DEWPCs; 01-06-2021 at 11:53 AM. Reason: omitted info

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try

    WHERE:
    [Expiration] not between DateAdd("m",[uniformTerm]-1,[Start date]) and DateAdd("m",[uniformTerm]+1,[Start date])

    which gives +- 1 month

    or if you want say +- 15 days

    WHERE: abs(datediff("d",[Expiration],DateAdd("m",[uniformTerm],[Start date])))>15

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Could you please try this for the flag:

    IIF(ABS(DateAdd("m",[uniformTerm],[Start date]) - [Expiration])>1,"Flag","No Flag")

    It gives you the difference in days between the existing Expiration and the calculated one and it flags the record is greated than one day.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    DEWPCs is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2020
    Posts
    5
    Excellent!!! Access amazes and intimidates me. Both of the approaches work great. I have yet to determine which works better for the project I am working on.
    Let me know if I should create a new post for this separate issue...
    Now I have created an exact expiration date field from the Ajax iif recommendation from earlier and I am creating a report that color codes contracts expiring 30, 60, 90, 120 days from the present date. I was able to create a Form and use conditional formatting to color code the dates within 30 days however I am having difficulty finding a way to isolate 30-60 days and the rest. This is the "expression" under conditional formatting, i am using [End Date]<Date()30. It does what I need for the first group, they are made red, but I want the rest another color.


    one love
    Daniel.

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you are using conditional formating, the order matters - the first condition encounters that is true will win the day. e.g.

    field value is ..... <30 red
    field value is .... <40 orange
    field value is ... <60 green

    if the field value is 25, colour will be red
    if the field value is 35, colour will be orange

    reverse the order


    field value is ... <60 green
    field value is .... <40 orange
    field value is ..... <30 red

    and the colour for both will be green

    there is no need to specify from values (i.e. between 31 and 40) since if the value was 25, red will be chosen, the other options don't get a look in

  11. #11
    DEWPCs is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2020
    Posts
    5
    OK! wow, that's the simplest instruction yet. of course I was over thinking it.
    OK, One last question for now. I have this data looking how I want it mostly however I am attempting to organize it according to some calculated dates. However due to the fact that not every entry in the database has all the fields filled out properly some of the dates could not be calculated and are showing an #error
    How can I remove these #error entries from the primary database and dump them in another?

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    find the reason for the error. Then either modify your calculation to allow for it (e.g. if error is caused by a date field not being completed, in other words null), or exclude those records in your criteria.

    to dump them in another table, use the criteria per above and create two queries, one to copy the data to another table and the second to delete them from your main table.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It sounds like you have "Calculated Fields" in your Table(s) - Bad Idea!!
    Read This: Microsoft Access Tips for Casual Users

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sum IIF date comparison issue
    By sergi117 in forum Access
    Replies: 3
    Last Post: 10-05-2018, 11:30 AM
  2. problem with date comparison when querying in vba
    By charis89 in forum Programming
    Replies: 8
    Last Post: 08-20-2015, 04:48 PM
  3. Replies: 6
    Last Post: 12-11-2013, 09:27 PM
  4. RC Notation and Date Comparison
    By mkc80 in forum Access
    Replies: 2
    Last Post: 10-10-2012, 06:22 PM
  5. Short date comparison
    By andy101 in forum Programming
    Replies: 2
    Last Post: 03-17-2011, 04:36 AM

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