Results 1 to 3 of 3

Thread: Query (2003)

  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query (2003)

    I have a table with three fields for a date. Month, Day, and Year. I need to run a query using input from a form that gives the begin and end month and begin and end day. My criteria for the month field in the query pulls the data beginmonth and endmonth from the form and and shows the data from the table if it is >= beginmonth and <=endmonth. The same applies to the day field. My problem arises when I need to show data from say 10/08/04 through 11/02/04. The end day is less than the begin day and no data is returned. Does anyone have suggestions on this?

    Thanks,

    Kent

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query (2003)

    Add a calculated column to the query:

    CompleteDate: DateSerial([Year],[Month],[Day])

    Assuming that the day and month from the form are always in the current year, add the following to the criteria line for this column:

    Between DateSerial(Year(Date()),[Forms]![frmTest]![BeginMonth],[Forms]![frmTest]![BeginDay]) And DateSerial(Year(Date()),[Forms]![frmTest]![EndMonth],[Forms]![frmTest]![EndDay])

    frmTest is the name of the form, BeginMonth, BeginDay etc. are the names of the controls on the form. It might be easier to let the user enter a start date and end date on the form.

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (2003)

    That was perfect! Thanks Hans. I do let the person enter the input dates on the form.

    Thanks again.

    Kent

Posting Permissions

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