#error in report field for no data.

FrigidDigit

Board Regular
Joined
May 10, 2005
Messages
203
Hi Guys,

I did a search of the forum but didn't find any answers.

I have a report that is based on a query using date parameters. When running a report for a period during which no data is returned, the fields on the report returns "#error".

Is there any way to avoid this? This report will be used as a subreport so I don't know if a "no data event" can be used without cancelling the whole main report?

Thanks!

FD
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
The first thing I would try would be to have code in the OnOpen event of the main report check for records in the RecordSource query for the sub-report. If no records, then set the RecordSource for the sub-report to a zero length string ("").
HTH,
 

FrigidDigit

Board Regular
Joined
May 10, 2005
Messages
203
Vic,

Can you perhaps give me a hand in doing this? I new to Access and while I am familiar with Excel VBA, I am still getting to grips with the Access object model.

Thanks!
FD
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
I'll give you the steps, you look them up in Help, then I'll help you debug the code, if needed.
1. Using the query (or SQL) that is the record source for the sub-report, open a RecordSet as a SnapShot.
2. Once open, set a variable equal to rs.EOF. If there are no records in the recordset, then rs.EOF will be True. ("rs" is the variable name I used for the recordset object we opened in step 1.)
This is one way to find out if a query will have any records in it.
Vic
 

FrigidDigit

Board Regular
Joined
May 10, 2005
Messages
203
1st Attempt

Thanks Vic

This is what I've got (please excuse the clumsy coding I'm still learning):

I add the following to the open event of the subreport I want to run:

Private Sub Report_Open(Cancel As Integer)
Dim rstSGBReportData As Recordset
Dim eofNoData As rstSGBReportData.EOF 'eof??

Set rstSGBReportData = CurrentDb.OpenRecordset
("SELECT a_Trainee.Prov, a_Trainee.ID_Trainee, a_Trainee.TraineeSurn," & _
"a_Trainee.TraineeFirst, a_Trainee.ID13, a_Trainee.Gender, a_Trainee.SGB, " & _
"a_Trainee.SMT, a_Trainee.Rank, b_Programme.ProgName, b_Programme.Level, " & _
"b_Programme.ID_Programme, b_Programme.ProgStart, b_Programme.ProgEnd, " & _
"b_ProgrammeTraineeLink.DaysAttended, IIf([gender]=0,1,0) AS CountUnknown, " & _
"IIf([gender]=1,1,0) AS CountFemale, IIf([Gender]=2,1,0) AS CountMale " & _
"FROM b_Programme INNER JOIN (a_Trainee INNER JOIN b_ProgrammeTraineeLink " & _
"ON a_Trainee.ID_Trainee = b_ProgrammeTraineeLink.ID_Trainee) ON b_Programme.ID_Programme " & _
"= b_ProgrammeTraineeLink.ID_Programme WHERE (((a_Trainee.SGB)=2) AND " & _
"((b_Programme.Level)=9 Or (b_Programme.Level)=11) AND ((b_Programme.ProgStart) " & _
"Between [Forms]![00 QR Report Figures]![txtFromDate] And " & _
"[Forms]![00 QR Report Figures]![txtToDate]) AND ((b_Programme.ProgEnd) " & _
"Between [Forms]![00 QR Report Figures]![txtFromDate] And [Forms]![00 QR Report Figures]" & _
"![txtToDate]) AND ((b_ProgrammeTraineeLink.DaysAttended)>0)& "));")

If eofNoData = True Then
Exit Sub
End If

End Sub

I know that there is a problem with the closing parentheses in the sql statement but I am too thick too figure it out. I tried matching them but I can't figure out where I'm going wrong.

Also, the "If eofNoData = True Then" is pretty much a shot in the dark :)

Thanks again for helping out!
FD
 

Forum statistics

Threads
1,136,991
Messages
5,678,996
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top