DLookup error message

  • Thread starter Thread starter Dimitris Nikolakakis
  • Start date Start date
D

Dimitris Nikolakakis

I have a form with a TextBox and I have put a button to....
***********************************************************************
Dim x As Variant
x = DLookup("OrderID", "Orders", "[OrderID] =" &
Forms![DLGOfferTransform]!Text2)

If x <> Null Then
Dim Response
Response = MsgBox .... ..... .....
Else .... .... ....
************************************************************************

When I press the button I get the error message:
 
If OrderID is a Text Field, you should use:

x = DLookup("OrderID", "Orders", _
"[OrderID] = " & Chr$(34) & _
Forms![DLGOfferTransform]!Text2 & Chr$())

My guess is that you actually wants to check if the Record
exists or not. In this case, use DCount and check whether
the result is = 0 or not.

x = DCount("OrderID", "Orders", _
"[OrderID] = " & Chr$(34) & _
Forms![DLGOfferTransform]!Text2 & Chr$())
If x = 0 Then ...

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
I have a form with a TextBox and I have put a button to....
**********************************************************
Click to expand...
*************
Dim x As Variant
x = DLookup("OrderID", "Orders", "[OrderID] =" &
Forms![DLGOfferTransform]!Text2)

If x <> Null Then
Dim Response
Response = MsgBox .... ..... .....
Else .... .... ....
********************************************************** **************

When I press the button I get the error message:
--------------------------------------
Run-time error '3464':
Data type mismatch........................
---------------------------------------

In the table Orders.OrderID is Text




.
Click to expand...
 
Just like in a query when you are using text criteria, you need a delimiter around the text. Although you normally use a quote ("),
if the order id's never contain an apostrophe ('), you can save some headaches and use that. Try:

x = DLookup("OrderID", "Orders", "[OrderID] = '" & Forms![DLGOfferTransform]!Text2 & "'")

After the =, it is (' ") and at the end it is (" ' ") without the spaces. (It's hard to see apostrophes and quotes when they're run
together.)

Two additional things.

1 - I'm not sure why you're returning the OrderID in the 1st argument when you're supplying it to start with for the criteria.
Normally the first argument would be a different field from the one you're using for the criteria in the 3rd argument.

2 - In your If - Then line, instead of x <> Null, use:

If Not IsNull(x) Then

Good luck.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #15 of 19: 5 dialogues = 1 decalogue
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
Back
Top