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]

The iif(IsNull([Bay1]),1,0) just looks at Bay1 and if it is empty(Null) it scores 1, does the same for all the other bays adding up the scores as it goes. then checks the score is below 4 to see whether to show the Row. Same thing with Z's.
If you look at it in design view you can see more easily what it is doing.

Peter

 

[jeh]

Thanks Peter - I know you are giving of your own time, and I appreciate it.
Now all I have to do is get rid of the days where bay1, bay2, bay3, and bay4 are the same in all four fields.

I have been able to get help in these forums, and have made more work for myself by knowing where to ask the questions, and not really knowing how to do it.


However, I do give you brainy folks (Peter,Ivan,Mike,Paddy,Nimrod,Aladin,MarkW,Yogi, And I shouldn't have started this because I can't even remember all who has helped me,and don't want to leave anyone out) the credit for the things that do get done.

Have A great Day!

Jim

 

[bat17]

here you go

SELECT backwater.Date, backwater.Bay1, backwater.Bay2, backwater.bay3, backwater.bay4, backwater.offb, backwater.offin, backwater.operator, IIf(([bay1]=[bay2] And [bay3]=[bay2] And [bay4]=[bay2]),1,0) AS AllSame, 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]=[bay2] And [bay3]=[bay2] And [bay4]=[bay2]),1,0))=0) AND ((IIf(IsNull([Bay1]),1,0)+IIf(IsNull([Bay2]),1,0)+IIf(IsNull([Bay3]),1,0)+IIf(IsNull([Bay4]),1,0))<4));

Peter

 

[jeh]

Thanks Peter

For some reason it still shows the dates/hours where all 24 hours are the same in the "bay" fields. I created a new query and pasted your SQL in there like I have since you have taken on this project

Have I done something wrong? I tried to change the format of the date field to different date types, that doesn't seem to help either.


Thanks,

Jim

 

[bat17]

You have lost me a bit now. If you have the same value in the four bay fields the query should filter that record out.
It does not take into account the date time at all.
Are you saying that you have records with the same values showing?

Peter

 

[jeh]

Hi Peter,

Sorry, for the delay, had a couple of days off.

Yes. the data is 24 hours in the day. it does show the values that are the same in all 24 hours.

Thanks,

Jim