Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compile error (Access 2000)

    In my crosstab query i have put a where expression [Forms]![F1]![office] with which to open a report. depending on the choise.However,on clicking the control i get the error "compile error in query expression Month[invoicedate]
    But without this expression the query works ok.Is it something with the crosstab ?

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

    Re: Compile error (Access 2000)

    You'll have to find out where Month[invoicedate] is used - it should be Month([invoicedate])

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile error (Access 2000)

    Yes, you are right, thank you ! After i have corrected this i again have another error , when i click on the control in the form.
    The Microsoft Jet database engine does not recognize Forms!F1!office as a valid field name expression.
    I have have put in the crosstab query the expression Forms!F1!office
    Is it because of the crosstab ?

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

    Re: Compile error (Access 2000)

    You must "declare" parameters in crosstab queries, otherwise they won't be recognized:

    - Open the crosstab query in design view.
    - Select Query | Parameters...
    - Enter Forms!F1!office in the Parameter column.
    - Select the correct data type (probably Long Integer) in the Data Type column.
    - Click OK.
    - Save the query.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile error (Access 2000)

    I would like to ask you for a further help since even though i did what you advised me i still cannot open the report. I am sending you a small example. Thank you for your help
    Attached Files Attached Files

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

    Re: Compile error (Access 2000)

    The query in the database you posted doesn't refer to the form, so perhaps you posted the wrong version or removed too much.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile error (Access 2000)

    Sorry ! sent the wrong form.This is now the version with the form F1
    Attached Files Attached Files

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

    Re: Compile error (Access 2000)

    The query named 01 does not refer to the form.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile error (Access 2000)

    Something happens with my query and at the moment i refer it to the form the form is blank ?
    Attached Files Attached Files

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

    Re: Compile error (Access 2000)

    You keep on posting exactly the same query. There is no point in posting the same database again and again.
    The reason the query doesn't return any records is that there are no records in the Order Details table.

  11. #11
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile error (Access 2000)

    Sorry. Didnt mean harm.I just could not explain myself why the report is not filtered with the afid chosen. Please do apologize me.Thank you for your attention

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

    Re: Compile error (Access 2000)

    There are lots of problems with your database.

    I assume that you want to filter on the afid field in the Customers table, but afid hasn't been filled in, so you'll never get a record in your report.

    The form F1 should be unbound, i.e. its Record Source property should be empty.

    If you want to restrict the data in the report to those from the office selected on the form, you should either add criteria to the query, or specify a where-condition in the DoCmd.OpenReport instruction in the On Click event procedure of the button on the form.

    Handling a report based on a crosstab query is far from easy, since the number of columns can be different depending on the data. The easiest "fix" is to specify the column headings explicitly in the properties of the crosstab query, so that you can use a fixed report design. Otherwise, you have to design a dynamic crosstab report, and this requires a lot of complicated code.

    In the attached version of your database, I have done the following:

    I added some data to the Orders and Order Details tables, so that it is possible to test.

    I filled in values for afid in the Customers table, so that the query can actually select on afid.

    I modified the query:
    - Added column headings.
    - Added a condition to the afid field that refers to the option group on the form.
    (In all versions you posted, there was no condition!)

    I cleared the record source of the form.

    And I have recreated the report from scratch, to include the extra column headings.

    BTW it would be nice if the option buttons had labels, so that the user knows which button corresponds to which office.
    Attached Files Attached Files

  13. #13
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile error (Access 2000)

    Dear Hans

    A huge thanks for your email that actually has given a new twist to my database and the form now works like a charm ! There were a lot ot points that were not clear to me, how to construct the where condition, and also the details in the report.All these points are now clear to me and actually the dtabase is quite foolproof and i already strated working with with.
    Thank you very much Hans !

Posting Permissions

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