none
parameters in crosstab queries with date values RRS feed

  • Question

  • I have a crosstab query with data and I want to run my query such that it opens a parameter dialog prompting the user to enter the date. The query will show all the records matching the date value provided. I've tried [Enter the Date] in the crosstab query criteria and I got an error "the microsoft access database engine could not recognize the [0000] as a valid field name or expression" and when I try defining it explicitly using the parameter in the show/hide group and typing [Enter the Date ], data type: date/time, it runs the query and displays all the records instead of displaying only those records matching the entered date. is there a way I can make it work correctly? How?

    SQL for the crosstab query:

    TRANSFORM Avg(qryFullNames.MARKS) AS AvgOfMARKS
    SELECT qryFullNames.ADM_NO, qryFullNames.[STUDENT NAME]
    FROM qryFullNames
    GROUP BY qryFullNames.ADM_NO, qryFullNames.[STUDENT NAME], qryFullNames.EXAM_DATE
    PIVOT qryFullNames.LastOfSUBJECT_NAME;

    Any help will be highly appreciated.

    Tuesday, May 17, 2016 2:04 PM

Answers

  • Hi. You could try something like:

    PARAMETERS [Enter the Date] DateTime;
    TRANSFORM Avg(qryFullName.MARKS) AS AvgOfMARKS
    SELECT qryFullNames.ADM_NO, qryFullNames.[STUDENT NAME]
    FROM qryFullNames
    WHERE qryFullNames.[EXAM_DATE]=[Enter the Date]
    GROUP BY qryFullNames.ADM_NO, qryFullNames.[STUDENT NAME], qryFullNames.EXAM_DATE
    PIVOT qryFullNames.LastOfSUBJECT_NAME;

    (untested)
    Hope it helps...

    • Marked as answer by GKiprotich Tuesday, May 24, 2016 5:22 AM
    Tuesday, May 17, 2016 3:44 PM

All replies

  • Hi. You could try something like:

    PARAMETERS [Enter the Date] DateTime;
    TRANSFORM Avg(qryFullName.MARKS) AS AvgOfMARKS
    SELECT qryFullNames.ADM_NO, qryFullNames.[STUDENT NAME]
    FROM qryFullNames
    WHERE qryFullNames.[EXAM_DATE]=[Enter the Date]
    GROUP BY qryFullNames.ADM_NO, qryFullNames.[STUDENT NAME], qryFullNames.EXAM_DATE
    PIVOT qryFullNames.LastOfSUBJECT_NAME;

    (untested)
    Hope it helps...

    • Marked as answer by GKiprotich Tuesday, May 24, 2016 5:22 AM
    Tuesday, May 17, 2016 3:44 PM
  • Hi parameters,

    Did the suggestion from DB work for you?

    Based on your description, you want to query records according [Enter the Date] parameter? Am I right? If so, I found there is no where statement in your query. As the query from DB, if you want to filter records, you need to add where statement.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, May 18, 2016 7:18 AM
  • Hi, I did exactly as DB Guy suggested and I received an error message:

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

    may be I missed out on something?

    Regards,

    Kiprotich.

    Wednesday, May 18, 2016 10:15 AM
  • Hi, I did exactly as DB Guy suggested and I received an error message:

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

    may be I missed out on something?

    Regards,

    Kiprotich.


    First thing I would check is make sure there are no Null values in the Exam Date field.
    Wednesday, May 18, 2016 2:45 PM
  • When I use a crosstab query that needs a parameter I put the parameter in the lowest level. In this case it would be in qryFullNames. And be sure to declare the parameter in qryFullName as DB Guy showed.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, May 18, 2016 3:19 PM
  • Hi GKiprotich,

    If your issue still does not resolve, it would be helpful if you could share us your table design and simple test records.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, May 19, 2016 5:28 AM
  • I've tried to share my table design but my account has not been verified. It does not let me upload it. Sorry for the inconvenience.

    Regards,

    Kiprotich.

    Friday, May 20, 2016 7:30 AM
  • Hi Bill Mosca,

    I tried applying what you suggested, but does not work for date. I tried testing it with other fields with data type text and it works. I don't what to do.

    Regards,

    Kiprotich.

    Friday, May 20, 2016 7:50 AM
  • Can you post the SQL for both qryFullNames and the crosstab? Maybe we can spot something.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, May 20, 2016 3:28 PM
  • Hi GKiprotich,

    >> I've tried [Enter the Date] in the crosstab query criteria and I got an error "the microsoft access database engine could not recognize the [0000] as a valid field name or expression"

    Which value did you enter for [Enter the Date] something like “2016-5-23”, “20160523” or anything else? I suggest you replace [Enter the Date] with your entered value, and put the crosstab query to check the result.

    For sharing table design and data, you could upload them to OneDrive and share us the link here.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, May 23, 2016 6:13 AM
  • Hi. You could try something like:

    PARAMETERS [Enter the Date] DateTime;
    TRANSFORM Avg(qryFullName.MARKS) AS AvgOfMARKS
    SELECT qryFullNames.ADM_NO, qryFullNames.[STUDENT NAME]
    FROM qryFullNames
    WHERE qryFullNames.[EXAM_DATE]=[Enter the Date]
    GROUP BY qryFullNames.ADM_NO, qryFullNames.[STUDENT NAME], qryFullNames.EXAM_DATE
    PIVOT qryFullNames.LastOfSUBJECT_NAME;

    (untested)
    Hope it helps...

    Thanks for your help. I managed to solve it. I went back to check my tables and found that the EXAM_DATE field was of data type text, so I corrected it to date/time and now it is working.

    Thanks again.

    GKiprotich

    Tuesday, May 24, 2016 5:27 AM