Dlookup inside IIf in 2 Table Update Query

[cleech]

Hello all:
I'm trying to do something a little funky here and I'm not sure if I'm
approaching it the right way.

I'm running an update query that will contain SocSec numbers for 4
different fields. Obviously each one needs to be different for each
record and each field. I am taking the SocSec numbers from a table
that has each number listed along with an identifier (2,3,4,5) to
bring them into the correct fields.

What I'm trying, unscuccessfully, right now is the following:

IIf(Left([dbo_tblAIR_SUPmo]![txtRider1],3)="AIR",
(DLookUp("[SocialSec]","dbo_tblSocialSecurity",[dbo_tblSocialSecurity]!
[MOnumber]=[dbo_tbl5YT_CTPmo]![lngMOnum] And [dbo_tblSocialSecurity]!
[Insured]=2)),Null)

This does not work and I'm not sure that it can. When the I run the
update query, I get parameter prompts for [dbo_tblSocialSecurity]!
[MOnumber] and [dbo_tblSocialSecurity]![Insured].
Table [dbo_tblSocialSecurity] is not part of the query. I'm not sure
if this matters either.

Essentially what I would like to do is accomplish inserting these
SocSec numbers in this one update query. I know I can add
[dbo_tblSocialSecurity] to the query and have identifier criteria of
2, but this would involve running the query 4 times.

As always, any help is greatly aprreciated.

 

[John Spencer]

First, your DLookup does not appear to be structured properly.

IIf(Left([dbo_tblAIR_SUPmo]![txtRider1],3)="AIR",
DLookUp("[SocialSec]","dbo_tblSocialSecurity","[MOnumber]=" &
[dbo_tbl5YT_CTPmo]![lngMOnum] & " And Insured=2"),Null)

That assumes that [dbo_tbl5YT_CTPmo] is a table in the query and that
[dbo_tbl5YT_CTPmo]![lngMOnum] is a number field.

Beyond that it is difficult to say what you need to do.
You have not posted the SQL you are attempting to use and we have no idea of
the table structure(s) involved.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County