Need help with dlookup

[Guest]

I have information in 2 tables and need to have a field in a form to look in
a table for a value and return an amount.

This is what's in Excel cell but don't have an idea how to do this in
Access. "=VLOOKUP(E6,$C$18:$E$608,3)"

Your help will be appreciate.

 

[Guest]

In the field control source write
=DLookup("[Amonth field name]","[Table Name]","[Field of criteria] = " &
[FieldName in the form])
========================================
If the field of criteria is text, then
=DLookup("[Amonth field name]","[Table Name]","[Field of criteria] = '" &
[FieldName in the form] & "'")
===========================================
If there is no criteria, there is only one record in the table
=DLookup("[Amonth field name]","[Table Name]")

 

[John Vinson]

I have information in 2 tables and need to have a field in a form to look in
a table for a value and return an amount.

This is what's in Excel cell but don't have an idea how to do this in
Access. "=VLOOKUP(E6,$C$18:$E$608,3)"

Your help will be appreciate.

Excel is a spreadsheet, an excellent one.
Access is a relational database.

THEY ARE DIFFERENT.

Applying spreadsheet thinking to your database is *certain* to get you
into trouble - bad designs, overly complex applications, all sorts of
trouble.

Normally you would NOT need to use DLookUp to display data from a
table field on a form. Instead, you would either base the form on a
Query including the table, or use a tool such as a Combo Box to
reference the table containing the data you want to see.

What's the context? How are the two tables related? What's the
Recordsource for the form? What will you be doing with the value once
you look it up? It may in fact be the case that DLookUp is appropriate
but I hesitate to suggest it, because it very well may not be!

John W. Vinson[MVP]

 

[Guest]

How will this work if the table is a between amounts? I mean the first colum
shows an amount and the second column shows another amount.

From Amount To Amount insurance 1 Insurance 2

12,000 12.049 36 60
12,050 12,100 40 62

What I need is to bring the amount insurance to a field in a form searching
between those amounts in the first 2 columns. So if the amount is 12,074 it
will show 40 in a field and 62 in another field.

Thanks for your help Ofer.

Jeannette

In the field control source write
=DLookup("[Amonth field name]","[Table Name]","[Field of criteria] = " &
[FieldName in the form])
========================================
If the field of criteria is text, then
=DLookup("[Amonth field name]","[Table Name]","[Field of criteria] = '" &
[FieldName in the form] & "'")
===========================================
If there is no criteria, there is only one record in the table
=DLookup("[Amonth field name]","[Table Name]")


--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I have information in 2 tables and need to have a field in a form to look in
a table for a value and return an amount.

This is what's in Excel cell but don't have an idea how to do this in
Access. "=VLOOKUP(E6,$C$18:$E$608,3)"

Your help will be appreciate.

 

[Guest]

Try this

=NZ(DLookUp("[Insurance 2]","[Table Name]","[From Amount] <= " & [Amount
Field name in the form] & " And [To Amount] >= " & [Amount Field name in the
form] ),0)

The Nz used to return 0 if no record is found

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

How will this work if the table is a between amounts? I mean the first colum
shows an amount and the second column shows another amount.

From Amount To Amount insurance 1 Insurance 2

12,000 12.049 36 60
12,050 12,100 40 62

What I need is to bring the amount insurance to a field in a form searching
between those amounts in the first 2 columns. So if the amount is 12,074 it
will show 40 in a field and 62 in another field.

Thanks for your help Ofer.

Jeannette

In the field control source write
=DLookup("[Amonth field name]","[Table Name]","[Field of criteria] = " &
[FieldName in the form])
========================================
If the field of criteria is text, then
=DLookup("[Amonth field name]","[Table Name]","[Field of criteria] = '" &
[FieldName in the form] & "'")
===========================================
If there is no criteria, there is only one record in the table
=DLookup("[Amonth field name]","[Table Name]")


--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I have information in 2 tables and need to have a field in a form to look in
a table for a value and return an amount.

This is what's in Excel cell but don't have an idea how to do this in
Access. "=VLOOKUP(E6,$C$18:$E$608,3)"

