Dlookup a range with multiple criteria

I am trying to calculate markup based on a price range and code number, when
I use the range part of the code it runs fine but as soon as I add the
discount code field reference it returns the same number for all fields, the
code I have in my query is

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]" And [DISCOUNT CODE] & "[code]")

Any help would be much appreciated as I have been searching forums and
discussions groups for too long now.

Thanks very much

first,
And [DISCOUNT CODE] & "[code]"
makes no sense. There is no logical operator in this part of the code.

second, I am surprise it works at all. This part has incorrect syntax:
[Price] & " Between [start] And
[end]"

It should be
[Price] & " Between " & [start] & " And " & [end]

Also, where are code, start and end identified?

--
Dave Hargis, Microsoft Access MVP


"kazzaw" wrote:

I am trying to calculate markup based on a price range and code number, when
I use the range part of the code it runs fine but as soon as I add the
discount code field reference it returns the same number for all fields, the
code I have in my query is

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]" And [DISCOUNT CODE] & "[code]")

Any help would be much appreciated as I have been searching forums and
discussions groups for too long now.

Thanks very much

Thanks for the quick reply, sorry if I am confusing matters, to clarify

1. I have a table with markup start and end ranges, discount codes and
percentages
2. In a further table I have price and discount code

the object of the problem is i want the dlookup to find the price from table
2 in table 1 whilst matching the discount code from both tables.

The price field is a numeric field and the discount code a text field.

The first part of the code did pick up the percentage ratio ok, i found
similar code in this discussion group and amended it accordingly, or so I
thought:-

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]")

it was adding the discount code part of the criteria that didn't work, maybe
I have it all wrong it and it was pure chance I got a result.

I have also tried adding the = between the code and discount code and it
still doesn't work.

Help please




"kazzaw" wrote:

I am trying to calculate markup based on a price range and code number, when
I use the range part of the code it runs fine but as soon as I add the
discount code field reference it returns the same number for all fields, the
code I have in my query is

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]" And [DISCOUNT CODE] & "[code]")

Any help would be much appreciated as I have been searching forums and
discussions groups for too long now.

Thanks very much

Please look at the syntax here. The criteria has to be sent to the DLookup
as a string.
DLookUp("[markup]","[markup percentages]", "[Price] Between " & [start] & "
And " & [end] & " And " [DiscountCode] = """ & """[code]""")

But even then, it is not complete, because you did not say where start, end,
and Code are defined. They should be controls on your form or a variable you
have saved somewhere.
--
Dave Hargis, Microsoft Access MVP


"kazzaw" wrote:

Thanks for the quick reply, sorry if I am confusing matters, to clarify

1. I have a table with markup start and end ranges, discount codes and
percentages
2. In a further table I have price and discount code

the object of the problem is i want the dlookup to find the price from table
2 in table 1 whilst matching the discount code from both tables.

The price field is a numeric field and the discount code a text field.

The first part of the code did pick up the percentage ratio ok, i found
similar code in this discussion group and amended it accordingly, or so I
thought:-

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]")

it was adding the discount code part of the criteria that didn't work, maybe
I have it all wrong it and it was pure chance I got a result.

I have also tried adding the = between the code and discount code and it
still doesn't work.

Help please




"kazzaw" wrote:

I am trying to calculate markup based on a price range and code number, when
I use the range part of the code it runs fine but as soon as I add the
discount code field reference it returns the same number for all fields, the
code I have in my query is

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]" And [DISCOUNT CODE] & "[code]")

Any help would be much appreciated as I have been searching forums and
discussions groups for too long now.

Thanks very much

I have just got back to working on this dlookup and you are quite correct in
saying that the syntax is incomplete, I will try to explain exactly what I
want in the hope that you can help me further.

I have one table (updated today) that contains a list of prices with codes,
the fields that I want to lookup elsewhere are price and discount code.

The second table called markup percentages, the one I want to lookup from
has a list of markup codes also called discount code (similar to the one in
table1) and then price range fields start and end (so price range may start
at 20.00 and end at 200.00 but the actual price I want to lookup may 50) the
field I want to return from this table is called markup.

