Syntax Error in query expression

[scottyb121 via AccessMonster.com]

I am trying to view a report when you select a record and then click a button
on a form with this as the code:


Private Sub cmdEventLogReport_Click()
Dim stLinkCriteria As String
Dim stDocName As String

stDocName = "rptEventLogReport"


stLinkCriteria = "[PatientName]=" & Me.PatientName
'stLinkCriteria = "[PatientRecordID]=" & Me.PatientRecordID

DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

[PatientName] is a field in a query that matches up with the patient name
field in the form. When I select the record and then press the button i get
a run time error '3075' and I can't figure it out. The code that is
commented out, when not commented out works just fine with [PatientRecordID]
a field in the query that matches up with the patient record id field in the
form.

 

[John Spencer]

Since PatientName is a STRING, you need quote marks around the reference
to it. You can do that in a variety of ways.

stLinkCriteria = "[PatientName]=""" & Me.PatientName & """"

or

stLinkCriteria = "[PatientName]=" & chr(34) & Me.PatientName & Chr(34)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[scottyb121 via AccessMonster.com]

I tried that but I do that I get the error message: 'Invalid Procedure Call
or Argument'

The other issue that may be causing this is that the form with the cmd button
is based on a query, say Query1, and in that query the Patient Name is built
to be Last Name and then a comma and then first Name. The Patient Name in
Query2, which the report is based on, takes the patient name out of a string
with the comma to so the format looks the same(Last, First) but maybe the
comma in the Patient Name field in Query 2, since it is part of the string,
acts differently than the field on the form. If that is the case I don't
know what to do. I really hope that all made sense because I was confusing
myself just typing it.

Since PatientName is a STRING, you need quote marks around the reference
to it. You can do that in a variety of ways.

stLinkCriteria = "[PatientName]=""" & Me.PatientName & """"

or

stLinkCriteria = "[PatientName]=" & chr(34) & Me.PatientName & Chr(34)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am trying to view a report when you select a record and then click a button
on a form with this as the code:

[quoted text clipped - 17 lines]

a field in the query that matches up with the patient record id field in the
form.

 

[scottyb121 via AccessMonster.com]

Also when I go to debug the error message it takes me to the docmd.
OpenRreport line.

I tried that but I do that I get the error message: 'Invalid Procedure Call
or Argument'

The other issue that may be causing this is that the form with the cmd button
is based on a query, say Query1, and in that query the Patient Name is built
to be Last Name and then a comma and then first Name. The Patient Name in
Query2, which the report is based on, takes the patient name out of a string
with the comma to so the format looks the same(Last, First) but maybe the
comma in the Patient Name field in Query 2, since it is part of the string,
acts differently than the field on the form. If that is the case I don't
know what to do. I really hope that all made sense because I was confusing
myself just typing it.

Since PatientName is a STRING, you need quote marks around the reference
to it. You can do that in a variety of ways.

[quoted text clipped - 17 lines]

 

[John Spencer]

I don't see any reason that would fail. The syntax looks correct.

Is it possible that the field name is incorrect in the underlying query?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Also when I go to debug the error message it takes me to the docmd.
OpenRreport line.

I tried that but I do that I get the error message: 'Invalid Procedure Call
or Argument'

The other issue that may be causing this is that the form with the cmd button
is based on a query, say Query1, and in that query the Patient Name is built
to be Last Name and then a comma and then first Name. The Patient Name in
Query2, which the report is based on, takes the patient name out of a string
with the comma to so the format looks the same(Last, First) but maybe the
comma in the Patient Name field in Query 2, since it is part of the string,
acts differently than the field on the form. If that is the case I don't
know what to do. I really hope that all made sense because I was confusing
myself just typing it.

Since PatientName is a STRING, you need quote marks around the reference
to it. You can do that in a variety of ways.

[quoted text clipped - 17 lines]

a field in the query that matches up with the patient record id field in the
form.

 

[scottyb121 via AccessMonster.com]

No, I have double checked to make sure the field name is correct so it isn't
that. I can't figure out why I now keep getting the 'invalid procedure call
or argument' error.

Here is the code for the field name in the report query:
PatientName: IIf(Left([Event],9)="Encounter",Mid([Event],InStr([Event],
"Patient")+8,InStr([Event],"DOB")-InStr([Event],"Patient")-10))
This extracts the name from a long string of data that should have been
broken down into multiple fields

And here is the code for the field name in the form query:
PatientName: dbo_PatRegPatientDemographic.LastName & ", " &
dbo_PatRegPatientDemographic.FirstName & IIf(Not IsNull
(dbo_PatRegPatientDemographic.MiddleName) And Trim
(dbo_PatRegPatientDemographic.MiddleName)<>""," " & Left
(dbo_PatRegPatientDemographic.MiddleName,1))

I don't see any reason that would fail. The syntax looks correct.

Is it possible that the field name is incorrect in the underlying query?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Also when I go to debug the error message it takes me to the docmd.
OpenRreport line.

[quoted text clipped - 17 lines]

 

[scottyb121 via AccessMonster.com]

I just looked through the records when I run my query for the report and
every record in the PatientName field displays the name except for one person.
This person has 7 different records and in each one the PatientName field
shows a #Error instead of their name. So this must be the problem but I
can't figure out why every other record that fits the criteria displays their
name in the PatientName field except this person.

I don't see any reason that would fail. The syntax looks correct.

Is it possible that the field name is incorrect in the underlying query?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Also when I go to debug the error message it takes me to the docmd.
OpenRreport line.

[quoted text clipped - 17 lines]

 

[scottyb121 via AccessMonster.com]

I have figured it out!! The #Error message was coming about for that one
person because part of my code for field in the query was: InStr([Event],
"DOB". The patient name had DOB in it so the code wasn't executing the way
it was supposed to. I just changed it to "DOB=" and everything is working
now. Thank you John for taking time out to try and help me.

I don't see any reason that would fail. The syntax looks correct.

Is it possible that the field name is incorrect in the underlying query?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Also when I go to debug the error message it takes me to the docmd.
OpenRreport line.

[quoted text clipped - 17 lines]