Tech Support Guy banner
Status
Not open for further replies.

Access: How to Display Long Month From Text

1K views 14 replies 4 participants last post by  DKTaber 
#1 ·
I cannot find a function for an expression where I want to change a short date (e.g., 11/22/01) to the long month (i.e., "November"). If you use Month ([datefield]), you get "11", not "November". VBA has a "Monthname" function that will do this. Is there an equivalent function for expressions?

Failing this, I will have to write a VERY long nested iif's expression (i.e., iif(month([datefield])=1, "January", iif([month([datefield])=2, "February", iif...)))))))))))). Not even sure that's possible, because my recollection is that Access has a limit on nested iif's, and I think it's 8...which is not enough.
 
#2 ·
Okay, I take it you're using Access, and that you wish to enter records using the short date format.
Where do you want them to display in the longer date format?

Can't you just set the format of the date wherever it displays, to mmmm-dd-yy (which would display November-22-01)?


Gram
 
#3 ·
Originally posted by Gram123
Okay, I take it you're using Access, and that you wish to enter records using the short date format.
Where do you want them to display in the longer date format?

Can't you just set the format of the date wherever it displays, to mmmm-dd-yy (which would display November-22-01)?

Gram
Negatron! I should have put more details in the question. This is in an unbounded text box in the report header for a report form. The data source is an expression, specifically ="Renewal List for "&Month([DateJoined]). The query behind the report asks for the month you want to run it for, entered as a number, so if you want to run it for November renewals, you enter "11". All the records in the report are then people who joined the organization in November (DateJoined), so all I want to do is convert that first DateJoined field it encounters to MonthNAME([DateJoined]) so it displays as "November".
 
#4 ·
Weeeeellll... that's because you're trying to do two things at once.

Either they enter a date, or they don't. The number of a month is not--whatever database manager on earth you're using--
a date. It's just a number. So you have two choices:

1. Change your parameter to something like
Code:
[Enter any date from the month you like to join using the format mm/dd/yy]
in which case you may then format your report header as
Code:
="Renewal List for "& Format([Enter any date from the month you like to join using the format mm/dd/yy],"mmmm yyyy")
That's the simple answer.

2. The complicated answer calls a VBA function like this one

Code:
Public Function GetMnthNm(ByVal pbytMnthNum As Byte)
As String

    Select Case pbytMnthNum

        Case 1: GetMnthNm = "January"
        Case 2: GetMnthNm = "February"
        Case 3: GetMnthNm = "March"
        Case 4: GetMnthNm = "April"
        Case 5: GetMnthNm = "May"
        Case 6: GetMnthNm = "June"
        Case 7: GetMnthNm = "July"
        Case 8: GetMnthNm = "August"
        Case 9: GetMnthNm = "September"
        Case 10: GetMnthNm = "October"
        Case 11: GetMnthNm = "November"
        Case 12: GetMnthNm = "December"
    End Select
    
End Function
but hey, it lets 'em keep on putting in that one number. You paste that code into a public module (off the module tab of the db window), and then set your report header's control source to

Code:
="Renewal List for "& GetMnthNm([DateJoined])"
If you can't tell where I stand, well, it's for the first solution. What you're calling a DateJoined really isn't one, in db terms. And as for why the month names aren't in there, well, those depend on Windows, and the country you happen to be in, and not on the db. Which is why Access won't hard code them for you.

Either one will solve the problem, tho.
 
#5 ·
Originally posted by downwitchyobadself
Weeeeellll... that's because you're trying to do two things at once.

Either they enter a date, or they don't. The number of a month is not--whatever database manager on earth you're using--a date. It's just a number. So you have two choices:

1. Change your parameter to something like
Code:
[Enter any date from the month you like to join using the format mm/dd/yy]
in which case you may then format your report header as
Code:
="Renewal List for "& Format([Enter any date from the month you like to join using the format mm/dd/yy],"mmmm yyyy")
That's the simple answer.

