DATE FUNCTION

[Ian]

I have two tables:
tblShipments
- Our Reference
- ShipmentDate (dd/mm/yy)
- LoadingPort
- etc

tblCosts
- Date(mm/yy)
- RoadTransport
- Documentation
- SeaFreight
- etc

I would like to create a COST SHEET REPORT for each
shipment. The query should look at the ShipmentDate and
match the CostsDate for that specific month. Please bear
in mind that the date format for the ShipmentDate and
CostDate is different.

Can anybody assist?

Thanks
Ian

 

[JL]

I guess the Shipment date is defined as Date type and the
Cost Date is defined as Text type.

You could try create a query and format the date
(Shipmentdate) to a text string of MM/YY.
select Our Reference, format(ShipmentDate, "MM/YY"), ....
from TblShipmens;

Once you have done that. You can create another query to
using the revised the Shipmentdate query join back to the
Costdate table.

Then you should be able to get what you needed.

 

[Tom Ellison]

Dear Ian:

Your date values are not stored as dd/mm/yy or mm/yy. This refers to a
display format, not a data type. And, in trying to write the query you
need, the display format is meaningless, but the data type is essential.

I'm going to assume the ShipmentDate is a date/time value and that the
tblCosts.Date is an long integer (without the slash, of course) where 2003 -
July would be 200307. There are many other ways you could be doing this, to
be sure. Perhaps you can revise my suggested query to accomodate how you
are actually storing this.

On that basis, here's a way to do the job:

SELECT *
FROM tblShipments S, tblCosts C
WHERE C.Date Mod 100 = MONTH(S.ShipmentDate)
AND C.Date \ 100 = YEAR(S.ShipmentDate)

From this point you should be able to obtain any of the columns from either
table and perform the desired calculation.

From a business standpoint, I'm surprised that this give the appearance that
a customer's price may depend on when you ship the order. Is it the case
that if the order is delayed in shipment, the customer may have to pay more?
Somehow that doesn't make sense. In a similar vein, we must look up pricing
based on the date of the order. That way the price is what was quoted at
that time.