?
Solved

Can I type the Dlookup function in the ControlSource property of a text box on a form or report?

Posted on 2004-08-05
15
Medium Priority
?
398 Views
Last Modified: 2008-02-01
Hello
 
My question is:

Can I type the Dlookup function in the ControlSource property of a text box on a form or report?
Acctually, I want to poll Price from table named "Stock",so I will have Dlookup formula that looks like this:

=DLookup("[Price]", Stock", "[ItemNumber]like" & "'" & [ItemNumber] & "'")

This formula works great in Microsoft visual basic, but when I try to type it in ControlSource
 of a text box,syntax erros appears.

Any help is appreciated.

Thanks
0
Comment
Question by:adnan2004
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 11

Expert Comment

by:phileoca
ID: 11731093
yes, although on my reports, i think i put it in labels.
0
 
LVL 11

Expert Comment

by:phileoca
ID: 11731131
nm, i do it in textboxes.  and yes, in the control source.
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11731151
Your problem is SYNTAX...

DLookup("[Price]", Stock", "[ItemNumber] Like " & "'" & [ItemNumber] & "*'")
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 5

Expert Comment

by:morpheus30
ID: 11731156
Sorry...

DLookup("[Price]", "Stock", "[ItemNumber] Like " & "'" & [ItemNumber] & "*'")
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11731167
I don't know if you want the wild character or not, but that's usually why I would use the LIKE operator instead of the equal ("=") operator.
0
 
LVL 11

Expert Comment

by:phileoca
ID: 11731175
this is what i have on my report:
locMondayDate = DLookup("MondayDate", "HistoryWeeks", "[ID] = " & ScheduleWeek1)

yours appears to be syntax error. too many &'s
=DLookup("[Price]", Stock", "[ItemNumber]Like" & "'" & [ItemNumber] & "'")

if ItemNumber is a text field, use single quotes.
=DLookup("[Price]", Stock", "[ItemNumber] = '" & [ItemNumber] & "'")

if ItemNumber is a Number Field, you don't need single quotes.
=DLookup("[Price]", Stock", "[ItemNumber] =" & [ItemNumber])
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11731183
Oh, I saw something else too...

DLookup("[Price]", "Stock", "[ItemNumber] Like " & "'" & Me.[ItemNumber] & "*'")
0
 

Author Comment

by:adnan2004
ID: 11737310
Thnx for help.

When I try to type formulas you suggested:

=DLookup("[Price]", Stock", "[ItemNumber] = '" & [ItemNumber] & "'")
And
=DLookup("[Price]", "Stock", "[ItemNumber] Like " & "'" & Me.[ItemNumber] & "*'")

 in Control source of text box, i get erorr message:

"You omitted an operand or operator, you entered an invalid character or comma,
 or you entered text without surrounding quotation marks."

Price is Number and ItemNumber is Text.
I try it all, but it still doesnt work!
0
 
LVL 11

Accepted Solution

by:
phileoca earned 80 total points
ID: 11737363
=DLookup("[Price]", Stock", "[ItemNumber] = '" & [ItemNumber] & "'")
missing a quote at stock.  try this:

=DLookup("[Price]", "Stock", "[ItemNumber] = '" & [ItemNumber] & "'")
0
 

Author Comment

by:adnan2004
ID: 11737673
I try that also...
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 11737760
Did you try this?

=DLookup("Price", "Stock", "ItemNumber = '" & Me.[ItemNumber] & "'")
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 11742315
slight modification:
=DLookup("Price", "Stock", "ItemNumber = " & Forms!Formname!ItemNumber )
0
 

Author Comment

by:adnan2004
ID: 11743372
I think I have find solution:

=DLookup("[Price]"; Stock"; "[ItemNumber]like" & "'" & [ItemNumber] & "'")

As you can see only modification i made is changing "," in to ";" in Controul source of tex box, and it works perfect.

Thanks for helping me out.

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

864 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