In short I want to find the markup value from markup percentages table based
on the price (which will be within the range start and end) and discount code
in my updated today table.

This syntax DLookUp("[Markup]","Markup Percentages","[Discount Code] = '" &
[Discount Code] & "'") finds a markup based only on the code but when I try
to add the code for looking up the price range it does not recognise my
referencing to start and end, the code I used was: Expr2:
DLookUp("[Markup]"," Markup Percentages",[Price] Between [Markup
Percentages]![start] And [Markup Percentages]![end])

Any help would be greatly appreciated, thanks.



"Klatuu" wrote:

Please look at the syntax here. The criteria has to be sent to the DLookup
as a string.
DLookUp("[markup]","[markup percentages]", "[Price] Between " & [start] & "
And " & [end] & " And " [DiscountCode] = """ & """[code]""")

But even then, it is not complete, because you did not say where start, end,
and Code are defined. They should be controls on your form or a variable you
have saved somewhere.
--
Dave Hargis, Microsoft Access MVP


"kazzaw" wrote:

Thanks for the quick reply, sorry if I am confusing matters, to clarify

1. I have a table with markup start and end ranges, discount codes and
percentages
2. In a further table I have price and discount code

the object of the problem is i want the dlookup to find the price from table
2 in table 1 whilst matching the discount code from both tables.

The price field is a numeric field and the discount code a text field.

The first part of the code did pick up the percentage ratio ok, i found
similar code in this discussion group and amended it accordingly, or so I
thought:-

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]")

it was adding the discount code part of the criteria that didn't work, maybe
I have it all wrong it and it was pure chance I got a result.

I have also tried adding the = between the code and discount code and it
still doesn't work.

Help please




"kazzaw" wrote:

I am trying to calculate markup based on a price range and code number, when
I use the range part of the code it runs fine but as soon as I add the
discount code field reference it returns the same number for all fields, the
code I have in my query is

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]" And [DISCOUNT CODE] & "[code]")

Any help would be much appreciated as I have been searching forums and
discussions groups for too long now.

Thanks very much

i think i got the price and start and end fields the wrong way around, but
this change still returns error as the result:

Expr2: DLookUp("[Markup]"," Markup Percentages"," Between [start] and [end]
= " & [price])


"Klatuu" wrote:

Please look at the syntax here. The criteria has to be sent to the DLookup
as a string.
DLookUp("[markup]","[markup percentages]", "[Price] Between " & [start] & "
And " & [end] & " And " [DiscountCode] = """ & """[code]""")

But even then, it is not complete, because you did not say where start, end,
and Code are defined. They should be controls on your form or a variable you
have saved somewhere.
--
Dave Hargis, Microsoft Access MVP


"kazzaw" wrote:

Thanks for the quick reply, sorry if I am confusing matters, to clarify

1. I have a table with markup start and end ranges, discount codes and
percentages
2. In a further table I have price and discount code

the object of the problem is i want the dlookup to find the price from table
2 in table 1 whilst matching the discount code from both tables.

The price field is a numeric field and the discount code a text field.

The first part of the code did pick up the percentage ratio ok, i found
similar code in this discussion group and amended it accordingly, or so I
thought:-

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]")

it was adding the discount code part of the criteria that didn't work, maybe
I have it all wrong it and it was pure chance I got a result.

I have also tried adding the = between the code and discount code and it
still doesn't work.

Help please




"kazzaw" wrote:

I am trying to calculate markup based on a price range and code number, when
I use the range part of the code it runs fine but as soon as I add the
discount code field reference it returns the same number for all fields, the
code I have in my query is

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]" And [DISCOUNT CODE] & "[code]")

Any help would be much appreciated as I have been searching forums and
discussions groups for too long now.

Thanks very much

If you are going to use DLookup, you might try

