IF Function (1 Viewer)

[dreamz]

Hi

i need help using the "IF" function i think.


Basically i have 2 "columns"


A B
aaa
aaa
aaa aaa


Basically if i have say Column A that has some fields filled in and Column B with other fields filled in. I want to Populate a column C with either the stuff in A or B.

So If Column A is blank and Column B is blank, then i want Column C to stay blank.

If Column A is populated and Column B is blank, then i want Column C to be populated with what column A has.

If Column A is blank and B is populated, then i want Column C to be populated with what Column B has.



Thanks in Advance

D

 

[nateobot]

Just concatenate the two fields together.

ColumnC: [ColumnA] + [ColumnB]

 

[macca the hacke]

What about if both columns are populated - does either column take precednce over the other?

 

[dreamz]

i can't because sometimes both Column A and Column B has Values in it.

Needs to be the "IF" statement & also needs to be done in Access.

 

[dreamz]

What about if both columns are populated - does either column take precednce over the other?

If both columns are populated then i'll use whats in Column A

 

[nateobot]

ColumnC: IIf(Not IsNull([ColumnA]) And Not IsNull([ColumnB]),[ColumnA],[ColumnA] & [ColumnB])

 

[dreamz]

thanks a million.

giving it a try now

 

[dreamz]

i put that in Column C: but get no results


IIf(Not IsNull([Locally Assigned RB]) And Not IsNull([Assigned RB]),[Locally Assigned RB],[Locally Assigned RB] & [Assigned RB])

---Locally Assigned RB = Column A and Assigned RB = Column B--

 

[nateobot]

What do you mean by 'get no results'?

Can you post a subset of the data into a blank database?

 

[dreamz]

Once i put IIf(Not IsNull([Locally Assigned RB]) And Not IsNull([Assigned RB]),[Locally Assigned RB],[Locally Assigned RB] & [Assigned RB])

into my criteria of my 3rd column and hit Run, it comes up with 0 records found.

 

[dreamz]

a subset of the data is attached.

 

[nateobot]

See Query1 for Select version, Query2 for Update version in the attached.

Both work perfectly, all I did was copy/paste from my earlier post. Not sure what the problem you are seeing is.

 

[dreamz]

Great, thanks a mill

 

[nateobot]

Once i put IIf(Not IsNull([Locally Assigned RB]) And Not IsNull([Assigned RB]),[Locally Assigned RB],[Locally Assigned RB] & [Assigned RB])

into my criteria of my 3rd column and hit Run, it comes up with 0 records found.

Ahhh just re-read this and that is the problem. The IIf statement should be it's own column, not the criteria of a column.

 

[dreamz]

yeah i saw that. Guess i should of mentioned how little i know about Access


Thanks again

 

[dreamz]

Could i be a pain and ask for a brief explanation of it?

 

[nateobot]

Could i be a pain and ask for a brief explanation of it?

Not a pain in the least.

Column C: IIf(Not IsNull([Locally Assigned RB]) And Not IsNull([Assigned RB]),[Locally Assigned RB],[Locally Assigned RB] & [Assigned RB])

The 'Column C: ' Section simply tells the query engine what you want your field named. You can put almost anything you want before the colon and that is what will appear in the header. This is one way of naming functions that are happening or simply renaming a field for display purposes.

The IIf section is explained in full detail here: link but in laymans terms it looks like this: IIF(Criteria, If Criteria is true do this, otherwise do this). Notice each section is seperated by commas and the entire function is encapsulated by parens.

In our case we want to see if there is a value in [Locally Assigned RB] and [Assigned RB]. The IsNull(ItemToCheck) function tells us if the ItemToCheck has data in it (returning false if there is data, true if no data). We put the Not in there to return the opposite of what it should return. I know it sounds confusing.

Lets say [Locally Assigned RB] had a value of "Jerry" in it.
IsNull([Locally Assigned RB]) would return False to us, because the field is not null.
Not IsNull([Locally Assigned RB]) would return true to us because IsNull is False (see above) and the Not clause would return the opposite, or True.

You can apply the same logic to [Assigned RB].

If both of our statements are True (by using the AND keyword). We want to return [Locally Assigned RB]. If either field is Null then we will simply concatenate the fields. If you wanted to check if one field or the other was blank, then you could substitute the OR keyword instead.


Hope this makes a bit of sense and if someone would like to interject or correct me please do.

 

[dreamz]

much clearer now


Thanks again

 

[EMP]

The expression can be simplified to:-

Column C: IIf(Not IsNull([Locally Assigned RB]), [Locally Assigned RB], [Assigned RB])


That is, if Locally Assigned RB is not null, return it. Otherwise return Assigned RB.

It would automatically take care of the situation where both Locally Assigned RB and Assigned RB are null.

^