Multiple IIFs in a Query?

  • Thread starter Thread starter Beth Eadie
  • Start date Start date
B

Beth Eadie

I have a database that contains a table with all my user information. This
table contains information such as Name, Phone, Location, and the tests that
that user has taken. Each test (100, 200, 700, and 800) have their own
Yes/No fields if the user has taken it (e.g 100 Completed, 200 Completed,
etc) and then the date that they completed (100 Date Completed, 200 Date
Completed, etc). (This is probably bad database design, but design is not my
forte...)

I am wanting the user to be able to run a report by test (e.g show all users
that have taken a certain test). I have created the form with unbound combo
box with each test name. Now, here is my problem. I need to call the query
and have it filter the information for just that specific test. I am
assuming I need to write IIF statements for each test field, but I can't get
the coding correct. I have:

IIf([100SCa Completed?]=True And [Forms]![frmByTest]![cboByTest]="100.Sca",
<display the information>, <don't display the information>)

Is something like this even possible or would there be another way around
it? I know I can create 4 separate forms, queries, and reports for each of
the 4 tests, but I would like to make 1 form/report/query to do it all.

Any suggestions would be helpful.

Thanks,
Beth
 
Make your table right - use a union query.
SELECT Name, Phone, Location, "100SCa" AS TestType, [100 Date Completed] AS
TestComp
FROM YourTable
WHERE [100 Date Completed] Is Not Null AND [100SCa Completed?]=True
UNION ALL SELECT Name, Phone, Location, "200SCa" AS TestType, [200 Date
Completed] AS TestComp
FROM YourTable
WHERE [200 Date Completed] Is Not Null AND [200SCa Completed?]=True
.....
SELECT Name, Phone, Location, "800SCa" AS TestType, [800 Date Completed] AS
TestComp
FROM YourTable
WHERE [800 Date Completed] Is Not Null AND [800SCa Completed?]=True;

Save this query and then use it in a make table query.

Use one form with listbox to select test and one query that uses the listbox
as criteria to pull the data.
 
Thank you Karl.

I made my union query, however I am getting an error "The SELECT statement
includes a reserved word or argument name that is misspelled or missing or
the punctuation is incorrect." But I can't, for the life of me, figure out
my mistake.

This is a portion of what I have written:

SELECT [tblUsers&Courses].UserName, [tblUsers&Courses].Phone,
[tblUsers&Courses].Location,
[tblUsers&Courses].[100SCa Taken?] AS TestType,
[tblUsers&Courses].[100SCa Completed?] AS TestComp,
[tblUsers&Courses].[100SCa Date Completed],
FROM [tblUsers&Courses]
WHERE [100SCa Date Completed] Is Not Null AND [100SCa Completed?]=TRUE

UNION ALL SELECT [tblUsers&Courses].UserName, [tblUsers&Courses].Phone,
[tblUsers&Courses].Location,
[tblUsers&Courses].[200a Taken?] AS TestType,
[tblUsers&Courses].[200a Completed?] AS TestComp,
[tblUsers&Courses].[200a Date Completed],
FROM [tblUsers&Courses]
WHERE [200a Date Completed] Is Not Null AND [200a Completed?]=TRUE;

Do you see anything that would stop it from running?

Thanks,
Beth


KARL DEWEY said:
Make your table right - use a union query.
SELECT Name, Phone, Location, "100SCa" AS TestType, [100 Date Completed] AS
TestComp
FROM YourTable
WHERE [100 Date Completed] Is Not Null AND [100SCa Completed?]=True
UNION ALL SELECT Name, Phone, Location, "200SCa" AS TestType, [200 Date
Completed] AS TestComp
FROM YourTable
WHERE [200 Date Completed] Is Not Null AND [200SCa Completed?]=True
....
SELECT Name, Phone, Location, "800SCa" AS TestType, [800 Date Completed] AS
TestComp
FROM YourTable
WHERE [800 Date Completed] Is Not Null AND [800SCa Completed?]=True;

Save this query and then use it in a make table query.

Use one form with listbox to select test and one query that uses the listbox
as criteria to pull the data.


Beth Eadie said:
I have a database that contains a table with all my user information. This
table contains information such as Name, Phone, Location, and the tests that
that user has taken. Each test (100, 200, 700, and 800) have their own
Yes/No fields if the user has taken it (e.g 100 Completed, 200 Completed,
etc) and then the date that they completed (100 Date Completed, 200 Date
Completed, etc). (This is probably bad database design, but design is not my
forte...)

I am wanting the user to be able to run a report by test (e.g show all users
that have taken a certain test). I have created the form with unbound combo
box with each test name. Now, here is my problem. I need to call the query
and have it filter the information for just that specific test. I am
assuming I need to write IIF statements for each test field, but I can't get
the coding correct. I have:

IIf([100SCa Completed?]=True And [Forms]![frmByTest]![cboByTest]="100.Sca",
<display the information>, <don't display the information>)

Is something like this even possible or would there be another way around
it? I know I can create 4 separate forms, queries, and reports for each of
the 4 tests, but I would like to make 1 form/report/query to do it all.

Any suggestions would be helpful.

Thanks,
Beth
Click to expand...
Click to expand...
 
Well, I've split out my tables and tried to redo the union query (I think the
first code I pasted was incorrect since I was pulling from just 1 table). I
split tables out into each separate test.

I've updated my union query, but still getting the same error message. "The
SELECT statement includes a reserved word or argument name that is misspelled
or missing or
the punctuation is incorrect."

My code:

SELECT [tblUsers&Courses].UserName, [tblUsers&Courses].Phone,
[tblUsers&Courses].Location,
FROM [tblUsers&Courses]

UNION SELECT ALL [tblUsers&Courses].[100SCa Taken?] AS TestType,
[tblUsers&Courses].[100SCa Completed?] AS TestComp,
[tblUsers&Courses].[100SCa Date Completed],
FROM [tbl100Test]
WHERE [100SCa Date Completed] Is Not Null AND [100SCa Completed?]=TRUE

UNION ALL SELECT [tblUsers&Courses].[200a Taken?] AS TestType,
[tblUsers&Courses].[200a Completed?] AS TestComp,
[tblUsers&Courses].[200a Date Completed],
FROM [tbl200Test]
WHERE [200a Date Completed] Is Not Null AND [200a Completed?]=TRUE;

Do I have incorrect syntax somewhere?
Thanks,
Beth


KARL DEWEY said:
Make your table right - use a union query.
SELECT Name, Phone, Location, "100SCa" AS TestType, [100 Date Completed] AS
TestComp
FROM YourTable
WHERE [100 Date Completed] Is Not Null AND [100SCa Completed?]=True
UNION ALL SELECT Name, Phone, Location, "200SCa" AS TestType, [200 Date
Completed] AS TestComp
FROM YourTable
WHERE [200 Date Completed] Is Not Null AND [200SCa Completed?]=True
....
SELECT Name, Phone, Location, "800SCa" AS TestType, [800 Date Completed] AS
TestComp
FROM YourTable
WHERE [800 Date Completed] Is Not Null AND [800SCa Completed?]=True;

Save this query and then use it in a make table query.

Use one form with listbox to select test and one query that uses the listbox
as criteria to pull the data.


Beth Eadie said:
I have a database that contains a table with all my user information. This
table contains information such as Name, Phone, Location, and the tests that
that user has taken. Each test (100, 200, 700, and 800) have their own
Yes/No fields if the user has taken it (e.g 100 Completed, 200 Completed,
etc) and then the date that they completed (100 Date Completed, 200 Date
Completed, etc). (This is probably bad database design, but design is not my
forte...)

I am wanting the user to be able to run a report by test (e.g show all users
that have taken a certain test). I have created the form with unbound combo
box with each test name. Now, here is my problem. I need to call the query
and have it filter the information for just that specific test. I am
assuming I need to write IIF statements for each test field, but I can't get
the coding correct. I have:

IIf([100SCa Completed?]=True And [Forms]![frmByTest]![cboByTest]="100.Sca",
<display the information>, <don't display the information>)

Is something like this even possible or would there be another way around
it? I know I can create 4 separate forms, queries, and reports for each of
the 4 tests, but I would like to make 1 form/report/query to do it all.

Any suggestions would be helpful.

Thanks,
Beth
Click to expand...
Click to expand...
 
This union is wrong as you have to have the UserName,
[tblUsers&Courses].Phone,
[tblUsers&Courses].Location, in same query as the other information.

You have a comma after [tblUsers&Courses].[100SCa Date Completed] that is
wrong.
You might also add parenthsis like this in the WHERE --
SELECT [tblUsers&Courses].UserName, [tblUsers&Courses].Phone,
[tblUsers&Courses].Location,
[tblUsers&Courses].[100SCa Taken?] AS TestType,
[tblUsers&Courses].[100SCa Completed?] AS TestComp,
[tblUsers&Courses].[100SCa Date Completed]
FROM [tblUsers&Courses]
WHERE ([100SCa Date Completed] Is Not Null) AND ([100SCa Completed?]=TRUE)

If that does not work then try each part of the first union separately to
see if you get error as I do not see one.

Beth Eadie said:
Well, I've split out my tables and tried to redo the union query (I think the
first code I pasted was incorrect since I was pulling from just 1 table). I
split tables out into each separate test.

I've updated my union query, but still getting the same error message. "The
SELECT statement includes a reserved word or argument name that is misspelled
or missing or
the punctuation is incorrect."

My code:

SELECT [tblUsers&Courses].UserName, [tblUsers&Courses].Phone,
[tblUsers&Courses].Location,
FROM [tblUsers&Courses]

UNION SELECT ALL [tblUsers&Courses].[100SCa Taken?] AS TestType,
[tblUsers&Courses].[100SCa Completed?] AS TestComp,
[tblUsers&Courses].[100SCa Date Completed],
FROM [tbl100Test]
WHERE [100SCa Date Completed] Is Not Null AND [100SCa Completed?]=TRUE

UNION ALL SELECT [tblUsers&Courses].[200a Taken?] AS TestType,
[tblUsers&Courses].[200a Completed?] AS TestComp,
[tblUsers&Courses].[200a Date Completed],
FROM [tbl200Test]
WHERE [200a Date Completed] Is Not Null AND [200a Completed?]=TRUE;

Do I have incorrect syntax somewhere?
Thanks,
Beth


KARL DEWEY said:
Make your table right - use a union query.
SELECT Name, Phone, Location, "100SCa" AS TestType, [100 Date Completed] AS
TestComp
FROM YourTable
WHERE [100 Date Completed] Is Not Null AND [100SCa Completed?]=True
UNION ALL SELECT Name, Phone, Location, "200SCa" AS TestType, [200 Date
Completed] AS TestComp
FROM YourTable
WHERE [200 Date Completed] Is Not Null AND [200SCa Completed?]=True
....
SELECT Name, Phone, Location, "800SCa" AS TestType, [800 Date Completed] AS
TestComp
FROM YourTable
WHERE [800 Date Completed] Is Not Null AND [800SCa Completed?]=True;

Save this query and then use it in a make table query.

Use one form with listbox to select test and one query that uses the listbox
as criteria to pull the data.


Beth Eadie said:
I have a database that contains a table with all my user information. This
table contains information such as Name, Phone, Location, and the tests that
that user has taken. Each test (100, 200, 700, and 800) have their own
Yes/No fields if the user has taken it (e.g 100 Completed, 200 Completed,
etc) and then the date that they completed (100 Date Completed, 200 Date
Completed, etc). (This is probably bad database design, but design is not my
forte...)

I am wanting the user to be able to run a report by test (e.g show all users
that have taken a certain test). I have created the form with unbound combo
box with each test name. Now, here is my problem. I need to call the query
and have it filter the information for just that specific test. I am
assuming I need to write IIF statements for each test field, but I can't get
the coding correct. I have:

IIf([100SCa Completed?]=True And [Forms]![frmByTest]![cboByTest]="100.Sca",
<display the information>, <don't display the information>)

Is something like this even possible or would there be another way around
it? I know I can create 4 separate forms, queries, and reports for each of
the 4 tests, but I would like to make 1 form/report/query to do it all.

Any suggestions would be helpful.

Thanks,
Beth
Click to expand...
Click to expand...
Click to expand...
 
I was able to get it to work! Thank you for all your help!!

:)

KARL DEWEY said:
This union is wrong as you have to have the UserName,
[tblUsers&Courses].Phone,
[tblUsers&Courses].Location, in same query as the other information.

You have a comma after [tblUsers&Courses].[100SCa Date Completed] that is
wrong.
You might also add parenthsis like this in the WHERE --
SELECT [tblUsers&Courses].UserName, [tblUsers&Courses].Phone,
[tblUsers&Courses].Location,
[tblUsers&Courses].[100SCa Taken?] AS TestType,
[tblUsers&Courses].[100SCa Completed?] AS TestComp,
[tblUsers&Courses].[100SCa Date Completed]
FROM [tblUsers&Courses]
WHERE ([100SCa Date Completed] Is Not Null) AND ([100SCa Completed?]=TRUE)

If that does not work then try each part of the first union separately to
see if you get error as I do not see one.

Beth Eadie said:
Well, I've split out my tables and tried to redo the union query (I think the
first code I pasted was incorrect since I was pulling from just 1 table). I
split tables out into each separate test.

