Report VB Code

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I have a button that I have created which I would like to run a report
with 2 separate conditions. I want it to open the report based on the
current record's employee id & the current record's pay period end
date. I came up with the code below but I get a Type Mismatch. Can
anyone give me any ideas where my code is wrong?

Private Sub PrintHoursSummary_Click()
On Error GoTo Err_PrintHoursSummary_Click

Dim stDocName As String

stDocName = "TimesheetTimesbyVendorIDbyPayPeriod"
strWhere = "[vendorID] = " & "" & Me.VendorID & "" And strWhere =
"[DatePayPeriodEnd] = " & "" & Me.DatePayPeriodEnd & ""
DoCmd.OpenReport strDocName, acPreview, , strWhere

Exit_PrintHoursSummary_Click:
Exit Sub

Err_PrintHoursSummary_Click:
MsgBox Err.Description
Resume Exit_PrintHoursSummary_Click


End Sub
 
I have a button that I have created which I would like to run a report
with 2 separate conditions. I want it to open the report based on the
current record's employee id & the current record's pay period end
date. I came up with the code below but I get a Type Mismatch. Can
anyone give me any ideas where my code is wrong?

Private Sub PrintHoursSummary_Click()
On Error GoTo Err_PrintHoursSummary_Click

Dim stDocName As String

stDocName = "TimesheetTimesbyVendorIDbyPayPeriod"
strWhere = "[vendorID] = " & "" & Me.VendorID & "" And strWhere =
"[DatePayPeriodEnd] = " & "" & Me.DatePayPeriodEnd & ""
DoCmd.OpenReport strDocName, acPreview, , strWhere

Exit_PrintHoursSummary_Click:
Exit Sub

Err_PrintHoursSummary_Click:
MsgBox Err.Description
Resume Exit_PrintHoursSummary_Click


End Sub

What's the datatype of VendorID? Text fields need a delimiter, either ' or ";
Date/TIme fields need # as a delimiter; Number fields get no delimiter at all.

So assuming that VendorID is a number and DatePayPeriodEnd is a Date, you
should be able to use:

strWhere = "[vendorID] = " & Me.VendorID & " And [DatePayPeriodEnd] = #" _
& Format(Me.DatePayPeriodEnd,"mm/dd/yyyy") & "#"

Note that you should not be repeating the strWhere = part. What you need in
the end is a text string such as

[VendorID] = 123 AND [DatePayPeriodEnd] = #03/04/2011#

This is a valid SQL WHERE clause; you can create a query in the query design
window that gets the result you want and switch to SQL view to see how Access
wants it to look.

If your VendorID field is of Text datatype, then you need to include '
delimiters:

strWhere = "[vendorID] = '" & Me.VendorID & "' And [DatePayPeriodEnd] = #" _
& Format(Me.DatePayPeriodEnd,"mm/dd/yyyy") & "#"

to give you

[VendorID] = '123' AND [DatePayPeriodEnd] = #03/04/2011#

If you have the wrong type of delimiter you'll get this error message.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Back
Top