Attempting to Preview report results in No Data 1

[THWatson]

Using Acccess 2010. A church database.

I have an unbound form called "frmDateAndSeriesSelector"

On it there are 4 unbound text boxes. Two are for entering dates and are named "txtStartDate" and "txtEndDate". The other two are for entering Envelope Numbers, and are named "txtSeriesStart" and "txtSeriesEnd"

The purpose is to allow the user to determine donations between two dates for a series of Envelope Number donors.

When these text box controls are filled in, and the Preview command button is pushed, the result is "There is no data for the time period selected."

However, there is data there. Below is the Record Source for the report. When the report is run by itself, the parameters are called for, filled in, and everything works fine. I also have a query which is the same formulation and it runs fine.

SELECT tblNewGivings.EnvNbr, Switch([AssignedTo]="A",[FirstName] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " ") & [LastName]),[LastName] & " & " & [FirstName2] & " " & [LastName2]),[AssignedTo]="B",[FirstName] & " " & [LastName],[AssignedTo]="C",[FirstName2] & " " & [LastName]) AS CreditTo, Sum(tblNewGivings.Local) AS SumOfLocal, Sum(tblNewGivings.[M and S]) AS [SumOfM and S], Sum(tblNewGivings.Building) AS SumOfBuilding, Sum(tblNewGivings.Memorial) AS SumOfMemorial, Sum(tblNewGivings.Other) AS SumOfOther, Sum([Local]+[M and S]+[Building]+[Memorial]+[Other]) AS Total, tblEnvelopeNumbers.StartDate, tblEnvelopeNumbers.EndDate
FROM tblTrinity INNER JOIN (tblNewGivings INNER JOIN tblEnvelopeNumbers ON tblNewGivings.EnvNbr = tblEnvelopeNumbers.EnvNbr) ON tblTrinity.UniqueID = tblEnvelopeNumbers.UniqueID
WHERE (((tblNewGivings.[Date Given]) Between [StartDate] And [EndDate] And (tblNewGivings.[Date Given]) Between [Forms]![frmDateAndSeriesSelector]![txtStartDate] And [Forms]![frmDateAndSeriesSelector]![txtEndDate]))
GROUP BY tblNewGivings.EnvNbr, Switch([AssignedTo]="A",[FirstName] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " ") & [LastName]),[LastName] & " & " & [FirstName2] & " " & [LastName2]),[AssignedTo]="B",[FirstName] & " " & [LastName],[AssignedTo]="C",[FirstName2] & " " & [LastName]), tblEnvelopeNumbers.StartDate, tblEnvelopeNumbers.EndDate
HAVING (((tblNewGivings.EnvNbr)>0 And (tblNewGivings.EnvNbr) Between [Forms]![frmDateAndSeriesSelector]![txtSeriesStart] And [Forms]![frmDateAndSeriesSelector]![txSeriesEnd]))
ORDER BY tblNewGivings.EnvNbr;

Can anyone indicate why the result is "No Data" when called from the form? Or a better way to make this happen?

Thanks.

Tom

 

[dhookom]

This "There is no data for the time period selected." is not a standard Access error message. Where does it come from?
I would probably replace the monster SWITCH() function with a user-defined function that could be used anywhere in your application.

What happens if you one by one replace a reference to a control criteria with a hard-coded value?

Consider getting rid of the HAVING clause and move the criteria to the WHERE clause:

SELECT tblNewGivings.EnvNbr,
 Switch([AssignedTo]="A",[FirstName] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " ") & [LastName]),[LastName] & " & " & [FirstName2] & " " & [LastName2]),
   [AssignedTo]="B",[FirstName] & " " & [LastName],
   [AssignedTo]="C",[FirstName2] & " " & [LastName]) AS CreditTo,
 Sum(tblNewGivings.Local) AS SumOfLocal,
 Sum(tblNewGivings.[M and S]) AS [SumOfM and S],
 Sum(tblNewGivings.Building) AS SumOfBuilding,
 Sum(tblNewGivings.Memorial) AS SumOfMemorial,
 Sum(tblNewGivings.Other) AS SumOfOther,
 Sum([Local]+[M and S]+[Building]+[Memorial]+[Other]) AS Total,
 tblEnvelopeNumbers.StartDate, tblEnvelopeNumbers.EndDate
