select dates where at least one of four fields has text.

[jeh]

Hello,

I want to get five fields of data from one table. The catch is that i only want the data when one of the four fields have text in them. These are the field names. [Data](I want to be able and select startdate, and enddate) where[bay1]has text,[bay2]has text, [bay3]has text, and [bay4]has text. There will be dates that all four fields will have text, and somedays where only one field has text. Please help me, I don't have a clue here.

thanks for your time,

Jim

 

[bat17]

I think that you will need to create a new coulumn in the query.
NumberFields: IIf(IsNull([Bay1]),0,1)+IIf(IsNull([Bay2]),0,1)+IIf(IsNull([Bay3]),0,1)+IIf(IsNull([Bay4]),0,1)
You can then add a criteria of 1 for the field.

HTH

Peter

 

[jeh]

Hi Peter,

I don't know what I'm doing wrong , but it errors with "Invaild.(Dot), ot (!).
I'm still trying to figure out how to get around with the queries in Access.

Thanks for your reply!


Jim

 

[bat17]

If you would like to post the SQL that you have (with out my addition) I will see if I can tweak it for you.

Peter

 

[jeh]

Hi Peter,

SELECT backwater.date, backwater.bay1, backwater.bay2, backwater.bay3, backwater.bay4, backwater.offb, backwater.offin, backwater.operator
FROM backwater;

this is all done with the 'builder' I don't know how to do it from scratch.

this is what I hoped for. If bay1, bay2, bay3,and bay4 are have a value like "A" ect. (it is only one letter). then I want to see the record. if all four of the fields have a "Z" in them I don't want that record.

I know it is confusing, and the table probably could have been designed better, but I have been given the task of trying to make a report out of it.

Thanks again Peter for your help,

Jim

 

[bat17]

If I understand you correctly then you want to show any record that does not have 4 Z's in it.
Try this

SELECT backwater.Date, backwater.Bay1, backwater.Bay2, backwater.bay3, backwater.bay4, backwater.offb, backwater.offin, backwater.operator, IIf([Bay1]="z",1,0)+IIf([Bay2]="z",1,0)+IIf([Bay3]="z",1,0)+IIf([Bay4]="z",1,0) AS CountZs
FROM backwater
WHERE (((IIf([Bay1]="z",1,0)+IIf([Bay2]="z",1,0)+IIf([Bay3]="z",1,0)+IIf([Bay4]="z",1,0))<4));


HTH

Peter

 

[jeh]

Yes Peter. It helps a great deal. Thank you very much.

Jim

 

[jeh]

Peter: could I bother you for one more step in this query, if it's even possible.

What you have given me works perfect. would it be possible to add something else at the end of this SQL like: Show the records that have a value in the Bay1, Bay2, Bay3, and Bay4 fields. You have screened out the "Z's" for me, Now I need to get rid of the records that have no data in these fields.



SELECT backwater.Date, backwater.Bay1, backwater.Bay2, backwater.bay3, backwater.bay4, backwater.offb, backwater.offin, backwater.operator, IIf([Bay1]="z",1,0)+IIf([Bay2]="z",1,0)+IIf([Bay3]="z",1,0)+IIf([Bay4]="z",1,0) AS CountZs
FROM backwater
WHERE (((IIf([Bay1]="z",1,0)+IIf([Bay2]="z",1,0)+IIf([Bay3]="z",1,0)+IIf([Bay4]="z",1,0))<4));

Thanks again,

Jim

 

[bat17]

Try:-

SELECT backwater.Date, backwater.Bay1, backwater.Bay2, backwater.bay3, backwater.bay4, backwater.offb, backwater.offin, backwater.operator, IIf([Bay1]="z",1,0)+IIf([Bay2]="z",1,0)+IIf([Bay3]="z",1,0)+IIf([Bay4]="z",1,0) AS CountZs, IIf(IsNull([Bay1]),1,0)+IIf(IsNull([Bay2]),1,0)+IIf(IsNull([Bay3]),1,0)+IIf(IsNull([Bay4]),1,0) AS CountNulls
FROM backwater
WHERE (((IIf([Bay1]="z",1,0)+IIf([Bay2]="z",1,0)+IIf([Bay3]="z",1,0)+IIf([Bay4]="z",1,0))<4) AND ((IIf(IsNull([Bay1]),1,0)+IIf(IsNull([Bay2]),1,0)+IIf(IsNull([Bay3]),1,0)+IIf(IsNull([Bay4]),1,0))<4));

Peter

 

[jeh]

Peter -You are good!

I don't know what it says, but man it works great!!

Thank you Sir,

Jim