Convert Number to Text

[R_J_Potter@msn.com]

The source for My report is a crosstab query.

The query has a field Name "YEAR" Value = Year([Date])
Result is a numeric Value i.e. 2008

The report can include multible Years

I only want to show a two digit Year in the report i.e. 08

I was thinking to convert the Value of [Year] to a Text Value on the query and then use the Right Function =Right([Year],2) on the report Property sheet (Source)

I can not find the function to convert Number to Text.
Open to suggestions

 

[Isskint]

hello R_J_Potter

Firstly to answer your query, STR() is the function that will convert a number to text. You would normally use LTRIM() also to remove leading spaces (or RTRIM() to remove following spaces). Typical equation is LTRIM(STR(variable)).

HOWEVER it would be simpler to use the FORMAT() function in your query to produce a 2 digit year - Format(Year([Date]),"yy"). OR you could set the format of the query field to "yy" under Properties. LASTLY using [YEAR] as a field name is a poor idea as YEAR is also a function and not very descriptive, so you may be better using something more descriptive like SubscriptionYear, PayYear eyc.

 

[R_J_Potter@msn.com]

Thanks for refreshing my memory. I agree with your suggestions.
Thanks again
Ron

 

[R_J_Potter@msn.com]

Query Problem with YY: Format(Year([Date]),"yy")

I got strange results that I do not understand.

The query included Transaction Dates for Years 2008 through 2012

Instead of giving correct results as 08 or 09 or 10 or 11 or 12

Every transaction show result of 05 ???

The DATE range was between #1/1/2008# and #12/31/2012#

 

[Mr. B]

Just a note: If you have a field actually named "Date" (a reserved name in Access) in your table, it could be confusing Access. I tried this same thing on a field (not named "Date") and it worked as expected.

 

[vbaInet]

You use the Format function on the raw date field:

Format([Date], "yy")

Also, Date is reserved keyword and should be avoided. Here's a good list of Reserved Keywords:

http://allenbrowne.com/AppIssueBadWord.html

 

[R_J_Potter@msn.com]

Now, I know what I did incorrectly.

I did not use the YY: Format([Date],"yy") in the raw data query.
I used it in the crosstab query that was using the data from another query.

Now, regarding RESERVED words. I should probably go back and correct my Field Named "DATE" but I my application has grown to be over several hundred objects.

Wish List: Easy method to make these corrections (Find & Change Field Names)

This was the first time I found a need to use FORMAT in a query.
I should have known better since I've been using Access since 1994 starting with Version 1.0

 

[vbaInet]

There's a widely known tool in the MZ-tools website that you can use but apparently it doesn't do a proper deep search. If you don't have a lot of code or macros then it would suffice.

I would advise you leave it as it is for now but remember to enclose it in square brackets at all times.