If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Truncate date
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. |
#2
|
|||
|
|||
Truncate 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 "LarissaR" wrote in message ... 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. |
#3
|
|||
|
|||
Truncate date
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 "Mark Andrews" wrote: 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" wrote in message ... 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. |
#4
|
|||
|
|||
Truncate date
Don't forget about the DateValue function...
Returns a Variant (Date). Syntax DateValue(date) Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "Mark Andrews" wrote: 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" wrote in message ... 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. |
#5
|
|||
|
|||
Truncate date
Ah! I queried the forum for datepart and added it to my query. That worked
great. Thanks for the redirect! -Larissa "Mark Andrews" wrote: 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" wrote in message ... 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. |
#6
|
|||
|
|||
Truncate date
=?Utf-8?B?TGFyaXNzYVI=?= wrote
in : 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. "Mark Andrews" wrote: 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" wrote in message ... 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 PA is y I've altered my email address. |
Thread Tools | |
Display Modes | |
|
|