Too Much Information

[Afrosheen via AccessMonster.com]

I'm using a query that will show Last name First name Sex and Race.

When I input Female and Hispanic, the report shows 3 records. The problem is
that when I look at the query it shows the same information. There's only one
female and one Hispanic. The other two records show nothing in the sex or
race field. They're empty and I don't understand why they are showing up.

In the report the count is also 3. It should only show one. If I can get the
query right then I'm sure the report will fall in.

Thanks for reading this.

 

[Mariebob]

Could you post the SQL code of the query?

 

[Afrosheen via AccessMonster.com]

Thanks for the reply. Here is the complete SQL code from the query.

SELECT qryEmpMaster.Lname, qryEmpMaster.Fname, qryEmpMaster.Sex, qryEmpMaster.
Race
FROM qryEmpMaster
WHERE (((qryEmpMaster.Sex)=[Forms]![frmGender]![List5]) AND ((qryEmpMaster.
Race)=[Forms]![frmGender]![Combo7])) OR (((qryEmpMaster.Race)=[Forms]!
[frmGender]![Combo7]) AND ((([qryEmpMaster].[Sex]) Like [forms]![frmGender]!
[list5]) Is Null)) OR (((qryEmpMaster.Sex)=[Forms]![frmGender]![List5]) AND (
(([qryEmpMaster].[Race]) Like [forms]![frmGender]![combo7]) Is Null)) OR ((((
[qryEmpMaster].[Sex]) Like [forms]![frmGender]![list5]) Is Null) AND (((
[qryEmpMaster].[Race]) Like [forms]![frmGender]![combo7]) Is Null));

I don't know if that's what you wanted. Let me know.

Could you post the SQL code of the query?

I'm using a query that will show Last name First name Sex and Race.

[quoted text clipped - 7 lines]

Thanks for reading this.

 

[Mariebob]

Are you quite "fluent" in SQL? if not, I could replicate your exact query and
do the query myself. But if you are ok with it, try replacing your SQL with
something more like this (remove spaces, they are for clarity purposes):

SELECT qryEmpMaster.Lname, qryEmpMaster.Fname, qryEmpMaster.Sex,
qryEmpMaster.Race

FROM qryEmpMaster

WHERE 1=1 and

Iif(not isnull([Forms]![frmGender]![List5]), [qryEmpMaster].[Sex] =
[Forms]![frmGender]![List5],â€â€)

AND

Iif(not isnull([Forms]![frmGender]![List7]), [qryEmpMaster].[race] =
[Forms]![frmGender]![List7],â€â€);

 

[Afrosheen via AccessMonster.com]

I can cut and paste good. The question is now, do I put this on the query or
on the report form? On the report form then I believe it would go on the Load
or Current.

Not really fluent. I just try and learn from reading this and that. Find what
I need then hopefully there is code I can cut and paste and redo.

If you're real good at it let me know because I have another question I
haven't got answered yet and I'll send you a link.

Thanks for helping out.

Are you quite "fluent" in SQL? if not, I could replicate your exact query and
do the query myself. But if you are ok with it, try replacing your SQL with
something more like this (remove spaces, they are for clarity purposes):

SELECT qryEmpMaster.Lname, qryEmpMaster.Fname, qryEmpMaster.Sex,
qryEmpMaster.Race

FROM qryEmpMaster

WHERE 1=1 and

Iif(not isnull([Forms]![frmGender]![List5]), [qryEmpMaster].[Sex] =
[Forms]![frmGender]![List5],â€â€)

AND

Iif(not isnull([Forms]![frmGender]![List7]), [qryEmpMaster].[race] =
[Forms]![frmGender]![List7],â€â€);

 

[Mariebob]

Put this in the query, try it and tell me if it generates any error then.

For the other question: let's say SQL is my third language, after french and
english. Maybe it's better than my english. Send me your link and I'll see if
I can help.

I can cut and paste good. The question is now, do I put this on the query or
on the report form? On the report form then I believe it would go on the Load
or Current.

Not really fluent. I just try and learn from reading this and that. Find what
I need then hopefully there is code I can cut and paste and redo.

If you're real good at it let me know because I have another question I
haven't got answered yet and I'll send you a link.

Thanks for helping out.

Are you quite "fluent" in SQL? if not, I could replicate your exact query and
do the query myself. But if you are ok with it, try replacing your SQL with
something more like this (remove spaces, they are for clarity purposes):

SELECT qryEmpMaster.Lname, qryEmpMaster.Fname, qryEmpMaster.Sex,
qryEmpMaster.Race

