iif statement problem

[slimjen1]

All; I inherited a database that I need to revise but for now; I need help on an iif statement. I have a couple of columns in my database where t fields are blank; I don’t want it to return that record. I know in a query I can just say in the criteria “is not null”. My problem is that query has two columns that originate from the same table and the same column. Ex. Column in table called Type. Results can either be “standard” or “premium”. Well; two queries were spun off into two queries; one Type = standard and other Type=premium. So now I have the two queries in one main query to pull some final results therefore have two columns qryStandard.type and qryPremium.type. I want only records to return that are not null. I have only been able to get either qryStandard.type or qryPremium.type results. I hope I gave enough info to get assistance.
Thanks

 

[vbaInet]

Why can't you do the same in both queries? -->

I don’t want it to return that record. I know in a query I can just say in the criteria “is not null”.

 

[GinaWhipp]

I think what you need to do is put Is Not Null on the top line in the Criteria section for qryStandard.type and for qryPremium.type place the Is Not Null on the second line in the Criteria area. If that does not work please post the SQL for the query here.

 

[gblack]

If the queries are exactly the same... UNION them together. This will give you the values in both queries...

You can do this by:

First make two almost identical queries (one that gives you the Premium Data as you want to see it and the other that gives you all the standard data. Make sure both queries have the exact same amount of fields, if not, it won't work).

Then go into the SQL design of either one (doesn't matter which), copy the entire SQL to your clipboard (including the semicolon)...

Then go into the SQL design of the other query.
Delete the semicolon, hit enter so your are on a new line, type the word UNION , then hit enter again.

Then paste the query on your clipboard under the word UNION... then run it your query... it should give you both datasets stacked together...

Not sure if that's what you're looking for, but that's my best guess based on how I read your issue.

 

[slimjen1]

I tried but didn't work. This gets me one or the other:

SELECT qrySummary. RM, qrySummary.custid, qrySummary.dte, qrySummary.qryStandard.[Type], qrySummary.qryPremium.[Type]
FROM qrySummary
WHERE (((qrySummary.dte) Is Not Null) AND ((qrySummary. qryStandard.[Type]) Is Not Null)) OR (((qrySummary.dte) Is Not Null) AND ((qrySummary. qryPremium.[Type]) Is Not Null));

 

[vbaInet]

Ok. Why are there two queries from one query? What are the differences?

 

[slimjen1]

They yield different results when they run individual that the user needs. Inherited database. Long story. Will fix but need immediate results in the mean time.

 

[vbaInet]

Union Query as glback explained:

http://office.microsoft.com/en-gb/a...eries-by-using-a-union-query-HA010206109.aspx

...but in each query put the Not Is Null criteria and save them then use the two query names to form a Union.

 

[GinaWhipp]

Hmm, tip toeing out, I see you're in great hands!

 

[vbaInet]

Hmm, tip toeing out, I see you're in great hands!

Just helping out whilst you were on your coffee break

 

[GinaWhipp]

I wish... conference call and trying to catch up with all my responses now!

 

[GinaWhipp]

@slimjen1

Oh, I do like the idea of a UNION query, in this case, a much better option. Have you worked that out yet?

 

[slimjen1]

I think I have worked this out. Thanks for all your help.

 

[vbaInet]

I think I have worked this out. Thanks for all your help.

Care to share?