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

[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

 

[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?

 

[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

 

[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

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Douglas J. Steele]

I told you: use the DateValue function.