using VBA to return month name as part of a variable. 1

[cdck]

I am trying to code a variable in VBA to equal the month part of a date written out as a month name, instead of a number. I can't seem to come up with any way to do this; this is especially complicated when I need it to give me the name of a previous month instead of this one.

I have a field on the form that holds today's date, called txtDate. Here's where I am starting - and I don't really know where to go. Can anyone see what I'm trying to do, and suggest a fix?

[tt]Select Case optDate
Case Is = 1
sqlDate = " WHERE month(fDate) = month(Date())"
DateParam = "'(Month of " & Month(Me.txtDate.Value) & ", " & Year(Date) & ")'"
Case Is = 2
sqlDate = " WHERE Month(fDate) = month(Date())-1"
DateParam = "'(Month of " & Month(Me.txtDate.Value) - 1 & ", " & Year(Date) & ")'"
Case Is = 3
sqlDate = " WHERE year(fDate) = year(Date())"
DateParam = "'(" & Year(Date) & ")'"
Case Is = 4
sqlDate = " WHERE year(fDate) = year(Date())-1"
DateParam = "'(" & Year(Date) - 1 & ")'"
Case Is = 5
sqlDate = " WHERE fDate BETWEEN #" & Me.txtDateS.Value & "# AND #" & Me.txtDateE.Value & "#"
DateParam = "'(Between" & Me.txtDateS.Value & " and " & Me.txtDateE.Value & ")'"
Case Is = 6
sqlDate = " WHERE fDate > #2/29/08#"
DateParam = "'(from 3/1/08 to present)'"
End Select[/tt]

When I use Case 1 and insert the result into a table, I get
[tt](Month of 10, 2008)[/tt]

How do I get it to say October, and Case 2 to say September?

Cheryl dc Kern

 

[Remou]

For a month in text, it is best to use format:

Format(txtDate,"mmmmm")
Format(txtDate,"mmmm")

Format(DateAdd("m",-1,txtDate),"mmmm")

 

[cdck]

Wow, that was so simple, and I've used Format before. I guess I let the new setting get me confused.

Thank you so much for clearing me up!


Cheryl dc Kern

 

[Remou]

I hope you spotted the deliberate ( ) mistake.

It should either be 3 or 4 m's.

 

[cdck]

I didn't spot it, but I didn't repeat it either - guess my eyes crossed when I looked at it. Also, I re-typed it with "Me.txtDate.Value" instead of "txtDate". Was I being over-careful, or would it be more correct to use the whole thing?

Cheryl dc Kern

 

[Remou]

Me.txtDate.Value is fine, and a lot of people prefer it, I generally stick at Me.txtDate.

 

[PHV]

Another way is to use the MonthName function:
MonthName(Month(Me!txtDate))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886