Newbie IIf - IsNull problem

[bartoki]

I am new to Access, so thanks to anyone who can reply.

Problem: I want a field in a form to reference either one source in one table or another source in another table, depending on the results of evaluating another source as IsNull. My code for my control source looks like this...

=IIf(IsNull([EmployeeID])=True, [Location] , [Employees]![DeskLocation] )

I always get a #name error. If I substitute both arguments as "True Part" and "False Part" the expression works fine. If I substitute only one argument as text I get a #error when the other argument is the one being evaluated. So, it has something to do with be referencing [Location], which is in the same table as [EmployeeID], or [Employees]![DeskLocation]...or both. Or, the problem may be some other aspect of these sources, like formatting perhaps? Though I've made sure the format of all of these sources are all Text.

Thanks! I'm in a bind and I appreciate any assistance.

 

[quest4]

bartoki, I may be crossed eyed by now, but it looks like, by your staement, you are saying If EmployeeID is Null or Blank, then get Location and DeskLocation. Is that what you really want? I would have thought you would want the reverse, in which case change true to false. Hope that helps.

 

[KenReay]

Hi

Is the control by chance called DeskLocation ?

If yes, try renaming it to (say) txtDeskLocation, Access does not like having the control name of a calculated control, the same as one of the elements of the calculation

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bartoki]

quest4: What I want is if the employeeID is blank, I want it to return the location, if it is not blank, I want it to return DeskLocation.

KenReay: That was a good suggestion, though it didn't work. But imagine the problem is something along those lines. The name of the text box on this form was "Location", also the same as one of the elements in my expression. But I just changed the name to "LocationResult" and I'm still getting a #name.

Thanks...anything else...please...?

 

[KenReay]

Hi

Ok, could it be that one of the unqualified columns is present in both tables of the recordsourec query, and thus needs to be qualified with table name, as you ahev done for DeskLOcation
so for example:
=IIf(IsNull([Employees]![EmployeeID])=True, [Location] , [Employees]![DeskLocation] )


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[quest4]

Good suggestion, Ken. I was looking down the wrong path. That should get him working correctly.

 

[bartoki]

Ken,

Thanks for your digilence

No, that could not be it because the IIf is evaluated fine when I substiture "True Part" and "False Part" for the last two arguments. So, the reference made in the first argument is fine. The reference made in the true part always reverts back to just [Location] whenever I type in [Assets]![Location] due to some automatic process Access does.

Maybe it is important to mention here that the IsNull part references the [EmployeeID] column in the [Assets] table. The [EmployeeID] column there just references the ID# from my [Employees] table and returns the name of that employee as "Last, First". The false part of IIf references [DeskLocation] column in the [Employees] table, which also, of course, contains the [EmployeeID] column (the autonumbering column) though I do not reference this column at all in this source. Could the existence of the [EmployeeID] in my assets table and in my employees table create a problem?

Thanks again!!

 

[KenReay]

Hi

That was my point, hence the suggestion to qualify the column name with the table name.

But your added statement "IIf is evaluated fine when I substiture "True Part" and "False Part" for the last two arguments. " makes me think the problem lies with [Location] , [Employees]![DeskLocation]


you have renamed the control haven't you so it is not called Location?

DeskLocation ids in table Employees isn't it?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

That was my point, hence the suggestion to qualify the column name with the table name.

But your added statement "IIf is evaluated fine when I substiture "True Part" and "False Part" for the last two arguments. " makes me think the problem lies with [Location] , [Employees]![DeskLocation]


you have renamed the control haven't you so it is not called Location?

DeskLocation is in table Employees isn't it?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bartoki]

Agreed. My basic understanding of all of this leads me to believe something is wrong with the true and false part. Yes I renamed the name of the text box on this form from "Location" to "LocationResult". And, yes. [DeskLoaction] is in the table [Employees]!. I've built this equation both by typing it in and using the expression builder and that has not made a difference, so its not a typing error.

Also, from what I understand, Access evaluates IIf arguments separately, so if the first argument=True, then it only evaluates the True part. So, because I substituted "True Part" and "False Part" for either argument one at a time (=IIf(IsNull([EmployeeID]),"True Part",[Employees]![DeskLocation]) for example) and I got a #error when the evaluation of IIf returned this argument (in this case the false part), then I think that there is something wrong with both True and False arguments.

Thanks Ken!

 

[Krystoff]

Bartoki,

Quick question, are the fields you are trying to reference on the form where you have the textbox?

Here is a break down of what I think I have gathered:

=IIf(IsNull([EmployeeID])=True, [Location] , [Employees]![DeskLocation] )

LocationResult: Field on the form where above formula is being called.

EmployeeID: Field that is in Assets and in Employees table. Primary field in Employees table

Employees!DeskLocation: Field in employees table. Not sure if this field is on the form as well.

Location: Field in another table(Not sure which table). Also not sure if this field is on the form.
*****************************************************

If you can let me know if what I described is the case, it would make it easier for me to understand this.

 

[bartoki]

I'd be happy to clarify Krystoff, and thank you.

Most of that was accurate:
I do have EmployeeID on this form, called Assets, but I do not have Employees!DeskLocation. Location is not on this form either, other than my reference to it in the expression text box LocationResult.

Location is a column on another Table called Assets and not a field on this form.

You were also correct in saying that the EmployeeID column on Assets is a lookup referencing the corresponding "Last, First" from the EmployeeID column in the table Employees.

Also, let me clarify my error messages. When I have both true and false parts referencing a field I get a #name? error. When I substitute text for one part (i.e. "True" or "False&quot, I get a #error? error when the part referencing a field is evaluated.

Thanks again for this groups support!

 

[KenReay]

Hi

If you get an error no matter what you put in the 'result' true of false filed, then that is the oppostite of what I thought was happening and makes me think something is wrong with ([Employees]![EmployeeID]

Suggetsion put a hidden control on the form, bound to ([Employees]![EmployeeID] and use the name of that control in the IIF in place of ([Employees]![EmployeeID]

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bartoki]

Ken,

Granted I'm somewhat confused by this, but I think you were right the first time. Because the expression works fine when using "True" and "False" and when I only substitute one of those the substituted part works fine (while the one referencing a column yields an #error? error), I think that it has to be the true and false part that are not working.

Regardless, your workaround of placing a hidden text box on this form referencing those locations that I want in the true and false parts seems to work perfectly. The only issue is when I reference the Desk Location, I have to do a lookup, so the result it returns is the ID # versus the value for that ID. But I imagine this is quite simple to fix, much more easily surmountable than what I was facing.

Thank you so much for your help. That workaround is what I was looking for.

 

[bartoki]

Thought I would post my final results here.

Textbox named LocationResult on form Assets contains the following expression:

=IIf(IsNull([EmployeeID])=True,[hiddenAssetLocation],DLookUp("[DeskLocation]","Employees","[EmployeeID]=[hiddenDeskLocation]&quot)

whereas:
a) EmployeeID is a field in table Assets, which is a lookup referencing a field in table Employees.
b) hiddenAssetLocation is a hidden textbox on form Assets that simply references field AssetLocation on table Assets.
c) hiddenDeskLocation is a hidden combobox on form Assets that references DeskLocation located in table Employees.

I dont quite understand what was at hand here, but thanks to your help I stumbled through it. Eat it Access! Hooah!