Still need help w/DoCmd.OpenReport

[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 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)
Set StrQry = "sa_lin.item_no = '" & StartItem & "';"
If (Cusrs.RecordCount = 0) Then
CustQuery = 0
Else
DoCmd.OpenReport "cust", acPreview, , StrQry
End If

Exit Function

CustQueryError:

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

CustQuery = 0

End Function

 

[Tom]

Tony,
I'm not 100% sure, but I'm pretty sure you don't need the semi colon. I have
never used one in a "Where" statement on the open report command and have
never had any problems. Try that.
Tom

 

[Tony Girgenti]

Thanks Tom. That did it. No errors.

Now, all i have to do is figure out why i'm not getting any data on the
report. It shows an empty report with only a title and headers.

Thanks,
Tony

 

[Tom]

Tony,
I have a sneaking suspicion you aren't really getting any records (duh) even
though you are trying to trap for that with the .RecordCount.
I would set a Debug.Print CusQry after your SQL statement to see if you are
really picking up the criteria you want.
Also, I'm not sure it matters, but you should be able to take the
parenthesis off the Cusrs.RecordCount = 0. It should not be needed.
Just a couple of thoughts from a novice.
HTH
Tom

 

[SteveS]

I noticed that you used single quotes to enclose the
criteria. That is OK for text, but numbers don't
need/shouldn't have the single quotes; dates should be
enclosed with #'s:

"AND sa_hdr.post_dat BETWEEN #" & StartDate & "# AND #" &
EndDate & "# " & _


You might need to fully qualify the criteria if you are
referencing controls on a form:

Forms!frmMyFormName!StartDate


In the last part of CustQry = "Select...... ,
did you mean "' OR '" & CustCat & "' = 'ZZZZZ')?
^^^^^^^
Or did you want "' OR '" & cust.cat & "' = 'ZZZZZ')?
^^^^^^^^
You could add a message box (MsgBox CusQry) before
the "Set Cusrs =" statement to see what string (CustQry)
contains. (Comment it out or remove it later)

I would move the line "Set StrQry = ...." to ELSE part
because it is the criteria (WHERE) for DoCmd.OpenReport
line. I find it easier to understand when I look at it in
a year from now.


Just curious, why are you using a Function instead of a
Sub???

HTH

Steve

 

[Tony Girgenti]

Hi Steve. I really appreciate your efforts and detailed analysis of the
WHERE clause.

If the dates are stored as string, do i still need the "#" symbol ?
All of the fields in the StrQry are variables in the module and all are
dimmed as string.

The last part of the CustQry is correct i think. I'm looking to compare the
data from the table (cust.cat) to a specific CustCat(entered by user and
moved to variable in module) or if the user entered "ZZZZZ" in CustCat to
indicate that cust.cat can be anything.

CusQry is not really giving me a problem. It returns exactly the records
that i want. I'm having a problem with the StrQry and trying to get a
report to come up before i actually create an output file using the
recordset created with "Set Cusrs = ".

I'm using a Function instead of a Sub because it returns a value. The way
i'm calling it is "If CustQuery Then".
Meaning if CustQuery is True, then go ahead with the building of the output
file.

Thanks again Steve and any help that you can provide to get a report out of
this program would be greatly appreciated.

Tony

 

[Tom]

Tony,
Since you look like you are trying to come up with no records or else print
a report using the report "cust" I am wondering, what is the recordset
behind the report?
Is it a query exactly like CusQry, is it a table?
One way you could try to trouble shoot the problem would be to create the
recordset you want in the report as your StrQry and then pass it in as an
OpenArg instead of the WHERE.
Then in your report's OnOpen event you just put Me.RecordSource =
Me.OpenArgs. If you normally want the report to be based on some other
recordset then you could creat that SQL and use a conditional statement:
If IsNull(Me.OpenArgs) Then
Me.RecordSource = StrQueryToMakeRecordset
Else
Me.RecordSource = Me.OpenArgs
End If

At least this way you can find if the problem is with your WHERE that you
are sending to the report is not communicating with the report - or if the
recordset behind the report has some problem.
Just a few more thoughts from a novice.
Tom

 

[SteveS]

Tony,

If the variables are Dimmed as string, then the single
quote is correct.

The problem with searching for dates as strings is
that '05/01/03' does not match any of
these: '5/1/2003' , '5-1-2003' , '5-1-03' , '05-01-03'.

If the variable was dimmed as date, Access would see all
five date formats as the same.

Echoing Tom, what is the recordsource for the report? Is
the field "post_dat" in table "sa_hdr" defined as a date
type? Comparing a string type to a date type would return
0 records (i think - I've never tried it).

Steve

 

[Tony Girgenti]

Hi Steve and Tom.

The sa_hdr.post_dat is stored as text in the sa_hdr table. All fields in
all the tables for this database are stored as text except for dollar
amounts and quantityies. And all dates in the tables are stored as
YYYYMMDD. The text box on the form where i accept the dates has a format of
short date.

I took your advice from a previous post and started using the controls
directly from the forms instead of moving the control's value to a variable
and using the variable to do comparisons against the data.

I'm at a point now where the Select on the data works fine. My only problem
is with the DoCmd.OpenReport.
Does the report object have to be created in access before i can use the
openreport ? If so, the report object has it's own SQL statement behind it,
does the SQL that i use in the openreport command override the report's SQL
?

Thanks,
Tony

 

[SteveS]

Tony,

Does the report object have to be created in access
before i can use the openreport ?

Yes. You have to have the report created before you can
preview or print it.

If so, the report object has it's own SQL statement
behind it, does the SQL that i use in the openreport
command override the report's SQL?

The report will have a query or SQL statement as the
record source. In you code you posted, you are not setting
the recordsource for the report in the DoCmd.OpenReport
line.

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

The above line is the WHERE part of a SQL statement.


For example, a report could have a recordsource of:
SELECT * FROM tblTableName WHERE txtField1 = 'ZZZZ'

The report will always show only the records where
txtField1 = 'ZZZZ'.

Or the SQL could be:
SELECT * FROM tblTableName

Then in the code, open the report filtering the data (the
WHERE part)

Set StrQry = "sa_lin.item_no = '" & StartItem
DoCmd.OpenReport "rptReport1", acPreview, StrQry

Now you can change what records are returned by changing
the field and/or the data it is compared with.

So, create the report, using a query or SQL as the
recordsource without any criteria (the query) or the WHERE
part (SQL). Then in the code limit the records by using
the third argument of the DoCmd.OpenReport (the WHERE
part) like you did.

Did this make any sense?

Steve