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.
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!MemberList.
5 2202 NeoPa 32,556
Expert Mod 16PB
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!MemberList.
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
@NeoPa
It worked. Thanks Neopa
NeoPa 32,556
Expert Mod 16PB 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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: ShyGuy |
last post by:
I have a table with 4 fields. Three are used for criteria.
I can get the DLookup to work with 1 criteria with the following but
can't get it to work with 2 or three.
NumofAppts = DLookup("",...
|
by: technocraze |
last post by:
Hi guys,
Implementations application & programming environ
MS Acess + Visual Basic
Table fields
Serialno (pk) - Auto number
StudentId - text
Course - text
intake - number
|
by: developing |
last post by:
Hello
How do I specify multiple criteria for FindFirst or FindRecord (or anything else) that takes the criteria from a form. (text field and number field)
edit:
this will be in the after...
|
by: JHNielson |
last post by:
I have a query that I'm trying to update with a dlookup with multiple criteria
This is the string:
EVNT_DT: DLookUp("","","( .EVNT_QTR=.) & (.=.)")
When i run it it says it can't find the...
|
by: 2D Rick |
last post by:
I want to open a report using OpenReport and passing it a Where Clause
with multiple criteria.
I know the Where Clause below is way off but it conveys what I need.
Dates are of string type.
...
|
by: ChadK |
last post by:
I am trying to open a report based on what the user selects on a form.
Each individual criteria works but when I try to combine to pass
multiple criteria it doesn't. I have read what I can find on...
|
by: akirekab |
last post by:
I am using DCount, but I am not able to find how to set simple multiple criteria.
Here is sample of what i need.
=DCount("PatientProfileID","qryFaceToFaceReason_EAP_VG","FaceToFaceReasonID=2"...
|
by: kstevens |
last post by:
Please help. I know the sysntax is wrong. Here are some details. I am looking for the sum of past shipped items. I originally wrote this Dsum with only one criteria, but because of multiple...
|
by: Brendan Wolf |
last post by:
Happy Halloween all,
I have been struggling at work with a DLookup using multiple criteria. I would like a text box to display the results of a DLookup based on the values selected in three...
|
by: Brendan Wolf |
last post by:
Happy Halloween all,
I have been struggling at work with a DLookup using multiple criteria. I would like a text box to display the results of a DLookup based on the values selected in three...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |