Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Queries on yyyymmdd (2K SR-2`)

    I'm having trouble creating a date criteria for a query. I have accounting data downloaded from an AS400, where the dates are formatted as yyyymmdd. The financial year for my client is, eg, 20010401 to 20020331. The criteria should select whole months only, which I can do. But when I test is one the first month, ie 20010401, I get a blank.

    There's probably a better way to achieve this, but I have set up a separate table in order to enter the first date of each new year. I also have a report menu which contains a combo box to select the calendar reporting period.

    So far, my criteria looks like this:
    IIf([Forms]![frmReportMenu]![Period]=1,Between DLookUp("[FirstDateOfYear]","tblExtras","[ExtrasID]=1") And DLookUp("[EndPeriod]","tblTimePeriod","[TimePeriodID]=[Forms]![frmReportMenu]![PeriodID]"),Between DLookUp("[FirstDateOfYear]","tblExtras","[ExtrasID]=1") And DLookUp("[EndPeriod]","tblTimePeriod","[TimePeriodID]=[Forms]![frmReportMenu]![PeriodID]-1"))

    I can get the comparison and true and false arguments to work independently, but not together.

    Can anyone help me on this pls? Or better still, is there a more efficient way to achieve what I'm after?

    Many thanks for any help.

    Nick

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Date Queries on yyyymmdd (2K SR-2`)

    >>I have accounting data downloaded from an AS400, where the dates are formatted as yyyymmdd. <<

    You say the data was dowloaded from an AS400. Do you mean it was imported into an Access table? And if so, were the dates imported into a date field or a text field? The field type is important for criteria selection. You can still have a date field and have it formatted for display as yyyymmdd.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Queries on yyyymmdd (2K SR-2`)

    The data is linked via ODBC then I used a make table query to extract the fields and account range I needed into a separate table. Up till now, I haven't specified the data type in the make table query, but I'll set it up as a date data type and keep you informed.

    Many thanks
    Nick

Posting Permissions

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