I've updated my union query, but still getting the same error message. "The
SELECT statement includes a reserved word or argument name that is misspelled
or missing or
the punctuation is incorrect."

My code:

SELECT [tblUsers&Courses].UserName, [tblUsers&Courses].Phone,
[tblUsers&Courses].Location,
FROM [tblUsers&Courses]

UNION SELECT ALL [tblUsers&Courses].[100SCa Taken?] AS TestType,
[tblUsers&Courses].[100SCa Completed?] AS TestComp,
[tblUsers&Courses].[100SCa Date Completed],
FROM [tbl100Test]
WHERE [100SCa Date Completed] Is Not Null AND [100SCa Completed?]=TRUE

UNION ALL SELECT [tblUsers&Courses].[200a Taken?] AS TestType,
[tblUsers&Courses].[200a Completed?] AS TestComp,
[tblUsers&Courses].[200a Date Completed],
FROM [tbl200Test]
WHERE [200a Date Completed] Is Not Null AND [200a Completed?]=TRUE;

Do I have incorrect syntax somewhere?
Thanks,
Beth


KARL DEWEY said:
Make your table right - use a union query.
SELECT Name, Phone, Location, "100SCa" AS TestType, [100 Date Completed] AS
TestComp
FROM YourTable
WHERE [100 Date Completed] Is Not Null AND [100SCa Completed?]=True
UNION ALL SELECT Name, Phone, Location, "200SCa" AS TestType, [200 Date
Completed] AS TestComp
FROM YourTable
WHERE [200 Date Completed] Is Not Null AND [200SCa Completed?]=True
....
SELECT Name, Phone, Location, "800SCa" AS TestType, [800 Date Completed] AS
TestComp
FROM YourTable
WHERE [800 Date Completed] Is Not Null AND [800SCa Completed?]=True;

Save this query and then use it in a make table query.

Use one form with listbox to select test and one query that uses the listbox
as criteria to pull the data.


:

I have a database that contains a table with all my user information. This
table contains information such as Name, Phone, Location, and the tests that
that user has taken. Each test (100, 200, 700, and 800) have their own
Yes/No fields if the user has taken it (e.g 100 Completed, 200 Completed,
etc) and then the date that they completed (100 Date Completed, 200 Date
Completed, etc). (This is probably bad database design, but design is not my
forte...)

I am wanting the user to be able to run a report by test (e.g show all users
that have taken a certain test). I have created the form with unbound combo
box with each test name. Now, here is my problem. I need to call the query
and have it filter the information for just that specific test. I am
assuming I need to write IIF statements for each test field, but I can't get
the coding correct. I have:

IIf([100SCa Completed?]=True And [Forms]![frmByTest]![cboByTest]="100.Sca",
<display the information>, <don't display the information>)

Is something like this even possible or would there be another way around
it? I know I can create 4 separate forms, queries, and reports for each of
the 4 tests, but I would like to make 1 form/report/query to do it all.

Any suggestions would be helpful.

Thanks,
Beth
Click to expand...
Click to expand...
Click to expand...
 
Back
Top