Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Dlookups on a form

Posted on 2001-08-30
9
293 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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
 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

861 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