If, then, dlookup

[goaksmith]

Is it possible to have an if then statement in the control source of a text box. For example:

=(If([Text56]=" ", " ", (DLookUp("[Name]","OptionGroupTable","[OptionGroupNumber] =" & [Text56]))))

Right now when I do this it asks me to define If.

The reason I am doing this (in case there is another way and I am not thinking of it) is that in some cases the [Text56] field is blank. When it is blank the Dlookup function returns an error. What I want it to do is if there is a value in [Text56] to use the Dlookup funtion and if it is null leave it blank.

Thanks in advance!

Gretchen

 

[dcx693]

Regarding if...then statements: you can put an If then statement in the control source, kind of. You need to use the IIf function. Look it up. The syntax is very straightforward. Make sure to put an "=" sign in front of the IIf function, or it won't work.

You can fix your issue in other ways, though. If your text box is blank, it won't be equal to " ". The way you've written your statement above, you're testing to see if the field value is equal to a space character. To test for an empty string, you need to use this: "" . You might be better off testing for a Null value in the field using the Nz() function. Then your controlsource could be much simpler like: =Nz(DLookUp("[Name]","OptionGroupTable","[OptionGroupNumber] =" & [Text56]),0)

 

[goaksmith]

Working now

I tried to use the Nz, but could not get it to work. But when I used Iif:

=IIf([Text56] Is Null," ",(DLookUp("[Name]","OptionGroupTable","[OptionGroupNumber] =" & [Text56])))

It worked perfectly. Thanks!

 

[dcx693]

Hmmm...I'm very surprised that your expression worked since [Text56] Is Null doesn't sound like a valid Access expression, but what do I know! I would've tried IsNull([Text56])

 

[MattS]

dcx693 - I thought IsNull([TheField]) was a VBA expression, and have always used [TheField] Is Null is any queries or Form/Report controls.

Not 100% sure, but it's always worked for me.

Matt.

 

[Mile-O]

dcx693 - I thought IsNull([TheField]) was a VBA expression

It's an Access Function.

 

[Rich]

IsNull and Is Null are not the same, one can be used in sql, the other can't

 

[dcx693]

Interesting, so I tried out [Text56] Is Null and it did work in a query. Is there any reason to use this over IsNull()? The IsNull just seems more compact, so I'd tend to prefer it.

Also, any ideas on why the Nz() didn't work? It works in my queries and is much more compact than using IIf().

 

[Pat Hartman]

I am not sure that the two different statements are 100%interchangable between VBA and SQL. I would guess that at some point they may not be so I always stick to the defined syntax for each language.

"IsNull(SomeField)" when the statement will be evaluated by VBA
And
"SomeField Is Null" when the statement will be evaluated by SQL

Since IIf() is a function and will be passed off to VBA for evaluation, I would use IsNull() within the IIf() function.