Dmax criteria problems with date changes 1

[Gavroche00]

HI, I am new at this, so I apologize if stated poorly.

I have a new price chage for my taxi route. So I will use the Dmax function. The two important fields are [dateinput] and [startdate]. They are both date fields. I am getting lost in nthe criteria section with the single double # etc...

Is there a simplersolution to the problem of price changes?
My price table is basic

Startdate
01/01/04 $25.00
02/01/04 $30.00
03/01/04 $35.00

All I want is the query to return the correct amount depending on where the input date falls. Is this very hard programming or obvious and I am missing it?

Thank you for the replies

David

 

[chris153]

Are the startdate and dateinput fields in different tables?

If so link the tables in a query and add the price to the query results.

 

[Gavroche00]

Thank you for the reply Chris. The tables are linked via another table. So lets' say I select route#5, with input date 03/04/04, when I put that in the query, all 3 prices appear. I don't undesrtand what you mean by add the prices to the query results.

David

 

[dhookom]

Since you seem to be looking for the Max Price where the StartDate is less than or equal to DateInput, try:
DMax("[Price]","[tblPrices]","[StartDate] <=#" & [dateinput] & "#")

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Gavroche00]

Duane, thank you for the reply. Unfortunately, I realized just now that I stated the problem incorrectly at first, and my apologies for that. I have more then one route number, i.e. NY-BOS, NY-LA, NY-CHI. Now for every route, I have different prices. So I thought of using the dmax function and combining first looking for the route AND adding the date after the function identifies the route. But that isn't working. (I can use dlookup for the route, and that works, but that doesn't help with the price.)I am at a total loss as I don't see how to combine the route and then the price for the route.

Thank you for your help

David

 

[dhookom]

Apparently your tblPrices has a route field that is text and your other table has a route field. If so, try
DMax("[Price]","[tblPrices]","[StartDate] <=#" & [dateinput] & "# AND [Route]=""" & [Route] & """")

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Gavroche00]

Dear Duane,

You are correct. One of the field is an autonumber and the other is a regular numeric field. The auto number is in the tbl routes, to which there is a lookup table called tblrouteprices. When I try what you wrote, I get a bunch of errors. More imprtantly, can you tell me where I can learn about " ' & and the likes? I seem to get lost everytime and I don't get the meaning of them.

Thank you, and no problem if you can't reply to this

David

 

[dhookom]

Please provide your table names and field names. We don't know if tblRoutePrices contains the date field also. It also helps if you provide some sample data from the tables. This cuts out a lot of back and forth.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Gavroche00]

Thank you Duane.

tblPickup:
[InputDate],[routedate],[routenumber],[truck#],[tonnage]
02/29/04 02/20/04 1 196 23.15
02/29/04 02/23/04 4 197 21.56

tblRoutes:
[routenunber],[routecode],[origin],[destination]
1 nycbos New York Boston
2 nyclax New York Los Angeles
4 miawas Miami Washington

tblRouteprices:
[routenumber],[startdate],[customerprice],[driverprice]
1 01/01/04 $35.00 $30.00
1 02/01/04 $36.00 $31.00
2 12/01/03 $84.00 $75.00

I hope this makes it clearer. The input date is the date that the data is entered in the system. The route date is the physical date of the trip, and that is the one that is evlauated against the startdate in the table routeprices.

Thank you

David

 

[dhookom]

I would use a sql statement with a subquery like:
Select tblPickup.*, tblRoutes.RouteCode,
(Select Max(CustomerPrice)
From tblRoutePrices rp
Where rp.RouteNumber = tblPickup.RouteNumber
And rp.StartDate <= tblPickup.RouteDate) as Price
FROM tblPickup INNER JOIN tblRoutes On tblPickup.RouteNumber = tblRoutes.RouteNumber;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Gavroche00]

Thank you

David