Reports Generating Incorrect Data

[Matt Rebraca's Question]

Hello, I have created a database with 3 forms, 3 reports and multiple
queries. On the one form there are three drop down boxes (Analyst, Reviewer,
Year) and there are 3 cmd buttons (Detailed Report, Summary Report, Exit).
When I click either of the Report cmd buttons it generates a report for each
record in the database rather then one report based on the selectiosn I made
from the drop down boxes.

For example I have two records in the database for testing purposes. If I
select Matt for the analyst and Angella for the reviewer then hit one of the
report buttons it gives me a report for all the records in the database it
does not single out a report for Matt being the analyst & Angella being the
reviewer.

I have another form that does this perfectly and I cannot for the life of me
fidn the discrepency.

 

[Jerry Whittle]

What does the code for the On Click event of the button in question say?

 

[Matt Rebraca''s Question]

Dim db As DAO.Database

ComboA.Value = Null
ComboR.Value = Null
ComboY.Value = Null

End Sub

Private Sub Command6_Click()

Dim db As DAO.Database

Dim stDocName As String

Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set qdf = db.QueryDefs("Q4")

If IsNull(Me.ComboA.Value) Then
strAnalyst = " Like '*' AND"
Else
strAnalyst = " like '" & Me.ComboA.Value & "' AND"
End If

If IsNull(Me.ComboR.Value) Then
strReviewer = " Like '*'AND"
Else
strReviewer = " like '" & Me.ComboR.Value & "' AND"
End If

If IsNull(Me.ComboY.Value) Then
StrYear = " Like '*'"
Else
StrYear = " like '*" & Me.ComboY.Value & "*' "
End If


strSQL = "SELECT [combine].* FROM [combine] WHERE [combine].Analyst" &
strAnalyst & "[combine].Reviewer" & strReviewer & "[combine].[submit time]" &
StrYear & ";"
qdf.SQL = strSQL

Set Rst = CurrentDb.OpenRecordset("SELECT [combine].* From [combine] where "
& "[combine].[Analyst]" & strAnalyst & " [combine].[Reviewer]" & strReviewer
& "[combine].[submit time]" & StrYear & ";")
If Rst.BOF Then
MsgBox "No Data for this Selection"
Else
stDocName = "Detailed Report"
DoCmd.OpenReport stDocName, acPreview
End If

End Sub

Private Sub Command7_Click()

Dim db As DAO.Database

Dim stDocName As String

Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set qdf = db.QueryDefs("sumqry")

If IsNull(Me.ComboA.Value) Then
strAnalyst = " Like '*' AND"
Else
strAnalyst = " like '" & Me.ComboA.Value & "' AND"
End If

If IsNull(Me.ComboR.Value) Then
strReviewer = " Like '*'AND"
Else
strReviewer = " like '" & Me.ComboR.Value & "' AND"
End If

If IsNull(Me.ComboY.Value) Then
StrYear = " Like '*'"
Else
StrYear = " like '*" & Me.ComboY.Value & "*' "
End If


strSQL = "SELECT [CalAvg].* From [CalAvg] where " & " [CalAvg].[Analyst]" &
strAnalyst & " [CalAvg].[Reviewer]" & strReviewer & " [CalAvg].[submit time]"
& StrYear & ";"
qdf.SQL = strSQL


Set Rst = CurrentDb.OpenRecordset("SELECT [CalAvg].* From [CalAvg] where " &
"[CalAvg].[Analyst]" & strAnalyst & " [CalAvg].[Reviewer]" & strReviewer & "
[CalAvg].[submit time]" & StrYear & ";")
If Rst.BOF Then
MsgBox "No Data for this Selection"
Else
stDocName = "Summary Report"
DoCmd.OpenReport stDocName, acPreview
End If


End Sub
Private Sub Command9_Click()
On Error GoTo Err_Command9_Click


DoCmd.Close

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

 

[Jerry Whittle]

I'm not that great of a coder, but I don't see anything obvious. It might be
some of the joins or logic aren't quite right.

Sorry that I can't be of more help. You might want to cross post it on the
Forms Coding group.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Dim db As DAO.Database

ComboA.Value = Null
ComboR.Value = Null
ComboY.Value = Null

End Sub

Private Sub Command6_Click()

Dim db As DAO.Database

Dim stDocName As String

Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set qdf = db.QueryDefs("Q4")

If IsNull(Me.ComboA.Value) Then
strAnalyst = " Like '*' AND"
Else
strAnalyst = " like '" & Me.ComboA.Value & "' AND"
End If

If IsNull(Me.ComboR.Value) Then
strReviewer = " Like '*'AND"
Else
strReviewer = " like '" & Me.ComboR.Value & "' AND"
End If

If IsNull(Me.ComboY.Value) Then
StrYear = " Like '*'"
Else
StrYear = " like '*" & Me.ComboY.Value & "*' "
End If


strSQL = "SELECT [combine].* FROM [combine] WHERE [combine].Analyst" &
strAnalyst & "[combine].Reviewer" & strReviewer & "[combine].[submit time]" &
StrYear & ";"
qdf.SQL = strSQL

Set Rst = CurrentDb.OpenRecordset("SELECT [combine].* From [combine] where "
& "[combine].[Analyst]" & strAnalyst & " [combine].[Reviewer]" & strReviewer
& "[combine].[submit time]" & StrYear & ";")
If Rst.BOF Then
MsgBox "No Data for this Selection"
Else
stDocName = "Detailed Report"
DoCmd.OpenReport stDocName, acPreview
End If

End Sub

Private Sub Command7_Click()

Dim db As DAO.Database

Dim stDocName As String

Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set qdf = db.QueryDefs("sumqry")

If IsNull(Me.ComboA.Value) Then
strAnalyst = " Like '*' AND"
Else
strAnalyst = " like '" & Me.ComboA.Value & "' AND"
End If

If IsNull(Me.ComboR.Value) Then
strReviewer = " Like '*'AND"
Else
strReviewer = " like '" & Me.ComboR.Value & "' AND"
End If

If IsNull(Me.ComboY.Value) Then
StrYear = " Like '*'"
Else
StrYear = " like '*" & Me.ComboY.Value & "*' "
End If


strSQL = "SELECT [CalAvg].* From [CalAvg] where " & " [CalAvg].[Analyst]" &
strAnalyst & " [CalAvg].[Reviewer]" & strReviewer & " [CalAvg].[submit time]"
& StrYear & ";"
qdf.SQL = strSQL


Set Rst = CurrentDb.OpenRecordset("SELECT [CalAvg].* From [CalAvg] where " &
"[CalAvg].[Analyst]" & strAnalyst & " [CalAvg].[Reviewer]" & strReviewer & "
[CalAvg].[submit time]" & StrYear & ";")
If Rst.BOF Then
MsgBox "No Data for this Selection"
Else
stDocName = "Summary Report"
DoCmd.OpenReport stDocName, acPreview
End If


End Sub
Private Sub Command9_Click()
On Error GoTo Err_Command9_Click


DoCmd.Close

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

What does the code for the On Click event of the button in question say?

 

[Jeanette Cunningham]

Hi Matt,
do some testing with your code like this:
after the line >>

strSQL = "SELECT [combine].* FROM [combine] WHERE [combine].Analyst" &
strAnalyst & "[combine].Reviewer" & strReviewer & "[combine].[submit
time]" &
StrYear & ";"

add this line:
debug.print "sql: " & strSQL

do the same thing for the other SQL's on your form

run the report, close the form
hit Ctl + G to open the immediate window
you will see sql: followed by a space and then the query string
copy the query string and paste in into a new query in sql view
now switch the query to datasheet view and see which records it gives you
With the above process, you can often spot the error with your query.

Jeanette Cunningham

Dim db As DAO.Database

ComboA.Value = Null
ComboR.Value = Null
ComboY.Value = Null

End Sub

Private Sub Command6_Click()

Dim db As DAO.Database

Dim stDocName As String

Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set qdf = db.QueryDefs("Q4")

If IsNull(Me.ComboA.Value) Then
strAnalyst = " Like '*' AND"
Else
strAnalyst = " like '" & Me.ComboA.Value & "' AND"
End If

If IsNull(Me.ComboR.Value) Then
strReviewer = " Like '*'AND"
Else
strReviewer = " like '" & Me.ComboR.Value & "' AND"
End If

If IsNull(Me.ComboY.Value) Then
StrYear = " Like '*'"
Else
StrYear = " like '*" & Me.ComboY.Value & "*' "
End If


strSQL = "SELECT [combine].* FROM [combine] WHERE [combine].Analyst" &
strAnalyst & "[combine].Reviewer" & strReviewer & "[combine].[submit
time]" &
StrYear & ";"
qdf.SQL = strSQL

Set Rst = CurrentDb.OpenRecordset("SELECT [combine].* From [combine] where
"
& "[combine].[Analyst]" & strAnalyst & " [combine].[Reviewer]" &
strReviewer
& "[combine].[submit time]" & StrYear & ";")
If Rst.BOF Then
MsgBox "No Data for this Selection"
Else
stDocName = "Detailed Report"
DoCmd.OpenReport stDocName, acPreview
End If

End Sub

Private Sub Command7_Click()

Dim db As DAO.Database

Dim stDocName As String

Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set qdf = db.QueryDefs("sumqry")

If IsNull(Me.ComboA.Value) Then
strAnalyst = " Like '*' AND"
Else
strAnalyst = " like '" & Me.ComboA.Value & "' AND"
End If

If IsNull(Me.ComboR.Value) Then
strReviewer = " Like '*'AND"
Else
strReviewer = " like '" & Me.ComboR.Value & "' AND"
End If

If IsNull(Me.ComboY.Value) Then
StrYear = " Like '*'"
Else
StrYear = " like '*" & Me.ComboY.Value & "*' "
End If


strSQL = "SELECT [CalAvg].* From [CalAvg] where " & " [CalAvg].[Analyst]"
&
strAnalyst & " [CalAvg].[Reviewer]" & strReviewer & " [CalAvg].[submit
time]"
& StrYear & ";"
qdf.SQL = strSQL


Set Rst = CurrentDb.OpenRecordset("SELECT [CalAvg].* From [CalAvg] where "
&
"[CalAvg].[Analyst]" & strAnalyst & " [CalAvg].[Reviewer]" & strReviewer &
"
[CalAvg].[submit time]" & StrYear & ";")
If Rst.BOF Then
MsgBox "No Data for this Selection"
Else
stDocName = "Summary Report"
DoCmd.OpenReport stDocName, acPreview
End If


End Sub
Private Sub Command9_Click()
On Error GoTo Err_Command9_Click


DoCmd.Close

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

What does the code for the On Click event of the button in question say?