FROM qryEmpMaster

WHERE 1=1 and

Iif(not isnull([Forms]![frmGender]![List5]), [qryEmpMaster].[Sex] =
[Forms]![frmGender]![List5],â€â€)

AND

Iif(not isnull([Forms]![frmGender]![List7]), [qryEmpMaster].[race] =
[Forms]![frmGender]![List7],â€â€);

 

[Afrosheen via AccessMonster.com]

Here is the code. I keep getting a prompt. It asks
Prompt1 Enter Gender

Prompt2 The unknown prompt. I can't even see what It's asking.

Prompt3 Then the Race.

Other than that it looks like it's working. Get rid of the other prompt then
it should be perfect again.

Thanks for your help.

PS: I almost have the other problem square. Thanks..



SELECT qryEmpMaster.Lname, qryEmpMaster.Fname, qryEmpMaster.Sex, qryEmpMaster.
Race
FROM qryEmpMaster
WHERE 1=1 AND Iif(not isnull([Forms]![frmGender]![List5]),[qryEmpMaster].[Sex]
=[Forms]![frmGender]![List5],â€â€) AND Iif(not isnull([Forms]![frmGender]!
[List7]), [qryEmpMaster].[race] =[Forms]![frmGender]![List7],â€â€);

Put this in the query, try it and tell me if it generates any error then.

For the other question: let's say SQL is my third language, after french and
english. Maybe it's better than my english. Send me your link and I'll see if
I can help.

I can cut and paste good. The question is now, do I put this on the query or
on the report form? On the report form then I believe it would go on the Load

[quoted text clipped - 26 lines]

Iif(not isnull([Forms]![frmGender]![List7]), [qryEmpMaster].[race] =
[Forms]![frmGender]![List7],â€â€);

 

[Mariebob]

