Truncate date

[LarissaR]

I have a table with a date field and a value field. The date consists of
date, hours and minutes, and the value field has numbers in it. I'd like to
find the max value for every date, but the only way I can figure out to do
that would be to somehow remove the hours and minutes from each record in the
date field. I've tried a few things, including using =trunc(0,date) and have
tried changing the datatype for the date field from a date to a number and
back again, but changing it to a number caused it to round up in some cases.
I know the easiest way to do this is really to paste the date field into an
Excel file and truncate it there, and then paste the values back into a new
column, but I have nearly 30,000 records and it's too much for the system to
paste. I'm stuck. Can anyone help me? Many thanks, in advance.

 

[Mark Andrews]

How about using Format([yourdatefield],"mm/dd/yyyy") in the query and group
by that column and then do a max on the value field.

You could also use date type functions such as Day, Month, year, datepart
etc...

You could also use CDate() to change a string to a date.

HTH,
Mark

 

[LarissaR]

Mark,
Thanks for your reply. If I format the date field, it still keeps the hours
and minutes but doesn't show them, so I'll have multiple rows returned for
the same date still. Do you have time to tell me how to use the date type
functions? Maybe the datepart would work.
-Larissa

How about using Format([yourdatefield],"mm/dd/yyyy") in the query and group
by that column and then do a max on the value field.

You could also use date type functions such as Day, Month, year, datepart
etc...

You could also use CDate() to change a string to a date.

HTH,
Mark

I have a table with a date field and a value field. The date consists of
date, hours and minutes, and the value field has numbers in it. I'd like
to
find the max value for every date, but the only way I can figure out to do
that would be to somehow remove the hours and minutes from each record in
the
date field. I've tried a few things, including using =trunc(0,date) and
have
tried changing the datatype for the date field from a date to a number and
back again, but changing it to a number caused it to round up in some
cases.
I know the easiest way to do this is really to paste the date field into
an
Excel file and truncate it there, and then paste the values back into a
new
column, but I have nearly 30,000 records and it's too much for the system
to
paste. I'm stuck. Can anyone help me? Many thanks, in advance.

 

[Tom Wickerath]

Don't forget about the DateValue function...

Returns a Variant (Date).

Syntax
DateValue(date)


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

How about using Format([yourdatefield],"mm/dd/yyyy") in the query and group
by that column and then do a max on the value field.

You could also use date type functions such as Day, Month, year, datepart
etc...

You could also use CDate() to change a string to a date.

HTH,
Mark
__________________________________________


I have a table with a date field and a value field. The date consists of
date, hours and minutes, and the value field has numbers in it. I'd like to
find the max value for every date, but the only way I can figure out to do
that would be to somehow remove the hours and minutes from each record in the
date field. I've tried a few things, including using =trunc(0,date) and have
tried changing the datatype for the date field from a date to a number and
back again, but changing it to a number caused it to round up in some cases.
I know the easiest way to do this is really to paste the date field into an
Excel file and truncate it there, and then paste the values back into a new
column, but I have nearly 30,000 records and it's too much for the system to
paste. I'm stuck. Can anyone help me? Many thanks, in advance.

 

[LarissaR]

Ah! I queried the forum for datepart and added it to my query. That worked
great. Thanks for the redirect!
-Larissa

How about using Format([yourdatefield],"mm/dd/yyyy") in the query and group
by that column and then do a max on the value field.

You could also use date type functions such as Day, Month, year, datepart
etc...

You could also use CDate() to change a string to a date.

HTH,
Mark

I have a table with a date field and a value field. The date consists of
date, hours and minutes, and the value field has numbers in it. I'd like
to
find the max value for every date, but the only way I can figure out to do
that would be to somehow remove the hours and minutes from each record in
the
date field. I've tried a few things, including using =trunc(0,date) and
have
tried changing the datatype for the date field from a date to a number and
back again, but changing it to a number caused it to round up in some
cases.
I know the easiest way to do this is really to paste the date field into
an
Excel file and truncate it there, and then paste the values back into a
new
column, but I have nearly 30,000 records and it's too much for the system
to
paste. I'm stuck. Can anyone help me? Many thanks, in advance.

 

[Bob Quintal]

Mark,
Thanks for your reply. If I format the date field, it still keeps
the hours and minutes but doesn't show them, so I'll have multiple
rows returned for the same date still. Do you have time to tell me
how to use the date type functions? Maybe the datepart would work.
-Larissa

Depending where you format the date field, you can hide the time info
or remove it from the returned value.

If you format it in a table, query form or report Format property
box, it'll just hide the time but if you create a formatted fielfd
in the query it'll strip out the time.

Open the Query builder in design mode and choose your table.
In the field: row of a blank column, type
format([your Date],"yyyy-mm-dd") , substituting the real name of
your date and press enter
Access will change it to
expr1: format([your Date],"yyyymmdd")
Now build the rest of the totals query like you normally would.

It will group on the date.

How about using Format([yourdatefield],"mm/dd/yyyy") in the query
and group by that column and then do a max on the value field.

You could also use date type functions such as Day, Month, year,
datepart etc...

You could also use CDate() to change a string to a date.

HTH,
Mark

I have a table with a date field and a value field. The date
consists of
date, hours and minutes, and the value field has numbers in it.
I'd like to
find the max value for every date, but the only way I can
figure out to do that would be to somehow remove the hours and
minutes from each record in the
date field. I've tried a few things, including using
=trunc(0,date) and have
tried changing the datatype for the date field from a date to a
number and back again, but changing it to a number caused it to
round up in some cases.
I know the easiest way to do this is really to paste the date
field into an
Excel file and truncate it there, and then paste the values
back into a new
column, but I have nearly 30,000 records and it's too much for
the system to
paste. I'm stuck. Can anyone help me? Many thanks, in advance.