Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Posts
    23

    Unanswered: parameter query ?

    I am trying to see if there is a simple way to perform a parameter query on several fields using one command. I have seven fields on a form(Date1,Date2,Date3,Date4,Date5,Date6,Date7). I would like to set up a parameter query to be able to ask the question "ENTER THE BEGINNING DATE" and "ENTER THE ENDING DATE". I would like to have it query the table using all seven dates as if they were one DATE field.

    Hope this is clear. Any help would be appreciated.

    Thanks,
    Debit19

  2. #2
    Join Date
    Oct 2003
    Posts
    6

    Use your question in the query's criteria section of each Date field

    From your post it seems you have seven different fields in a table:

    Date1 through Date7

    In your parameter query put the following in the criteria section of Date1:

    Between [Enter Start Date] and [Enter End Date]

    In Date2 through Date7 sections put the same statement in each one's criteria:

    Between [Enter Start Date] And [Enter End Date]

    User will only enter the start and end dates once and they will be used to evaluate all seven dates.

  3. #3
    Join Date
    Jan 2005
    Posts
    23
    I tried this but seems to only pull up 1 record empty with no data. Maybe there is something else I am doing wrong.

    Debit19

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What is the SQL you have tried to date
    are these date bracketings exlcusive or ORs

    ie do you want the date bracketing to apply to all columns or to any one (or more) of those columns


    eg is it
    select somecolumn from tblMyData
    where datColumn1 between x and y AND datColumn2 between x and y AND...

    OR
    select somecolumn from tblMyData
    where datColumn1 between x and y OR datColumn2 between x and y OR...

  5. #5
    Join Date
    Jan 2005
    Posts
    23
    I think you mean do I need to apply to all of the columns. The answer is yes. Maybe a little more info would be helpful. I have a table that has Date1,Date2,Date3,Date4....so on representing the days of the week Sunday being Date1, Monday Date2 and so on. I use sort of a timesheet input form and I would like to be able to use a parameter query to ask only two questions "Enter the beginning date?" and "Enter the ending date?". I therefore need to have all of the columns that contain Date1, Date2.... be checked at the same time because there could be the same date on various records and I would want that the query to pull those records that have those dates.

    Thanks,
    Debit19

  6. #6
    Join Date
    Jan 2005
    Posts
    23
    Does anyone have any further help on this one. I have solved another problem I had but I am working on this portion now. Any help would be appreciated.

    Thanks,
    Debit19

  7. #7
    Join Date
    Oct 2003
    Posts
    6

    OR statement should work

    Back to what "healdem" said. If you use an OR statement it will allow the range of dates for "any" of the columns. If you use and AND statement it means every column must meet your date range criteria. I've tried this and it works fine. Since you are only getting one blank record as your result, it appears you have and "AND" statement--every column must meet your date range. Leaving your query as is, try entering a date range that is very broad that every column will fall within. You should get a result.

    Do you want "any" of your fields to fall within the given date range or do you want "every" date field to fall within the given date range?

    dyount

  8. #8
    Join Date
    Jan 2005
    Posts
    23
    Thanks for your response. I think what my problem is that I have the days of the week on each record. I am trying to pull individual dates from those records but when one of those dates fit the criteria it pulls the whole record. Is there a way to separate the line numbers within a record and extract them as if they were individual records or do I physically have to redesign the form so that entry is one line per record. Hope this makes sense.

    Thanks,
    Debit19

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I don't see any significant justification that supports having seven date fields, instead of one.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Jan 2005
    Posts
    23
    r123456,
    Thanks for the reply. Maybe if I explain a little better you can see what I am trying to do. I have a time sheet that has seven days on it ex: Sun = Day1, Mon=Day2, etc...

    Each time sheet can have up to 7 entries on it. The reason is that I would like to summarize the total hours worked on the form when the user finishes enter the timesheet. Then they would go on to the next time sheet. What I want to query is a beg date and ending date and have it check all of the dates for those dates in the query and have it pull only those lines that refer to that date. So one record could have the dates 01/01/05 thru 01/07/05, but when I query the dates I may enter only the dates 01/01/05 thru 01/02/05 and I only want those lines that the date falls into that criteria. Can this be done?

    Debit19

  11. #11
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    I'm not really sure if I get you right .. but as it seems to me you want to calculate your beg and end date from your 7 fields on your form
    you need to do a bit of coding to calc the two dates and use them to run the query

    there is a little trick you might use .. in excel there's a max function .. i coded something similar for access

    Public Function getMax(ParamArray Values()) As Double
    Dim i As Integer
    Dim Result As Double
    For i = 0 To UBound(Values)
    If Values(i) > Result Then Result = Values(i)
    Next
    End Function

    pass all your fields to that routine and you'll get the highest date
    just check how it handles null values

Posting Permissions

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