DLookUp("[Markup]","[Markup Percentages]",[Price] & " Between [start]
and [end]")

and if you also need to get the markup based on both price and discount
code try:
DLookUp("[Markup]","[Markup Percentages]",[Price] & " Between [start]
and [end] And [Discount Code] = '" & [Discount Code] & "'")

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


kazzaw wrote:
i think i got the price and start and end fields the wrong way around, but
this change still returns error as the result:

Expr2: DLookUp("[Markup]"," Markup Percentages"," Between [start] and [end]
= " & [price])


"Klatuu" wrote:

Please look at the syntax here. The criteria has to be sent to the DLookup
as a string.
DLookUp("[markup]","[markup percentages]", "[Price] Between " & [start] & "
And " & [end] & " And " [DiscountCode] = """ & """[code]""")

But even then, it is not complete, because you did not say where start, end,
and Code are defined. They should be controls on your form or a variable you
have saved somewhere.
--
Dave Hargis, Microsoft Access MVP


"kazzaw" wrote:

Thanks for the quick reply, sorry if I am confusing matters, to clarify

1. I have a table with markup start and end ranges, discount codes and
percentages
2. In a further table I have price and discount code

the object of the problem is i want the dlookup to find the price from table
2 in table 1 whilst matching the discount code from both tables.

The price field is a numeric field and the discount code a text field.

The first part of the code did pick up the percentage ratio ok, i found
similar code in this discussion group and amended it accordingly, or so I
thought:-

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]")

it was adding the discount code part of the criteria that didn't work, maybe
I have it all wrong it and it was pure chance I got a result.

I have also tried adding the = between the code and discount code and it
still doesn't work.

Help please




"kazzaw" wrote:

I am trying to calculate markup based on a price range and code number, when
I use the range part of the code it runs fine but as soon as I add the
discount code field reference it returns the same number for all fields, the
code I have in my query is

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]" And [DISCOUNT CODE] & "[code]")

Any help would be much appreciated as I have been searching forums and
discussions groups for too long now.

Thanks very much

John you are a star, this has worked perfectly, thank you so much.

Karen

"John Spencer" wrote:

If you are going to use DLookup, you might try

DLookUp("[Markup]","[Markup Percentages]",[Price] & " Between [start]
and [end]")

and if you also need to get the markup based on both price and discount
code try:
DLookUp("[Markup]","[Markup Percentages]",[Price] & " Between [start]
and [end] And [Discount Code] = '" & [Discount Code] & "'")

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


kazzaw wrote:
i think i got the price and start and end fields the wrong way around, but
this change still returns error as the result:

Expr2: DLookUp("[Markup]"," Markup Percentages"," Between [start] and [end]
= " & [price])


"Klatuu" wrote:

Please look at the syntax here. The criteria has to be sent to the DLookup
as a string.
DLookUp("[markup]","[markup percentages]", "[Price] Between " & [start] & "
And " & [end] & " And " [DiscountCode] = """ & """[code]""")

But even then, it is not complete, because you did not say where start, end,
and Code are defined. They should be controls on your form or a variable you
have saved somewhere.
--
Dave Hargis, Microsoft Access MVP


"kazzaw" wrote:

Thanks for the quick reply, sorry if I am confusing matters, to clarify

1. I have a table with markup start and end ranges, discount codes and
percentages
2. In a further table I have price and discount code

the object of the problem is i want the dlookup to find the price from table
2 in table 1 whilst matching the discount code from both tables.

The price field is a numeric field and the discount code a text field.

The first part of the code did pick up the percentage ratio ok, i found
similar code in this discussion group and amended it accordingly, or so I
thought:-

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]")

it was adding the discount code part of the criteria that didn't work, maybe
I have it all wrong it and it was pure chance I got a result.

I have also tried adding the = between the code and discount code and it
still doesn't work.

Help please




"kazzaw" wrote:

I am trying to calculate markup based on a price range and code number, when
I use the range part of the code it runs fine but as soon as I add the
discount code field reference it returns the same number for all fields, the
code I have in my query is

DLookUp("[markup]","[markup percentages]",[Price] & " Between [start] And
[end]" And [DISCOUNT CODE] & "[code]")

Any help would be much appreciated as I have been searching forums and
discussions groups for too long now.

Thanks very much