Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Wyoming, USA
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    no data returned in query (2003)

    Hello again,

    I am creating a parameter query in a database that all tables are linked to another database. The reason I have linked all the tables is because I don't want to do anything to the origianl database that might crash my cash register system. I'm an Access neophyte.

    I can get the query to return data as long as a I don't specify what date or between what dates I want to search. The DateTime field that I am searching tracks all sales that are made by date and time. The linked table that I am actually querying i have tried to change the format tot short date. If I don't search for a specific date or between dates returns just the short date data. If I use criteria I get zero returned data. I have tried using the DateValue function and get the same results.

    Any help would be appreciated.

    TIA

    Bret

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

    Re: no data returned in query (2003)

    Formatting a field doesn't change the underlying value, only the way it is displayed and printed. If your date/time field contains both date and time, the time will still be there even if you don't see it because of the Short Date format.

    One way to get around it is to use criteria in the form

    Between #03/24/2005# And #03/25/2005#

    if you want to retrieve records for today (03/24/2005). Note the # around the date values. Another possibility is to include a calculated column in the query:

    TheDate: DateValue([DateTimeField])

    and use #03/24/2005# as criteria.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Wyoming, USA
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no data returned in query (2003)

    Thanks Hans,

    What do I do if I want to create a parameter qurery that will allow me to change the search criteria every time I run the query? Another words by operator input.

    Thanks again,

    Bret

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

    Re: no data returned in query (2003)

    Do you want to let the user specify a single date or a range of dates (from ... to ...)?

  5. #5
    Star Lounger
    Join Date
    Oct 2003
    Location
    Wyoming, USA
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no data returned in query (2003)

    I am thinking a range would be the best. That way they can do either. As soon as I put Between[Enter Start Date] And{Enter End Date] in the criteria line and then run the query it will not have any data returned when I enter a start and end date. I have checked and there is data for the dates entered.

    Bret

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

    Re: no data returned in query (2003)

    That's strange - a Between ... And ... condition should work. You could try the following:
    - Open the query in design view.
    - Select Query | Parameters...
    - Enter [Enter Start Date] in the first row, exactly the way it is used in the criteria line, and select Date/Time from the Data Type dropdown list.
    - Enter [Enter End Date] in the second row, exactly the way it is used in the criteria line, and select Date/Time from the Data Type dropdown list.
    - Click OK.

Posting Permissions

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