Full Version: Dlookup on Forms
UtterAccess Forums > Microsoft® Access > Access Forms
jsw
In my mind I know what I want to do is quite simple, but for some reason I cannot get it to work. For illustration, I will use the following table:

A B C D
1 11 12 AA DA
2 12 13 AB DB
3 13 14 AC DC
4 14 15 AD DD

In my form, I am showing the values of Row3ColA (13, 14, AC & DC). What I want to do is also show on the same form the Column C record that equals Row3ColB (14) which would be the value of Row4ColC (AD).

Any thoughts?

Thanks...

PS I see the table does not come out correctly when I post...Column A should be over the values of 11, 12, 13 and 14, then Column B should be 12, 13 14 15 and so on.

Edited by: jsw on Tue Feb 26 10:58:59 EST 2008.
strive4peace
sorry, it is too confusing with data that doesn't make sense! Could you kindly put some realistic values in as well as explain what you are trying to do and why in English terms?

What is the RecordSource for the form?

I see you are a user of Excel ... Access cannot reference rows the same way that Excel can -- it can be done but is quite different.

thank you
jsw
hmmmmm....I don't know how much better I could explain it. And I understand Access is not the same as Excel....but a column of data is a column of data and a row of data is a row of data. Please, if you can, imagine that 1,2,3 and 4 are the Primary keys and A, B, C and D are the titles of my data fields?
That is happening is that Primary Key #3 has the data of 13, 14 (which is made similar to 13), AC and DC and show up on my form. What I want to do is also show the value of 14 (Primary Key #4) and it's corresponding data from the same data column ©, which in this case would be the value of AD also on the same form.
Thanks...
Vostromus
Looks like you might want to try this.
=DLookup("C","my_tbl","A = " & mid("13,14,AC,DC",4,2))
Not sure how you have the values stored on the form though.
DLookup~Tech On The Net
jsw
When you ask how I have the values stored, do you mean in one table?
All values are in one table so do I need to reference the table in the formula?
I do have over 35 data fields and over 600 rows of data.
I guess I am explaining it all wrong...but don't know how else to explain it...it's clear to me!!! LOL
Would be easy if I could email someone a screen shot of my form.
jsw
Ok...How about this explanation:

In my form I display a Primary Key. This Primary Key fieldname is titled "Module" and on my form is showing the value of "A". Underneath this value on my form, it shows another value of "B" which comes from a fieldname titled "SimToMod" (becasue "A" is simialr to "B"). Elsewhere on the form I have a value displayed from a fieldname titled "Flagnotes". All values displayed on my form relate back to the Primary Key (Module), which in this case is "A". What I want to do is also display on the form the "Flagnotes" value of the SimToMod "B" (which is also listed in the Primary Key "Module" data field.

So if I were to write it out it would look like this:

Show the [Flagnotes] of [Module] "B" when [SimToMod] "B" is listed on the form with [Module] "A"

Those items in brackets are actual fieldnames.

Does that make anymore sense?
Vostromus
=DLookup("C","my_tbl","A = " & mid("13,14,AC,DC",4,2))
That I was trying to say was that the string contained in the mid() function can be whatever you wish. If you have a text box on your form you can set the control source to that and it will return your answer. If the string you want to search is in a text box you could do this as well....
assume (mytextbox.value = "13,14,AC,DC" ) This is all the fields concatinated in row 3.
=DLookup("C","my_tbl","A = " & mid(mytextbox.value,4,2)) The mid() will return 14 so you will return the value of column C where the value of column A = 14.
Where the value of the text box is a string that contains the value you want to look up. You will want to change the "A = "... part of the dLookup to the value that you are trying to find in column A then you will return what is in column C.
Hope that helps....
jsw
I get a #NAME? error. :-(
id you by chance catch my "edited" reply above?
I think I was editing it when you sent your last reply
jsw
I had a fieldname incorrect...now I get an
Error
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.