Results 1 to 3 of 3
  1. #1
    cphelps is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2013
    Location
    Australia
    Posts
    6

    Question Parameter query error message when wildcard used with Between statement on date field

    I have a query that is used as a data source for a report in a database that tracks safety incidents. The query/report displays data where it is prompted for a project number and the Incident Date between a range of two dates (i.e. start and end dates). The SQL code for the query is as follows:



    Code:
    SELECT qryIncidentDetails.[Job No], qryIncidentDetails.[Incident Number], qryIncidentDetails.[Issued to], qryIncidentDetails.Severity, qryIncidentDetails.Safety, qryIncidentDetails.Environmental, qryIncidentDetails.Quality, qryIncidentDetails.Complaint, qryIncidentDetails.[Near hit], qryIncidentDetails.Other, qryIncidentDetails.Location, qryIncidentDetails.[Incident Date], qryIncidentDetails.Detail, qryIncidentDetails.[Incident Description], qryIncidentDetails.Reporter, qryIncidentDetails.[Imediate Action], qryIncidentDetails.[Completion Due], qryIncidentDetails.[Completed Date], qryIncidentDetails.Completer, qryIncidentDetails.[Root Cause], qryIncidentDetails.[Long Term Action], qryIncidentDetails.[Responsible 1], qryIncidentDetails.[Responsible 2], qryIncidentDetails.[Date 1], qryIncidentDetails.[Date 2], qryIncidentDetails.[Long Term Due], qryIncidentDetails.[Long Term Completed], qryIncidentDetails.Verification, qryIncidentDetails.Verifier, qryIncidentDetails.[Verification Date], qryIncidentDetails.[Closed/Open], qryIncidentDetails.Injured, qryIncidentDetails.Comments, qryIncidentDetails.Keyword2, qryIncidentDetails.Keyword1, qryIncidentDetails.[Keyword 1], qryIncidentDetails.[Keyword 2], qryIncidentDetails.[Incident Time], qryIncidentDetails.Witness, qryIncidentDetails.[Entry Date], qryIncidentDetails.Summary, qryIncidentDetails.[Project Manager], qryIncidentDetails.Photos, qryIncidentDetails.Investigation
    FROM qryIncidentDetails
    WHERE (((qryIncidentDetails.[Job No]) Like [Enter Job No]) AND ((qryIncidentDetails.[Incident Date]) Between [Enter From Date] And [Enter To Date]))
    ORDER BY qryIncidentDetails.[Incident Date];
    The query and subsequent report run fine if a project number, start date and end date are entered. The query will also run if the project number is wildcarded (*) with the start and end dates entered.

    However if the Start and End dates fields are wildcarded the following error message is received:

    The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complex elements. Try simplifying the expression by assigning parts of the expression to variables.

    I have Googled looking for an answer. I am hoping that somebody here may be able to guide me to an answer or advice on how to handle this.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I NEVER use query parameter input popups. Can't validate user entry. Better to have entry on form and refer to controls on form as parameters. Then date criteria:

    Between Nz(tbxFromDate,#1/1/1900#) And Nz(tbxEndDate,#12/31/2900#)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    cphelps is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2013
    Location
    Australia
    Posts
    6
    Thanks for making this suggestion. I will try it out and get back to let you know whether this suits our purpose.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-18-2013, 11:35 AM
  2. Query parameter prompt - Format message
    By daved292 in forum Queries
    Replies: 2
    Last Post: 06-08-2012, 11:09 AM
  3. Date Range with Error Message Box
    By Huddle in forum Access
    Replies: 4
    Last Post: 05-11-2012, 01:33 PM
  4. If OR statement (maybe a wildcard?)
    By Lorlai in forum Queries
    Replies: 5
    Last Post: 07-27-2011, 10:21 AM
  5. Parameter Query & Wildcard
    By Rick West in forum Queries
    Replies: 8
    Last Post: 12-29-2009, 10:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums