Open a report to specific data

  • Thread starter Thread starter margaret
  • Start date Start date
M

margaret

I have a form that I have a button that opens a report. I want it to open to
a specific criteria. I have the following code:

Private Sub Command111_Click()
On Error GoTo Err_Command111_Click

Dim stDocName As String
Dim stWhere As String

stDocName = "RPTNew Day by FullName"
stWhere = "[FullName]=" & Me![fullname]
DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_Command111_Click:
Exit Sub

Err_Command111_Click:
MsgBox Err.Description
Resume Exit_Command111_Click

End Sub

However, when opening it give me this message: Syntex error comma in query
expression '[fullname]=lname,fname' (obviously with the last name and first
name). I can run this report outside the form and it runs great, so I think
it has something to do with my stuff above.

Thanks.
 
Hi Margaret,

I expect the problem arises from your strWhere, which contains the fieldname
and the valuse you want to filter by. The value is a string, so it must be
enclosed in delimiters. The simplest way is this:
stWhere = "[FullName]='" & Me![fullname] & "'"
(expanded for clarity: stWhere = "[FullName]=' " & Me![fullname] & " ' ")

However, if you have anyone named O'Hara, for example, this will fail. A
safer option is:
stWhere = "[FullName]=""" & Me![fullname] & """"
(expanded for clarity: stWhere = "[FullName]=" " " & Me![fullname] & " " "
")

HTH,

Rob
 
That worked perfectly ... thanks for your help.

Rob Parker said:
Hi Margaret,

I expect the problem arises from your strWhere, which contains the fieldname
and the valuse you want to filter by. The value is a string, so it must be
enclosed in delimiters. The simplest way is this:
stWhere = "[FullName]='" & Me![fullname] & "'"
(expanded for clarity: stWhere = "[FullName]=' " & Me![fullname] & " ' ")

However, if you have anyone named O'Hara, for example, this will fail. A
safer option is:
stWhere = "[FullName]=""" & Me![fullname] & """"
(expanded for clarity: stWhere = "[FullName]=" " " & Me![fullname] & " " "
")

HTH,

Rob

margaret said:
I have a form that I have a button that opens a report. I want it to open
to
a specific criteria. I have the following code:

Private Sub Command111_Click()
On Error GoTo Err_Command111_Click

Dim stDocName As String
Dim stWhere As String

stDocName = "RPTNew Day by FullName"
stWhere = "[FullName]=" & Me![fullname]
DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_Command111_Click:
Exit Sub

Err_Command111_Click:
MsgBox Err.Description
Resume Exit_Command111_Click

End Sub

However, when opening it give me this message: Syntex error comma in
query
expression '[fullname]=lname,fname' (obviously with the last name and
first
name). I can run this report outside the form and it runs great, so I
think
it has something to do with my stuff above.

Thanks.
Click to expand...
Click to expand...
 
Back
Top