Newbie If Then question......

[SDGregory]

I am a newbie trying to write a simple script that just isn't working for me! I am trying to create a text field that only shows text if another field has data in it. I've experimented with different syntax's and can't find a working one. My scenario: On a print report, if a fax number is input, then write text "FAX:" and then the field. The If XXX Then XXX is my dilema. MS Access doens't use "IF THEN". Any help is appreciated!

 

[RivetHed]

hi if you're talking about access expressions rather than VBA the syntax is:
IIF("Enter argument here","True","False&quot
pretty well the same as XL if you ever use that

 

[SDGregory]

Yes, I think an Access expression will work for this but I'm still a little lost. How would I get a text result out of this expression? If it were: IIF([FaxNumber], "True) then what? I'm just unfamiliar with Access syntax and am having a hard time finding any program help topics covering this. The IsNull function may work, but I still couldn't get the right syntax.

 

[Eupher]

What RivetHed is trying to show you is the generic format of the IIf() function. The first part is the expression to be evaluated, the second part is what happens if it's true, the third part is what happens if it's false. So in this case, you could create an unbound text box on your report and set the Control Source property to:

=IIf(IsNull([FaxNumber]), "", "FAX: " & [FaxNumber])

So, if the [FaxNumber] field is null, the control will display a null string ("&quot, otherwise it will display FAX: followed by whatever value is in the [FaxNumber] field. Remember, you'll have to susbstitute the real name of YOUR faxnumber field in the expression.

HTH...

Ken S.

 

[SDGregory]

Thanks for your help. I have the string =IIf([FaxNumber],"Fax:","&quot right now and it seems to be working. I say seems because it displays "Fax:" whenever a fax number is inputed but it also shows for some records that there is no fax number. Thats my next puzzling dilema. Thanks for you help!

 

[Eupher]

In what manner does your report show that some records have no fax number? Does it just leave a blank space where the fax number would be?

Ken S.

 

[SDGregory]

I have two separate fields right now, one for the fax text itself (=IIf([FaxNumber],"Fax:","&quot), and one for the actual fax number. I really haven't found any consistency why it shows for some records and not others. Maybe I should combine the two like you suggested. That way the actual fax number shows when it is inputed and shouldn't show anything if nothing is there (it will test the value first). But I still don't understand why there would be a difference for a separate test field or and 'all-in-one'?

 

[SDGregory]

After combining the two, it does leave a blank area where the fax number would be and will show the text "Fax:". Again it is still inconsistent for the records w/o fax numbers. Sometimes it shows, sometimes it doesn't. ....Hmmmm....very interesting.....

 

[Eupher]

SDGregory, I have not been able to reproduce the phenomenon you describe. I would take a look at the actual data in the table. Has anything about the table structure ever changed that could affect the data? A different input mask, perhaps? One would think that this would have no bearing on an empty field, but perhaps somehow some records actually hold blank (i.e. non-printing) characters. You might try querying the table, and specify "Is Not Null" for the criteria for the fax number field and see what the datasheet displays. You might try an update query and search for records that have non-numeric data in the fax field, and replace with "". I don't know off the top of my head what query expression would accomplish this, just throwing out ideas. And of course, anything you try in this regard should be thoroughly tested on a backup copy of your table before you execute it on live data.

Ken S.

 

[RivetHed]

Hi again, I think Eupher was on the right lines in his previous post that there is a difference between a zero length string and a null value, you might want to try something like IIF(len([FaxNumber])>0,"Fax","&quot as this will evaluate the length of the field and will therefore ignore both null and zero length strings.

HTH

 

[SDGregory]

Thanks for your help. I was thinking somewhat the same thing whereas there is input in the field, but it is a 'blank' character. I tested this theory in new records never touching the fax field and it seemed to work. For my existing records I will try the new string you mentioned testing the length being 0 or more. Again, thanks for your help and I will have to inform you on my results!

 

[misscrf]

ok, my question is similar, so I thought I would ask it here. I have a report that shows an inventory for monitors. There is a field for workstation serial number in the table that feeds the report. I have the following expression to specify whether there is a workstation attatched to the monitor or not:

=IIf([WS] Is Not Null,"Yes","No")

Problem is, I only see "Yes" and I know that some should be saying no because there are records in the table/record source that have an empty field for the workstation.

What am I doing wrong please?
Thanks!

 

[Eupher]

misscrf, try this:

=IIf(IsNull([WS]), "No", "Yes")

Ken S.