How do I remove Time part from Date/Time data in Access

[Guest]

I start with a linked excel table, (generated automatically by a SCADA system
that I cannot edit) that has 3 fields:

RepDate, Date/Time
Turb, Number
State, Number

Data is entered autoimatically every 15 minutes and the RepDate data has
both date and time. I want to count the number of times State = 1 each day.
I also want to establish relationships between RepDate and fields in other
tables that contain dates only (no time). Changing the format allows a
select query to generate the count I want; but every thing I try still leaves
a 'type mismatch' error when joining to other tables in subsequent queries.
Changing the format only seems to hid the time, not remove it.

I think my problem would be solved if I could either: 1) remove the Time
part form the RepTime data, or 2) set all the times equal but not change the
date. Maybe I don't understand where the problem is originating? What do
you think?

Thanks for sharing you suggestions,

Doug M

 

[Jeff Boyce]

Doug

Are you saying you are formatting the display of a field via a query? Are
you aware that what is stored and how it might be displayed are separable?

Depending on whether Access considers the RepDate to be a Date/Time field
(in Access), you could look into the Access Date & Time-related functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Doug:

You should be able to use the DateValue function when joining the tables.
What this does is return a date/time value with a zero time of day, there
being no such thing as a date value per se, i.e. it returns a value of the
date/time at midnight at the start of the day. The JOIN would thus go like
this:

FROM Table1 INNER JOIN Table2 ON DATEVALUE(Table1.RepDate) = Table2.DateField

Ken Sheridan
Stafford, England

 

[Guest]

Ken,

I believe this is exactly what I was missing. I'll try your solution and
let you know if I am successful. Thanks for your help on a Friday evening.

Regards,

Doug M
Shreveport, Louisiana, USA

 

[Guest]

Jeff,

Thank you for your response. I believe the answer is in the Date/Time
related functions, propably in the DateValue function. That's what I'm going
to try next.

Regards,

Doug M

 

[Guest]

Ken,

THAT"S IT!

You know, you nearly lost me when you described the INNER JOIN function.
But I could tell from your explanation that the DateValue function was
exactly what I wanted to do. I found the DateValue function in the
Expression Builder's Built-In Funtions and I was on my way. I can't believe
that will all the searches I made in Access Help, I didn't identify the
DateValue function myself.

To use the DateValue function, I simply inserted:

Date: DateValue([Table1]![RepDate])

in the Field: row of my Select Query. Later, when I linked the Select
Query to other tables (with dates only, see my original post), I no longer
have the type mismatch error!

Thanks again

Doug M

 

[Steve]

Try this:
DateOnly = Int([RepDate])

 

[StopThisAdvertising]

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ????
Over 1150 users have come to me from the newsgroups requesting help '1150 users ????
(e-mail address removed)

--
To Steve:
Over 525!! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R