Results 1 to 9 of 9
  1. #1
    gjeffers0615 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    4

    IF Then Else Statement in Access Query Design

    Hi. I'm new to this forum and relatively new to Access and VBA. I am extracting data from a table where all the date fields are numeric like 20160101. The field that I'm concerned with [TermDate] has 99999999 as a future date if the TermDate has yet to be determined or an actual numeric date like 20160101 if a Term Date exists. I'm trying to convert my date fields to actual Date formats. I would like to write a query that would do something like this:

    If the [TermDate] is equal to 99999999 Then change/update that date/value to #12/31/9999# Else change the numeric value to an actual date format. I'm using CDate(Left([rnchdt],4) & "/" & Mid([rnchdt],5,2) & "/" & Right([rnchdt],2)) to convert to the date format.



    Thanks for any assistance in advance!

  2. #2
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    have you tried entering 20160101 into a field designated as a date field type? I haven't - but the recognition of dates has really gotten very good - and its possible that it will handle it as part of the import - otherwise rearrange it and there won't be a problem.

    9999 won't work as a year - I don't think. I would suggest to leave blank, as a blank in this case is meaningful, but if something must be there it needs to be in a more reasonable range.

  3. #3
    gjeffers0615 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    4
    Thanks for the quick response. I have tried entering the numeric date format into a Date Type field and it errors incorrect data type. Also, I am pulling from a master dataset with read access only so I have no choice but to import the future-non determined TermDates as 99999999. That's the format my company uses and always has supposedly. For my purpose any year in the distant future would work. Its just a place holder until an actual Term Date has been determined. I am trying to convert the numeric Term Dates to a Date type so that I can perform a DateAdd function to determine future follow-up dates after a Group has Termed.

  4. #4
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    right - so it wasn't clear your import process - but I'll assume you can put stuff into a table "as is" - and then append it to its final table and as part of that you can manipulate stuff around...

    for instance you can detect the 9999 and replace with whichever value works best

    you can flip flop the characters of the date (text field) and then write them into a true date type field

    what I was thinking before at the first date - is often csv files you open with excel - - and then using the excel format cell feature I think your reversed date text field will actually be recognized and rearranged automatically... but I hadn't tried appending that reverse lay out into an Access date field so wasn't sure if it would take or not....

    but its very common to have a temp staged table where you bring things in their funky native format - - and then with a query & calculated fields one manipulates things - and then append the correct format into a final table....

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also, I am pulling from a master dataset with read access only
    I would use the DateSerial function
    Code:
    DateSerial(Left([rnchdt],4), Mid([rnchdt], 2), Right([rnchdt],2))
    In a query, you could use
    Code:
    TermDate: IIf([rnchdt]=99999999,#12/31/9999#,DateSerial(Left([rnchdt],4),Mid([rnchdt],5,2),Right([rnchdt],2)))

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    a date of 12/31/9999 is perfectly acceptable, you just need to change 99 to 12 or 31 respectively

    so modify to

    CDate(Left([rnchdt],4) & "/" & iif(Mid([rnchdt],5,2)>12,12, Mid([rnchdt],5,2)) & "/" & iif(Right([rnchdt],2)>31,31,Right([rnchdt],2)))

    I'm not sure how 20160102 will be translated with your current formula - it may be 2nd Jan (US format) or 1st Feb (UK format). Your example of 20160101 is for 1st Jan which would be the same either way. So your mid and right functions may need to be swapped round

  7. #7
    gjeffers0615 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    4
    Quote Originally Posted by ssanfu View Post
    I would use the DateSerial function
    Code:
    DateSerial(Left([rnchdt],4), Mid([rnchdt], 2), Right([rnchdt],2))
    In a query, you could use
    Code:
    TermDate: IIf([rnchdt]=99999999,#12/31/9999#,DateSerial(Left([rnchdt],4),Mid([rnchdt],5,2),Right([rnchdt],2)))

    Thanks Steve. That exactly what I was trying to accomplish but couldn't figure out the correct syntax.

    Gary

  8. #8
    gjeffers0615 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    4
    Thanks Ajax! The 12/31/9999 works perfect.

    Gary

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Glad it worked out for you...

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

Similar Threads

  1. iif statement in access query
    By frustratedwithaccess in forum Access
    Replies: 6
    Last Post: 12-15-2014, 11:59 AM
  2. Replies: 2
    Last Post: 11-07-2013, 12:13 PM
  3. switch statement in a access query
    By Dominic in forum Access
    Replies: 1
    Last Post: 03-27-2013, 11:32 PM
  4. Replies: 1
    Last Post: 12-20-2010, 09:09 AM
  5. Replies: 8
    Last Post: 12-04-2010, 07:53 PM

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
  •  
Other Forums: Microsoft Office Forums