Qry Criteria too Complex

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for taking the time to read my question.

I had this as my criteria and it worked:

Like
IIf(IsNull([Forms]![frmReportCenter]![cbo_RegionForReport]),"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

I changed it to this and now Access is telling me it is too complex:

Like
IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=1,"*",IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=2,"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

Have I done something wrong? Is it really too complex?
 
Brad said:
Thanks for taking the time to read my question.

I had this as my criteria and it worked:

Like
IIf(IsNull([Forms]![frmReportCenter]![cbo_RegionForReport]),"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

I changed it to this and now Access is telling me it is too complex:

Like
IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=1,"*",IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=2,"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

Have I done something wrong? Is it really too complex?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're using LIKE incorrectly. The way you've set it up it looks like
you should be using the equal sign, instead.

But, the expression seems to indicate that if the ComboBox equals either
1 or 2 that you want the column to equal anything (*); otherwise, you
want only records where the column equals the contents of the ComboBox.
If this is true, you can use an expression like this:

IIf(Forms!frmReportCenter!cbo_RegionForReport IN (1,2), [column_name],
Forms!frmReportCenter!cbo_RegionForReport)

Substitute the real column name for [column_name].

What it does: It will set the comparison to this:

If the value is 1 or 2:

column_name = column_name

This will mean you don't care what is in the column, just return all
records (filtered by any other column's criteria).

Otherwise, (the ELSE part of the IIf() function) the criteria will be
this:

column_name = Forms!frmReportCenter!cbo_RegionForReport

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/9pc4echKqOuFEgEQJxPwCdGQno9D5t8kcG86pLOMQIIar32I0AoIP0
qKJVr9SVVTCo+gB97T0zds77
=UG3B
-----END PGP SIGNATURE-----
 
Hmm, I've learned something new today. Thanks!

It only works if I choose 1 (All) or 2(All - By Region), but if I choose any
other value it doesn't work, which is the same as what happened with my Like
iif(...)

My combobox looks like this

Region SelectType ShowType
1 "All" "All"
2 "All - By Region" "All - By Region"
"AB" "AB"
"MBE" "MBE"
"MBW" "MBW"

Column width is 0;0;1
Bound column is 1

I am using a Union query as the source.
SELECT tblFarmData.Region, "" AS SelectType, [tblFarmData]![Region] AS
ShowType FROM tblFarmData GROUP BY tblFarmData.Region, "",
[tblFarmData]![Region] HAVING ((Not (tblFarmData.Region) Is Null)) UNION
SELECT 1 AS Region, "All" AS SelectType, "All" AS ShowType FROM tblFarmData
UNION SELECT 2 AS Region, "All - By Region" AS SelectType, "All - By Region"
AS ShowType FROM tblFarmData
ORDER BY tblFarmData.Region;

I also tried this in my criteria to get double quotes around the text value.

Like
IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=1,"*",IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=2,"*",""""
& [Forms]![frmReportCenter]![cbo_RegionForReport] & """"))

Same result and error.

Brad

MGFoster said:
Brad said:
Thanks for taking the time to read my question.

I had this as my criteria and it worked:

Like
IIf(IsNull([Forms]![frmReportCenter]![cbo_RegionForReport]),"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

I changed it to this and now Access is telling me it is too complex:

Like
IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=1,"*",IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=2,"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

Have I done something wrong? Is it really too complex?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're using LIKE incorrectly. The way you've set it up it looks like
you should be using the equal sign, instead.

But, the expression seems to indicate that if the ComboBox equals either
1 or 2 that you want the column to equal anything (*); otherwise, you
want only records where the column equals the contents of the ComboBox.
If this is true, you can use an expression like this:

IIf(Forms!frmReportCenter!cbo_RegionForReport IN (1,2), [column_name],
Forms!frmReportCenter!cbo_RegionForReport)

Substitute the real column name for [column_name].

What it does: It will set the comparison to this:

If the value is 1 or 2:

column_name = column_name

This will mean you don't care what is in the column, just return all
records (filtered by any other column's criteria).

Otherwise, (the ELSE part of the IIf() function) the criteria will be
this:

column_name = Forms!frmReportCenter!cbo_RegionForReport

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/9pc4echKqOuFEgEQJxPwCdGQno9D5t8kcG86pLOMQIIar32I0AoIP0
qKJVr9SVVTCo+gB97T0zds77
=UG3B
-----END PGP SIGNATURE-----
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It depends on the data type of the column you are querying - it has to
be the same as the bound column of the ComboBox box. In this case a
Text data type. Try running the query w/ just "AB" as the column's
criteria (assuming that there are records w/ "AB" in the column). If
that works, put the expression in a Field cell and run the query to see
if the expression is returning what it is supposed to return - the value
of the combo box's bound column. Use the expression I posted.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/90a4echKqOuFEgEQLpjQCg6c8ei7MIPhwbbQTsK0HxB87e7UMAoNTX
KxFuqwzyNYGA/DEJCb0aonuw
=wRg9
-----END PGP SIGNATURE-----
Hmm, I've learned something new today. Thanks!

It only works if I choose 1 (All) or 2(All - By Region), but if I choose any
other value it doesn't work, which is the same as what happened with my Like
iif(...)

My combobox looks like this

Region SelectType ShowType
1 "All" "All"
2 "All - By Region" "All - By Region"
"AB" "AB"
"MBE" "MBE"
"MBW" "MBW"

Column width is 0;0;1
Bound column is 1

I am using a Union query as the source.
SELECT tblFarmData.Region, "" AS SelectType, [tblFarmData]![Region] AS
ShowType FROM tblFarmData GROUP BY tblFarmData.Region, "",
[tblFarmData]![Region] HAVING ((Not (tblFarmData.Region) Is Null)) UNION
SELECT 1 AS Region, "All" AS SelectType, "All" AS ShowType FROM tblFarmData
UNION SELECT 2 AS Region, "All - By Region" AS SelectType, "All - By Region"
AS ShowType FROM tblFarmData
ORDER BY tblFarmData.Region;

I also tried this in my criteria to get double quotes around the text value.

Like
IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=1,"*",IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=2,"*",""""
& [Forms]![frmReportCenter]![cbo_RegionForReport] & """"))

Same result and error.

Brad

:

Brad said:
Thanks for taking the time to read my question.

I had this as my criteria and it worked:

Like
IIf(IsNull([Forms]![frmReportCenter]![cbo_RegionForReport]),"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

I changed it to this and now Access is telling me it is too complex:

Like
IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=1,"*",IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=2,"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

Have I done something wrong? Is it really too complex?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're using LIKE incorrectly. The way you've set it up it looks like
you should be using the equal sign, instead.

But, the expression seems to indicate that if the ComboBox equals either
1 or 2 that you want the column to equal anything (*); otherwise, you
want only records where the column equals the contents of the ComboBox.
If this is true, you can use an expression like this:

IIf(Forms!frmReportCenter!cbo_RegionForReport IN (1,2), [column_name],
Forms!frmReportCenter!cbo_RegionForReport)

Substitute the real column name for [column_name].

What it does: It will set the comparison to this:

If the value is 1 or 2:

column_name = column_name

This will mean you don't care what is in the column, just return all
records (filtered by any other column's criteria).

Otherwise, (the ELSE part of the IIf() function) the criteria will be
this:

column_name = Forms!frmReportCenter!cbo_RegionForReport

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/9pc4echKqOuFEgEQJxPwCdGQno9D5t8kcG86pLOMQIIar32I0AoIP0
qKJVr9SVVTCo+gB97T0zds77
=UG3B
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
 
I created a field labeled Test in my query.

Test: [Forms]![frmReportCenter]![cbo_RegionForReport]

It returned the value of the combobox. If I picked "All" or "All - By
Region" it returned 1 or 2, otherwise it returned "AB", "MBE", or "MBW"

I also set Test: to this.

Test: IIf([Forms]![frmReportCenter]![cbo_RegionForReport] In
(1,2),[tblFarmData]![Region],[Forms]![frmReportCenter]![cbo_RegionForReport])

Same error as if it was in the criteria line. All and All - By Region work
but not the other values.


If I put "MBW" in the criteria line of [Region] it puts double quotes around
it. If I put an asterisk it puts Like "*". Both work as filtering criteria.

the [Region] field is text, so that is why if the combobox is 1 or 2 it
returns "*". The other values are already text. So the criteria statement
should return either "*" or MBW, MBE, or AB. The text values don't work,
that is why I tried adding the """" to the formula, to get the quotes in
there.

I checked the RowSource of the combobox and the bound column. It is text.
The numbers in that field align left, like the other text values.

Maybe I should just write a function???

Brad

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It depends on the data type of the column you are querying - it has to
be the same as the bound column of the ComboBox box. In this case a
Text data type. Try running the query w/ just "AB" as the column's
criteria (assuming that there are records w/ "AB" in the column). If
that works, put the expression in a Field cell and run the query to see
if the expression is returning what it is supposed to return - the value
of the combo box's bound column. Use the expression I posted.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/90a4echKqOuFEgEQLpjQCg6c8ei7MIPhwbbQTsK0HxB87e7UMAoNTX
KxFuqwzyNYGA/DEJCb0aonuw
=wRg9
-----END PGP SIGNATURE-----
Hmm, I've learned something new today. Thanks!

It only works if I choose 1 (All) or 2(All - By Region), but if I choose any
other value it doesn't work, which is the same as what happened with my Like
iif(...)

My combobox looks like this

Region SelectType ShowType
1 "All" "All"
2 "All - By Region" "All - By Region"
"AB" "AB"
"MBE" "MBE"
"MBW" "MBW"

Column width is 0;0;1
Bound column is 1

I am using a Union query as the source.
SELECT tblFarmData.Region, "" AS SelectType, [tblFarmData]![Region] AS
ShowType FROM tblFarmData GROUP BY tblFarmData.Region, "",
[tblFarmData]![Region] HAVING ((Not (tblFarmData.Region) Is Null)) UNION
SELECT 1 AS Region, "All" AS SelectType, "All" AS ShowType FROM tblFarmData
UNION SELECT 2 AS Region, "All - By Region" AS SelectType, "All - By Region"
AS ShowType FROM tblFarmData
ORDER BY tblFarmData.Region;

I also tried this in my criteria to get double quotes around the text value.

Like
IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=1,"*",IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=2,"*",""""
& [Forms]![frmReportCenter]![cbo_RegionForReport] & """"))

Same result and error.

Brad

:

Brad wrote:

Thanks for taking the time to read my question.

I had this as my criteria and it worked:

Like
IIf(IsNull([Forms]![frmReportCenter]![cbo_RegionForReport]),"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

I changed it to this and now Access is telling me it is too complex:

Like
IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=1,"*",IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=2,"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

Have I done something wrong? Is it really too complex?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're using LIKE incorrectly. The way you've set it up it looks like
you should be using the equal sign, instead.

But, the expression seems to indicate that if the ComboBox equals either
1 or 2 that you want the column to equal anything (*); otherwise, you
want only records where the column equals the contents of the ComboBox.
If this is true, you can use an expression like this:

IIf(Forms!frmReportCenter!cbo_RegionForReport IN (1,2), [column_name],
Forms!frmReportCenter!cbo_RegionForReport)

Substitute the real column name for [column_name].

What it does: It will set the comparison to this:

If the value is 1 or 2:

column_name = column_name

This will mean you don't care what is in the column, just return all
records (filtered by any other column's criteria).

Otherwise, (the ELSE part of the IIf() function) the criteria will be
this:

column_name = Forms!frmReportCenter!cbo_RegionForReport

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/9pc4echKqOuFEgEQJxPwCdGQno9D5t8kcG86pLOMQIIar32I0AoIP0
qKJVr9SVVTCo+gB97T0zds77
=UG3B
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your ComboBox RowSource query should NOT have artifically inserted
quotes around the AB, MBE, etc. The query you're trying to make is
looking for "AB" (AB with quotes around it) instead of a value of AB.
In SQL:

WHERE Region = '"AB"'

Note the double quotes inside the single quotes. Unless you're storing
the values with quotes around them, of course, the query won't find
anything. That's y u've got to remove the double quotes in the ComboBox
query.

Let me see the query's (not the ComboBox query) SQL. Then I can see
what u're doing.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ//LfIechKqOuFEgEQKt8gCfY4VCa/OZWY6elIr3uh5RHJIjoRcAoIBf
XglIJJKacnMQ7XLmUgNmQMJL
=VdJJ
-----END PGP SIGNATURE-----
I created a field labeled Test in my query.

Test: [Forms]![frmReportCenter]![cbo_RegionForReport]

It returned the value of the combobox. If I picked "All" or "All - By
Region" it returned 1 or 2, otherwise it returned "AB", "MBE", or "MBW"

I also set Test: to this.

Test: IIf([Forms]![frmReportCenter]![cbo_RegionForReport] In
(1,2),[tblFarmData]![Region],[Forms]![frmReportCenter]![cbo_RegionForReport])

Same error as if it was in the criteria line. All and All - By Region work
but not the other values.


If I put "MBW" in the criteria line of [Region] it puts double quotes around
it. If I put an asterisk it puts Like "*". Both work as filtering criteria.

the [Region] field is text, so that is why if the combobox is 1 or 2 it
returns "*". The other values are already text. So the criteria statement
should return either "*" or MBW, MBE, or AB. The text values don't work,
that is why I tried adding the """" to the formula, to get the quotes in
there.

I checked the RowSource of the combobox and the bound column. It is text.
The numbers in that field align left, like the other text values.

Maybe I should just write a function???

Brad

:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It depends on the data type of the column you are querying - it has to
be the same as the bound column of the ComboBox box. In this case a
Text data type. Try running the query w/ just "AB" as the column's
criteria (assuming that there are records w/ "AB" in the column). If
that works, put the expression in a Field cell and run the query to see
if the expression is returning what it is supposed to return - the value
of the combo box's bound column. Use the expression I posted.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/90a4echKqOuFEgEQLpjQCg6c8ei7MIPhwbbQTsK0HxB87e7UMAoNTX
KxFuqwzyNYGA/DEJCb0aonuw
=wRg9
-----END PGP SIGNATURE-----
Hmm, I've learned something new today. Thanks!

It only works if I choose 1 (All) or 2(All - By Region), but if I choose any
other value it doesn't work, which is the same as what happened with my Like
iif(...)

My combobox looks like this

Region SelectType ShowType
1 "All" "All"
2 "All - By Region" "All - By Region"
"AB" "AB"
"MBE" "MBE"
"MBW" "MBW"

Column width is 0;0;1
Bound column is 1

I am using a Union query as the source.
SELECT tblFarmData.Region, "" AS SelectType, [tblFarmData]![Region] AS
ShowType FROM tblFarmData GROUP BY tblFarmData.Region, "",
[tblFarmData]![Region] HAVING ((Not (tblFarmData.Region) Is Null)) UNION
SELECT 1 AS Region, "All" AS SelectType, "All" AS ShowType FROM tblFarmData
UNION SELECT 2 AS Region, "All - By Region" AS SelectType, "All - By Region"
AS ShowType FROM tblFarmData
ORDER BY tblFarmData.Region;

I also tried this in my criteria to get double quotes around the text value.

Like
IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=1,"*",IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=2,"*",""""
& [Forms]![frmReportCenter]![cbo_RegionForReport] & """"))

Same result and error.

Brad

:



Brad wrote:


Thanks for taking the time to read my question.

I had this as my criteria and it worked:

Like
IIf(IsNull([Forms]![frmReportCenter]![cbo_RegionForReport]),"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

I changed it to this and now Access is telling me it is too complex:

Like
IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=1,"*",IIf([Forms]![frmReportCenter]![cbo_RegionForReport]=2,"*",[Forms]![frmReportCenter]![cbo_RegionForReport]))

Have I done something wrong? Is it really too complex?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're using LIKE incorrectly. The way you've set it up it looks like
you should be using the equal sign, instead.

But, the expression seems to indicate that if the ComboBox equals either
1 or 2 that you want the column to equal anything (*); otherwise, you
want only records where the column equals the contents of the ComboBox.
If this is true, you can use an expression like this:

IIf(Forms!frmReportCenter!cbo_RegionForReport IN (1,2), [column_name],
Forms!frmReportCenter!cbo_RegionForReport)

Substitute the real column name for [column_name].

What it does: It will set the comparison to this:

If the value is 1 or 2:

column_name = column_name

This will mean you don't care what is in the column, just return all
records (filtered by any other column's criteria).

Otherwise, (the ELSE part of the IIf() function) the criteria will be
this:

column_name = Forms!frmReportCenter!cbo_RegionForReport

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/9pc4echKqOuFEgEQJxPwCdGQno9D5t8kcG86pLOMQIIar32I0AoIP0
qKJVr9SVVTCo+gB97T0zds77
=UG3B
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

filter query based on single column list box 2
Building Complex Criteria for Queries in Access 2007 1
My criteria doesn't work. 7
Criteria to return all records if selection from form is null 3
Parameterised query to return all records or those Between two val 2
If Statement with "*" in Query 2
query too complex 3
Query Too Complex with Total line 8

Back
Top