Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Error 3075 missing operator (Access 2000)

    I am building a report using VBA. The report is generated from a cross tab query. So far I have got the detail part organized. Now I am trying to enter totals in the Report footer.

    The code that follows fails where I try to establish the value of var2. The message I get is error 3075 missing operator. Can anyone tell what I am doing incorrectly?

    Set qdf = db.QueryDefs![qrybymonth_crosstab]

    SetUpReportNet 'Calls up template for report

    var = qdf.Fields.Count 'how many fields in the query
    'Set up fields in report
    For n = 0 To var - 1
    var1 = qdf.Fields(n).Name 'Gets name of field
    Set ctlText(n) = CreateReportControl(Reports(0).Name, acTextBox, acDetail, , var1, l, 0, w, 300)
    l = l + w
    w = 800
    Next

    'Enter totals

    l = 2880
    Dim var2 As Currency

    For n = 1 To var - 1
    var1 = qdf.Fields(n).Name
    var2 = DSum(var1, "qrybymonth_crosstab")

    Next n

    Regards

    Mitch

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error 3075 missing operator (Access 2000)

    Check what the name is from the field when you get the error.
    Probabily it contain characters that are not allowed.
    Do you have empty fields in the header of the crosstab query ?
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Error 3075 missing operator (Access 2000)

    Hi Francois

    Thanks for the response. At this point of setting up the report I have not dealt with any headers, therefore nothing is in the acheader section. As you can see from my previous post I use exactly the same filed names to enter the sums in acdetail. Further I have run this bit of code replacing the variable var1 with the actual name of the first field stepped though the code and it works fine. As the report is built dynamically I only have fields that actually have values. The field names are;

    For the first field

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

    Re: Error 3075 missing operator (Access 2000)

    Try

    var2 = DSum("[" & var1 & "]", "qrybymonth_crosstab")

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Error 3075 missing operator (Access 2000)

    Hans

    Thank you that worked just fine I had tried square brackets and quotes but had not concactenated them.

    Thanks again.

    Mitch

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

    Re: Error 3075 missing operator (Access 2000)

    Without the square brackets, VBA get confused, since the field names 1, 2, ... look like Integers. Using [1] etc. makes it clear that they are NOT numbers.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error 3075 missing operator (Access 2000)

    And if one of the record has an empty date field used as column header, the crosstab generate a field name "<>" (without quotes), that give the error 3075 when you try to sum it in code.
    Francois

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Error 3075 missing operator (Access 2000)

    Hans

    Thank you I understand and now have my report built. Have run into another problem and this may not be the place to post so I apologise in advance. I want to conditionally format the detail results in the report. To do that I have to determine what if any value is in a field. I can only return the name of the field not its value.

    Below is all the code for the report so far

    Long code (almost 3,500 characters) moved to attachment by HansV

    I have highlighted the part that inserts the detail records. I have tried qdf.fields(n).value to return the value of each ctlText record but that does not work. It is probably a simply answer but I have not been able to work it out. Again my apologies if I am posting this incorrectly.

    Thanks
    Mitch

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

    Re: Error 3075 missing operator (Access 2000)

    A querydef is just a definition of the structure of the query, it doesn't contain the data. To get at the values, you need to open a recordset.

  10. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Error 3075 missing operator (Access 2000)

    Hans

    Thanks again but have to admit after trying what I can think of I still cannot return the values from the query.

    I have set up a function to test what I am doing;

    Function makesumtbl()
    1. Dim rst As Recordset
    2. Dim db As Database
    3. Dim qdf As QueryDef
    4. Set db = CurrentDb

    5. Set qdf = db.QueryDefs![qrybymonth_crosstab] 'Sets query to crosstab from table created previous step
    6. With qdf
    7. Set rst = qdf.OpenRecordset()
    8. rst.MoveLast
    9. End With

    10. var = qdf.Fields.Count 'No fields
    11. var2 = rst.RecordCount 'No Records
    12. var3 = rst.Name

    13. For n = 1 To var2
    14. For n1 = 1 To var - 1
    15. var1 = qdf.Fields(n1).Name
    16. MsgBox var1
    17. x = var3!var1.Value
    18. Next n1
    19. Next n


    20. For n = 2 To var - 1
    21. var1 = qdf.Fields(n).Name
    22. MsgBox var1
    23. With rst
    24. Do While Not .EOF
    25. MsgBox var1.Value
    26. .MoveNext
    27. Loop
    28. .MoveFirst
    29. End With
    Next n

    End Function
    I have tried to get values a number of ways the last two attempts are shown above lines 13 to 19 and 20 to 29.

    In the first case I am told that the object is missing I would have thought var3 was the object as it is the name of the recordset. Tried adding acquery and query neither works.

    Once again any help greatly appreciated.

    Mitch

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error 3075 missing operator (Access 2000)

    Can this code help you ?

    Function makesumtbl()
    Dim rst As dao.Recordset
    Dim db As dao.Database
    Dim x As Integer
    Set db = CurrentDb
    Set rst = db.OpenRecordset("qrybymonth_crosstab")

    'This will enumerate each field in each record.
    Do While Not rst.EOF
    For x = 0 To rst.Fields.Count - 1
    MsgBox rst.Fields(x).Name & " = " & rst.Fields(x)
    Next x
    rst.MoveNext
    Loop

    'This will sum each field starting with the second field

    For x = 1 To rst.Fields.Count - 1
    MsgBox DSum("[" & rst.Fields(x).Name & "]", "qrybymonth_crosstab")
    Next x
    End Function

    You'll have to set a reference to Microsoft DAO 3.6.
    In the Visual Basic editor, select Tools / References.
    Scroll down to Microsoft DAO 3.6 Object Library and check it. Close the references window.
    Francois

  12. #12
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Error 3075 missing operator (Access 2000)

    Francois

    Many thanks that appears to work just fine. If you have time I would like to understand why you switched to DAO?

    Regards

    Mitch

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error 3075 missing operator (Access 2000)

    Honestly ? Because it is what I always use and know best. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Francois

  14. #14
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Error 3075 missing operator (Access 2000)

    Thanks again problem solved.

    Mitch

Posting Permissions

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