IIF? usage (2000/All)

Topic

New Reply

I have a simple form that has fieelds for student given names (and preferred name) and family name for data entry.

At the top of the form I have two fields that the user can’t change. These fields are to be the family name field and preferred name (depending on whether one is entered) or the the given names.

Basically it is a “friendly name” at the top of the form for users as a confirmation that they are working with the desired student (but they can check the given names for confirmation).

I have used an IIF function to check whether the preferred name field is blank (and then use the given names) but this is showing the #Name? (as if the Control Source is not defined correctly).

This is the code I tried. Where am I going wrong, please?

txtFirstNames.Value = IIf(Me.txtPreferredName  "", [txtPreferredName], [txtGivenNames])

Reply | Quote

Replies

Whenever you use the expression me you are referring to the current form.

so txtFirstNames.Value = IIf(Me.txtPreferredName “”, [txtPreferredName], [txtGivenNames]) requires that there be a control on the form called txtPreferredName

If txtpreferredname is a field in the underlying query, but is not a control on the form (which I think is the case) just remove the me.

txtFirstNames.Value = IIf([txtPreferredName] “”, [txtPreferredName], [txtGivenNames])

Another issue. If the field txtPreferredName is really empty its value will be Null . This is different from “” which is a zero length string. It would be safe to test for both.

txtFirstNames.Value = IIf(([txtPreferredName] “”) and not isNull([txtpreferredname]) , [txtPreferredName], [txtGivenNames])

Reply | Quote

Thanks John for your post – but I am still experiencing the same problem.

It possibly is just obvious but what am I doing wrong please?

Reply | Quote

Is txtPreferredName both the name of the field and the name of the controL? If so, that is causing you the problems. If you refer to a field in an IIF statement or code, it can’t have the same name as the control you’re populating.

Reply | Quote

Many thanks Pat, Hans and Charlotte for your posts.

That did the trick – I had been ‘inadvertantly’ using VBA (because I didn’t know better).

And Charlotte, has your tiara always sparkled? I just noticed it now.

Reply | Quote

I added the sparkles to my crown at Christmas and like them so well, I kept them.

Reply | Quote

I called it a tiara – but a quick check reveals that it should have been a crown or at the very least diadem, Your Highness

Reply | Quote

You have set the control source of FirstName to a line of VBA instead of a formula (expression). It should be

=IIf(([txtPreferredName]””) And Not IsNull([txtPreferredName]),[txtPreferredName],[txtGivenNames])

i.e. without FirstName.Value at the beginning.

Reply | Quote

In the FirstName text box on the form change the ControlSource from:

Firstname.Value=IIf(([txtPreferredName]””) And Not IsNull([txtPreferredName]),[txtPreferredName],[txtGivenNames])

to

=IIf(([txtPreferredName]””) And Not IsNull([txtPreferredName]),[txtPreferredName],[txtGivenNames])

Reply | Quote