Get field values from a query and assign to array variables

[larpup]

I've created a query and need to assign each field to a variable so I
can run code from a function to return a value on a form. I've
accomplished this with DLookups, but want to do this with a query. The
query works, just don't know how to reference each field to do the math

inWeight is an actual weight, say 200 lbs.
varRate(1) would be $10.00
varRate(0) would be $50.00
So 200 * $10.00 <= $50.00. (See code below)

If intWeight * varRate(1) <= varRate(0) Then
FunctionName= varRate(0)
Forms!Shipments!WeightRate = Round(FunctionName,2)

I need to return 50. In order to do so, I need to get the value from
the fields in the returned query. How can this be accomplished? A
little lost here...

Lar

 

[TC]

What query? You've told us nothing about it. You need to tell us its
name, its fields, and how those fields relate to your variables.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[larpup]

Sorry bout that.. Hope I've clarified it below.

Here is the query.
SELECT AirRates.TariffName, AirRates.Origin, AirRates.AciArea,
AirRates.MIN, AirRates.HUN, AirRates.THOU, AirRates.TWO,
AirRates.THREE, AirRates.FIVE, AirRates.TEN, AirRates.FIFTEEN,
AirRates.TWENTY INTO CanBeErasedNow
FROM AirRates
WHERE (((AirRates.TariffName)=7014) AND ((AirRates.Origin)="LAX") AND
((AirRates.AciArea)="BWI"));

TariffName, Origin and AciArea are retrieved from the form so the query
has been created quite easily. The code I would like to run
referencing the fields in the query query is as follows. I have
notated the variables as well. There is more code, but this is the crux
of it.

intWeight is the Weight on the Form
Var(0) = [MIN] (Minimum)
Var(1) =[HUN] (Hundred lb. rate)
Var(2) =[THOU] (Thousand lb. rate)

The code determines the correct field to use and returns the value of
the field * Weight to a textbox on the form. (Unless it is a minimum,
then the [MIN] is returned.

If intWeight * varRate(1) <= varRate(0) Then
FunctionName= varRate(0)
Forms!Shipments!WeightRate = Round(FunctionName, 2)

'Return rated as 1000#
ElseIf (intWeight >= varRate(2) * 1000 / varRate(1)) And (intWeight <
1000) Then
FunctionName= varRate(2) * 1000
Forms!Shipments!WeightRate = Round(FunctionName, 2)

'Return rated utilizing the 100 lb rate
ElseIf (intWeight * varRate(1) >= varRate(0) Or intWeight * varRate(1)
< 1000 * varRate(2)) _
And (intWeight < 1000) Then
FunctionName= intWeight * varRate(1)
Forms!Shipments!WeightRate = Round(FunctionName, 2)

Hope this helps and sorry again for not clarifying.

Lar

 

[TC]

Here is how to run that query & get the results through code:

dim db as database, rs as recordset
set db = currentdb()
set rs = db.openrecordset ("Name Of Query Here")
with rs
if .bof and .eof then
msgbox "no records!"
else
while not .eof
msgbox "TariffName = " & ![TariffName]
' (similar for other fields)
.movenext
wend
endif
end with
set db = nothing

Does that help?

TC (MVP Access)
http://tc2.atspace.com

 

[larpup]

TC,

Yup, just had to iterate thru the fields, had a brain blank.... Works
perfectly. Thanks so much.

Lar