Date Conversion from one or two digits to a month

[USMarine430]

I have a text box which feeds of of the control source labeled as "month" - this column has nothing but the numbers 1-12 in it. And so appears in the text box is this number.

The report prompts the query which has the string:
Like IIf(IsNull([forms]![frmrepotsform]![cbomonth]),"*",([forms]![frmrepotsform]![cbomonth])) - singling out the month for the report asked - which is the month (number) that appears.

The title sums it up - I am wondering if there exists the means to convert the numbers 1-12 (not belonging to a series of other numbers or anything else that would represent a date) to word value of a month. I know that "mmmm" is representative of converting something to the month name - but how would you use the expression builder to convert a single set of numbers to a month name?

Semper Fi-
Dave

 

[jzwp22]

...Like IIf(IsNull([forms]![frmrepotsform]![cbomonth]),"*",([forms]![frmrepotsform]![cbomonth]))

I'm not sure I quite understand your query snippet above since you did not include the field name on which Like is operating. What is the datatype of that field? Outside of that, are you after the monthname() function which returns the name of the month when you input the number into the function?

 

[USMarine430]

About the snipet - I have no idea, I worked with a colleague of mine and that is what he came up with - it works well. . . and I am no where near expedient in my Access knowledge to tell you anything more than the fact that it works. . .

Please see the attachment . . .

 

[Alansidman]

I think that you will need another table with two fields.

tblMonth
--------
monthID PK (In your table this would contain the numbers 1 to 12)
monthDesc (In your table this would contain the corresponding months)

In your query add this table and join the monthID to your numerical value in your primary table. Add the monthDesc to your query. Uncheck the box for the month number in your primary table in the query. Now when you run the query it should bring back the name of the month and not the numerical value.

I hope this is what you are looking for.

Alan

 

[USMarine430]

Pretty darn close - I want to keep the numerical value in the report itself - i just want to convert the numerical value generated in the text box to an alphabetical variant. Is there a where clause I can make? or an "if" statement that would convert the number entered into the query to letter format in the text box?

Thanks again to both of you for your help!

 

[Alansidman]

Let me make sure I understand. You have a text box on a form that has numbers 1-12 and you want instead for those to show up as January, February, etc. Is this correct? Is that text box a combobox that has the selections or are you free to put anything you want in the text box?

Alan

 

[boblarson]

You can display a month name from a number in a query easily enough:

MyMonthName:Format(DateSerial(Year(Date()),[OriginalMonthFieldHere],1),"mmmm")

 

[USMarine430]

Well - Here is what I did - I created a new table like you said Alan, I joined it to the existing table in the query, and then I went in to change the control source of the text box to the newly added "monthDesc" - so now when the query prompts me for the month, it is converted to a text variant in the lone textbox.

in the kind words of the Guinnes chaps: "BRILLIANT"

Thanks for your help!!!

 

[jzwp22]

You should be able to add another control to the report and set it's control source to =monthname([month])

If the name of the field in the underlying table is also called month that could pose problems since the word month is a reserved word in Access. Here is a site that lists all reserved words. You will have to make sure that if the the field name is month that you enclose it in square brackets like I show above.

 

[boblarson]

Dang it - I always forget about that monthname function.

 

[Alansidman]

Dang it - I always forget about that monthname function.

Me, too. So much simpler

 

[boblarson]

Me, too. So much simpler

Too bad a DayName function doesn't exist. That would be helpful as well.