Solved

Dlookups on a form

Posted on 2001-08-30
9
292 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

776 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