convert date to mmmyy as text for variable

[tbac]

I want to take a date such as 6/1/2006 and create a string variable "Jun06" but I am having problems getting the string variable to end up as "Jun06". It wants to create a string variable, "6/1/2006

 

[SkipVought]

Hi,

Use the Format function

sdate = Format([Mydate], "mmmyy")

Keep in mind that this will NOT sort in the proper sequence as it is just a string.


Skip,

_[red][/red]

 

[Golom]

? Format(#6/1/2006#,"mmmyy")
Jun06

 

[tbac]

That will display a date in a particular format but it will not be in the form of a string variable. I am doing code that will determine the status of shipments in a table. What I want is to create a string variable to insert into a text field that gives a status. The status will appear in a table and will be either: "Comm" or "Test" or "Send Jun06" I thought I had it when I created a calculated field in the Query with this format: CS: "Send " & [Month] & [Year], where the Month wast a calculated field: Month:Format(Month([Send]),"mmm") and Year was a calculated field: Year: Format(Year([Send]),"yy"). But [Month] and [Year] are both incorrect and do not reflect the date of [Send], which nis an actual date field.

 

[Golom]

Of course it is a string variable. Strings are the only thing that Format produces.

If "[Send]" is a DateTime field then

CS: "Send " & Format([Send],"mmmyy")

will produce "Send Jun06"

What you were doing will have problems because

[blue]Month([Send])[/blue]
Produces a number between 1 and 12

[blue]Format(Month([Send]),"mmm")[/blue]
Will Produce "Dec" when Month([Send]) is 1 and "Jan" otherwise.

Similar comments apply to Format(Year([Send]),"yy") Which will produce "05" where Year([Send]) is any year between 1828 and 2192.

 

[SkipVought]

tbac,

problem is, you did not read the FAQ that xlbo suggested in your other post.

DATE VALUES are just NUMBERS.

What you SEE (1/1/2006 or January 1 06) is a DISPLAY.

That your query did not work, you must UNDERSTAND the nature of the data you are working with.

Skip,

_[red][/red]