Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Query (2000)

    I have a crosstab query created and am trying to define criteria. The criteria I've tried is a between function in a date value from fields in a form. It can't recognize the field in the form that I've directed it to look at for a value. HELP???

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

    Re: Crosstab Query (2000)

    I'm not sure I understand your description entirely. If you have criteria for a date field to be between two dates specified on a form, it should look like this, with the appropriate names substituted:

    Between [Forms]![frmCriteria]![txtStartDate] And [Forms]![frmCriteria]![txtEndDate]

    If that doesn't work, try declaring the parameters explicitly:
    - Open the query in design view.
    - Select Query | Parameters...
    - Under Parameter, enter [Forms]![frmCriteria]![txtStartDate] and under Data Type, select Date/Time.
    - In the second row, enter [Forms]![frmCriteria]![txtEndDate] and select Date/Time.
    - Click OK.

    Note: you must enter the parameters exactly the same way they are used in the criteria for the query.

  3. #3
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query (2000)

    Thanks for the recommendation which I've tried. I now get the error invalid bracketing. Any suggestions there? Does it make a difference that this is a crosstab query or should you be able to still use this type of criteria?

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

    Re: Crosstab Query (2000)

    It is certainly possible to use this kind of criteria in a crosstab query. "Bracketing Error" probably means that you misplaced a square bracket [ or ]. Check very carefully that you use criteria of the form

    <big><code>Between [Forms]![frmCriteria]![txtStartDate] And [Forms]![frmCriteria]![txtEndDate]</code></big>

    and that the parameters are specified the same way:

    <big><code>[Forms]![frmCriteria]![txtStartDate]</code></big>
    <big><code>[Forms]![frmCriteria]![txtEndDate]</code></big>

    Pay attention to the placement of the brackets and ! characters.

  5. #5
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query (2000)

    I decided to go back to the drawing board with the criteria and parameters and was able to bypass that error however I now get this error (which I was getting before your suggestion to put in explicit parameters):
    The Microsoft Jet database engine does not recognize '[Forms]![PayrollDates]![beginning]' as a valid field name or expression. I've checked the form and it appears ok. It is not linked to a query, it just has an SQL filter on the form. HELP?
    Thanks, Kari

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

    Re: Crosstab Query (2000)

    Have you declared [Forms]![PayrollDates]![beginning] as a parameter in Query | Parameters? This is essential when creating a crosstab parameter query. If you have, check the form name and control name once more.

  7. #7
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query (2000)

    Yes, I have done that. Does it make a difference if the field that I've trying to pull from the form is a list box?

    Thanks, Kari

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

    Re: Crosstab Query (2000)

    I just checked. It works fine with a list box too.

    If you can't solve this, you can post a stripped-down copy of the database if you like.
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
    <LI>Attach the zip file to a reply.[/list]That would enable Loungers to look at the problem directly

  9. #9
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query (2000)

    Ok. I have attached the database as a zip. Can you look at it and let me know what your findings are? Thanks very much. Kari
    Attached Files Attached Files

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

    Re: Crosstab Query (2000)

    Computer software in general, and Access in particular, is not very forgiving. If you look very carefully at the Query | Parameters... dialog, you will see that you have put a space before [Forms]!... in both parameters. This is enough to make Access fail to recognize the parameters! <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> If you remove those leading spaces, the query will work.
    Attached Images Attached Images
    • File Type: png x.PNG (1.6 KB, 0 views)

  11. #11
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query (2000)

    Oh Thank you so much! Your a genius. Can you steer me in the right direction to create a report from this crosstab query? It would change every time it runs so I wasn't sure how to create the report once without recreating each payday! Thanks, Kari

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

    Re: Crosstab Query (2000)

    A report based on a crosstab query is special, since the columns may be different every time you open the report. To make the report dynamic requires a good bit of programming in VBA. I don't know how familiar you are with programming in Access; I wouldn't recommend trying on dynamic crosstab reports as a first excercise.
    To get an idea of how it's done, you might have a look at the databases attached to <post#=35485>post 35485</post#> and <post#=134439>post 134439</post#>. They demonstrate two different approaches to creating reports based on crosstab queries with dynamic column headings. Another example can be found in <post#=248210>post 248210</post#>.

  13. #13
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query (2000)

    I'm hardly familiar with VB so any other "easy" recommendation. Can I copy one of those postings and update the names of my forms/controls? UGGGHHH. We have an in-house IT guy however he is too busy and I just want to figure it out. I've appreciated all your help thus far.

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

    Re: Crosstab Query (2000)

    If you're lucky, you can copy one of those reports into your database and get it to work with minor tweaking, but it depends on how the query and report should look.

    Questions, questions:
    - Is the query you're going to use just like the one you posted, or more complicated?
    - Do you want totals per row in the report, and/or totals per column?
    - What is the maximum number of columns you expect? Are you going to group the dates by week, month, quarter or year?

  15. #15
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query (2000)

    Hi Hans!
    Yes, the report will be based upon the query that I posted. No totals will be necessary and the maximum columns ever would be 10. The columns won't need to exceed an inch in width either. Can you work with this? It is really a fairly simple report. I'm hopeful you can help and then I will be able to retain for future reference. Thanks so much. Kari

Page 1 of 2 12 LastLast

Posting Permissions

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