Solved Need help with Dlookup the value in the same query. (1 Viewer)

[nax009]

Hi. I'm new for MS Access here. I really want to ask if it possible to lookup for value in the field in query that match the field name?

For example here ... I created a query containing ...

FormularID
DimensionA for input
DimensionB for input
Formula1 = DimensionA+DimensionB
Formula2 = DimensionA/DimensionB
.
.
.
Formula20 = ...
FSelect input Formula2
Result return value from field "Formula2": DimensionA/DimensionB

Please help me solve this one. I've been looking for this for ages.
Any better ideas are welcome, I'll come back to read it. Thank you in advance.
And sorry for my bad English.

 

[Ranman256]

do NOT put dlookups in queries. too slow.
join the lookup table in yr query.

use Dlookup in form field if needed when i has nothing to do with the query.
=Dlookup([Field2Return], query, [where clause])

 

[MajP]

Can you show a demo table with data and expected out puts? This is not very clear what you want to do.

 

[nax009]

Can you show a demo table with data and expected out puts? This is not very clear what you want to do.

See the attachment of this reply. I want user to input a name of formula then lookup for it in the same query and store it in the same query as well. Is it possible to do that? Or any other idea if it possible.

 

[theDBguy]

Hi. Welcome to AWF!

How many FormulaID do you have?

 

[The_Doc_Man]

What you are asking to do is essentially EITHER a denormalized data layout or something that should be done via a combobox and SELECT CASE grouping. I won't show you how to do denormalization.

To do it the right way, you would use the combo box to select a number from 1 to however many formulas you have. Then you would use a SELECT CASE statement

SELECT CASE Me.FormulaCombobox
    CASE 1
        Result = ... formula 1
    CASE 2
        Result = ... formula 2
etc.

You would put this code in the combo box Change event or click event (since you click on a combo to select it). What you do with the result of the computation is up to you.

 

[nax009]

Hi. Welcome to AWF!

How many FormulaID do you have?

Let me give you a sample here.

 

[arnelgp]

is this close to what you need?
see Form1.

 

[nax009]

is this close to what you need?
see Form1.

YES, this is what I want. Care to give me some explain a bit? THANK YOU SO MUCH!

 

[arnelgp]

the combobox items came from the Column from your query (see Load event of form1).
when you select an item from the combo, the SQL of query QueryForm is changed to
add that column.
then the SourceObject of the subform is changed to Query.QueryForm.

 

[theDBguy]

Let me give you a sample here.

I haven't seen @arnelgp file yet, but since you're showing multiple input, how were you planning on receiving/entering those input in the query? Just curious...

 

[MajP]

Not sure if this may be of interest. But I think you are trying allow the user to see a column of their choosing. A data sheet allows you to show and hide column.

The above idea could be modified to initially hide the formula fields and then make the user select the one they want, Currently you have to hide the ones you do not want.

Whatever fields you click in the right listbox are hidden, but you could set it up the other way.
This allows you to bring in any query. Sort the fields. Choose which fields to Show/hide. Export the final to Excel.

If you drop this form into your database it should work as is. You need to import all the modules too.
1. Select your query from the picker
2. Sort the display if you want
3. Show hide which formulas to see.