...If you can't tell where I stand, well, it's for the first solution. What you're calling a DateJoined really isn't one, in db terms.
Either one will solve the problem, tho.
Tried solution #1. Doesn't work. It DOES produce a long month, but no matter what month number is entered in the parameter, it returns only "December" or "January". Put in 12, get December; put in 11; get January; put in 10, get January; put in 6, get January! I even when so far as to make the data source = Month([Enter month as number]&"/1/00") to give it a recognizable date format, and put a format of "mmmm" in the format property field. Still produces only December or January.
 
#6 ·
A few minutes later, after trying some other things, I threw in the towel on trying to get Access to read the text number as a month number, or the number appended to text that makes it a full date, and simply went with a nested "IIf" expression similar to what you showed for VBA (iif([field]="1","January", iif([field]="2", "February", etc.). Works perfectly. I always knew I could do that (I have at least 100 similar nested iif expressions throughout the database), but had hoped there was a way to do it without writing a long expression or VBA code.

BTW, I noticed that your message, and my reply, spread way across my screen, and I had to constantly use the scroll bar to read it. That has not been true with any other forum to which I've posted messages. Is there something different about this forum, or is there some setting I don't know about that will force text to remain within the screen (wrap) so you don't have to scroll?
 
#8 ·
Hmm.

The wideness of the screen, yes, is due to my code. And then the fact that you quoted it. There's no way around it (except that I'll try to post my code better), because everything within the tags is treated as literal text, with no line breaks thrown in. This is irritating to read, but on the other hand it means you can literally copy the code straight out of the thread and drop it into the VBA editor. Which most of the time is pretty useful.

As far as that parameter not working, well, it's tricky, because you have to make Access think it's a date, and (apparently) you're not comparing it to a date field. (When I compare it to a date field, it comes out fine. When I don't, I too get January up and down the block.) So, no big deal.

The iif works fine, sure, but it's heavy on memory, and it slows queries way down. I won't go into details, perhaps it's not an issue for you. I would at least recommend replacing it with the Switch function, which you can read about in Access help by typing Switch Function example into the answer wizard. It's a lot easier to read (and edit) than nested iif's.

Glad you got it working, anyway.
 
#9 ·
I have a report that is generated from a Switchboard button, but before opening the report, the button first opens a small form where the user enters the month as a number (1 = "January", etc.). The field that contains the number is [Forms]![InputMergeMonth]![MergeMonth]. In the report header, I want to print the month, but as a name, so I used a nested IIF function to convert from the number to the spelled-out month. Only problem is, I keep getting "#Name" in the field, meaning Access doesn't recognize the name.

The control source formula is currently

=IIf([Forms!InputMergeMonth!MergeMonth]="1","January",IIf([Forms!InputMergeMonth!MergeMonth]="2","February",IIf([Forms!InputMergeMonth!MergeMonth]="3","March",IIf([Forms!InputMergeMonth!MergeMonth]="4","April",IIf([Forms!InputMergeMonth!MergeMonth]="5","May",IIf([Forms!InputMergeMonth!MergeMonth]="6","June",IIf([Forms!InputMergeMonth!MergeMonth]="7","July",IIf([Forms!InputMergeMonth!MergeMonth]="8","August",IIf([Forms!InputMergeMonth!MergeMonth]="9","September",IIf([Forms!InputMergeMonth!MergeMonth]="10","October",IIf([Forms!InputMergeMonth!MergeMonth]="11","November",IIf([Forms!InputMergeMonth!MergeMonth]="12","December"))))))))))))

Originally, I had [] around each element (i.e., [Forms]![InputMergeMonth]![MergeMonth]). Didn't work. Then put brackets at the beginning and end; i.e., [[Forms... Same result. Then I tried removing the brackets altogether. Same result.

What do I have to do to make Access recognize this as a legitimate field?
 
#10 ·
Well, for one thing, you're syntax is wrong, try:

Take December's part out and make it the "else" part of all the ifs. In other words, if it doesn't match 1 through 11, just make it "December". Or you have to put some file "else" part, like "invalid" or "" or "bad date" or something.

