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
|
|||
|
|||
Truncating a date
I have a report that pulls data from one query. There is a field called
"YrMnth" which actually contains the date in short date format. All of the dates we pull into the table that the query pulls from will be the same year. I want my report to pull in this year for the header page...but I don't want any particluar 'date' to come up, only the year. I have tried pulling in the field into the report header and tried to change the control source to =DatePart("yyyy", [YrMnth]). THis however does not seem to work because the data from the report comes from a query and not a table. All I get is an error (#Error) when the report is displayed. Anyone have any suggestions of how to truncate the date into only pulling up the year??? Thanks, Clay |
#2
|
|||
|
|||
Truncating a date
I expect the name of your control might be YrMnth. You could try change it
to txtYear. Otherwise set the control source to: [YrMnth] and set the format to: yyyy -- Duane Hookom MS Access MVP -- "LADOCITGUY" wrote in message ... I have a report that pulls data from one query. There is a field called "YrMnth" which actually contains the date in short date format. All of the dates we pull into the table that the query pulls from will be the same year. I want my report to pull in this year for the header page...but I don't want any particluar 'date' to come up, only the year. I have tried pulling in the field into the report header and tried to change the control source to =DatePart("yyyy", [YrMnth]). THis however does not seem to work because the data from the report comes from a query and not a table. All I get is an error (#Error) when the report is displayed. Anyone have any suggestions of how to truncate the date into only pulling up the year??? Thanks, Clay |
#3
|
|||
|
|||
Truncating a date
Are you sure 'YrMonth' contains a complete date? The expression should work
if it does. The name sort of implies that it might not - intuitively, I'd expect a column named 'YrMonth' to exclude the day. Is 'YrMonth' based on an expression in the source query? If so, could you post that expression? -- Brendan Reynolds "LADOCITGUY" wrote in message ... I have a report that pulls data from one query. There is a field called "YrMnth" which actually contains the date in short date format. All of the dates we pull into the table that the query pulls from will be the same year. I want my report to pull in this year for the header page...but I don't want any particluar 'date' to come up, only the year. I have tried pulling in the field into the report header and tried to change the control source to =DatePart("yyyy", [YrMnth]). THis however does not seem to work because the data from the report comes from a query and not a table. All I get is an error (#Error) when the report is displayed. Anyone have any suggestions of how to truncate the date into only pulling up the year??? Thanks, Clay |
#4
|
|||
|
|||
Truncating a date
How about creating a tect box with the Control Source =Left(yourdatefield, 4)
Or Right, or Mid$? "LADOCITGUY" wrote: I have a report that pulls data from one query. There is a field called "YrMnth" which actually contains the date in short date format. All of the dates we pull into the table that the query pulls from will be the same year. I want my report to pull in this year for the header page...but I don't want any particluar 'date' to come up, only the year. I have tried pulling in the field into the report header and tried to change the control source to =DatePart("yyyy", [YrMnth]). THis however does not seem to work because the data from the report comes from a query and not a table. All I get is an error (#Error) when the report is displayed. Anyone have any suggestions of how to truncate the date into only pulling up the year??? Thanks, Clay |
#5
|
|||
|
|||
Truncating a date
Just after I posted that, it occurred to me to wonder - just what *does* the
DatePart function make of an incomplete date, anyway? It turns out that the function can actually interpret some incomplete dates ... ? datepart("yyyy","11/2005") 2005 So, I may be wrong about that being the cause of the problem - you might want to try Duane's suggestion first, it may save you some time. -- Brendan Reynolds "Brendan Reynolds" wrote in message ... Are you sure 'YrMonth' contains a complete date? The expression should work if it does. The name sort of implies that it might not - intuitively, I'd expect a column named 'YrMonth' to exclude the day. Is 'YrMonth' based on an expression in the source query? If so, could you post that expression? -- Brendan Reynolds "LADOCITGUY" wrote in message ... I have a report that pulls data from one query. There is a field called "YrMnth" which actually contains the date in short date format. All of the dates we pull into the table that the query pulls from will be the same year. I want my report to pull in this year for the header page...but I don't want any particluar 'date' to come up, only the year. I have tried pulling in the field into the report header and tried to change the control source to =DatePart("yyyy", [YrMnth]). THis however does not seem to work because the data from the report comes from a query and not a table. All I get is an error (#Error) when the report is displayed. Anyone have any suggestions of how to truncate the date into only pulling up the year??? Thanks, Clay |
#6
|
|||
|
|||
Truncating a date
I tried it another way and it worked. I had to create an expression in the
query with: YEAR: DatePart("yyyy",[YrMnth]) THis simply pulls the date out and I then pulled that "YEAR" expression into the report header and formatted nicely. THanks Clay "Brendan Reynolds" wrote: Are you sure 'YrMonth' contains a complete date? The expression should work if it does. The name sort of implies that it might not - intuitively, I'd expect a column named 'YrMonth' to exclude the day. Is 'YrMonth' based on an expression in the source query? If so, could you post that expression? -- Brendan Reynolds "LADOCITGUY" wrote in message ... I have a report that pulls data from one query. There is a field called "YrMnth" which actually contains the date in short date format. All of the dates we pull into the table that the query pulls from will be the same year. I want my report to pull in this year for the header page...but I don't want any particluar 'date' to come up, only the year. I have tried pulling in the field into the report header and tried to change the control source to =DatePart("yyyy", [YrMnth]). THis however does not seem to work because the data from the report comes from a query and not a table. All I get is an error (#Error) when the report is displayed. Anyone have any suggestions of how to truncate the date into only pulling up the year??? Thanks, Clay |
#7
|
|||
|
|||
Truncating a date
I would not recommend using string/text functions with a date value. There
are date functions to extract or manipulate parts of dates. -- Duane Hookom MS Access MVP -- "confumbled" wrote in message ... How about creating a tect box with the Control Source =Left(yourdatefield, 4) Or Right, or Mid$? "LADOCITGUY" wrote: I have a report that pulls data from one query. There is a field called "YrMnth" which actually contains the date in short date format. All of the dates we pull into the table that the query pulls from will be the same year. I want my report to pull in this year for the header page...but I don't want any particluar 'date' to come up, only the year. I have tried pulling in the field into the report header and tried to change the control source to =DatePart("yyyy", [YrMnth]). THis however does not seem to work because the data from the report comes from a query and not a table. All I get is an error (#Error) when the report is displayed. Anyone have any suggestions of how to truncate the date into only pulling up the year??? Thanks, Clay |
#8
|
|||
|
|||
Truncating a date
Using the names of functions or properties as column names leads to
problems. Year is the name of a function. Choose another name such as SalesYear or something equally meaningful. "LADOCITGUY" wrote in message ... I tried it another way and it worked. I had to create an expression in the query with: YEAR: DatePart("yyyy",[YrMnth]) THis simply pulls the date out and I then pulled that "YEAR" expression into the report header and formatted nicely. THanks Clay "Brendan Reynolds" wrote: Are you sure 'YrMonth' contains a complete date? The expression should work if it does. The name sort of implies that it might not - intuitively, I'd expect a column named 'YrMonth' to exclude the day. Is 'YrMonth' based on an expression in the source query? If so, could you post that expression? -- Brendan Reynolds "LADOCITGUY" wrote in message ... I have a report that pulls data from one query. There is a field called "YrMnth" which actually contains the date in short date format. All of the dates we pull into the table that the query pulls from will be the same year. I want my report to pull in this year for the header page...but I don't want any particluar 'date' to come up, only the year. I have tried pulling in the field into the report header and tried to change the control source to =DatePart("yyyy", [YrMnth]). THis however does not seem to work because the data from the report comes from a query and not a table. All I get is an error (#Error) when the report is displayed. Anyone have any suggestions of how to truncate the date into only pulling up the year??? Thanks, Clay |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reports with Date Range - Errors | jwr | Setting Up & Running Reports | 12 | August 8th, 2005 12:57 PM |
Revised Date Question | Spectra | Running & Setting Up Queries | 0 | March 18th, 2005 12:19 PM |
Making Excel generate Access-Like Reports | VJ7777 | General Discussion | 15 | September 12th, 2004 05:48 AM |
QDE (Quick Date Entry) | Norman Harker | General Discussion | 3 | September 3rd, 2004 08:00 AM |
more dates!!! | brigid | Running & Setting Up Queries | 6 | May 26th, 2004 10:59 AM |