Truncating a date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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?
 
How about creating a tect box with the Control Source =Left(yourdatefield, 4)
Or Right, or Mid$?
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
How about creating a tect box with the Control Source =Left(yourdatefield,
4)
Or Right, or Mid$?

LADOCITGUY said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top