Comparing against a table between values.

[Guest]

Say if have a table with Range and Reference Value like this
<Range> <Value>
60000 1.32
32000 6.52
12000 9.28
etc

How can I compare a user input value from a form against the range and
retrieve the value? eg. user inputs 14000 and it picks the next highest 32000
range and retrieve 6.52.

Thanks in advance.

 

[Allen Browne]

See Tom Ellison's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html
for suggestions on how to design and look up ranges.

 

[Guest]

Thanks! But I got another problem now...

I keep getting an "Error 2001, You cancelled the previous operation" when I
try to run this code to get the value.

temp = DLookup("Value", "BMC_Query", "[Range] <= " & Me.txtMaxCapacity & "
AND [Rangemax] > " & Me.txtMaxCapacity)

What did I do wrong here?

 

[Allen Browne]

The error indicates that one of the arguments for DLookup() is not formed
correctly.

Examples:
- txtMacCapacity is null.
- Range is not a Number type field (so extra delimiters are needed.)
- BMC_Query is does not contain a Value field or a Range field, or a
Rangemax field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks! But I got another problem now...

I keep getting an "Error 2001, You cancelled the previous operation" when
I
try to run this code to get the value.

temp = DLookup("Value", "BMC_Query", "[Range] <= " & Me.txtMaxCapacity & "
AND [Rangemax] > " & Me.txtMaxCapacity)

What did I do wrong here?

See Tom Ellison's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html
for suggestions on how to design and look up ranges.

 

[Guest]

Really weird, I checked though all those errors you said but the same error
still keeps prompting.

The error indicates that one of the arguments for DLookup() is not formed
correctly.

Examples:
- txtMacCapacity is null.
- Range is not a Number type field (so extra delimiters are needed.)
- BMC_Query is does not contain a Value field or a Range field, or a
Rangemax field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks! But I got another problem now...

I keep getting an "Error 2001, You cancelled the previous operation" when
I
try to run this code to get the value.

temp = DLookup("Value", "BMC_Query", "[Range] <= " & Me.txtMaxCapacity & "
AND [Rangemax] > " & Me.txtMaxCapacity)

What did I do wrong here?

See Tom Ellison's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html
for suggestions on how to design and look up ranges.

Say if have a table with Range and Reference Value like this
<Range> <Value>
60000 1.32
32000 6.52
12000 9.28
etc

How can I compare a user input value from a form against the range and
retrieve the value? eg. user inputs 14000 and it picks the next highest
32000
range and retrieve 6.52.

 

[Guest]

Nvm, I found the error. It was a spelling mistake which took me half a day to
realise... Thanks!

The error indicates that one of the arguments for DLookup() is not formed
correctly.

Examples:
- txtMacCapacity is null.
- Range is not a Number type field (so extra delimiters are needed.)
- BMC_Query is does not contain a Value field or a Range field, or a
Rangemax field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks! But I got another problem now...

I keep getting an "Error 2001, You cancelled the previous operation" when
I
try to run this code to get the value.

temp = DLookup("Value", "BMC_Query", "[Range] <= " & Me.txtMaxCapacity & "
AND [Rangemax] > " & Me.txtMaxCapacity)

What did I do wrong here?

See Tom Ellison's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html
for suggestions on how to design and look up ranges.

Say if have a table with Range and Reference Value like this
<Range> <Value>
60000 1.32
32000 6.52
12000 9.28
etc

How can I compare a user input value from a form against the range and
retrieve the value? eg. user inputs 14000 and it picks the next highest
32000
range and retrieve 6.52.