Ignoring Time in a Date/Time Field

[Guest]

I am very new to MS-Access and have what may be a simple question..

I have two tables both contain records with account numbers and service dates. Records may have duplicate account numbers but when that happens the service date field in each record will differ. Both tables should have records with the same account numbers and service dates as each other. For example, if Table A contains account number 100005678 with service date 10/10/2002 then Table B will have one record with the same account number and service date

My problem is that the service date fields include both a date and a time but the time part does not alway match between the two tables. The time is not important and I need to be able to either ignore the time or strip it from both tables altogether if that is possible. The account number and service date combination are the only data elements in the records that can be used together in order to find an exact match between the records in each table. I haven't been able to make this work though because the time difference causes my queries to not find the matching records when the time differs. The query sees those fields as not being in agreement with one another

Is there an easy way for me to compare only on the date without worrying about the time difference

Thanks
David

 

[Nikos Yannacopoulos]

David,

Before you try this, make a backup of your dattabase just in case (better
safe that sorry!).

Some background: Access date/time fields are actually numeric fields (I
believe type is Double), where the integer part represents 24-hour days
since 01/01/1900 (so today is 38079), and the decimal part represents the
time as a fraction of the 24 hours (so today, 04/02/2004, 10:00 is
38079.4166667). The fact that date/time fields are displayed in a date/time
format by default, does not change the fact that underneath they are just
numbers.

To your problem: I suspect you are using the built-in Now() function at data
entry, hence the time part in your fields, while you don't actually want it.
So, your problem is two-fold, (a) clear out the times in your existing data,
and (b) exclude the time part as new data is created.
(a) Make a new query in design view; select the first table, and get the
date field to the grid. Change the query type to Update (Query > Update
Query); notice a new line in the grid headed "Update to"; in that line below
the date field, type in the expression "Int([DateFieldName])" (without the
quotes, and changing DateFieldName to the actual field name). Run (Query >
Run). This action will strip off the decimal part (time), leaving just
"clean" dates in the table. Repeat for the second table.
(b) Wherever you are using the Now() function at data entry (forms?),
replace it with the Date() function, which returns the current date, without
a time part. If you are importing data from some external source (Excel
sheet? Text file?) and the time part comes from there, use a query to read
the external data and apply an Int() function like before on the date field,
and append to your tables from that query, as opposed to appending directly
from the external file.

HTH,
Nikos

I am very new to MS-Access and have what may be a simple question...

I have two tables both contain records with account numbers and service

dates. Records may have duplicate account numbers but when that happens the
service date field in each record will differ. Both tables should have
records with the same account numbers and service dates as each other. For
example, if Table A contains account number 100005678 with service date
10/10/2002 then Table B will have one record with the same account number
and service date.

My problem is that the service date fields include both a date and a time

but the time part does not alway match between the two tables. The time is
not important and I need to be able to either ignore the time or strip it
from both tables altogether if that is possible. The account number and
service date combination are the only data elements in the records that can
be used together in order to find an exact match between the records in each
table. I haven't been able to make this work though because the time
difference causes my queries to not find the matching records when the time
differs. The query sees those fields as not being in agreement with one
another.

 

[Douglas J. Steele]

Nikos's advice is bang-on, but there are a couple of minor errors in it.

Rather than using Int to convert your date/time values to date only, I'd
recommend using the built-in DateValue function. That's because if you have
dates prior to 30 Dec, 1899 (not 01 Jan 1900, as Nikos stated), I believe
Int will return the wrong value.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

David,

Before you try this, make a backup of your dattabase just in case (better
safe that sorry!).

Some background: Access date/time fields are actually numeric fields (I
believe type is Double), where the integer part represents 24-hour days
since 01/01/1900 (so today is 38079), and the decimal part represents the
time as a fraction of the 24 hours (so today, 04/02/2004, 10:00 is
38079.4166667). The fact that date/time fields are displayed in a date/time
format by default, does not change the fact that underneath they are just
numbers.

To your problem: I suspect you are using the built-in Now() function at data
entry, hence the time part in your fields, while you don't actually want it.
So, your problem is two-fold, (a) clear out the times in your existing data,
and (b) exclude the time part as new data is created.
(a) Make a new query in design view; select the first table, and get the
date field to the grid. Change the query type to Update (Query > Update
Query); notice a new line in the grid headed "Update to"; in that line below
the date field, type in the expression "Int([DateFieldName])" (without the
quotes, and changing DateFieldName to the actual field name). Run (Query >
Run). This action will strip off the decimal part (time), leaving just
"clean" dates in the table. Repeat for the second table.
(b) Wherever you are using the Now() function at data entry (forms?),
replace it with the Date() function, which returns the current date, without
a time part. If you are importing data from some external source (Excel
sheet? Text file?) and the time part comes from there, use a query to read
the external data and apply an Int() function like before on the date field,
and append to your tables from that query, as opposed to appending directly
from the external file.

HTH,
Nikos

I am very new to MS-Access and have what may be a simple question...

I have two tables both contain records with account numbers and service

dates. Records may have duplicate account numbers but when that happens the
service date field in each record will differ. Both tables should have
records with the same account numbers and service dates as each other. For
example, if Table A contains account number 100005678 with service date
10/10/2002 then Table B will have one record with the same account number
and service date.

My problem is that the service date fields include both a date and a

time
but the time part does not alway match between the two tables. The time is
not important and I need to be able to either ignore the time or strip it
from both tables altogether if that is possible. The account number and
service date combination are the only data elements in the records that can
be used together in order to find an exact match between the records in each
table. I haven't been able to make this work though because the time
difference causes my queries to not find the matching records when the time
differs. The query sees those fields as not being in agreement with one
another.

Is there an easy way for me to compare only on the date without worrying about the time difference?

Thanks,
David

 

[Guest]

Thanks... I used the DateValue() function and that seems to have worked well.