DoCmd.OpenReport

[TechTutors]

Dim ReportName

If Me.Action = 1 Then ReportName = "Rpt_ActivationBounty_Detail"
If Me.Action = 2 Then ReportName = "Rpt_RevenueShare_Detail"
If Me.Action = 3 Then ReportName = "Rpt_ClientSummary"
If Me.Action = 4 Then ReportName = "Rpt_CheckRequisition"
If Me.Action = 5 Then ReportName = "Rpt_ItemCodes_NONVAR_AUDIT"
If Me.Action = 6 Then ReportName = "Rpt_ItemCodes_VAR_AUDIT"

DoCmd.OpenReport ReportName, acViewPreview, , "Partner_ID=" & Me.Lkp



The client selects a Partner from a drop-down list... Then selects which
report they want to open for that Partner. They Hit GO! and an "enter
parameter" fields comes up asking them to type the Partner_ID... They type
it in and it works just fine...

.... Except for a handful of partners... I recieve a Run-Time error 3075...
Syntax error (missing operator) in query expression
'(Partner_ID=SAIDPARTNER)'.

Any help would greatly be appreciated!

Thanks!

 

[Dirk Goldgar]

Dim ReportName

If Me.Action = 1 Then ReportName = "Rpt_ActivationBounty_Detail"
If Me.Action = 2 Then ReportName = "Rpt_RevenueShare_Detail"
If Me.Action = 3 Then ReportName = "Rpt_ClientSummary"
If Me.Action = 4 Then ReportName = "Rpt_CheckRequisition"
If Me.Action = 5 Then ReportName = "Rpt_ItemCodes_NONVAR_AUDIT"
If Me.Action = 6 Then ReportName = "Rpt_ItemCodes_VAR_AUDIT"

DoCmd.OpenReport ReportName, acViewPreview, , "Partner_ID=" & Me.Lkp



The client selects a Partner from a drop-down list... Then selects which
report they want to open for that Partner. They Hit GO! and an "enter
parameter" fields comes up asking them to type the Partner_ID... They
type
it in and it works just fine...

... Except for a handful of partners... I recieve a Run-Time error 3075...
Syntax error (missing operator) in query expression
'(Partner_ID=SAIDPARTNER)'.

What's the value of Me.Lkp when it fails? What type of field is Partner_ID?

 

[Tom Lake]

Dim ReportName

If Me.Action = 1 Then ReportName = "Rpt_ActivationBounty_Detail"
If Me.Action = 2 Then ReportName = "Rpt_RevenueShare_Detail"
If Me.Action = 3 Then ReportName = "Rpt_ClientSummary"
If Me.Action = 4 Then ReportName = "Rpt_CheckRequisition"
If Me.Action = 5 Then ReportName = "Rpt_ItemCodes_NONVAR_AUDIT"
If Me.Action = 6 Then ReportName = "Rpt_ItemCodes_VAR_AUDIT"

DoCmd.OpenReport ReportName, acViewPreview, , "Partner_ID=" & Me.Lkp



The client selects a Partner from a drop-down list... Then selects which
report they want to open for that Partner. They Hit GO! and an "enter
parameter" fields comes up asking them to type the Partner_ID... They
type
it in and it works just fine...

... Except for a handful of partners... I recieve a Run-Time error 3075...
Syntax error (missing operator) in query expression
'(Partner_ID=SAIDPARTNER)'.

Any help would greatly be appreciated!

Is Partner_ID numeric? If not, try

DoCmd.OpenReport ReportName, acViewPreview, , "Partner_ID='" & Me.Lkp & "'"

Tom Lake

 

[J_Goddard via AccessMonster.com]

Hi -

This sounds like what might happen if a partner name has an apostrophe in it,

e.g. Jake's Machine Shop.

As for the Partner_ID being asked for again, what is the record source for
the report? If it is a query, it sounds like the query has a parameter in it,
which is unnecessary because the Docmd.OpenReport.... filters the records.
Another possibility is that the report contains a sort field which is not in
the source query (typing error maybe?)

HTH

John

 

[TechTutors]

The fields are set to text and the reports are working off queries, of
course. I'm not sure what you mean by what the value of the field is when it
fails. If the incorrect information is put in the parameter, then the
actions will fail and no reports will come up. I hope that answered your
question.

In this case, however, there are - in fact - reports for these "SAIDPARTNERS"

I hope that answered your question.

 

[TechTutors]

Unfortunately, that did not work. Thank you though!

 

[TechTutors]

As a matter of fact, the query that runs the report has a crieteria parameter
in it for a different field. For descriptive purposes, the field is called
[Inactive?] and the criteria is set to "no".

Aside from that, in this particular query, the Parnter_IDs are set to
acending.

As for the names themeselves, they don't have any specific characters that
might corrupt the process. One has a "." in it... but the others are plain
text.

Thanks!

 

[J_Goddard via AccessMonster.com]

Hi -

According to your first post, the client selects the partner from a dropdown
list, and that's fine. What you need to do is determine why they are being
asked to enter the Partner_ID again, as a parameter to - what? the query?

When does the error 3075 appear? When data is entered in the "parameter" box,
or before?

Can you give us an example of real data that causes the error, i.e. a real
Partner_ID? There must be something about the data that causes the problem
to be intermittent.

John

As a matter of fact, the query that runs the report has a crieteria parameter
in it for a different field. For descriptive purposes, the field is called
[Inactive?] and the criteria is set to "no".

Aside from that, in this particular query, the Parnter_IDs are set to
acending.

As for the names themeselves, they don't have any specific characters that
might corrupt the process. One has a "." in it... but the others are plain
text.

Thanks!

Hi -

[quoted text clipped - 35 lines]

 

[TechTutors]

