Error 3075 missing operator (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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 ?

Reply | Quote

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

Reply | Quote

Try

var2 = DSum(“[” & var1 & “]”, “qrybymonth_crosstab”)

Reply | Quote

Hans

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

Thanks again.

Mitch

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Honestly ? Because it is what I always use and know best.

Reply | Quote

Thanks again problem solved.

Mitch

Reply | Quote

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.

Reply | Quote