Report VB Code

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
 
J

John W. Vinson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top