Combine Format function?

[AccessKay]

Hi,

I have a field called TransDate that is in the format 01/01/2010 and I want
it to look like 2010Jan. Is there some way to combine the Format function or
should I be using some other function?

Thanks for the help,

Kay

 

[Jerry Whittle]

In a query:

YearMonth: Format([TransDate], "yyyymmm")

 

[Marshall Barton]

I have a field called TransDate that is in the format 01/01/2010 and I want
it to look like 2010Jan. Is there some way to combine the Format function or
should I be using some other function?

Just use a custom format in whatever you are using to
display the date. See Format Property in VBA Help for the
specific codes you can use to constuct custom formats. I
think you want something like:
yyyymmm

 

[AccessKay]

Of course this worked...Thank you!

In a query:

YearMonth: Format([TransDate], "yyyymmm")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

I have a field called TransDate that is in the format 01/01/2010 and I want
it to look like 2010Jan. Is there some way to combine the Format function or
should I be using some other function?

Thanks for the help,

Kay

 

[Marshall Barton]

Watch out for doing that in a query because the result is a
Text field. You don't really need to use the Format
function unless you are concatenating the formatted date
into a larger string. You can just set the query field's
Format property to make the query's datasheet look the same
and leave the field as a date field.

OTOH, you should not be concerned about how a query's
datasheet looks because users should only see the date in a
form or report text box. Then you can set the text box's
Format property instead of messing with the query.
Actually, at that point, you don't need another query field
at all, just bind a text box to the TransDate field and
format it anyway you like.
--
Marsh
MVP [MS Access]

Of course this worked...Thank you!

In a query:

YearMonth: Format([TransDate], "yyyymmm")

I have a field called TransDate that is in the format 01/01/2010 and I want
it to look like 2010Jan. Is there some way to combine the Format function or
should I be using some other function?

 

[AccessKay]

Thanks Marshall. I appreciate the additional information. I read in my
books that it’s easier to set everything up first in a query before you do
the report but now I’m learning that this is not always true.

Kay

 

[Marshall Barton]

No, it is true. It's the definition of "everything" that's
in question. The "everything" for a report (and most forms)
is to create a query that retrieves the appropriate fields
on all the needed and only the needed data records.
Calculations that involve information from more than a
single record, except for most aggregations, also should be
done in the query.

Formatting the field values is only done for display
purposes and should be as late in the game as possible
(typically a text box's Format property and rarely as early
as the query). Sorting, grouping and other non-trivial
formatting should also be done in the report.

 

[AccessKay]

Thanks again! I really appreciate you taking the time to expand on this. I
often have problems with “the big pictureâ€. This understanding is a big help
to me.

Kay

No, it is true. It's the definition of "everything" that's
in question. The "everything" for a report (and most forms)
is to create a query that retrieves the appropriate fields
on all the needed and only the needed data records.
Calculations that involve information from more than a
single record, except for most aggregations, also should be
done in the query.

Formatting the field values is only done for display
purposes and should be as late in the game as possible
(typically a text box's Format property and rarely as early
as the query). Sorting, grouping and other non-trivial
formatting should also be done in the report.
--
Marsh
MVP [MS Access]

Thanks Marshall. I appreciate the additional information. I read in my
books that it’s easier to set everything up first in a query before you do
the report but now I’m learning that this is not always true.

.