Multi-table Value Query Help!!

[natalie99]

Hi All :)

I am trying to stipulate rules for the return of a pricing value in Access.

I know this should be very easy, I simply cannot seem to make my expressions work!

The two tables have the following (ignoring all irrelevant fields)

TABLE 1

Minimum Bandwidth, eg. 0 , 40.01 , 80.01
Maximum Bandwidth, eg. 40 , 80 , 138
End Price, eg. 150.26 , 327.18 , 459.87

TABLE 2

Bandwidth 26.4 , 48 , 64.1 , etc


I want to place an expression in the query stating that the end price for a record is defined by the bandwidth, ie.

Minimum Bandwith<=Bandwidth and Maximum Bandwidth>Bandwidth

then display the End Price

How do I type this in the design view so that the value of field Bandwidth in TABLE 2 is used, and the expression does not interperet it as the word bandwidth, which is not an integer!

In the criteria for field Minimum Bandwidth:

<="Bandwidth"

Thanks for any help with my most likely simple problem :)

Natalie

[Stewart Ross]

...How do I type this in the design view so that the value of field Bandwidth in TABLE 2 is used, and the expression does not interperet it as the word bandwidth, which is not an integer!

In the criteria for field Minimum Bandwidth:

<="Bandwidth"
...

Hi Natalie. Assuming that you have put Table 2 into your query's design view you refer to the field within brackets:

 <=[Bandwidth]

-Stewart

[natalie99]

Assuming that you have put Table 2 into your query's design view you refer to the field within brackets:

Code: (text)
<=[Bandwidth]

Hi

Thanks for the reply!

I have tried this and it says

'Data Type Mismatch in Criteria Expression

However, both fields are set to Number in the Table 1 and Table 2 design views.

Also, the query design view automatically changes the criteria from:



<=(Bandwidth)

to

<=("Bandwidth")

Thanks!

[natalie99]

right, sqaure brackets, duh, SORRY! and thanks for the help!

[Stewart Ross]

right, sqaure brackets, duh, SORRY! and thanks for the help!

You're very welcome, Natalie. I'm sure you'll find it hard to forget which kind of bracket to use from now on too!!

-Stewart