DoCmd.OpenReport problem

[Tony Girgenti]

In the attached function coding the "Set" line works ok and doesn't return
any errors. Yet when it gets to the "DoCmd" it gives "Error 3075 (Syntax
error (missing operator) in query expression".

If it works for the "OpenRecordSet", why won't it work for the "OpenReport"
? Since, it basically uses the same statement.

Any help provided would be greatly appreciated.

Thanks,
Tony

Function CustQuery()

On Error GoTo CustQueryError

CustQuery = 1

CusQry = "SELECT DISTINCT cust.*, sa_lin.item_no AS itemnumber " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE sa_lin.item_no BETWEEN '" & StartItem & "' AND '" &
EndItem & "' " & _
"AND sa_hdr.post_dat BETWEEN '" & StartDate & "' AND '" &
EndDate & "' " & _
"AND cust.zip_cod BETWEEN '" & StartZipCode & "' AND '" &
EndZipCode & "' " & _
"AND (cust.cat = '" & CustCat & "' OR '" & CustCat & "' =
'ZZZZZ') " & _
"ORDER BY cust.nbr;"

Set Cusrs = dbs.OpenRecordset(CusQry, dbOpenSnapshot)

If (Cusrs.RecordCount = 0) Then
CustQuery = 0
Else
DoCmd.OpenReport "cust", acPreview, , Mid$(CusQry, 180)
End If

Exit Function

CustQueryError:

MsgBox "Error " & Err.Number & " (" & Err.Description & ")", vbCritical

CustQuery = 0

End Function

 

[Graham Mandeno]

Hi Tony

Have you checked what Mid$(CusQry, 180) is actually returning? It will
certainly not be a valid WHERE condition.

There are two ways you can do this:

You could declare another variable for the Where condition:

sWhere = sa_lin.item_no BETWEEN '" & StartItem & "' AND '" _
& EndItem & "' AND sa_hdr.post_dat BETWEEN '" & ... etc

Then:

CusQry = "Select .... WHERE " & sWhere & " ORDER BY cust.nbr"
....
DoCmd.OpenReport "cust", acPreview, , sWhere

But you might be better to use the NoData event of the report to check if
there is anything to print.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Jonathan]

Hi Tony,

I can only assume that 180 is the start position of the
field name after the word 'where' in the sql string.

Have you tried including brackets around the field names
in the sql?

Luck
Jonathan

 

[Emilia Maxim]

Tony,

In the attached function coding the "Set" line works ok and doesn't return
any errors. Yet when it gets to the "DoCmd" it gives "Error 3075 (Syntax
error (missing operator) in query expression".

If it works for the "OpenRecordSet", why won't it work for the "OpenReport"
? Since, it basically uses the same statement.

because OpenReport is not OpenRecordSet

CusQry = "SELECT DISTINCT cust.*, sa_lin.item_no AS itemnumber " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE sa_lin.item_no BETWEEN '" & StartItem & "' AND '" &
EndItem & "' " & _
"AND sa_hdr.post_dat BETWEEN '" & StartDate & "' AND '" &
EndDate & "' " & _
"AND cust.zip_cod BETWEEN '" & StartZipCode & "' AND '" &
EndZipCode & "' " & _
"AND (cust.cat = '" & CustCat & "' OR '" & CustCat & "' =
'ZZZZZ') " & _
"ORDER BY cust.nbr;"

Set Cusrs = dbs.OpenRecordset(CusQry, dbOpenSnapshot)

If (Cusrs.RecordCount = 0) Then
CustQuery = 0
Else
DoCmd.OpenReport "cust", acPreview, , Mid$(CusQry, 180)
End If

Did you count the characters correctly? Put a break point or a MsgBox
right before the OpenReport statement to display the Mid$(CusQry, 180)
string. It should start with 'sa_lin.item_no BETWEEN...'

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Tony Girgenti]

Hello Graham. Thanks for your help.

