date comparison

  • Thread starter Thread starter Miranda
  • Start date Start date
M

Miranda

hi guys,

i was wondering if there is a SQL operator to do something like this:

i've got a table of {equipment, cost, dateEffective}
so maybe: truck $400 12/03/2004
Truck $600 28/06/2004

I need to figure out how much to charge for the equipment. I have a date
that the piece of equipment was used and need to decided which price to
charge....is there anyway to find the nearest date? i've been trying to use
DATEDIFF....but i run into a problem when there are more than 2 entries for
that kind of equip...

any suggestions???

Thanks A LOT!!
miranda
 
Several possibilities:

1. Subquery
If you need the current price for every row of a report, use a subquery by
typing something like this into the Field row of the report's query. (Note
that the results are read-only, so not suitable for a form where you need to
update values.)

Cost: (SELECT TOP 1 Cost FROM Equipment WHERE ((Cost.Equipment =
MainTable.Equipment) AND (Cost.dateEffective >= MainTable.SaleDate)) ORDER
BY dateEffective, ID )

2. DLookup.
If you just need to look this up in one record, or to get updatable results:

=DLookup("Cost", "MyTable", "(Equipment = """ & [Equipment] & """) AND
(dateEffective >= " & Format([SaleDate], "\#mm\/dd\/yyyy\#") & ")")

DLookup() is a little dicey, as you do not get to specify the order of
records. There is an extended version of DLookup() which is also faster in
this link:
http://allenbrowne.com/ser-42.html
 
Thanks a lot allen, i wasn't even thinking about it in that way...much
appreciated!

Allen Browne said:
Several possibilities:

1. Subquery
If you need the current price for every row of a report, use a subquery by
typing something like this into the Field row of the report's query. (Note
that the results are read-only, so not suitable for a form where you need to
update values.)

Cost: (SELECT TOP 1 Cost FROM Equipment WHERE ((Cost.Equipment =
MainTable.Equipment) AND (Cost.dateEffective >= MainTable.SaleDate)) ORDER
BY dateEffective, ID )

2. DLookup.
If you just need to look this up in one record, or to get updatable results:

=DLookup("Cost", "MyTable", "(Equipment = """ & [Equipment] & """) AND
(dateEffective >= " & Format([SaleDate], "\#mm\/dd\/yyyy\#") & ")")

DLookup() is a little dicey, as you do not get to specify the order of
records. There is an extended version of DLookup() which is also faster in
this link:
http://allenbrowne.com/ser-42.html

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

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

Miranda said:
i was wondering if there is a SQL operator to do something like this:

i've got a table of {equipment, cost, dateEffective}
so maybe: truck $400 12/03/2004
Truck $600 28/06/2004

I need to figure out how much to charge for the equipment. I have a date
that the piece of equipment was used and need to decided which price to
charge....is there anyway to find the nearest date? i've been trying to use
DATEDIFF....but i run into a problem when there are more than 2 entries for
that kind of equip...
Click to expand...
Click to expand...
 
Back
Top