DoCmd.OpenReport yields no records

[TonyG]

With the attached function, i'm trying to create a report that shows the same recordset that is extracted using the "Set Cusrs = dbs.OpenRecordset(CusQry, dbOpenSnapshot)" statement which works fine. It gives me exactly the records that i want. Yet when it does the "DoCmd.OpenReport" statement it only gives me a blank report. No errors. Why ?

The "sa_hdr.post_dat" table field is defined as TEXT formatted YYYYMMDD.

Any help greatly appreciated.

Thanks,
Tony
===========================================================
Function CustQuery()

On Error GoTo CustQueryError

CustQuery = 1

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

CusQry = &quot;SELECT cust.nam, &quot; & _
&quot;cust.adrs_1, &quot; & _
&quot;cust.adrs_2, &quot; & _
&quot;cust.city, &quot; & _
&quot;cust.state, &quot; & _
&quot;cust.zip_cod, &quot; & _
&quot;cust.email_adrs, &quot; & _
&quot;cust.phone_no_1, &quot; & _
&quot;sa_lin.item_no AS itemnumber, &quot; & _
&quot;MAX(sa_hdr.post_dat) AS postdate &quot; & _
&quot;FROM (cust &quot; & _
&quot;INNER JOIN sa_hdr &quot; & _
&quot;ON cust.nbr = sa_hdr.cust_no) &quot; & _
&quot;INNER JOIN sa_lin &quot; & _
&quot;ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no &quot; & _
&quot;WHERE &quot; & RptQry & &quot;GROUP BY cust.nam, &quot; & _
&quot;cust.adrs_1, &quot; & _
&quot;cust.adrs_2, &quot; & _
&quot;cust.city, &quot; & _
&quot;cust.state, &quot; & _
&quot;cust.zip_cod, &quot; & _
&quot;cust.email_adrs, &quot; & _
&quot;cust.phone_no_1, &quot; & _
&quot;sa_lin.item_no &quot; & _
&quot;ORDER BY cust.nam;&quot;

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 &quot;cust3&quot;, acPreview, , RptQry
End If
End If

Exit Function

CustQueryError:

MsgBox &quot;Error &quot; & Err.Number & &quot; (&quot; & Err.Description & &quot&quot;, vbCritical

CustQuery = 0

End Function

 

[KenReay]

Hi

Are you sure this SQL works?

You haev WHERE and GROUP BY, I thought if you used GROUP BY you used HAVING, not WHERE

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TonyG]

Hi Ken.

Thanks for your reply.

I am positive this SQL works. If it doesn't, then i don't know wher my output file is getting it's contents.

Did you see this part of my original post &quot;It gives me exactly the records that i want.&quot; ?

Thanks,
Tony

 

[dhookom]

I'm watching &quot;Clean House&quot; on cable so forgive my suggestions.
Function CustQuery()
On Error GoTo CustQueryError
CustQuery = 1
RptQry = &quot;1=1 &quot;
If Forms!form1!strItmChk = True Then
rptQry = rptQry & &quot;item_no >= '&quot; & _
[Forms]![Form1]![StrItm] & &quot;' &quot;
End If
If [Forms]![Form1]![EndItmChk] = True Then
rptQry = rptQry & &quot; item_no<='&quot; & _
[Forms]![Form1]![EndItm] & &quot;' &quot;
End If
'etc
I would check each control and build the where clause. I doubt you need to add the table name since this is probably causing you the problems. If this code is in the Form1 then you can replace all the Forms!Form1 with Me.

Duane
MS Access MVP

 

[TonyG]

Hi Duane. Thanks for your reply.

Before i try your suggestion, it looks to me like your are just giving me another way to string together my RptQry. Is that right ?

Please tell me if there is something that i am not seeing in your suggestion. If I sound ungrateful, please believe that is not the case.

Just to clarify, my problem is not getting a correct resordset, but in getting a report that yields the same recordset as the openrecordset.

Thanks,
Tony

 

[dhookom]

I presented both what I think is a cleaner method and also the suggestion that you should not include the table names in your where clause. Your report's record source/fields rarely contain a table.field. Your where clause should not either. The Table.Field syntax works great in a query but not a where clause for a report.

Duane
MS Access MVP

 

[TonyG]

Hi Duane.

I tried what you suggested and got the same results. Just a blank report.

Another thing that happens is when it gets to the &quot;DoCmd.OpenReport&quot;, it asks for &quot;item_no&quot;, &quot;bal&quot;, &quot;cat&quot; and &quot;email_adrs&quot; in four seperate dialog boxes.

Any firther help that you can provide would be appreciated.

Thanks,
Tony

 

[KenReay]

Hi

The fact you are geeting dialog boxes requesting values for Iem_No etc suggests that you have object names defined in your report whicha re not in the recordset (frequently this is in the Sorting and Grouping section), and/or you have errors in the SQL string (frequently caused by miss-spelling a columnnname).

Also For names such as Item_no (ie those including embedded spaces and/or characters other than 0 thru 9 and A thru Z, I would enclose them in [] so, [Item_No]

Hope this helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dhookom]

If you are going to filter your report with a where clause, you must have the referenced fields in the record source of your report. Have you tried
debug.Print rptQry
Is your report bound to a record source?
Do you get records even if you have no filter?

Duane
MS Access MVP

 

[TonyG]

Thanks to Ken and Duane.

I think we are making progress. I tried both of your suggestions and got results that are better than what i was getting( a blank report). I'm getting 10 records on the report but i'm only getting 8 records from the recordset query. And i can see the difference being that the report is not filtering out the duplicate post_dat and item_no fields.

I tried putting the &quot;MAX(sa_hdr.post_dat) AS postdate&quot; part of the query into the report design view, record source, SQL View. It gives me an error 3122 when i run it(You tried to execute a query that does not include the specified expression 'NAM' as part of an aggregate function.).

How do i get the report to use the &quot;MAX(sa_hdr.post_dat) AS postdate&quot; and &quot;GROUP BY&quot; statements ?

I put the brackets([]) around table fields, used &quot;Me.&quot; instead of &quot;[Forms]![Form1]&quot; and the report does have a record source.

Also, it now asks for &quot;bal&quot;, &quot;cat&quot; and &quot;email_adrs&quot;. I checked for spelling and didn't see anything that would cause that. These objects are in the record set. I checked it twice.

Any continued help that you can provide to resolve this problem, would be greatly appreciated.

Thanks,
Tony

 

[dhookom]

The only way to use Max() and other aggregates is be viewing &quot;TOTALS&quot;. You can click the sigma on the tool bar (backward 3).

Duane
MS Access MVP

 

[TonyG]

Duane,

I see what you are talking about, but when i try it, i get an error. I forget what the error is because i tried so many thing after that.

How do other Access programmers create reports that use the same reocord set as one that is from a query ?

Thanks,
Tony

 

[dhookom]

All of my reports are based on queries (or SQL statements ) with little or no criteria applied. I open reports using a &quot;where&quot; clause.

Duane
MS Access MVP