FROM tblTrinity
 INNER JOIN (tblNewGivings
 INNER JOIN tblEnvelopeNumbers ON tblNewGivings.EnvNbr = tblEnvelopeNumbers.EnvNbr)
 ON tblTrinity.UniqueID = tblEnvelopeNumbers.UniqueID
WHERE tblNewGivings.[Date Given] Between [StartDate] And [EndDate]
    And tblNewGivings.[Date Given] Between [Forms]![frmDateAndSeriesSelector]![txtStartDate] And [Forms]![frmDateAndSeriesSelector]![txtEndDate]
	And tblNewGivings.EnvNbr Between [Forms]![frmDateAndSeriesSelector]![txtSeriesStart] And [Forms]![frmDateAndSeriesSelector]![txSeriesEnd]
GROUP BY tblNewGivings.EnvNbr, Switch([AssignedTo]="A",[FirstName] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " ") & [LastName]),[LastName] & " & " & [FirstName2] & " " & [LastName2]),[AssignedTo]="B",[FirstName] & " " & [LastName],[AssignedTo]="C",[FirstName2] & " " & [LastName]), tblEnvelopeNumbers.StartDate, tblEnvelopeNumbers.EndDate
ORDER BY tblNewGivings.EnvNbr;

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Thanks for your reply.

Regarding your comments:
No Data "There is no data for the time period selected." is not a standard Access error message. Where does it come from?
It's just a custom No Data message that I constructed.

Switch Function I constructed this several years ago. It's all for the purposes of tracking donations and issuing receipts in the manner requested. In most situations a husband and wife donate together. In other situations one is the donor. In still other situations both donate separately. Your point is well taken that it looks as a bit of a monster, and it is used in a number of places in the database. It was the only way I could figure out how to do it at the time.

Consider getting rid of the HAVING clause and move the criteria to the WHERE clause: I did that. The change in the query runs fine. The report runs fine on its own with this new formulation.
The thing I just can't get my head around is why the parameters don't pass from the Form "frmDateAndSeriesSelector" to the report. No matter what I do it seems I get this No Data message.

Here is all the code behind the report.

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.SumOfLocal = 0 Then
Me.SumOfLocal.Visible = False
Else: Me.SumOfLocal.Visible = True
End If
If Me.SumOfBuilding = 0 Then
Me.SumOfBuilding.Visible = False
Else: Me.SumOfBuilding.Visible = True
End If
If Me.SumOfMemorial = 0 Then
Me.SumOfMemorial.Visible = False
Else: Me.SumOfMemorial.Visible = True
End If
If Me.SumOfM_and_S = 0 Then
Me.SumOfM_and_S.Visible = False
Else: Me.SumOfM_and_S.Visible = True
End If
If Me.SumOfOther = 0 Then
Me.SumOfOther.Visible = False
Else: Me.SumOfOther.Visible = True
End If
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for the time period selected.", vbOKOnly, "No Data"
Cancel = True

End Sub

When I comment out the No Data section of the code, and try to run the report from the prepared form, it faults out in the Format section because it's looking for sums.

If I prepare a new form with only a command button, no text boxes, the report runs and, as expected, asks for the parameters it would get from the form.

What happens if you one by one replace a reference to a control criteria with a hard-coded value That is the one thing I haven't tried yet, and I am going to do that now.

Tom

 

[THWatson]

Duane
I found the problem!

I commented out all the code behind the report. Then I ran it from the form. I received a message saying that Access couldn't find txSeriesEnd

Note either the original Having clause, or that same clause when shifted to a Where clause:

HAVING (((tblNewGivings.EnvNbr)>0 And (tblNewGivings.EnvNbr) Between [Forms]![frmDateAndSeriesSelector]![txtSeriesStart] And [Forms]![frmDateAndSeriesSelector]![txSeriesEnd]))

The very last piece in the code is [txSeriesEnd]. The correct formulation should, of course, be [txtSeriesEnd] which matches the name of the text box in the form. For the want of one letter "t" the whole thing failed.

I had looked and looked and didn't spot that...until I commented out the No Data code.

I really appreciate your help. Sorry to take your time on such a thing as one letter "t".

Tom