Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strange Query Behavior (2002/SP3)

    I am getting baffling results from a query. Table is linked to SQL where the field is varchar though the information is a date in "mm/dd/yyyy hh:mm:ss XM" format. In Access I create a parameter query to ask for a starting date and end date. It works but data is not correct so I thought I'd use the CDate function in the query to convert the field to a date/time format followed by the parameter on that new field. No dice. Even though I put in a date range for this year, it still shows some records from last year in the results after running the query. Any help would be appreciated.

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

    Re: Strange Query Behavior (2002/SP3)

    You MUST use a calculated column in the query that uses CDate or DateValue to convert the varchar field to a date.

    Try declaring the parameters explicitly as date/time values:
    - Open the query in design view.
    - Select Query | Parameters...
    - Enter the starting date parameter EXACTLY as it occurs in the criteria line, including the square brackets, e.g. [Enter Start Date]
    - Set the data type to Date/Time.
    - Enter the end date parameter EXACTLY as it occurs in the criteria line, including the square brackets, e.g. [Enter End Date]
    - Set the data type to Date/Time.
    - Click OK.

  3. #3
    New Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Query Behavior (2002/SP3)

    Hans,

    You da Man!!! That worked. I GREATLY APPRECIATE YOU VERY MUCH. I've only beaten my head most of the day on this.

    Thanks again,
    Rob

Posting Permissions

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