I'm not sure i understand what you are trying to tell me here.
The "Mid$(CusQry, 180)" points directly to the beginning of "sa_lin.item_no
BETWEEN...etc."
I know that because i watched it before it went to the "DoCmd.OpenReport".

It looks like your telling me to set up the where condition string in a
different manner. I don't see how that will help.

Also, why check the NoData event when i know the problem is in the syntax ?
If i can't get past the syntax problem, what's the sense in checking for no
data ?

Thanks,
Tony

Hi Tony

Have you checked what Mid$(CusQry, 180) is actually returning? It will
certainly not be a valid WHERE condition.

There are two ways you can do this:

You could declare another variable for the Where condition:

sWhere = sa_lin.item_no BETWEEN '" & StartItem & "' AND '" _
& EndItem & "' AND sa_hdr.post_dat BETWEEN '" & ... etc

Then:

CusQry = "Select .... WHERE " & sWhere & " ORDER BY cust.nbr"
...
DoCmd.OpenReport "cust", acPreview, , sWhere

But you might be better to use the NoData event of the report to check if
there is anything to print.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

In the attached function coding the "Set" line works ok and doesn't return
any errors. Yet when it gets to the "DoCmd" it gives "Error 3075 (Syntax
error (missing operator) in query expression".

If it works for the "OpenRecordSet", why won't it work for the "OpenReport"
? Since, it basically uses the same statement.

Any help provided would be greatly appreciated.

Thanks,
Tony

Function CustQuery()

On Error GoTo CustQueryError

CustQuery = 1

CusQry = "SELECT DISTINCT cust.*, sa_lin.item_no AS itemnumber " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE sa_lin.item_no BETWEEN '" & StartItem & "' AND '" &
EndItem & "' " & _
"AND sa_hdr.post_dat BETWEEN '" & StartDate & "' AND '" &
EndDate & "' " & _
"AND cust.zip_cod BETWEEN '" & StartZipCode & "' AND '"

&

EndZipCode & "' " & _
"AND (cust.cat = '" & CustCat & "' OR '" & CustCat & "' =
'ZZZZZ') " & _
"ORDER BY cust.nbr;"

Set Cusrs = dbs.OpenRecordset(CusQry, dbOpenSnapshot)

If (Cusrs.RecordCount = 0) Then
CustQuery = 0
Else
DoCmd.OpenReport "cust", acPreview, , Mid$(CusQry, 180)
End If

Exit Function

CustQueryError:

MsgBox "Error " & Err.Number & " (" & Err.Description & ")", vbCritical

CustQuery = 0

End Function

 

[Tony Girgenti]

I agree with you.

But i don;t see how that helps my problem.

Thanks,
Tony

 

[Tony Girgenti]

Hello Jonathan. Thanks for your help.

Yes, 180 starts with the "salin.item_no". I checked it with watch and a
message box.

I aslo tried brackets around field names.

Any other ideas ?

Thanks,
Tony

 

[Graham Mandeno]

Hi Tony

The "Mid$(CusQry, 180)" points directly to the beginning of "sa_lin.item_no
BETWEEN...etc."
I know that because i watched it before it went to the "DoCmd.OpenReport".

That may well be so, but it will also include the ORDER BY clause, which
will not be valid in the OpenReport argument.

It looks like your telling me to set up the where condition string in a
different manner. I don't see how that will help.

It will be less prone to errors. The way you have done it, you need to
laboriously count all the characters in your string up to the where
condition, and then the length of the where condition (to eliminate the
ORDER BY part). If you change your SQL at all then the lengths will need to
be changed, or at least checked. And the length of the WHERE condition is
variable anyway, as numbers and dates can have varying lengths.

Also, why check the NoData event when i know the problem is in the syntax ?
If i can't get past the syntax problem, what's the sense in checking for no
data ?

At the moment you're opening the recordset twice - once to check for no data
and then again to print the report. Why not just open it once and let the
report check for no data?

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Tony Girgenti]

Graham. Thanks for your help.

I tried your suggestion of removing the "ORDER BY" clause and gave me
basically the same error except it didn't say "(missing operator)". So, we
are making some progress.

