Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameters in Queries

    I have been trying to create a query that will as the user to input a date that they would like searched. In the Design View I click in the Criteria cell and in brackets enter the prompt. Then I go to query, parameters, and select date/time. But when I run the query it returns nothing. If I remove the prompts and parameters it will return all dates that have data.

    What am I doing wrong? Thanks.

  2. #2
    Mike B
    Guest

    Re: Parameters in Queries

    do worry about setting the parameters for the query....just set the prompts....HTH

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameters in Queries

    You only need to set the parameters explicitly if this query will be called in a higher level query. In that case, you need to add the expression in brackets to the parameter and set the type. Otherwise, just put the parameter expression in the critiera field. However, you should be aware that dates are a little tricky because the formatting for dates can prevent matches with the criteria. The simplest way I've found to handle it is to format the date field in the query as a short date (unless, of course, you need to compare the time portion as well). Then I wrap the parameter expression in a CDate function to make sure it gets interpreted as a date. That usually clears up any problems.

    Of course, if no records have exactly the date entered, the query won't return any records, so you might want to think about what you really need to do here. Do you want only records that match that date, or records >= that date or what?
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters in Queries

    Thanks for the help. Yes I do want a specific date. The tables that I am working with are linked to the original tables that are in the program. When I checked the format it tells me that it is set for Short Date.

    Being very stupid about Access can you give me an example of how to write the formula that you suggest?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameters in Queries

    When you check the format in the query or in the table? Short Date is a display format, so the date is actually stored in the normal fashion but displayed as a short date. In a query, you have to click on the date field in the query grid, bring up its properties dialog, and make sure the format there is set to Short Date as well. Then in the criteria expression under that field, use something like CDate([Enter Date]), which will convert the passed parameter to a date that it can compare to the value in the date field.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters in Queries

    I checked properties in the query for the date cell and it didn't specify anything. So I changed it to short date. Ran the query and without any parameters it still returns all records in the database as before. So I added CDate([Enter Date]) and ran it again. It prompted for a date and then returned no records. I tried using several different expressions of the date and still get no records (3/2/01, 03/02/01, 3/02/01, etc.).

    Thanks for all the help so far, just don't give up on me.

    ;-)

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters in Queries

    I have an observation that may or may not be relevant.
    I had a similar problem with a query in Access 2000 (no sr

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameters in Queries

    Are your dates formatted with 4-digit years? If so, you may have to enter 4-digit years in the criteria.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameters in Queries

    Well, it appears to work on mine, but it's possible that there are no time stamps on any of my dates.

    An alternative is to take out the CDate in the criteria and add a Format(DateField,"Short Date") function to the field expression. Then you would be matching strings, which might be easier.
    Charlotte

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters in Queries

    Dear Charlotte,

    I am puzzled by this so I've posted an example.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters in Queries

    <P ID="nt"><font size=-1>(No Text)</font>
    Attached Files Attached Files

  12. #12
    Mike B
    Guest

    Re: Parameters in Queries

    have a look at the returned zip

  13. #13
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters in Queries

    The original table does have the date and time of the sale. So in Rupert's suggestion is this what is causing me to not be able to recall specific dates?

  14. #14
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters in Queries

    I get a "Data type mismatch in Criteria Expression" error when I used Format(DateField,"Short Date")

    The original table does contain both time and date in this cell. If I click on the date in the cell it then will display the time of the sale. I am building the query from a linked table. Can I make some change to the linked table for the DateTime column without effecting the original database?

    Thanks,

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameters in Queries

    Why don't you post the SQL you're using.

    I suspect you're putting the expression in the criteria field, when what I meant was to put it in the grid field, where DateField represents the name of the date field in the query. What you're doing it telling the query to pretend that all it has in this field is a string containing a short date. Then whatever you type into the parameter in short date format in the criteria should not cause a type mismatch.
    Charlotte

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
  •