• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Dlookups on a form

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
jcalb
Asked:
jcalb
  • 4
  • 4
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
PaurthsCommented:
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
 
PaurthsCommented:
sorry Jim,
crossposted...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jcalbAuthor Commented:
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
 
jcalbAuthor Commented:
When I run #3 I get this--> #error
0
 
jcalbAuthor Commented:
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
 
jcalbAuthor Commented:
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
 
PaurthsCommented:
try this:

=DLookUp("[client]","clientfund","[fund] = 'bond' and [clientz]= " & Chr(34) & [Forms]![PrintForm]![Combo46]
& Chr(34))
0
 
PaurthsCommented:
problem was u forgot a  "  after  --> [clientz] =
and one  "  too many at the end.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now