Can't get data on report using where condition

  • Thread starter Thread starter Tony Girgenti
  • Start date Start date
T

Tony Girgenti

Hello,

I started working with Allen Browne on this problem, but i haven't heard
from him in a couple of days, so i thought i might try this forum for a
solution.

I'm try to produce a report on externally linked ODBC tables using the where
condition at the end.

The problem is with the lines that have "sa_hdr.post_dat" in them.
"sa_hdr.post_dat" is a date field in the table formatted as text "yyyymmdd".
The form dates are formatted as short date. The same condition is used to
create a record set and it works fine. It gives me the exact records that i
want.

However, when it is used in the docmd.openreport, it does not give any
records, just a blank page. I have tried numerous ways to format the
"sa_hdr.post_dat".

Any help appreciated.
Thanks,
Tony

RptQry = "(sa_lin.item_no >= '" & [Forms]![Form1]![StrItm] & "' " & _
"OR " & [Forms]![Form1]![StrItmChk] & " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]![Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk] & " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk] & " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk] & " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]![Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk] & " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]![Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk] & " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]![StrSalAmt] & " " & _
"OR " & [Forms]![Form1]![StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]![EndSalAmt] & " " & _
"OR " & [Forms]![Form1]![EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]![CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk] & " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk] & " = false) " & _
"or (" & [Forms]![Form1]![ExcCustChk] & " = true and
cust.email_adrs > ' ')) "

DoCmd.OpenReport "cust3", acPreview, , RptQry
 
Are you saying that you can comment out the DoCmd.OpenReport statment and
add code that just executes the SQL statement that you create for the
DoCmd.OpenReport and see the records you expect, but that using the same
query with DoCmd.OpenReport does not return those same records?

If you have not tried that, specifically, please try it. Executing what you
_believe_ to be the Query that would result just is not a reliable test.
Precede what you've built, of course, with the SELECT inforamtion, JOINs,
and the WHERE keyword.

I've found it almost as simple to construct the entire SQL statement for the
RecordSource, and, in the Report's Open event, pick it up from where I have
it stashed and replace the entire Record Source. And, it'll be easy to test
as you'll have a complete SQL statement.

If Allen hasn't spotted a problem in that long SQL statement, there's little
need for me to examine it in detail, so be aware that I haven't done so.

Larry Linson
Microsoft Access MVP
 
Tony

If you've already written a query that returns the recordset you want in
your report, why not use that instead of the OpenReport,,,, WHERE condition?

Good luck

Jeff Boyce
<Access MVP>
 
I was trying to keep from showing all the coding, but i'll show you the
whole function.
The "Set Cusrs" line works fine and i get the records i want. It uses the
"RptQry" string which is the same string i'm using with the
"DoCmd.OpenReport".

Function CustQuery()

On Error GoTo CustQueryError

CustQuery = 1

RptQry = "(sa_lin.item_no >= '" & [Forms]![Form1]![StrItm] & "' " & _
"OR " & [Forms]![Form1]![StrItmChk] & " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]![Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk] & " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk] & " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk] & " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]![Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk] & " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]![Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk] & " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]![StrSalAmt] & " " & _
"OR " & [Forms]![Form1]![StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]![EndSalAmt] & " " & _
"OR " & [Forms]![Form1]![EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]![CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk] & " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk] & " = false) " & _
"or (" & [Forms]![Form1]![ExcCustChk] & " = true and
cust.email_adrs > ' ')) "

CusQry = "SELECT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"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 " & RptQry & "GROUP BY cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no " & _
"ORDER BY cust.nam;"

Debug.Print RptQry

Set Cusrs = dbs.OpenRecordset(CusQry, dbOpenSnapshot)

If (Cusrs.RecordCount = 0) Then
CustQuery = 0
Else
If [Forms]![Form1]![Frame39] <> 2 Then
Debug.Print RptQry
DoCmd.OpenReport "cust3", acPreview, , RptQry
End If
End If

Exit Function

CustQueryError:

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

CustQuery = 0

End Function
 
Tony

A SQL statement ("SELECT ...) is a query... and vice versa.

Jeff Boyce
<Access MVP>
 
Not an issue -- there's a lot of "vocabulary" to learn using Access... and
I'm still learning it!

So, do you have a working solution for your original post?

Jeff Boyce
<Access MVP>
 
No. I do not have a solution yet.
If you saw the complete function that i posted for Larry Linson, i don't
understand why the "where" part of the query works for the record sets but
not for the "openreport" command.

Tony
 
Tony

If I followed what you and Tony were describing, it sounds like he asked you
to try running the report when you've changed the source of the report to
your query (and removed the WHERE syntax in the .OpenReport command). Did
that work?

I'm thinking it might take quite a bit of setup work to try to duplicate
your exact conditions. If you are looking for a way to get the report
running, rather than a way to make the way you're using work, I think you
can get there.

(That's another thing I'm still learning ... if it doesn't work the way I
want it to, is there another way to get there?)

Good luck

Jeff Boyce
<Access MVP>
 
Hi Jeff.

I'm sorry, but i'm not sure of what you are asking me. In my function, the
"RptQry" string is what is used in the DoCmd.OpenReport and it does not
work. The "CusQry" consists of a Select + RptQry(prefixed with "WHERE") +
"GROUP BY&ORDER BY etc. ending with a semicolon and it works fine. It gives
me exactly the records i want.

Is that what you are asking ?
What do you mean by "If I followed what you and Tony were describing". Do
you mean Tony and Allen or Tony and Larry ?

If you have a way for me to get the report that i want by using some other
technique, i would gladly try any suggestion you have. I've been try to
resolve this now for weeks.

I really appreciate the time and effort extended to me by the gurus of this
forum including you and Larry and Allen. Without the help of this forum, i
probably would not have my program working the way it is now. I have tried
everything that was suggested

Thanks,
Tony
 
Tony

I confess to feeling a little lost at this point!

Let's try this... step away from everything you already have built. Start
with a fresh new query, in design mode. Create the query by selecting
tables, joining them, and selecting fields. Confirm that the query returns
the data you expect. When the query runs as expected, click on the Design
button and select the SQL view. Compare that SQL to yours.

Now, open your report (or a copy thereof) in design view. Set its source to
the query you just created above. Run the report from the database window.
Does it work as expected?

Now return to what you were attempting earlier. Is there a reason you have
to set a WHERE clause as the report opens, or could you just point to the
above report (which is based on the above-above query)?

Regards

Jeff Boyce
<Access MVP>
 
Hi Jeff.

I really appreciate your help, but i spent hours trying to accomplish what
you are requesting. I can't figure how to make the query do the "MAX",
"INNER JOIN", "GROUP BY" and "ORDER BY".

It seems to me, that i already have a query that returns the record sets (in
the order that i want them and filtered exactly the way i need) that i am
looking for. All i want to do is put them on a report. Why would i want to
try to create a whole new query just for the report. Cant i just tell the
report to use the same query ?

Thanks,
Tony
 
Back
Top