The name of the dropdown is "Lkp." This lists all the Partners they can
choose from.

The code I provided is the "Go!" button, by the way.

Now, the 3075 appears after the client selects their partner from the drop
down, selects which the corresponding report and hit's "Go!" Once they hit
go... the error appears. A parameter box doesn't even pop up.

An example of one of the partners is: Safety Sam

Hi -

According to your first post, the client selects the partner from a dropdown
list, and that's fine. What you need to do is determine why they are being
asked to enter the Partner_ID again, as a parameter to - what? the query?

When does the error 3075 appear? When data is entered in the "parameter" box,
or before?

Can you give us an example of real data that causes the error, i.e. a real
Partner_ID? There must be something about the data that causes the problem
to be intermittent.

John

As a matter of fact, the query that runs the report has a crieteria parameter
in it for a different field. For descriptive purposes, the field is called
[Inactive?] and the criteria is set to "no".

Aside from that, in this particular query, the Parnter_IDs are set to
acending.

As for the names themeselves, they don't have any specific characters that
might corrupt the process. One has a "." in it... but the others are plain
text.

Thanks!

Hi -

[quoted text clipped - 35 lines]

Thanks!

 

[Dirk Goldgar]

The fields are set to text and the reports are working off queries, of
course. I'm not sure what you mean by what the value of the field is when
it
fails. If the incorrect information is put in the parameter, then the
actions will fail and no reports will come up. I hope that answered your
question.

In this case, however, there are - in fact - reports for these
"SAIDPARTNERS"

I hope that answered your question.

Not exactly, but if Partner_ID is a text field, your problem might go away
if you changed the button's code from this:

DoCmd.OpenReport ReportName, acViewPreview, , "Partner_ID=" & Me.Lkp

to this:

DoCmd.OpenReport ReportName, acViewPreview, , _
"Partner_ID=" & Chr(34) & Me.Lkp & Chr(34)

 

[J_Goddard via AccessMonster.com]

Hi -

Safety Sam is not as plain as it appears - it has a blank in it. In the code
for the GO button, make sure to put single quotes around the value:

DoCmd.OpenReport ReportName, acViewPreview, , "Partner_ID='" & Me.Lkp & "'"

Does this help?

John

The name of the dropdown is "Lkp." This lists all the Partners they can
choose from.

The code I provided is the "Go!" button, by the way.

Now, the 3075 appears after the client selects their partner from the drop
down, selects which the corresponding report and hit's "Go!" Once they hit
go... the error appears. A parameter box doesn't even pop up.

An example of one of the partners is: Safety Sam

Hi -

[quoted text clipped - 29 lines]

 

[TechTutors]

Worked like a charm! THANKS!!

Mind if I ask what this did? Thanks!

 

[TechTutors]

Seems to have worked! Thanks!!!

Not exactly, but if Partner_ID is a text field, your problem might go away
if you changed the button's code from this:


to this:

DoCmd.OpenReport ReportName, acViewPreview, , _
"Partner_ID=" & Chr(34) & Me.Lkp & Chr(34)


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Worked like a charm! THANKS!!

You're welcome.

Mind if I ask what this did? Thanks!

In order to be interpreted properly, literal text values in SQL statements
need to be enclosed in quotes. The quotes can be single quotes/apostrophes
(') or double quotes ("), but of course you must use the same type of quote
at either end of the literal. The WhereCondition argument of the
DoCmd.OpenForm method will be applied (behind the scenes) as part of a SQL
statement, so this rule applies to it.

Your original where-condition ...

"Partner_ID=" & Me.Lkp

.... didn't put any quotes around the partner ID value from the Lkp combo
box. So if, for example, that value happened to be "FRED", then the
completed where-condition would have a text value like this:

Partner_ID=FRED

Since FRED isn't a nmber and isn't the name of a field in the table, the
query engine will assume it's a parameter and prompt you to enter it.

What you need to pass instead of the above constructed where-condition is
either of the following:

Partner_ID='FRED'
Partner_ID="FRED"

When building name values into strings, it's usually safer to use the
double-quotes, since names often contain apostrophes. That's what I chose
to do.

The trick, when building a SQL string like this in code, is to get the
double-quotes into a string without confusing them with the literal quotes
that are being used to enclose the string literals from which the larger
element is being built. You can do it by doubling up the quotes, like this
....

"Partner_ID=""" & Me.Lkp & """"

.... but that's a bit hard to read. So I prefer to use the Chr(34) function
to return the double-quote character itself, and concatenate that into the
string I'm building. Hence I suggested this:

"Partner_ID=" & Chr(34) & Me.Lkp & Chr(34)

If Me.LKP is "FRED", then the above resolves to:

Partner_ID="FRED"

Clear as mud?

--

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[TechTutors]

Unfortunately, that didn't work... but...

DoCmd.OpenReport ReportName, acViewPreview, , "Partner_ID='" & Chr(34) &
Me.Lkp & Chr(34)

.... did

I'm not sure why... but I asked the person who provided it... Waiting for a
reply now.

MANY... MANY... Thanks for walking through this with me!

Hi -

Safety Sam is not as plain as it appears - it has a blank in it. In the code
for the GO button, make sure to put single quotes around the value:

DoCmd.OpenReport ReportName, acViewPreview, , "Partner_ID='" & Me.Lkp & "'"

Does this help?

John

The name of the dropdown is "Lkp." This lists all the Partners they can
choose from.

The code I provided is the "Go!" button, by the way.

Now, the 3075 appears after the client selects their partner from the drop
down, selects which the corresponding report and hit's "Go!" Once they hit
go... the error appears. A parameter box doesn't even pop up.

An example of one of the partners is: Safety Sam

Hi -

[quoted text clipped - 29 lines]

Thanks!