So:

IIf([Forms!InputMergeMonth!MergeMonth]="11","November","December")))))))))))

Now, I know you're limited to 7 embedded ifs in Excel. I'm not sure about Access.
 
#11 ·
Originally posted by Dreamboat
Well, for one thing, you're syntax is wrong, try:

Take December's part out and make it the "else" part of all the ifs. In other words, if it doesn't match 1 through 11, just make it "December". Or you have to put some file "else" part, like "invalid" or "" or "bad date" or something.

So:

IIf([Forms!InputMergeMonth!MergeMonth]="11","November","December")))))))))))

Now, I know you're limited to 7 embedded ifs in Excel. I'm not sure about Access.
Access does not have that low a limit (don't know what it is because I've never exceeded it). However, I actually replaced the nested if's with a more efficient method using the Switch function, as follows:

=Switch([Forms!InputMergeMonth!MergeMonth]="1","January", [Forms!InputMergeMonth!MergeMonth]="2","February", [Forms!InputMergeMonth!MergeMonth]="3","March", [Forms!InputMergeMonth!MergeMonth]="4","April", [Forms!InputMergeMonth!MergeMonth]="5","May", [Forms!InputMergeMonth!MergeMonth]="6","June", [Forms!InputMergeMonth!MergeMonth]="7","July", [Forms!InputMergeMonth!MergeMonth]="8","August", [Forms!InputMergeMonth!MergeMonth]="9","September", [Forms!InputMergeMonth!MergeMonth]="10","October", [Forms!InputMergeMonth!MergeMonth]="11","November", [Forms!InputMergeMonth!MergeMonth]="12","December")

It produces the same thing..."#Name". This is made stranger by the fact that when the report is run, it asks for [Forms]![InputMergeMonth]![MergeMonth], to which I respond with a number between 1 and 12, and the report correctly produces only records where the JoinDate month is that number. It's only in the Page Header that it doesn't recognize the existence of the field. I even replaced all the code above with just [Forms]![InputMergeMonth]![MergeMonth] to see if it would show just the number, but it gives me an error message, "Can't find [Forms]![InputMergeMonth]![MergeMonth]". How can it not find it when it asked for it, I entered it, it uses it to select the records, and does so correctly!!!?
 
