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

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

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
adnan2004
Asked:
adnan2004
  • 5
  • 4
  • 3
  • +1
1 Solution
 
phileocaCommented:
yes, although on my reports, i think i put it in labels.
0
 
phileocaCommented:
nm, i do it in textboxes.  and yes, in the control source.
0
 
morpheus30Commented:
Your problem is SYNTAX...

DLookup("[Price]", Stock", "[ItemNumber] Like " & "'" & [ItemNumber] & "*'")
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
morpheus30Commented:
Sorry...

DLookup("[Price]", "Stock", "[ItemNumber] Like " & "'" & [ItemNumber] & "*'")
0
 
morpheus30Commented:
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
 
phileocaCommented:
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
 
morpheus30Commented:
Oh, I saw something else too...

DLookup("[Price]", "Stock", "[ItemNumber] Like " & "'" & Me.[ItemNumber] & "*'")
0
 
adnan2004Author Commented:
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
 
phileocaCommented:
=DLookup("[Price]", Stock", "[ItemNumber] = '" & [ItemNumber] & "'")
missing a quote at stock.  try this:

=DLookup("[Price]", "Stock", "[ItemNumber] = '" & [ItemNumber] & "'")
0
 
adnan2004Author Commented:
I try that also...
0
 
morpheus30Commented:
Did you try this?

=DLookup("Price", "Stock", "ItemNumber = '" & Me.[ItemNumber] & "'")
0
 
Sayad Aziz AhmadCommented:
slight modification:
=DLookup("Price", "Stock", "ItemNumber = " & Forms!Formname!ItemNumber )
0
 
adnan2004Author Commented:
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
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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