Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query-by-form problem (a2002)

    I'm having trouble making dates work in my query-by-form. I've made two copies of one of the queries I'm having trouble with: the first has date parameters I enter at the time I run the query (works) and the second pulls the same date values from an open form (doesn't work). Can you locate the problem? It's got to be something very basic but I'm just not seeing it.

    Works: Returns 853 records
    PARAMETERS [Enter start date] DateTime, [Enter end date] DateTime;
    SELECT tblConsProg.ConsProgID, tblConsProg.ExitDate, tblConsProg.CloseStaffDate
    FROM tblConsProg
    WHERE (((tblConsProg.ExitDate) Between [Enter start date] And [Enter end date])) OR (((tblConsProg.CloseStaffDate) Between [Enter start date] And [Enter end date]));

    Doesn

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

    Re: query-by-form problem (a2002)

    The value in a control on a form may not be a true date/time, especially if it was just typed into an unbound control. You could try this:

    WHERE (((tblConsProg.ExitDate) Between CDate([Forms]![frmOtcmMsrmntRptCrtr]![txtEnterStartDate]) And CDate([Forms]![frmOtcmMsrmntRptCrtr]![txtEnterEndDate]))) OR (((tblConsProg.CloseStaffDate) Between CDate([Forms]![frmOtcmMsrmntRptCrtr]![txtEnterStartDate]) And CDate([Forms]![frmOtcmMsrmntRptCrtr]![txtEnterEndDate])));
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query-by-form problem (a2002)

    I gave that a shot but got the following message: "This expression is typed incorrectly, or it is too complex to be evaluated." Is there another way to accomplish this?

    esw

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

    Re: query-by-form problem (a2002)

    Try wrapping the criteria references to start and end dates in the CDate function in the query grid instead of in the SQL. Sometimes it's hard to get the parens right in the SQL itself, especially since you don't need all of those the query designer puts in . Are you sure you're comparing apples to apples? Is the table field really a date/time field? Are the form controls unbound? You aren't working with a SQL Server table are you?

    I just reread your original post. Try changing the parameters to Text instead of making them date/time in the query. Unless the form controls are bound, they aren't date/time.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query-by-form problem (a2002)

    That did it. Thanks!

    esw

Posting Permissions

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