Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Proper Query Syntax? (a97)

    In the attached DB, in the Query qry_Undup_Count_By_Muni_Student_Detail_1

    I
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need Proper Query Syntax? (a97)

    <P ID="edit" class=small>(Edited by patt on 24-Jan-04 07:41. Change reply to mention crosstab queries)</P>For a start, what is the following doing?
    >>Or Like [Enter Start Date: Example m/d/y, or OK for All] & "*") <<

    It depends on where you are running the query from. If running from VBA code you will probably have to change the SQL of the bottom query on the fly. From memory I don't think you can use a parameter in a query that is referenced by a crosstab query.

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

    Re: Need Proper Query Syntax? (a97)

    You need to add the identical expression, complete with brackets, in the query parameters. That will allow the crosstab to deal with the parameter query. Just having the parameter in the criteria isn't enough. One suggestion I would make in the criteria, though, is to use a CDate function around the <= and >= expression. Otherwise, you're going to need to concatenate # signs around the date string. In other words >=CDate([Enter Start Date: Example m/d/y, or OK for All] ). I would also suggest you change the prompt to say "click OK" rather than just "OK" or you're going to have someone enter "OK" instead of a date. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need Proper Query Syntax? (a97)

    As Charlotte says you need to add the PARAMETERS line in the query qry_Undup_Count_By_Muni_Student_Detail_1and the query should probably be something like:

    PARAMETERS [Enter Start Date: Example m/d/y, or OK for All] DateTime, [Enter End Date: Example m/d/y, or OK for All] DateTime;
    SELECT tblStudentReg.lngStudentID, Format([dtmRegDate],"yyyy") AS [Year]
    FROM tblStudents INNER JOIN tblStudentReg ON tblStudents.lngStudentID = tblStudentReg.lngStudentID
    WHERE (((tblStudentReg.dtmRegDate)>=[Enter Start Date: Example m/d/y, or OK for All] And (tblStudentReg.dtmRegDate)<=[Enter End Date: Example m/d/y, or OK for All]) AND ((tblStudents.strRecordType)="applicant") AND (([Enter Start Date: Example m/d/y, or OK for All]) Is Not Null) AND (([Enter End Date: Example m/d/y, or OK for All]) Is Not Null)) OR (((tblStudentReg.dtmRegDate)>=[Enter Start Date: Example m/d/y, or OK for All]) AND ((tblStudents.strRecordType)="applicant") AND (([Enter Start Date: Example m/d/y, or OK for All]) Is Not Null) AND (([Enter End Date: Example m/d/y, or OK for All]) Is Null)) OR (((tblStudentReg.dtmRegDate)<=[Enter End Date: Example m/d/y, or OK for All]) AND ((tblStudents.strRecordType)="applicant") AND (([Enter Start Date: Example m/d/y, or OK for All]) Is Null) AND (([Enter End Date: Example m/d/y, or OK for All]) Is Not Null)) OR (((tblStudents.strRecordType)="applicant") AND (([Enter Start Date: Example m/d/y, or OK for All]) Is Null) AND (([Enter End Date: Example m/d/y, or OK for All]) Is Null))
    GROUP BY tblStudentReg.lngStudentID, Format([dtmRegDate],"yyyy");

    I don't believe that you can use Like * for a date field, you need to check if the parameter is null or not null.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Proper Query Syntax? (a97)

    Thank you Charlotte & Pat

    Works perfectly

    It also seems to work as follows:

    PARAMETERS [Enter Start Date: Example m/d/y, or OK for All] DateTime, [Enter End Date: Example m/d/y, or OK for All] DateTime;
    SELECT tblStudentReg.lngStudentID, Format([dtmRegDate],"yyyy") AS Year
    FROM tblStudents INNER JOIN tblStudentReg ON tblStudents.lngStudentID = tblStudentReg.lngStudentID
    WHERE ((((tblStudentReg.dtmRegDate)>=[Enter Start Date: Example m/d/y, or OK for All] Or (tblStudentReg.dtmRegDate) Like [Enter Start Date: Example m/d/y, or OK for All] & "*") And ((tblStudentReg.dtmRegDate)<=[Enter End Date: Example m/d/y, or OK for All] Or (tblStudentReg.dtmRegDate) Like [Enter End Date: Example m/d/y, or OK for All] & "*")) AND ((tblStudents.strRecordType)="applicant"))
    GROUP BY tblStudentReg.lngStudentID, Format([dtmRegDate],"yyyy");

    Can you point to Access Help on Parameters, could not find much depth on the subject?

    Charlotte, your point taken
    It seems I am limited by number of characters available on dialog box (45 char?)

    Enter Parameter Value
    Enter Start Date: Example m/d/y, or OK for All

    Enter Start Date: Ex. m/d/y, or Click OK for All

    John

Posting Permissions

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