Search Record by Month and Year for Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like create reports for the following, would appreciate if someone
could let me know what is wrong with my code, because the report just ignores
the selection values. Thanks a lot!

1. Select date between [date] and [date].
------------------------------------------
Private Sub View_Report_Click()
On Error GoTo Err_View_Report_Click

Dim stDocName As String
Dim strWhere As String
Dim strJoin As String

If IsNull([BeginDate]) And IsNull([EndDate]) Then
MsgBox "Please enter Begin Date and End Date."
DoCmd.GoToControl "BeginDate"
ElseIf IsNull([BeginDate]) Then
MsgBox "Please enter the Begin Date."
DoCmd.GoToControl "BeginDate"
ElseIf IsNull([EndDate]) Then
MsgBox "Please enter the End Date."
DoCmd.GoToControl "EndDate"
ElseIf [BeginDate] > [EndDate] Then
MsgBox "The End Date must be greater than Begin Date."
DoCmd.GoToControl "BeginDate"
Else
strWhere = "[TransactionDate] BETWEEN " & Me.[BeginDate] & " And " &
Me.[EndDate]
stDocName = "TR_Search_Bank sort by JCompany"
DoCmd.OpenReport stDocName, acPreview, , strWhere
DoCmd.Close acForm, "Dialog_Search by MY"
End If

Exit_View_Report_Click:
Exit Sub

Err_View_Report_Click:
DoCmd.Close acForm, "Dialog_Search by MY"
Resume Exit_View_Report_Click

End Sub
 
I forgot to add that the reports derives its data from the
Transaction_Records table. The date field is named TransactionDate and the
type is Date/Time.

Thanks again.
 
Try:
strWhere = "[TransactionDate] BETWEEN #" & _
Me.[BeginDate] & "# And #" & Me.[EndDate] & "#"
You can build your where clause like
strWhere = "Month([TransactionDate]) =" & _
Me.txtMth & " And Year(TransactionDate) = " & Me.txtYear
 
Thanks for helping. I tried the code but it didnt work.

For Dates, I used:
strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "And TransactionDate Between " & _
Format(Me.BeginDate, "\#m\/d\/yyyy\#") _
& " And " & Format(Me.EndDate, "\#m\/d\/yyyy\#")

and it worked.

However, for Month and Year, because I have two combo box cbxMonth and
cbxYear, I used this code:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")

But nothing happened. Please let me know what I did wrong, thanks a lot!

Duane Hookom said:
Try:
strWhere = "[TransactionDate] BETWEEN #" & _
Me.[BeginDate] & "# And #" & Me.[EndDate] & "#"
You can build your where clause like
strWhere = "Month([TransactionDate]) =" & _
Me.txtMth & " And Year(TransactionDate) = " & Me.txtYear


--
Duane Hookom
MS Access MVP


spacerocket said:
I would like create reports for the following, would appreciate if someone
could let me know what is wrong with my code, because the report just ignores
the selection values. Thanks a lot!

1. Select date between [date] and [date].
------------------------------------------
Private Sub View_Report_Click()
On Error GoTo Err_View_Report_Click

Dim stDocName As String
Dim strWhere As String
Dim strJoin As String

If IsNull([BeginDate]) And IsNull([EndDate]) Then
MsgBox "Please enter Begin Date and End Date."
DoCmd.GoToControl "BeginDate"
ElseIf IsNull([BeginDate]) Then
MsgBox "Please enter the Begin Date."
DoCmd.GoToControl "BeginDate"
ElseIf IsNull([EndDate]) Then
MsgBox "Please enter the End Date."
DoCmd.GoToControl "EndDate"
ElseIf [BeginDate] > [EndDate] Then
MsgBox "The End Date must be greater than Begin Date."
DoCmd.GoToControl "BeginDate"
Else
strWhere = "[TransactionDate] BETWEEN " & Me.[BeginDate] & " And " &
Me.[EndDate]
stDocName = "TR_Search_Bank sort by JCompany"
DoCmd.OpenReport stDocName, acPreview, , strWhere
DoCmd.Close acForm, "Dialog_Search by MY"
End If

Exit_View_Report_Click:
Exit Sub

Err_View_Report_Click:
DoCmd.Close acForm, "Dialog_Search by MY"
Resume Exit_View_Report_Click

End Sub
Click to expand...
Click to expand...
 
Several issues with your code:
1) You are missing " AND " prior to Month(...)
2) I expect your Month combo box returns a number 1-12 so use
strWhere = strWhere & " AND Month([TransactionDate]) =" & Me.cbxMonth _
3) I expect Year combo box returns a number so use:
& " And Year(TransactionDate) = " & Me.cbxYear

--
Duane Hookom
MS Access MVP


spacerocket said:
Thanks for helping. I tried the code but it didnt work.

For Dates, I used:
strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "And TransactionDate Between " & _
Format(Me.BeginDate, "\#m\/d\/yyyy\#") _
& " And " & Format(Me.EndDate, "\#m\/d\/yyyy\#")

and it worked.

However, for Month and Year, because I have two combo box cbxMonth and
cbxYear, I used this code:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")

But nothing happened. Please let me know what I did wrong, thanks a lot!

Duane Hookom said:
Try:
strWhere = "[TransactionDate] BETWEEN #" & _
Me.[BeginDate] & "# And #" & Me.[EndDate] & "#"
You can build your where clause like
strWhere = "Month([TransactionDate]) =" & _
Me.txtMth & " And Year(TransactionDate) = " & Me.txtYear


--
Duane Hookom
MS Access MVP


spacerocket said:
I would like create reports for the following, would appreciate if
someone
could let me know what is wrong with my code, because the report just ignores
the selection values. Thanks a lot!

1. Select date between [date] and [date].
------------------------------------------
Private Sub View_Report_Click()
On Error GoTo Err_View_Report_Click

Dim stDocName As String
Dim strWhere As String
Dim strJoin As String

If IsNull([BeginDate]) And IsNull([EndDate]) Then
MsgBox "Please enter Begin Date and End Date."
DoCmd.GoToControl "BeginDate"
ElseIf IsNull([BeginDate]) Then
MsgBox "Please enter the Begin Date."
DoCmd.GoToControl "BeginDate"
ElseIf IsNull([EndDate]) Then
MsgBox "Please enter the End Date."
DoCmd.GoToControl "EndDate"
ElseIf [BeginDate] > [EndDate] Then
MsgBox "The End Date must be greater than Begin Date."
DoCmd.GoToControl "BeginDate"
Else
strWhere = "[TransactionDate] BETWEEN " & Me.[BeginDate] & "
And " &
Me.[EndDate]
stDocName = "TR_Search_Bank sort by JCompany"
DoCmd.OpenReport stDocName, acPreview, , strWhere
DoCmd.Close acForm, "Dialog_Search by MY"
End If

Exit_View_Report_Click:
Exit Sub

Err_View_Report_Click:
DoCmd.Close acForm, "Dialog_Search by MY"
Resume Exit_View_Report_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top