Please - Help w/ Expression in a query

[Guest]

Greetings,

I have a question for the group that I hope somone can help with.

I have this query I'm wrestling with:

The query has a criteria value that comes from a combobox value on a user
form.

The combobox allows users to select a client code to filter the query for a
report. The combobox rowsource property comes from a query:

SELECT tblClients.UNOSCd, IIf([UNOSCd]="ClinImmune","NOT 'COPM'",[UNOSCd])
AS CenterValue
FROM tblClients;

The combobox's bound property is set to column 2

So the Combobox choices look like this:
Column1 (visible column): COUC, COSL, COCH, COPM, ClinImmune
Column 2 (bound Column): COUC, COSL, COCH, COPM, NOT 'COPM'

It all works, except the last choice ClinImmune / NOT 'COPM'

If NOT 'COPM' is typed into the query, it works fine.
If NOT 'COPM' comes from the combobox value, it does not.

What is going on? Any help will be greatly appreciated.

 

[Marshall Barton]

I have this query I'm wrestling with:

The query has a criteria value that comes from a combobox value on a user
form.

The combobox allows users to select a client code to filter the query for a
report. The combobox rowsource property comes from a query:

SELECT tblClients.UNOSCd, IIf([UNOSCd]="ClinImmune","NOT 'COPM'",[UNOSCd])
AS CenterValue
FROM tblClients;

The combobox's bound property is set to column 2

So the Combobox choices look like this:
Column1 (visible column): COUC, COSL, COCH, COPM, ClinImmune
Column 2 (bound Column): COUC, COSL, COCH, COPM, NOT 'COPM'

It all works, except the last choice ClinImmune / NOT 'COPM'

If NOT 'COPM' is typed into the query, it works fine.
If NOT 'COPM' comes from the combobox value, it does not.

The logic (anything other than a value) in a query must be
in the query. I.e. things like =, Not, etc. can not be used
in a parameter.

Instead of using a query parameter to filter the data for
the report, you should use the OpenReport method's
WhereCondition argument. First remove the criteria from the
query. Then look at the form button's Click event procedure
and modify it so it includes something like:

If Me.combobox = "NOT 'COPM'" Then
stCriteria = "[field name] <> 'COPM'"
Else
stCriteria = "[field name] = '" & Me.combobox & "'"
End If
DoCmd.OpenReport stDoc, acViewPreview , , stCriteria

 

[Guest]

Thanks! I'll try that out.

Peace,

Scott C

I have this query I'm wrestling with:

The query has a criteria value that comes from a combobox value on a user
form.

The combobox allows users to select a client code to filter the query for a
report. The combobox rowsource property comes from a query:

SELECT tblClients.UNOSCd, IIf([UNOSCd]="ClinImmune","NOT 'COPM'",[UNOSCd])
AS CenterValue
FROM tblClients;

The combobox's bound property is set to column 2

So the Combobox choices look like this:
Column1 (visible column): COUC, COSL, COCH, COPM, ClinImmune
Column 2 (bound Column): COUC, COSL, COCH, COPM, NOT 'COPM'

It all works, except the last choice ClinImmune / NOT 'COPM'

If NOT 'COPM' is typed into the query, it works fine.
If NOT 'COPM' comes from the combobox value, it does not.

The logic (anything other than a value) in a query must be
in the query. I.e. things like =, Not, etc. can not be used
in a parameter.

Instead of using a query parameter to filter the data for
the report, you should use the OpenReport method's
WhereCondition argument. First remove the criteria from the
query. Then look at the form button's Click event procedure
and modify it so it includes something like:

If Me.combobox = "NOT 'COPM'" Then
stCriteria = "[field name] <> 'COPM'"
Else
stCriteria = "[field name] = '" & Me.combobox & "'"
End If
DoCmd.OpenReport stDoc, acViewPreview , , stCriteria