Combine Format function?
in [Queries]
|
Prev: Update Query
Next: View unavailable
From: AccessKay on 22 Mar 2010 09:55 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
From: Jerry Whittle on 22 Mar 2010 10:26 In a query: YearMonth: Format([TransDate], "yyyymmm") -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "AccessKay" wrote: > 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 >
From: Marshall Barton on 22 Mar 2010 10:29 AccessKay wrote: >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 -- Marsh MVP [MS Access]
From: AccessKay on 22 Mar 2010 10:33 Of course this worked...Thank you! "Jerry Whittle" wrote: > In a query: > > YearMonth: Format([TransDate], "yyyymmm") > -- > Jerry Whittle, Microsoft Access MVP > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > > > "AccessKay" wrote: > > > 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 > >
From: Marshall Barton on 22 Mar 2010 11:34
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] AccessKay wrote: >Of course this worked...Thank you! > > >"Jerry Whittle" wrote: >> In a query: >> >> YearMonth: Format([TransDate], "yyyymmm") >> >> "AccessKay" wrote: >> > 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? |