DLookup multiple criteria errors

[rich1838]

I have created a form in which I need two fields to in order to select 1 of 2 choices for the last field. One is a text field and one is a currency field. Each time I create a DLookup indiviually, it works. When I combine I have had every error under the sun depending on how I modify it. The division field is text and dues is currency. The two would yield a text field. Here is the code:

repstatus = DLookup("[status]", "dues", "dues=" & Forms!memberlist!weeklydues & "" "And division='" & Forms!memberlist!division & "'")

Two of my "division" choices (out of 4)each pay the same amount of dues but depending on classification one is a full member and one is not.

If this helps, there are 4 levels in the division and each level makes a choice of 2 representation levels. The complexity comes in because level 2's highest choice costs the same as level 3's lowest choice, so I can't just make a simple level to rep. choice based on cost.

[Seth Schrock]

What error do you get when you run the code as shown here? With Just a quick glance, I believe that you need an & just prior to your "AND and you would want a space between the double quote and the word AND.

Look at the following website: http://answers.microso ft.com/en-us/office/forum/office_2007-access/using-multiple-criteria-for-dlookup/http://answers.microso ft.com/en-us/office/forum/office_2007-access/using-multiple-criteria-for-dlookup/ed4746c9-8594-4f7d-99fc-c236b1861d3a

[NeoPa]

First off, have a look at Quotes (') and Double-Quotes (") - Where and When to use them. It may clarify the issue for you.

For your VBA try :

RepStatus = DLookup("[Status]", "[Dues]", "([Dues]=" & Forms!MemberList.WeeklyDues & ") AND ([Division]='" & Forms!MemberList.Division & "')")

It seems you weren't too far away with your attempt. Sometimes it helps to set up your filter string separately though, then apply it once it's done :

Dim strWhere As String

strWhere = Replace("([Dues]=%Du) AND ([Division]='%Di')", _
                   "%Du", Forms!MemberList.WeeklyDues)
strWhere = Replace("strWhere", "%Di", Forms!MemberList.Division)
RepStatus = DLookup(Expr:="[Status]", Domain:="[Dues]", Criteria:=strWhere)

It just makes it so much easier to get it right when you can see what you're aiming for.

NB. If MemberList is the current form then the full reference is unnecessary as Me. is equivalent to Forms!MemberLis t.

[rich1838]

What error do you get when you run the code as shown here? With Just a quick glance, I believe that you need an & just prior to your "AND and you would want a space between the double quote and the word AND.

The expression after update you entered as the event property setting produced the following error:Syntax Error.

I tried your changes and got error 3075 syntax error

[rich1838]

First off, have a look at Quotes (') and Double-Quotes (") - Where and When to use them. It may clarify the issue for you.

For your VBA try :

RepStatus = DLookup("[Status]", "[Dues]", "([Dues]=" & Forms!MemberList.WeeklyDues & ") AND ([Division]='" & Forms!MemberList.Division & "')")

It seems you weren't too far away with your attempt. Sometimes it helps to set up your filter string separately though, then apply it once it's done :

Dim strWhere As String

strWhere = Replace("([Dues]=%Du) AND ([Division]='%Di')", _
                   "%Du", Forms!MemberList.WeeklyDues)
strWhere = Replace("strWhere", "%Di", Forms!MemberList.Division)
RepStatus = DLookup(Expr:="[Status]", Domain:="[Dues]", Criteria:=strWhere)

It just makes it so much easier to get it right when you can see what you're aiming for.

NB. If MemberList is the current form then the full reference is unnecessary as Me. is equivalent to Forms!MemberLis t.

It worked. Thanks Neopa

[NeoPa]

Rich:
I tried your changes and got error 3075 syntax error

It would be helpful if you posted exactly what you tried rather than trying to describe the process of the changes you made.

Have you seen post #3 (I notice you replied at a similar time so may not have noticed it earlier)? If so how did you fare with that?

PS. Never mind. We cross-posted again.