Referencing table fields not in a query

[strict9]

Hello all,

Fairly simple (I believe) question here: Several queries of mine need a
default value if the requested value is null. For example:

Location: IIf(IsNull([Office Name]),"Office 560",[Office Name])

Now what I want to do is replace "Unknown" with the value in a table
field. I tried doing:

Location: IIf(IsNull([Office Name]),[DefaultValues]![Office],[Office
Name])

However, this doesn't work because the table DefaultValues just
contains several fields containing these default values, so I can't
link it to any other tables in my query.

Is there a way to reference a table's field if that table is not in the
query? Or another method of doing this, aside from adding the default
value to every single record of my main query table? Thanks!

 

[David Lloyd]

One alternative would be to use the DLookup function. For example:

Location: IIf(IsNull([Office
Name]),DLookup("[Office]","DefaultValues"),[Office Name])

The DLookup function also allow a third parameter to specify a WHERE clause,
if needed. See Access Help for more information on this function.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hello all,

Fairly simple (I believe) question here: Several queries of mine need a
default value if the requested value is null. For example:

Location: IIf(IsNull([Office Name]),"Office 560",[Office Name])

Now what I want to do is replace "Unknown" with the value in a table
field. I tried doing:

Location: IIf(IsNull([Office Name]),[DefaultValues]![Office],[Office
Name])

However, this doesn't work because the table DefaultValues just
contains several fields containing these default values, so I can't
link it to any other tables in my query.

Is there a way to reference a table's field if that table is not in the
query? Or another method of doing this, aside from adding the default
value to every single record of my main query table? Thanks!

 

[[MVP] S.Clark]

You could probably add a Dlookup() if you must do it all inline.

If you can run an update query, prior to this query, to populate the
defaults, it may be more efficient, given that you already have an IIF()
slowing things down for you. No since in erraticating all peformance with
another slow function.

 

[strict9]

Thanks for the suggestions. Now that you mention it, it does seem more
efficient to run a update query once than a Dlookup() several times.

Also Steve: I do have about 10 IIf() statements in each query; is there
a more efficient way to check for nulls like I have above, or do you
just recommend putting those in VBA functions instead of inline?

 

[[MVP] S.Clark]

Ok, falling back to the definition of a default, how is that the field gets
to be Null, if there is a desired default? Why isn't the default getting
set during the data entry?

Rule of thumb is that if you have to use IIF(), then there's an inherent
database problem somewhere prior to the query. Bad data structure,
misimplemented UI, etc.