Make sure that you remove any spaces or "enters" after you paste the SQL.
Like, in the code you sent me below, "qryempmaster." and "race" should be on
the same line (and also "[Forms]![frmGender]!" and "[List7]".

I did this and now I got only two prompts.

Let me know if it works...

Here is the code. I keep getting a prompt. It asks
Prompt1 Enter Gender

Prompt2 The unknown prompt. I can't even see what It's asking.

Prompt3 Then the Race.

Other than that it looks like it's working. Get rid of the other prompt then
it should be perfect again.

Thanks for your help.

PS: I almost have the other problem square. Thanks..



SELECT qryEmpMaster.Lname, qryEmpMaster.Fname, qryEmpMaster.Sex, qryEmpMaster.
Race
FROM qryEmpMaster
WHERE 1=1 AND Iif(not isnull([Forms]![frmGender]![List5]),[qryEmpMaster].[Sex]
=[Forms]![frmGender]![List5],â€â€) AND Iif(not isnull([Forms]![frmGender]!
[List7]), [qryEmpMaster].[race] =[Forms]![frmGender]![List7],â€â€);

Put this in the query, try it and tell me if it generates any error then.

For the other question: let's say SQL is my third language, after french and
english. Maybe it's better than my english. Send me your link and I'll see if
I can help.

I can cut and paste good. The question is now, do I put this on the query or
on the report form? On the report form then I believe it would go on the Load

[quoted text clipped - 26 lines]

Iif(not isnull([Forms]![frmGender]![List7]), [qryEmpMaster].[race] =
[Forms]![frmGender]![List7],â€â€);

 

[Afrosheen via AccessMonster.com]

I took out all spaces I found. The problem is when I went to run it I still
got all the Unknown Prompt. I even took out the space before and after the
AND and it gave me a syntax error.
The unknown prompt is from "" double quotes. I didn't know if I should take
them out because they are attached to the fields. And those are the only ones
there. I found the unknown prompt. It was in those double quotes. I took them
out and it worked ok. Now to see if the report stayed the same

The query works ok when entering the information within the query. The report
is generated from a form that is attached to the query. Nothing shows up in
the report.

Make sure that you remove any spaces or "enters" after you paste the SQL.
Like, in the code you sent me below, "qryempmaster." and "race" should be on
the same line (and also "[Forms]![frmGender]!" and "[List7]".

I did this and now I got only two prompts.

Let me know if it works...

Here is the code. I keep getting a prompt. It asks
Prompt1 Enter Gender

[quoted text clipped - 28 lines]

Iif(not isnull([Forms]![frmGender]![List7]), [qryEmpMaster].[race] =
[Forms]![frmGender]![List7],â€â€);

 

[Mariebob]

I don't have any problem here with the double quotes...

Try replacing them with 1=1

I took out all spaces I found. The problem is when I went to run it I still
got all the Unknown Prompt. I even took out the space before and after the
AND and it gave me a syntax error.
The unknown prompt is from "" double quotes. I didn't know if I should take
them out because they are attached to the fields. And those are the only ones
there. I found the unknown prompt. It was in those double quotes. I took them
out and it worked ok. Now to see if the report stayed the same

The query works ok when entering the information within the query. The report
is generated from a form that is attached to the query. Nothing shows up in
the report.

Make sure that you remove any spaces or "enters" after you paste the SQL.
Like, in the code you sent me below, "qryempmaster." and "race" should be on
the same line (and also "[Forms]![frmGender]!" and "[List7]".

I did this and now I got only two prompts.

Let me know if it works...

Here is the code. I keep getting a prompt. It asks
Prompt1 Enter Gender

[quoted text clipped - 28 lines]

Iif(not isnull([Forms]![frmGender]![List7]), [qryEmpMaster].[race] =
[Forms]![frmGender]![List7],â€â€);

 

[Afrosheen via AccessMonster.com]

Update:
The gender side works ok. If I put in female or male it will give a list of
all female or male and their race.

If I just put in for example Hispanic It will give me a long list. There's
only one Hispanic that i work with.

I did take the Double quotes out and put in the 1=1..

the race is not working. I just put in my form Male Black. It gave me all the
males any race.

Thanks for your help. You've been a shinning light in a poor programming day.
By the way, the other program is not working still.
Still want the link?

I don't have any problem here with the double quotes...

Try replacing them with 1=1

I took out all spaces I found. The problem is when I went to run it I still
got all the Unknown Prompt. I even took out the space before and after the

[quoted text clipped - 21 lines]

Iif(not isnull([Forms]![frmGender]![List7]), [qryEmpMaster].[race] =
[Forms]![frmGender]![List7],â€â€);

 

[Afrosheen via AccessMonster.com]

dont worry about the problem. i found it. On the race button it was set to a
list7. It's suppose to be combo7. After i changed that, then everything
worked great,.


Again. Thanks for your help.

Update:
The gender side works ok. If I put in female or male it will give a list of
all female or male and their race.

If I just put in for example Hispanic It will give me a long list. There's
only one Hispanic that i work with.

I did take the Double quotes out and put in the 1=1..

the race is not working. I just put in my form Male Black. It gave me all the
males any race.

Thanks for your help. You've been a shinning light in a poor programming day.
By the way, the other program is not working still.
Still want the link?

I don't have any problem here with the double quotes...

[quoted text clipped - 5 lines]

Iif(not isnull([Forms]![frmGender]![List7]), [qryEmpMaster].[race] =
[Forms]![frmGender]![List7],â€â€);

 

[Afrosheen via AccessMonster.com]

Good morning Mariebob,
If your still there and interested in looking a my SQL here it is.

strWhere = "SELECT tblTaps.StaffId, tblMain.Lname, tblMain.Fname, tblTaps.
WorkPlan, tblTaps.WorkRec, tblTaps.IntDue, tblTaps.IntRec, tblTaps.FinalDue,
tblTaps.FinalRec, tblTaps.RatingCyc, tblTaps.FiscalYr, tblTaps.Rating,
tblTaps.Notes, tblTaps.Archive, tblTaps.DateAch, tblMain.IdPict, tblMain.
Supervisor, tblMain.Location" _
& "FROM tblMain INNER JOIN tblTaps ON tblMain.StaffId = tblTaps.StaffId" _
& "WHERE (((tblTaps.WorkPlan)="""& me.combo149&""") AND ((tblTaps.Archive)
=False) AND ((tblMain.Supervisor)="""&me.cboloc&"""))"

This is right from the query. When I type the information in on the fields it
comes out correct.

Where the tblTaps.WorkPlan is the combo149 is a combo box.
Where the tblMain.Supervisor is the cboloc is a combo box.

Check out on the board also under the VBA for SQL Guru.

Thanks in advance, If you can do this. I keep getting a compile error.

I don't have any problem here with the double quotes...

Try replacing them with 1=1

I took out all spaces I found. The problem is when I went to run it I still
got all the Unknown Prompt. I even took out the space before and after the

[quoted text clipped - 21 lines]

Iif(not isnull([Forms]![frmGender]![List7]), [qryEmpMaster].[race] =
[Forms]![frmGender]![List7],â€â€);