Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Criteria for a crosstab query (97)

    I have four queries that are based on one another. I was going to use the first query to limit the records from a table. The next 2 crosstab queries are based and the first query. The last query puts together the 2 crosstab queries. The last query comes out just like I want it but it returns all the records.
    So I tried to limit the records. In the first query on the criteria line I have 'not like i*' and also on the same line I have '[enter fiscal month]'. When I double click on the first query it asks me for the month, I enter it and it returns the correct result. But when I double click the 4th query, I get an error - "The Microsoft Jet database does not recognize '[enter fiscal month]' as a valid field name or expression." If I take out [enter fiscal month] out of the first query, the fourth query works fine and takes out all the i*. If I put a 1 in the criteria line for the month in the first query, I get one of the other fields in the query is not a valid field name or expression.
    If I put [enter fiscal month] in the 4th query, i get "The Microsoft Jet database does not recognize '[enter fiscal month]' as a valid field name or expression." If I put a number in the 4th query it works and returns the correct records.
    I would like to have the user enter a number in the pop up box instead of going into the 4th query and change the number.
    Any ideas?
    All the best to you.
    John

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

    Re: Criteria for a crosstab query (97)

    Put the parameter [Enter Fiscal Month] in the first query - it is most efficient to limit the records at the start. Declare this parameter explicitly in the first query and in the queries based on it: select Query | Parameters..., enter [Enter Fiscal Month] in the parameter box and Integer in the Data Type dropdown list.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Criteria for a crosstab query (97)

    I did as instructed (I hope). I put the [enter fiscal month] in all 4 queries in the query|parameters selection. Now when I try to open the 4th query it says that it does not recognize the 4th query as a valid field name or expression.
    When I went to design mode in the 4th query, it asked me for the fiscal month twice. Is that normal?
    Any thoughts???
    Best Regards,
    John

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

    Re: Criteria for a crosstab query (97)

    I tested this on some queries I built earlier on to test the fiscal month and week functions. Before explicitly declaring the parameter, I got the "not recognized as a valid field name or expression" error message, but it worked fine after declaring the parameter. Check very carefully that the spelling is absolutely identical in all cases - a tiny difference will throw Access off.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Criteria for a crosstab query (97)

    Hi Hans-
    I check all the spelling and it looks good. I did find that it works for fiscal month 12 but not fiscal month 1. Query 1, 2 , and 3 work with fiscal month 1. I am stumped. Attached is a stripped down version of the database. If you could look at it when you have a chance I would appreciate it.
    Thanks,
    John
    Attached Files Attached Files

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

    Re: Criteria for a crosstab query (97)

    The problem here is that the column headings in a crosstab query can vary, depending on the underlying data. This is the reason that Access prompts you for the parameter if you open the fourth query in design view. If you enter 1 for fiscal month, the values AU and EG don't occur in the DW field in tblStdRate, so they don't occur as columns in the crosstabs. Since the fourth query has fields AU and EG, an error occurs.

    You can get around this by explicitly specifying the column headings you need in the crosstab queries. Open a crosstab query in design view. Click in an empty part of the upper half of the query window, then activate the Properties window. Enter the column headings you need (on my system, I must use semicolons as delimiter, but it might be commas on yours, click in Column Headings and press F1 for help.) Test if the query works OK. If so, copy the Column Headings property into the other crosstab query as well. If all goes well, the fourth query will work OK now, and you should find that you aren't prompted for the parameter when you open the query in design view.

    Note: if you want to include ALL values of DW in the column headings, use AU,E1,EG,FA,FM,MA,MS,PD,SA,SL

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Criteria for a crosstab query (97)

    It works beautiful. Thank you so much. I did use commas to separate the field names.

    Where would I put an expression that if the cell is empty, it would return a value of zero? I tried putting AAU:iif(is null([AU]),"0",[au]) in the field row of the fourth query, but it didn't work. I get the expression I entered contains invalid syntax. You may have entered a comma without a preceding value or identifier.

    In checking the spelling it doesn't look like the expression is going to display correctly with the brackets. So I will try to put the word bracket where it goes. AAU:iif(is null(bracket AU bracket),"0",bracket au bracket)

    Best Wishes,
    John

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

    Re: Criteria for a crosstab query (97)

    The function is IsNull without a space between Is and Null. Access has a shorter alternative: the Nz function. Nz(Arg1, Arg2) returns Arg1, unless that is Null, then it returns Arg2. So you could use NZ([AU],0)

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Criteria for a crosstab query (97)

    Its works!! Was there ever any question that it would?
    I ended up putting it in the report based on that query instead. I had to put the [queryname.au] to get it to work. Is there an advantage to putting it in the query or the report?
    You have been such a big help.
    All the best to you,
    John

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

    Re: Criteria for a crosstab query (97)

    Hi John,

    I don't think it matters much whether you use Nz in the query or in the report. In theory, it's more efficient to put it in the query, but I doubt you'll notice any difference in practice.

Posting Permissions

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