Dlookup [field] = form entry

[staticxxx]

Hello,

Ive been scratching my head for days now on what I thought was a simple expression.

=Dlookup ( [FieldName], "Table", "Criteria = & Form!Form1[textbox1])

Is it possible to have [FieldName] = textbox2 some how? Or am I in over my head?

Appreciate any feedback!

 

[Jeanette Cunningham]

Yes. I suggest that you paste and copy your Dlookup code here.
And tell us what error you get.

 

[staticxxx]

This is the exact code.

=DLookUp("[= & store2]","store to store distance","[store] = Form![store1]")

I get #Error in the box in which this is the source code.

If i replace = & store2 with the name of the field I get the desired value. I would however like for the user to enter the field through store2, which is the name of the textbox.

 

[John Big Booty]

Try;

=DLookUp(Me.store2, "[store to store distance]","[store] = " & Me.[store1])

This assumes that both Store2 and Store1 reside on your form.

It also assumes that Store1 is a numeric field, if it is a text field your formula will look like;

=DLookUp(Me.store2, "[store to store distance]","[store] = [B][COLOR="Red"]'[/COLOR][/B]" & Me.[store1] &"[B][COLOR="Red"]'[/COLOR][/B]")

As an aside consider a naming protocol for your DB object and control, something along the lines of ; TBL_TableName, FRM_FormName, QRY_QueryName. This will make it much easier to determine the type of object you are referring to when you write code or formulas. Also avoid spaces and other special characters, limit yourself to alpha and numeric characters and the underscore(_)

 

[John Big Booty]

Sorry my bad the formulas should look like;

=DLookUp("store", "[store to store distance]","[store] = " & Me.[store1])

or

=DLookUp("store", "[store to store distance]","[store] = [B][COLOR="Red"]'[/COLOR][/B]" & Me.[store1] &"[B][COLOR="Red"]'[/COLOR][/B]")

However I'm a little confused what you are trying to achieve with Store2. Could you explain what you are trying to achieve?

 

[staticxxx]

I am trying to use a form as a calculator to show distances between different stores.

The table looks like this only larger:

Store | 1 | 2 | 3 | 4 | 5 |
1 | 23 | 43 | 34 | 65 | 14 |
2 | 34 | 23 | 54 | 72 | 38 |
3 | 41 | 45 | 54 | 76 | 44 |
4 | 35 | 85 | 34 | 73 | 64 |
5 | 32 | 67 | 29 | 87 | 37 |
where the data is the KM between stores.

I have three text boxes on a form:

Store1, Store2, KM

I would like the user to input the store numbers into text boxes;store1 and store2, and the amount of KM would be displayed in text box KM via the dlookup code in the control source.

However both the field name and criteria for the dlookup code must come from textboxes store1 and store2.

So:

=DLookUp("[3]","store to store distance","[store] = 4")

Would return the value 34.

But I need the red part of this code to come from the form. I have figured out that I can replace 4 with Form![store1] but the same logic does not seem to apply with 3.

3 would be the Field, and 4 would be the unique ID

Thanks for your thoughts.

I also recognize the benefits with the naming conventions you mentioned and will put the advice to practice Thanks!

 

[John Big Booty]

OK I see and understand what you are trying to do. Firstly DLookup() does not work in the way that you are trying to use it. It works as follows;

DLookup("Field To Return", "Domain to look in", "Your look up criteria")

That is not to say however that you can't use multiple criteria in your DLookup(). However that is not going to help us in this situation.

You are currently using spreadsheet approach to this problem. I'm going to have to think on this a little before I can suggest a better method for storing you data, which will allow you to more easily extract the required information.

 

[boblarson]

Something like this SHOULD work:

=Nz(DLookUp("[" & [Forms]![YourFormNameHere]![textBox1NameHere] & "]","store to store distance","[store] = " & [Forms]![YourFormNameHere]![YourTextBox2NameHere]), "")

And you do want the NZ there as it will return an invalid use of null error until the values are filled in. The NZ should help that not do that.

 

[boblarson]

You are currently using spreadsheet approach to this problem. I'm going to have to think on this a little before I can suggest a better method for storing you data, which will allow you to more easily extract the required information.

And I would suggest that also even though I did post a way to get around the current problem. JBB is correct. You don't want to be thinking in spreadsheet mode when you are using a Relational Database.