Help with an event procedure in a report

[Anita]

I have a report that is functioning well. Data is gathered for the report
through an event procedure. Now it selects data by a Month number "1" for
January info. I would like this to be changed to a specific date. Like
1/9/2008 so I can have more than one years info in the same database or
gather data for a specific date in the same month. I did not design this
database nor am I a desinger. Thanks for your input. The event procedure is
copied below

Dim gPageTotal As Currency
Dim gCount As Integer
Dim gTheDate As Integer
Dim gWarrentDate As String
Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
gPageTotal = gPageTotal + Amount
Me!Text42 = gCount
gCount = gCount + 1
If gCount > 20 Then
Me!PageBreak44.Visible = True
End If

End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
Me!Text27 = Me!Text25
Me!Text40 = gPageTotal
Me!warrentxt = gWarrentDate

End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Me!PageBreak44.Visible = False
gCount = 1
gPageTotal = 0
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String

'Get user input
gTheMonth = InputBox("Enter a number for the month:", "Which Month?")
gWarrentDate = InputBox("What is the warrent date:", "Warrent Date?")

'Verify user input

'Build the select statement and set it as the new record source
strSql = "SELECT Vouchers.Line, Vouchers.Number, Vouchers.Date,
VENDLIST.Payee, Vouchers.Amount "
strSql = strSql + "FROM VENDLIST INNER JOIN Vouchers ON VENDLIST.Vendno
= Vouchers.Vendno "
strSql = strSql + "WHERE (((Vouchers.Date) Like """ +
FormatNumber(gTheMonth, 0)
strSql = strSql + "/*/*"")) ORDER BY Vouchers.Number;"
Me.RecordSource = strSql
'MsgBox (strSql)
End Sub

 

[Marshall Barton]

I have a report that is functioning well. Data is gathered for the report
through an event procedure. Now it selects data by a Month number "1" for
January info. I would like this to be changed to a specific date. Like
1/9/2008 so I can have more than one years info in the same database or
gather data for a specific date in the same month. I did not design this
database nor am I a desinger. Thanks for your input. The event procedure is
copied below

Dim gPageTotal As Currency
Dim gCount As Integer
Dim gTheDate As Integer
Dim gWarrentDate As String
Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
gPageTotal = gPageTotal + Amount
Me!Text42 = gCount
gCount = gCount + 1
If gCount > 20 Then
Me!PageBreak44.Visible = True
End If

End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
Me!Text27 = Me!Text25
Me!Text40 = gPageTotal
Me!warrentxt = gWarrentDate

End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Me!PageBreak44.Visible = False
gCount = 1
gPageTotal = 0
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String

'Get user input
gTheMonth = InputBox("Enter a number for the month:", "Which Month?")
gWarrentDate = InputBox("What is the warrent date:", "Warrent Date?")

'Verify user input

'Build the select statement and set it as the new record source
strSql = "SELECT Vouchers.Line, Vouchers.Number, Vouchers.Date,
VENDLIST.Payee, Vouchers.Amount "
strSql = strSql + "FROM VENDLIST INNER JOIN Vouchers ON VENDLIST.Vendno
= Vouchers.Vendno "
strSql = strSql + "WHERE (((Vouchers.Date) Like """ +
FormatNumber(gTheMonth, 0)
strSql = strSql + "/*/*"")) ORDER BY Vouchers.Number;"
Me.RecordSource = strSql
'MsgBox (strSql)
End Sub

To search for a specific date, change the Where clause to:

.... & "WHERE Vouchers.Date = CDate(" & gTheMonth & ")" ...

Then remember to enter the date when the input box pops up.
You will probable want to change the input box title and
text to remind you what to enter.

On a different topic, the code in the page header and detail
format events is not a reliable way to calculate a total or
count the lines. Keep this in mind if you ever see
something funny wht these value in the report.

 

[Anita]

Thanks for the help Marshall, when I made the change you suggested below I
received a Run Time Error "13" Type Mismatch on the gTheMonth = Input Box
line. What does it want me the change.

 

[Marshall Barton]

Since you never declared the variable gTheMonth in the
report's module, I don't see how it could get that error.
Is the program really using the gTheDate variable and you
made a transcription error?

OTOH, maybe gTheMonth is declared in some other module.
Either way, gTheMonth should be declared As String.

I see that you did not use the line:
OPTION EXPLICIT
at the top of the module. Without that line, you will not be
notified when you use a variable name that you forgot to
declare.