It looks like i'm doing the same recordset twice but later on in the code i
actually use the first record set to create an output file.

Thanks,
Tony

Hi Tony

The "Mid$(CusQry, 180)" points directly to the beginning of "sa_lin.item_no
BETWEEN...etc."
I know that because i watched it before it went to the

"DoCmd.OpenReport".

That may well be so, but it will also include the ORDER BY clause, which
will not be valid in the OpenReport argument.

It looks like your telling me to set up the where condition string in a
different manner. I don't see how that will help.

It will be less prone to errors. The way you have done it, you need to
laboriously count all the characters in your string up to the where
condition, and then the length of the where condition (to eliminate the
ORDER BY part). If you change your SQL at all then the lengths will need to
be changed, or at least checked. And the length of the WHERE condition is
variable anyway, as numbers and dates can have varying lengths.

Also, why check the NoData event when i know the problem is in the

syntax

?
If i can't get past the syntax problem, what's the sense in checking for no
data ?

At the moment you're opening the recordset twice - once to check for no data
and then again to print the report. Why not just open it once and let the
report check for no data?

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Emilia Maxim]

I agree with you.

But i don;t see how that helps my problem.

You do agree with me asking if you checked the Mid$(CusQry, 180)
string?? I'm glad about that...

BTW, date type criteria must be enclosed in #, and it seems you do
have a date in the WHERE clause. And you could try enclosing the
BETWEEN... AND... expressions in brackets, maybe Jet messes up all
those ANDs.

And I fully agree with the suggestions Graham made, they're very
reasonable.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Tony Girgenti]

Hello Emilia. Thanks for your help.

I'm sorry for sounding so short with you in a previous reply.

I'll try your suggestion of putting "#" signs around the dates.

Thanks,
Tony

 

[Tony Girgenti]

There has to be something else wrong with what i am doing, because with a
simple assignment as i have it, it still gives the same error.

I tried all of your suggestions and none of them seem to help.

Does anybody have any idea of why this simple statement causes the
openreport to give the error "Error 3075 (Syntax error in query
expression)".

Function CustQuery()

On Error GoTo CustQueryError

CustQuery = 1

CusQry = "SELECT DISTINCT cust.*, sa_lin.item_no AS itemnumber " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE sa_lin.item_no BETWEEN '" & StartItem & "' AND '" &
EndItem & "' " & _
"AND sa_hdr.post_dat BETWEEN '" & StartDate & "' AND '" &
EndDate & "' " & _
"AND cust.zip_cod BETWEEN '" & StartZipCode & "' AND '" &
EndZipCode & "' " & _
"AND (cust.cat = '" & CustCat & "' OR '" & CustCat & "' =
'ZZZZZ') " & _
"ORDER BY cust.nbr;"

Set Cusrs = dbs.OpenRecordset(CusQry, dbOpenSnapshot)

StrQry = "sa_lin.item_no = '" & StartItem & "';"

If (Cusrs.RecordCount = 0) Then
CustQuery = 0
Else
MsgBox StrQry
DoCmd.OpenReport "cust", acPreview, ,StrQry

End If

Exit Function

CustQueryError:

MsgBox "Error " & Err.Number & " (" & Err.Description & ")", vbCritical

CustQuery = 0
End Function

 

[Tony Girgenti]

Well folks, i finally found the answer.

Tom in another message said to remove the semicolon at the end and that was
what did it.

Thanks to everybody for their help, especially Tom.

Tony

 

[Gregski.co.uk]

heheheheheh, it's so often the smallest, most insignificant things, isn't
it?
Those are the things that the debugger could perhaps be picking up in future
releases.......
One that gave me headaches was the use of REM statements at the end of
normal statements, like this:

x=4 'this makes variable x equal to the number 4

Now the above works fine and dandy under Access97, but try and compile the
project under Access2000 and you get
serious problems!

Weird little things..... the semicolon issue sounds very similar. One
convention for all contexts for pure SQL would be nice, MS......