#12 ·
Okay, well, for starters let's clear up the bracket issue: Whatever is in one set of brackets is one kind or another of Access object. So your thing ain't ever going to work using
Code:
[Forms!InputMergeMonth!MergeMonth]
because it will be looking for a field named "Forms!InputMergeMonth!MergeMonth", which though it would be a terrible field name, could nevertheless be used. (As you may remember from your "Date" and "Type" fields; note that in SQL Access puts brackets around a field name it can't handle; coincidence? No.)

The best way to handle the bracket situation in a form's control source is not to use them at all. If Access needs them, it will put them in. And as for the error you're getting about "can't find" etc., it's because you're providing two different things, which though they look the same to you are actually not. In any case, the brackets should look like this:
Code:
 [Forms]![InputMergeMonth]![MergeMonth]
So: next subject. I can't tell from your post what the name of the little form is, and what the control where the user puts in the month is. If the form is named InputMergeMonth and the text box in which the user inputs a number is MergeMonth, then there should be no problem, as long as the form is loaded when the report is opened. Goes without saying that if you have a form control named "Forms!InputMergeMonth!MergeMonth", with or without brackets, you should change it immediately, no excuses. And of course no brackets or exclamation marks anywhere in a control name.

I say it should work no problem; there are a few noteworthy exceptions:
  1. Any control source referencing anything else--i.e. any control source not named for a field in the report's underlying query--should begin with an equal sign
  2. Make sure you actually need the quotation marks around the number values. A text box whose Format property is set to standard and whose Decimal Places property is set to 0, for example, will always return numerical values, not strings. (Strings are text values in quotation marks.)
  3. The name of the report control (or form control, for that matter) must not be the same as a part of its control source. A control named MyControl with the control source
    Code:
    ="Show me " & [MyControl]
    will produce an error in that control. A good rule is to always use a control name including a word such as "Display" when you are trying to distinguish between the control and some modified contents; naming the above example MyControlDisplay will beat the error.
All this being said, I don't understand why you're opening a form to do exactly what a parameter in the underlying query could do. Unless your form does other things, you're making things awfully hard on yourself.

Without fully understanding the relationship between your switchboard, your month-input form, and your report, here's what I would suggest:
  1. In the underlying query, create a criteria for the JoinDate such as [Enter month number]. (Seems to me we went over that...)
  2. On the report, create a hidden control called something like txtMnthHolder. Set its control source to
    Code:
    =[Enter month number]
  3. Create a second hidden control called something like txtMnthNm. Set its control source to the switch function, replacing each instance of your Forms!InputMergeMonth!MergeMonth statement with txtMnthHolder, or whatever name you give the first control.
  4. Any reference to the number or the month should then be made from within the report, so you might have a report header text box whose control source was
    Code:
    ="Report for " & [txtMnthNm] & " (" & [txtMnthHolder] & ")"
  5. You can then get rid of your InputMergeMonth form altogether.

Hope this helps.
 
#13 ·
Down: The length of your response shows you put a lot of time into this; many thanks.

You mention that [Forms!InputMergeMonth!MergeMonth] will never work. I didn't think so either. You should know that the original syntax I used was [Forms]![InputMergeMonth]![MergeMonth] -- just the way it is in the query. It was only after trying that that I went to just beginning and ending brackets, and then to NO brackets at all. None of them worked.

The name of the little form into which the user enters the month as a number (but the field is actually text) is [InputMergeMonth]. The field where the number is entered is indeed [MergeMonth]. This form is what appears first when the user selects a "Mail Merge Letters" button from the Switchboard. After entering the month, the user picks one of three buttons in the form that contain hyperlinks to mail-merge letters.

However, I was trying to use the same query as the data source for a report where that little form would NOT be loaded. I didn't think that would matter, because when you run the query directly (not through the form), a parameter box displays asking for [Forms]![InputMergeMonth]![MergeMonth]. You enter a number, and the query works perfectly. The place it DOESN'T work is where I want the converted, spelled-out month to appear in the report header, which is where the Switch ([Forms]![InputMergeMonth]![MergeMonth] = "1", "January", etc. expression comes in. It's inexplicable to me that the query is run, asks for [Forms]![InputMergeMonth]![MergeMonth], you supply it, it correctly selects only the records for that month…but doesn't recognize the SAME field (in the header) that it just requested and received input for???!

In response to the "noteworthy exceptions" that might cause a problem…

1. The field IS in the underlying query.
2. The "number" in the [MergeMonth] field is actually text, so yes, the "" are necessary
3. The name of the field in the report form is NOT the same as the control itself (it's just Text ##, where "##" is a number assigned by Access).

Having had no success no matter what I did with the brackets, I gave up and created a separate query for this report in which I replaced the [Forms]![InputMergeMonth]![MergeMonth] with [Enter join month as a number (1,2,3, etc)], and use that in the Switch expression in the header. Of course, it works perfectly. What I was trying to do is be efficient by using the same query for both the mail-merge letters AND this report. I'm frustrated that I can't figure out why a parameter that WORKS when you run the query directly, and WORKS to select the correct records in the report…mysteriously doesn't recognize itself when it's put into the header! I realize it's unusual to have a field named [Forms]![InputMergeMonth]![MergeMonth] (as you say, nobody would ever name a field that…unless it is for some other purpose, which is exactly my situation), but if the query RUNS CORRECTLY, the query PRODUCES THE RIGHT RECORDS…why does that parameter not "know itself" when it gets to the header?

In short, the underlying issue has not been solved, it's just been worked around. Instead of being able to use one query for mail-merges and a report, I now have two queries that are identical except for the parameter field.

BTW, one very nice piece of information you provided during this thread – the Switch function – is absolutely super. I don't know why I was unaware of it, but I wasn't. That alone is worth its weight in gold! Thank you, thank you!
 
#14 ·
The problem is one of syntax. It took me a sec to test it myself to confirm, but what I thought to be the case, is: "forms!formname!controlname" is not a parameter, as far as a text box is concerned; it is a reference, which will always go looking for a form with the name required. Query parameters can go either way; report controls cannot.

The header works, of course, when the form is loaded. It just doesn't work when it can't find the form. It's that simple.

I see why you're doing what you're doing--run report from same query, regardless of whether or not a given form is loaded--but the place to grab that parameter is not in the report header. Normally you would need to use code to manipulate something like the what-ifs, because you're actually checking the state of objects in your database. But you can get around it. Here's how:
  1. Leave the Forms!etc. parameter in the query.
  2. The reference is tied to some field in your query, I'm assuming, as a criteria to limit records? JoinDate or something? Set your header to the underlying field. And not to the parameter. If the related field is called JoinDate, it'll be like
    Code:
    ="Header for " & Switch([JoinDate]=1,"January",[JoinDate]=2,"February"...)
  3. Make all references in your report to the related field, and not to the parameter; leave the parameter in the query only. If it finds the form, it'll get the value from there. If it doesn't, it will prompt the user.
I tested this to make sure it works, and it does. So there you go. Hope this will finally put this one to rest. Remember: a parameter is a point of limitation only, a filter in correct terminology. Filters and data are not the same thing, and filtering by form and by query are not the same thing either.
 
#15 ·
Originally posted by downwitchyobadself
The problem is one of syntax. It took me a sec to test it myself to confirm, but what I thought to be the case, is: "forms!formname!controlname" is not a parameter, as far as a text box is concerned; it is a reference, which will always go looking for a form with the name required. Query parameters can go either way; report controls cannot.

The header works, of course, when the form is loaded. It just doesn't work when it can't find the form. It's that simple.

I see why you're doing what you're doing--run report from same query, regardless of whether or not a given form is loaded--but the place to grab that parameter is not in the report header. Normally you would need to use code to manipulate something like the what-ifs, because you're actually checking the state of objects in your database. But you can get around it. Here's how:
  1. Leave the Forms!etc. parameter in the query.
  2. The reference is tied to some field in your query, I'm assuming, as a criteria to limit records? JoinDate or something? Set your header to the underlying field. And not to the parameter. If the related field is called JoinDate, it'll be like
    Code:
    ="Header for " & Switch([JoinDate]=1,"January",[JoinDate]=2,"February"...)
  3. Make all references in your report to the related field, and not to the parameter; leave the parameter in the query only. If it finds the form, it'll get the value from there. If it doesn't, it will prompt the user.
I tested this to make sure it works, and it does. So there you go. Hope this will finally put this one to rest. Remember: a parameter is a point of limitation only, a filter in correct terminology. Filters and data are not the same thing, and filtering by form and by query are not the same thing either.
It's very helpful to know that Access regards a reference as a parameter for purposes of record selection, but does not regard the same reference as a control in the report. It will save me time in the future.

I'm aware that I could use just Month(DateJoined) with the Switch function to produce the spelled-out month while leaving the [Forms]! etc. field in the query. However, that is the ONLY query in the entire database, which contains a dozen tables and hundreds of queries and forms, where the question the query asks is "cryptic" for the users I'm dealing with. So I'm going to stay with the two queries, one for the merge letters, the other for the report, and for the latter, use [Enter the month as a number (1,2,3, etc)].

I'm sure you're faced with the same situation all the time...that most of the users can turn the PC on and pick from a switchboard, but that's about it. When I ask them to do something in native Access, their faces turn white and they start to shake. So I think I'll leave it like it is.

Many thanks again, Down, for your perserverance. I wish I had discovered the TechSupportGuy forums years ago. Best peer-to-peer support forum I've ever seen, thanks to people like you.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top