How to join date/time field when "days" are same but "time" differ

11-08-07, 10:17 PM

Mikael Lindqvist

Good evening everyone,

I have one TRANSACTION table that contains:
1."date/time" field (YYMMDD hh:mm:ss)
2."quantity field"

And another PRICE table that contans:
1."date/time" field (YYMMDD hh:mm:ss)
2."price field"

Now, I want to join the "date/time" fields so I can perform a simple
"quantity * price" calculation on each transaction-row.

But the time differes (hh:mm:ss) and I just can't find a way to make Access
accept a relation between the days (it outputs nothing). How can I make it
ignore difference in the "time-section" and only look at the YYMMDD section?

Kindly,
Mikael
Sweden

11-08-07, 10:37 PM

Douglas J. Steele

Use the DateValue function.

I have to question why your Price table has a single Date/Time value in it,
though. Shouldn't you perhaps have EffectiveFrom and EffectiveTo fields, and
you'd then match the transaction's date using TransactionTime BETWEEN
Price.EffectiveFrom AND Price.EffectiveTo?

11-09-07, 06:49 AM

Mikael Lindqvist

Well, that is certainly one way to do it. But since my price will vary almost
with every day of the month I believe relation link between "transaction
table" and "price table" is the most convinient approach (I don't want to
"hard-code" between intervals).

So my question again, is it possible to match DATE/TIME even if the "time
part" varies (hours, minutes, seconds). I want the relation to ONLY look at
the YYMMDD part!

Kindly,
Mikael

"Mikael Lindqvist" wrote:
[..]

11-09-07, 12:36 PM

John Spencer

Use the DateValue function. It strips off the time. It does require that
you give it a non-null valid date or date string.

SELECT Transaction.Date
, Transaction.Quantity
, Price.Price
, Transaction.Quantity * Price.Price as ExtendedPrice
FROM Transaction INNER JOIN Price
ON DateValue(Transaction.Date) = DateValue(Price.Date)

Another way to do this would be to build 3 queries
SELECT DateValue([Date]) as DateOnly, Quantity
FROM Transaction

SELECT DateValue([Date]) as DatePrice, Price.Price
FROM Price

Now join those two queries on DateOnly and DatePrice

11-09-07, 10:51 PM

Douglas J. Steele

I told you: use the DateValue function.

Similar Threads
How to insert the "modified time" attribute in "date taken" attrib in batch mode Hello, I am using windows vista rc1. I tried many softwares ,to insert the date modified attrbute into the "date taken" attribute of an image file..but was not able to...
How can we have a "last 7 days" member in a time dimension or other means to achieve dynamic time based on the current date (now()) ? Hello, We use AS 2005. We have a standard time dimension based on a pre-populated time table. Some of our reports can be emailed to recipient every morning (time-based...
how to correct http 502 Proxy Error "no buffer space is supported", "64-Host not availabe", "connection time out" Now, I'm running ISA 2000 sp1 feature pack 1 on Windows 2003 Enterprise service pack 1. My kind of internet is ADSL , My server has two NICs : one internal NIC, and one...
how to correct http 502 Proxy Error "no buffer space is supported", "64-Host not availabe", "connection time out" Now, I'm running ISA 2000 sp1 feature pack 1 on Windows 2003 Enterprise service pack 1. My kind of internet is ADSL , My server has two NICs : one internal NIC, and one...
remove "automatically update" feature on "insert date & time" In Word, our office has many files created from a template where the "insert date & time" feature was set to automatically update. Now, whenever you open one of these files,...