...but what if??? (1 Viewer)

[zavexx]

I have a query with 3 fields. If all 3 fields have the same text value, I want the output value to say "complete", if not "open"
Is there a function to do this? I've looked at IIF and SWITCH

Your help is greatly appreciated.

 

[Gasman]

And why did IIF() not work?

 

[The_Doc_Man]

There would have to be an expression that contained something similar to

IIF ( ( [SL FORM] <> "Open" ) AND ( [CLOSING SET] <> "Open" ) AND ( [DATA ROOM] <>  "Open" ), "COMPLETE", "NOT COMPLETE" )

This assumes that "Open" is why something wasn't complete.

I put extra spaces in there for readability but Access would probably remove them if you entered that in SQL mode.

 

[CJ_London]

Nothing like guessing what the actual requirement is, Gasman asks the right question

if the requirement really is 'If all 3 fields have the same text value' and perhaps those fields are not related to the criteria shown

IIF([SL FORM] = [CLOSING SET] AND [SL FORM] =[DATA ROOM] , "Complete", "Open" )

may need to use the nz function if some of the values are null.

 

[MarkK]

For something like this it might make sense to generate a new field that provides part of the logic, like...

AllFieldsEqual: Field1 = Field2 AND Field1 = Field3 AND Field2 = Field3

...and then you can put a WHERE clause on this field.

 

[CJ_London]

you only need 2 comparisons - if field1=field2 and field1=field3, then field2 will equal field3

 

[zavexx]

And why did IIF() not work?

And why did IIF() not work?

My apologies...I am a complete novice. Thank you for your input.

 

[The_Doc_Man]

you only need 2 comparisons - if field1=field2 and field1=field3, then field2 will equal field3

No, one more field in this case: add ... AND Field1 = "the particular value that marks this as closed"... AND that assumes that it is the same value for each field. Which from the looks of it might not be the case.

 

[MajP]

@zavexx
Curious about what values can be stored in those fields. Having values in the field with the same name as the field hints of a non-normalized design.
In other words, why have a field "SL Form" with a value of "SL Form"?
Can SL Form only have two values Open or SL Form? What does a value of SL_Form mean. Is that mean a stage that it is in or a completion of a task?

 

[CJ_London]

No, one more field in this case: add ... AND Field1 = "the particular value that marks this as closed".

that was not was asked.

If all 3 fields have the same text value, I want the output value to say "complete", if not "open"

We are all making an assumption based on the picture of the query which has criteria (so far as we can see) limiting the records returned to 'open' and one other value which is different for each field - the only common value is 'open'. (based on the assumption that the SL Form criteria only has two values for the criteria)

As it is the iif statement will return 'complete' if all three field values are 'open'

And all this assumes the three fields referred to are the ones we can see in the query.

 

[zavexx]

No, one more field in this case: add ... AND Field1 = "the particular value that marks this as closed"... AND that assumes that it is the same value for each field. Which from the looks of it might not be the case.

Thank you The Doc Man. I am a complete novice, some of my understanding is very limited. The code you provided almost worked, the end result is showing complete even though the data in the fields are not all complete

My thinking is, If SL Form is "Complete" and Closing Set is "Complete", but Data Room is "Open", then the end result or return value should be "Open".

I performed this task in excel but completely clueless in access.

You are teaching me a lot and I'm grateful!

 

[zavexx]

@zavexx
Curious about what values can be stored in those fields. Having values in the field with the same name as the field hints of a non-normalized design.
In other words, why have a field "SL Form" with a value of "SL Form"?
Can SL Form only have two values Open or SL Form? What does a value of SL_Form mean. Is that mean a stage that it is in or a completion of a task?

Let me start by saying I'm a novice, complete beginner. So I'll explain it my elementary language...
The headers are - SL Form - Closing Set - Data Room. When the documents have not been received the user will enter Open, once the document has been received the user will enter complete. The user wants to send a report to the client showing an open status. Even though some of the records show "complete" for 2 and "open" for 1, the status is still open.

 

[CJ_London]

we are assuming SL Form, Closing Set and Data Room are fields in a table. If that is the case, why do you need the criteria? are there other values these fields can contain other than 'open' and 'complete'?

As it is your criteria will return records where (for example) the SL Form field contains a value 'open' or 'SL Form'. It will not return a record where the value is 'complete'.

If that is the requirement then your criteria should be 'open' or 'complete' for all 3 fields. And if they are the only possible values, you do not need the criteria anyway. And I bow to Doc's higher perception of the requirement that you will need

AND Field1 = "Complete"

otherwise you will also get records where all 3 fields values are 'open'

 

[MajP]

This is kind of an extension of @CJ_London comments. At a minimum it looks like a status fields that is either Not Received, Received (or maybe more statuses "assigned", "Draft", "pending delivery", "Rejected")

Do you or the boss ever care about when it was received? Because you could get the same information you have now(receive or not) and more by changing these to date fields.
SL_Form_RecievedDate
ClosingSet_RecievedDate
Data_Room_RecievedDate

Now you leave it blank until received. The closed are when all have a date, open is any record missing a date in one of these fields.

Are these the only documents that you care about? Will it ever grow?
If it is likely to grow then you may not want to make these columns because you will have to modify your forms whenever the documents you care about increases or decreases. Instead you would have a child table with fields like
TblDocuments
--DocumentName
--DateRecieved
--ForgeignKeyField ' this field would relate back to the parent table like "Proposals", "Plan", etc
Now a you can have unlimited amount of documents

Same here if tracking more types of statuses than just simply received or not.
TblDocuments
--DocumentName
--DocumentStatus
--DocumentStatusDate
--ForgeignKeyField

That then would give you a record of when the document was "assigned", when it was in Draft, and when it was received.