Update field in subform from a query 1

[MontgomeryPete]

I need to update a table with a unique fare each time a reservation is taken. The user is working in a sub form (data source: Table Ride). The fare calculation is somewhat complicated, so an update with a combo box is not appropriate. Fares are $2, $4 and $6, depending on the qualifications of the passenger and the travel destination.
I calculate the fare with a SELECT statement as follows:

 SELECT Ride.RideNo,Passenger.ADAExpirDate , Ride.PickUpFareZone,Ride.DropOffFareZone, Locations_1.DispatchZone, Locations.DispatchZone,
IIf([ADAExpirDate]>Date(),IIf([Locations_1.DispatchZone]=1 And [Locations.DispatchZone]=1,2,4),6) AS RideFare;

I then want to update the field Ride.Fare, in the same sequence, as follows:

UPDATE Ride SET Fare = RideFare, WHERE Ride.RideNo = [Forms]![frmSubscriptionRideDetail]![RideNo]

Obviously, I’m really tangled up with the proper procedures here. Can any one point the way?

Thanks, Pete

 

[Remou]

How about DlookUp?

[tt]Fare=DlookUp("IIf([ADAExpirDate]>Date(),IIf([Locations_1.DispatchZone]=1 And [Locations.DispatchZone]=1,2,4),6)","Name Of Table","RideNo=" & [Forms]![frmSubscriptionRideDetail]![RideNo])[/tt]

Assuming that RideNo is numeric, if it is not, you will need single quotes.

 

[MontgomeryPete]

Thanks Remou

You finally got me to understand the DLookup function. I had been avoiding it because I used to get random results and didn't know why.

Pete