Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter query with > (2003)

    I have a query that searches for dates older than " ". It works fine until I make it into a parameter query with the criteria >[ ]. Though I am prompt the query returns all records. Interestingly. if I enter '>' at the prompt followed by the number of days it intermittently returns the appropriate records
    Any ideas

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

    Re: Parameter query with > (2003)

    I'm not sure what you're doing here. What is older than " "?
    What column contains the criteria? A date field, or a calculated field, or...?

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter query with > (2003)

    You may be defining the parameter incorrectly.

    In the criteria cell of the appropriate field in your query, type something like this: >=[Specify the earliest date to view:] . Then run the query and ttpe out the earliest date in the prompt.

    NB: Ensure that the date format matches the format stored in your database field.
    Regards,
    Rudi

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query with > (2003)

    I have an intake contact form which records the date of contact(visitdate). I want the query to return a list of contacts which have not been heard from in more than e.g 7 days or 28 days. I have the calculated control "Date()-[visitdate. In this control criteria I want to be able to query >7 or >28 days. It works fine for that input. If I use the parameteer query criteria >[number of days since contact] it does not work if I enter 7 or >7.

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

    Re: Parameter query with > (2003)

    You must do two things:
    1) Add a prompt text for the parameter, for example [Older than (days)]
    2) Declare the parameter as a number:
    - In design view, select Query | Parameters...
    - In the Parameter column, enter the parameter text exactly as in the criteria line, e.g. Older than (days)
    - In the Data type column, select Long Integer.
    - Click OK.

    A parameter is a string by default. If you don't specify that it is a number, the comparison will be alphanumeric instead of numeric.

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query with > (2003)

    I did what you suggested and entered '>' some number of days. The problem seems to be the >. Now I get an error message that says " the value you entered is not valid for this field. Can the parameters be split to an alphanumeric '>' and a numeric component?

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

    Re: Parameter query with > (2003)

    The criteria should look like this

    >[Some number of days]

    where "Some number of days" (without the quotes) is the text to be displayed in the parameter prompt.

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query with > (2003)

    I thought that I had done just that but I guess that I hadn't . It is now working well. Thanks

    An additional hurdle has surfaced. The records that are returned are all older than the # of days specified in the parameter query. Now there are multiple instances of the same customer for different dates (though all greater than the number of days above). Is there any way to select only the last date for each customer from the recordset. From a practical point I would like to know the most recent contact with a customer that is greater than a certain number of days ago

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

    Re: Parameter query with > (2003)

    Create a query with
    - The field identifying the customer.
    - The date field.
    - The calculated field returning the number of days.

    Add the criteria on the calculated field and declare the parameter.

    Select View | Totals or click the Totals button on the toolbar.
    Leave the Total option for the customer field as the default Group By.
    Set the Total option for the date field to Max.
    Set the Total option for the calculated field to Where. This will automatically clear the Show check box.

    This query will return the most recent date for each customer that is more than the specified number of days ago.

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query with > (2003)

    Yes but
    If your customer was contacted on three dates 1/1 1/10 and 1/20 on you ran than query for >1 days it would return 1/20. But if you ran it for >2days it would return 1/10.
    What I was trying to accomplish was for the query to return a set of records only if the customer had not been contact for e.g 7 days. Of those records I would like to select only the last(i.e. most recent date) for that customers contacts.

    Bottom line I would like a list of customers that have not been contacted for a given length of time(>" ") and want to know the last time that they were contacted

    Again many thanks

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

    Re: Parameter query with > (2003)

    OK, that is a different question than you originally asked, so you need a different query.

    Create a query with
    - The field identifying the customer.
    - The date field.
    - Select View | Totals or click the Totals button on the toolbar.
    - Leave the Total option for the customer field as the default Group By.
    - Set the Total option for the date field to Max.
    - Enter the following in the criteria line for the date field:

    >Date()-[Some number of days]

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query with > (2003)

    I apologize for the repost. It came out of temporal sequence and I was concerned that it would be overlooked

    Yes but
    If your customer was contacted on three dates 1/1 1/10 and 1/20 on you ran the query for >1 days it would return 1/20. But if you ran it for >2days it would return 1/10.
    What I was trying to accomplish was for the query to return a set of records that would identify each customer that had not been contacted for e.g 7 days. Of those records I would like to select only the last(i.e. most recent date) for that customers contacts.

    Bottom line I would like a list of customers that have not been contacted for a given length of time(>" ") and want to know the last time that they were contacted

    Again many thanks

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

    Re: Parameter query with > (2003)

    I'm confused. That is the question I tried to answer.

  14. #14
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query with > (2003)

    Me too The query is as follows if I am understanding you properly

    PARAMETERS [some number of days] Long;
    SELECT Patients.First, Patients.Name, Max(Visit.Visit) AS MaxOfVisit, Date()-[visit] AS Expr1
    FROM (Patients INNER JOIN Demographics ON Patients.PatientID = Demographics.PatientID) INNER JOIN Visit ON (Patients.PatientID = Visit.PatientID) AND (Patients.PatientID = Visit.PatientID)
    GROUP BY Patients.First, Patients.Name, Date()-[visit]
    HAVING (((Max(Visit.Visit))>Date()-[Some number of days]))
    ORDER BY Patients.Name;


    If I have a table with multiple customers and dates this query returns the same customer on multiple dates if they are > than the entered number I have two concerns
    1-I want a unigue customer list that is, each customer is listed only once.
    2-the query should return results pertinent to the most recent contact. I only need to know if 'I haven't' made contact in the past 'number of says' For example did I speak to customer a within the past wee, or month etc

    I'm sorry if I am not clear but I do appreciated your efforts

    Thanks
    Nebbia

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

    Re: Parameter query with > (2003)

    Oops, I made a mistake, sorry about that - the > in the criteria should have been <

    You don't need the Expr: Date()-[visit] field in this query, this is already handled by the criteria on the Visit field.. Also, you don't seem to use the Demographics table in the query, so you should remove it from the query. The SQL would look like this:

    SELECT Patients.Name, Patients.First, Max(Visits.Visit) AS LatestVisit
    FROM Patients INNER JOIN Visits ON Patients.PatientID = Visits.PatientID
    GROUP BY Patients.Name, Patients.First
    HAVING (((Max(Visits.Visit))<Date()-[Some number of days]));

    (By placing Name before First, the query is automatically sorted on Name, then on First).

    See attached demo.

Page 1 of 2 12 LastLast

Posting Permissions

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