DLookup Question

[Wet_Blanket]

I am using DLookup in a form, and I was wondering if it is possible to have more than one condition for it. For example, I am using this one:

Account2.Value = DLookup("acct_num", "[AMS_Accounts]", "acc_lnk_id=" & Link_ID)

Can I create a Dlookup that populates Account2 with acct_num when acc_lnk_id = Link_ID and acct_num <> Account1?

If so, how? If not, any viable alternatives for the same result?

 

[RuralGuy]

I think this link should help.

 

[MSAccessRookie]

I am using DLookup in a form, and I was wondering if it is possible to have more than one condition for it. For example, I am using this one:

Account2.Value = DLookup("acct_num", "[AMS_Accounts]", "acc_lnk_id=" & Link_ID)

Can I create a Dlookup that populates Account2 with acct_num when acc_lnk_id = Link_ID and acct_num <> Account1?

If so, how? If not, any viable alternatives for the same result?

The WHERE part of the statement should be able to have any valid where statement that you can structure. For Instance:

[B]   dim WhereStatement As String[/B]
 
[B]   WhereStatement = "acc_lnk_id=" & Link_ID & " And acct_num<>" & Account1[/B]
 
[B]   Account2.Value = DLookup("acct_num", "[AMS_Accounts]", WhereStatement)[/B]

You may need to use cStr(Link_ID) and cStr(Account1), if they are Numbers instead of Strings

 

[Wet_Blanket]

I think this link should help.

So I came up with this:

Account2.Value = DLookup("acct_num", "[AMS_Accounts]", "acc_lnk_id=" & Link_ID & " AND acct_num <>'" & Account1 & "'")

Which appears to work. However, if there is no record that matches this, would I get an error? After this running successfully on a number of accounts, I received an error for an account where it states an operator is missing.

 

[RuralGuy]

I believe DLookup() returns a Null if no match is found.