Question DLOOKUP Function

[deejabram]

I am attempting to write a DLookup function but am getting an ERROR in return. Can someone tell me what I am doing wrong?

Essentially I want the Value in one field automatically returned from the value of another field from another table. This way if the source data ever changes, it will be changed everywhere.

I am putting the formula in form design as the default value of the field. An example of the formula is as shown below:

=DLookUp("QtySold","Standard","Widgits")

Qty Sold Being the Record Name

Standard being the Field Name

Widgits being the Value

Can anyone tell me what I am doing wrong and help educate me on this process?

 

[vbaInet]

I think you can make a bit more progress by looking at this:

http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[deejabram]

Actually, I read that entire article at least 7 times before even posting here. I try to research my questions myself prior to asking on these boards. Maybe I am just going blind. I have been working on this project for a month now, and after awhile I am just mush. You answered a previous question on mine in another thread, which, admittedly I should have known the answer to. Perhaps I need to step away for while.

 

[vbaInet]

Relax and you will get it

My main point was that you've got the format all mixed up. The format is:

DLookup("Field Name", "Table Name", "WHERE condition")

But what you did was:

DLookup("Field Name", "Field Name", "no condition")

Do you see what I'm getting at?

 

[deejabram]

Still reviewing it. Have tried combinations of the formula. here is the exact formula:

=DLookUp("Picks","Standards","Work Area=EDC Racks DR")

"Picks" is the Field Name
"Standards" is the Table name that the field "Picks" is in
"EDC Racks" is the Field Name that the value is located (meaning the WHERE clause)

- or do I have this part totally jacked up?

 

[vbaInet]

Ok, you're warming up. If your field names contain spaces, you must enclose them in square brackets. For example, Work Area should be [Work Area]. The reason why you should avoid having spaces in your field names.

Also, your criteria is a String, so it must be enclosed in single quotes, i.e. EDC Racks DR should be 'EDC Racks DR'

 

[deejabram]

Awesome. I am now getting a value, but it is the wrong value. Any common mistakes that I could be making?

 

[vbaInet]

Expand on that. It is the wrong value because the same value is being displayed on every row? Or??

 

[deejabram]

It is the wrong value because it should be returning the value of "11" but it is returning a "6" instead. For trouble shooting I looked for every instance in that table where the number "6" was. It appears a few times, but nothing that would link it to 'EDC Racks DR'

 

[vbaInet]

Interesting! Upload your db and I'll have a look. Tell me how to replicate the problem.

 

[vbaInet]

2010 db? I can't use that I'm afraid

Convert it to a 2007 db.

In the meantime, you said there's no Picks value in your table with a Work Area of 'EDC Racks RM'? Have you tested this in a query?

 

[deejabram]

Really didnt need a query as the table isnt that big. I dont have 2007 Access either. I think it is something very simple. I have a fresh mind today and I am going to try to catch what is wrong here without wasting any more of your time. I appreciate your assistance.

 

[vbaInet]

When you're debugging you don't rule out even the most easiest methods. Trying it out in a query is worth the time. Include all the fields from the table in a query, put the criteria under the Work Area field and see what record it points to.

You don't need Access 2007 to convert a copy of your database to an older version. Here's how:

http://office.microsoft.com/en-us/a...se-in-an-earlier-file-format-HA010341553.aspx

 

[deejabram]

I FIGURED IT OUT! It just came to me. I have the form looking up the value from a query. However, that query is not linked to my standards table, so therefore it CANNOT look up the value of 'EDC Racks DR' because there is no reference to that field!!!!

New day. Fresh mind!

Thanks again for your patience and guidance!

 

[vbaInet]

New day. Fresh mind!

Always a good thing

You're welcome!