Solved

Dlookups on a form

Posted on 2001-08-30
9
290 Views
Last Modified: 2006-11-17
I am trying to set up a Dlookup using as criteria a saved value from a text box from the form.  The Dlookup's source is a query.  I can set up the Dlookup on the form to get a value, but I can't get it to work when the criteria is set to equal the saved value from the text box.
0
Comment
Question by:jcalb
  • 4
  • 4
9 Comments
 
LVL 57
ID: 6440864
If the value of the text box is a numeric:

 DLookup("xx","xxx","[somefield] = " & Forms![myForm]![myControl])

if text:

 DLookup("xx","xxx","[somefield] = " & chr$(34) & Forms![myForm]![myControl] & chr$(34))


THe chr$(34)'s are quote characters.

Jim.
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6440880
hi jcalb,

perhaps syntax is not correct...

for numeric value in txtBox

var = dlookup("field","queryname","criteriafield = " & me.txtBox)


for text value in textbox
var = dlookup("field","queryname", "criteriafield = " & chr(34) & me.txtBox & chr(34))


for datevalue in textbox
var = dlookup("field","queryname","criteriafield = #" & me.txtBox & "#")

(if in europe)
var = dlookup("field","queryname","criteriafield = #" & format(me.txtBox, "mm d yyyy") & "#")


cheers
Ricky
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6440884
sorry Jim,
crossposted...
0
 

Author Comment

by:jcalb
ID: 6441600
I need a multiple criteria:
#1-Works, but is a single
#2 Works, it is a single, but references a form
#3 Is what I need, a double criteria using an inputted criteria and the value that is saved on the form.

#1
=DLookUp("[client]","clientfund","[fund] ='bond' and [clientz]='AV'")

#2
=DLookUp("[client]","clientfund","[clientz]=" & Chr(34) & [Forms]![PrintForm]![Combo46] & Chr(34))

#3
=DLookUp("[client]","clientfund","[fund] = 'bond' and [clientz]=  & Chr(34) & [Forms]![PrintForm]![Combo46] & Chr(34)")
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:jcalb
ID: 6441612
When I run #3 I get this--> #error
0
 

Author Comment

by:jcalb
ID: 6441624
I'm sorry,
#1 is a multiple expression, it works.
#2 is a single using a form value, it works.
#3 is a multiple expression using a form value.  This is the one I'm having trouble getting to work.  It returns #error.  
0
 

Author Comment

by:jcalb
ID: 6441655
I'm sorry,
#1 is a multiple expression, it works.
#2 is a single using a form value, it works.
#3 is a multiple expression using a form value.  This is the one I'm having trouble getting to work.  It returns #error.  
0
 
LVL 12

Accepted Solution

by:
Paurths earned 50 total points
ID: 6441686
try this:

=DLookUp("[client]","clientfund","[fund] = 'bond' and [clientz]= " & Chr(34) & [Forms]![PrintForm]![Combo46]
& Chr(34))
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6441714
problem was u forgot a  "  after  --> [clientz] =
and one  "  too many at the end.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now