Extracting only the Date part of a Date/Time field

[Amy E. Baggott]

I am trying to convert data imported from a different database into Access.
The other database stores an exhibitor's Priority Points and their Seniority
earned for a given year as two separate records. I am trying to create a
table with one record for each exhibitor that contains both the Priority
Points and the Seniority as two fields in the same record. The only way to
match them up is by exhibitor ID and Date Earned. The problem is that the
Date Earned from the other database is stored as a date and time (e.g.,
2/24/2004 9:04:08 PM), which was set at the time each record was created.
That means I have a lot of records where I'm not finding matches because the
time was a couple of seconds different between the Priority Points record
being created and the Seniority record being created. How do I get Access to
match them up by just looking at the month, day, and year and ignore the time
part of the field?

 

[Ken Snell \(MVP\)]

You can use a non-equijoin in the query:

SELECT TableName1.*, TableName2.*
FROM TableName1 INNER JOIN TableName2
ON TableName1.DateOnlyField =
DateValue(TableName2.DateTimeField);

 

[Amy E. Baggott]

Only problem with that is that I have data in Table 1 that has a matching
date with data in Table 2, but different times. So there is Data in Table 1
that is not directly matched with Table 2 and vice versa. Even if Access
allowed for a full outer join, which it doesn't (I wonder why? It would be
mind-bogglingly useful for finding discrepancies, but that's a whole nother
topic), I would then end up with two separate records for each exhibitor for
that day, one with the Priority Points and no Seniority, and the other with
Seniority and no Priority Points. That's what I'm trying to avoid because
there are nearly three thousand records in each table that are
"not-quite-but-should-be" matches.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

You can use a non-equijoin in the query:

SELECT TableName1.*, TableName2.*
FROM TableName1 INNER JOIN TableName2
ON TableName1.DateOnlyField =
DateValue(TableName2.DateTimeField);

 

[John Spencer (MVP)]

You can get just the date by using the DateValue function.

DateValue(SomeFieldwithDateAndTime) will become just the date. The problem is
that if you have something that cannot be evaluated as a dateTime the function
will error.

So assuming that all the fields have dates and times you can join on the
DateValue(Tablea.FieldA) = DateValue(TableB.FieldB)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Sheridan]

Do I understand that in the external table there will be two rows per
exhibitor/date, one with a Null Priority points column and one with a Null
Seniority column and slightly different times in each? If so you can return
each pair of rows per exhibitor/date form the external table by joining two
instances of the table on the Exhibitor ID column and the date value of the
Date Earned column:

SELECT T1.[Exhibitor ID], DATEVALUE(T1.[Date Earned]) AS Date_Earned,
T1.[Priority Points], T2.[Seniority]
FROM [The Table] AS T1 INNER JOIN [The Table] AS T2
ON T1.[Exhibitor ID] = T2.[Exhibitor ID]
AND DATEVALUE(T1.[Date Earned]) =DATEVALUE(T2.[Date Earned])
WHERE T1.[Priority Points] IS NOT NULL AND T2.[Seniority] IS NOT NULL;


BTW if you do ever want to simulate a FULL OUTER JOIN you can do so by
undertaking a UNION operation on a LEFT OUTER JOIN and a RIGHT OUTER JOIN.
The UNION operation suppresses duplicated rows in the result set, so rows
returned by both joins will appear only once, along with those rows returned
only by one of the joins:

SELECT a,b,c, NULL,NULL
FROM x LEFT JOIN y
ON x.c = y.c
UNION
SELECT NULL,NULL,c,d,e
FROM x RIGHT JOIN y
ON y.c = x.c;

Alternatively you can undertake two UNION ALL operations, using one INNER
JOIN and two subqueries to restrict the results. While more complex than the
above it might well be quicker:

SELECT a,b.c,d,e
FROM x INNER JOIN y
ON x.c = y.c
UNION ALL
SELECT a,b.c,NULL,NULL
FROM x
WHERE NOT EXISTS
(SELECT *
FROM y
WHERE y.c = x.c)
UNION ALL
SELECT NULL,NULL,c,d,e
FROM y
WHERE NOT EXISTS
(SELECT *
FROM x
WHERE x.c = y.c);

Ken Sheridan
Stafford, England

Only problem with that is that I have data in Table 1 that has a matching
date with data in Table 2, but different times. So there is Data in Table 1
that is not directly matched with Table 2 and vice versa. Even if Access
allowed for a full outer join, which it doesn't (I wonder why? It would be
mind-bogglingly useful for finding discrepancies, but that's a whole nother
topic), I would then end up with two separate records for each exhibitor for
that day, one with the Priority Points and no Seniority, and the other with
Seniority and no Priority Points. That's what I'm trying to avoid because
there are nearly three thousand records in each table that are
"not-quite-but-should-be" matches.

 

[Ken Sheridan]

Correction: you don't need to return the Nulls when using a UNION operation
of course. It should have been:

SELECT a,b,c,d,e
FROM x LEFT JOIN y
ON x.c = y.c
UNION
SELECT a,b,c,d,e
FROM x RIGHT JOIN y
ON y.c = x.c;

The Nulls are only required with the second method, when using UNION ALL
operations.

Ken Sheridan
Stafford, England