Your help will be appreciate.

 

[Guest]

I am definitively doing something wrong or maybe I din't explain myself
correctly.

I will try again, the form have a field named "Financed Amount", I must
type an amount in that field and then the =NZ(DLookUp... will be in the
fields named insurance 1 and insurance 2. So the form should bring the
amounts provide by the table after search in the range FromAmount-ToAmount.

When the user open the form it will be empty and after type the financed
amount the form should bring the insurance 1 and insurance 2 amounts.

Sorry for keep asking but I feel really lost with this form and the formulas.

Thanks Ofer

Try this

=NZ(DLookUp("[Insurance 2]","[Table Name]","[From Amount] <= " & [Amount
Field name in the form] & " And [To Amount] >= " & [Amount Field name in the
form] ),0)

The Nz used to return 0 if no record is found

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

How will this work if the table is a between amounts? I mean the first colum
shows an amount and the second column shows another amount.

From Amount To Amount insurance 1 Insurance 2

12,000 12.049 36 60
12,050 12,100 40 62

What I need is to bring the amount insurance to a field in a form searching
between those amounts in the first 2 columns. So if the amount is 12,074 it
will show 40 in a field and 62 in another field.

Thanks for your help Ofer.

Jeannette

In the field control source write
=DLookup("[Amonth field name]","[Table Name]","[Field of criteria] = " &
[FieldName in the form])
========================================
If the field of criteria is text, then
=DLookup("[Amonth field name]","[Table Name]","[Field of criteria] = '" &
[FieldName in the form] & "'")
===========================================
If there is no criteria, there is only one record in the table
=DLookup("[Amonth field name]","[Table Name]")


--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have information in 2 tables and need to have a field in a form to look in
a table for a value and return an amount.

This is what's in Excel cell but don't have an idea how to do this in
Access. "=VLOOKUP(E6,$C$18:$E$608,3)"

Your help will be appreciate.

 

[John Vinson]

How will this work if the table is a between amounts? I mean the first colum
shows an amount and the second column shows another amount.

From Amount To Amount insurance 1 Insurance 2

12,000 12.049 36 60
12,050 12,100 40 62

What I need is to bring the amount insurance to a field in a form searching
between those amounts in the first 2 columns. So if the amount is 12,074 it
will show 40 in a field and 62 in another field.

I strongly suspect there are better solutions, but to answer literally
- you need two textboxes each with its own DLookUp:

=DLookUp("[Insurance 1]", "[tablename]", "[Amount] >= " & [From
Amount] & " AND [Amount] <= " & [To Amount])

and the same with Insurance 2. Use your own fieldname for Amount, you
didn't say where that's from.


John W. Vinson[MVP]

 

[Guest]

Thanks for the info John.

How can I make this DLookUp or another formula to find a given number
between the 2 columns (From Amount and To Amount).

I already have the table with the information provide before but want to get
the info thru a form where I will type a certain amount and then will need to
receive the amounts for the two insurances according to the table. It will
look like a quote or invoice and will also have other totals, sum,
percentages, etc.

Thanks for your time. I will be glad to have your help on this

How will this work if the table is a between amounts? I mean the first colum
shows an amount and the second column shows another amount.

From Amount To Amount insurance 1 Insurance 2

12,000 12.049 36 60
12,050 12,100 40 62

What I need is to bring the amount insurance to a field in a form searching
between those amounts in the first 2 columns. So if the amount is 12,074 it
will show 40 in a field and 62 in another field.

I strongly suspect there are better solutions, but to answer literally
- you need two textboxes each with its own DLookUp:

=DLookUp("[Insurance 1]", "[tablename]", "[Amount] >= " & [From
Amount] & " AND [Amount] <= " & [To Amount])

and the same with Insurance 2. Use your own fieldname for Amount, you
didn't say where that's from.


John W. Vinson[MVP]

 

[John Vinson]

Thanks for the info John.

How can I make this DLookUp or another formula to find a given number
between the 2 columns (From Amount and To Amount).

Ummm...

I told you how. Did you try it?

=DLookUp("[Insurance 1]", "[tablename]", "[Amount] >= " & [From
Amount] & " AND [Amount] <= " & [To Amount])

Replace [Amount] with the name of the form control containing the
amount that you want to look up.


John W. Vinson[MVP]

 

[Guest]

Yes, I tried. I feel really stupid because it sound like really easy and I
can't get it working.

This is what I have in the control source for the insurance:

=DLookUp("[Comprehensive]","[Comprehensive and collision]","[FinancedAmount]

= " & [From Amount] & " And [To Amount] <= " & [To Amount])

=DLookUp("[Collision]","[Comprehensive and collision]","[FinancedAmount] >=
" & [From Amount] & " And [To Amount] <= " & [To Amount])

But no matter what amount I typed in the "Financed Amount" field on the
form, it always bring me back the amount on the first record of the table.

Can you still help me?

Thanks,

Jeannette

Thanks for the info John.

How can I make this DLookUp or another formula to find a given number
between the 2 columns (From Amount and To Amount).

Ummm...

I told you how. Did you try it?

=DLookUp("[Insurance 1]", "[tablename]", "[Amount] >= " & [From
Amount] & " AND [Amount] <= " & [To Amount])

Replace [Amount] with the name of the form control containing the
amount that you want to look up.


John W. Vinson[MVP]

 

[John Vinson]

Yes, I tried. I feel really stupid because it sound like really easy and I
can't get it working.

This is what I have in the control source for the insurance:

=DLookUp("[Comprehensive]","[Comprehensive and collision]","[FinancedAmount]

= " & [From Amount] & " And [To Amount] <= " & [To Amount])

=DLookUp("[Collision]","[Comprehensive and collision]","[FinancedAmount] >=
" & [From Amount] & " And [To Amount] <= " & [To Amount])

But no matter what amount I typed in the "Financed Amount" field on the
form, it always bring me back the amount on the first record of the table.

Can you still help me?

You'll hate this... it looks like a simple typo, using [To Amount] <=
[To Amount] as the last criterion, which will of course always be
TRUE.

Try

=DLookUp("[Collision]", "[Comprehensive and collision]",
"[FinancedAmount] >= " & [From Amount] &
" And [FinancedAmount] <= " & [To Amount])


Or... maybe I misread it... if the [Comprehensive and collision] table
CONTAINS the From Amount and To Amount fields, turn it around:

=DLookUp("[Collision]", "[Comprehensive and collision]",
"[From Amount] <= " & [FinancedAmount] &
" And [To Amount] >= " & [FinancedAmount])

Of course if multiple records meet that criterion you'll see all of
them.

John W. Vinson[MVP]

 

[Guest]

Thanks for your help. It works and it's great.

You're the master.

Jeannette

Yes, I tried. I feel really stupid because it sound like really easy and I
can't get it working.

This is what I have in the control source for the insurance:

=DLookUp("[Comprehensive]","[Comprehensive and collision]","[FinancedAmount]

= " & [From Amount] & " And [To Amount] <= " & [To Amount])

=DLookUp("[Collision]","[Comprehensive and collision]","[FinancedAmount] >=
" & [From Amount] & " And [To Amount] <= " & [To Amount])

But no matter what amount I typed in the "Financed Amount" field on the
form, it always bring me back the amount on the first record of the table.

Can you still help me?

You'll hate this... it looks like a simple typo, using [To Amount] <=
[To Amount] as the last criterion, which will of course always be
TRUE.

Try

=DLookUp("[Collision]", "[Comprehensive and collision]",
"[FinancedAmount] >= " & [From Amount] &
" And [FinancedAmount] <= " & [To Amount])


Or... maybe I misread it... if the [Comprehensive and collision] table
CONTAINS the From Amount and To Amount fields, turn it around:

=DLookUp("[Collision]", "[Comprehensive and collision]",
"[From Amount] <= " & [FinancedAmount] &
" And [To Amount] >= " & [FinancedAmount])

Of course if multiple records meet that criterion you'll see all of
them.

John W. Vinson[MVP]

 

[John Vinson]

You're the master.

Just the Wysard... and your humble servant. Glad to be of assistance!

John W. Vinson[MVP]