Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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