Alphabetic month over Numeric month

  • Thread starter Thread starter Rohn
  • Start date Start date
R

Rohn

I have a query with a birth date that I separated out by Month, Day, Year... here is the SQL for the Query:
SELECT [First_Name] & " " & [Last_Name] AS Name, dbo_EMPLOYEE.BIRTH_DATE, dbo_EMPLOYEE.ADDRESS_1, dbo_EMPLOYEE.CITY, dbo_EMPLOYEE.STATE, dbo_EMPLOYEE.ZIP_1, Month([BIRTH_DATE]) AS [Month], Day([BIRTH_DATE]) AS [Date], Year([BIRTH_DATE]) AS [Year]
FROM dbo_EMPLOYEE
WHERE (((dbo_EMPLOYEE.BIRTH_DATE) Like [Enter BirthMonth: 1 - 12]) AND ((dbo_EMPLOYEE.TERMINATE_DATE) Is Null));

I open my report and the connected query asks me to select a month (1- 12) then I have the report displaying the current month at the top of the report. I am trying to see all employees who have a birthday that month. Everything works fine, except that the month shows the number that I selected and not the alphabet month. Like, I choose 5 for May and I want May to be displayed at the top of my report? How can I do that? Is it a simple format somewhere that I am missing?

I know, simple issue..... but it bugs me.
_____
Rohn Everson
 
The Month function returns the month number, not the name.

Try Format([BIRTH_DATE], "mmm") for Jan, Feb, Mar, or Format([BIRTH_DATE],
"mmmm") for January, February, March.

And I'd suggest using using other alias names than Month, Date and Year:
those are all reserved words, and shouldn't be used.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query with a birth date that I separated out by Month, Day, Year...
here is the SQL for the Query:
SELECT [First_Name] & " " & [Last_Name] AS Name, dbo_EMPLOYEE.BIRTH_DATE,
dbo_EMPLOYEE.ADDRESS_1, dbo_EMPLOYEE.CITY, dbo_EMPLOYEE.STATE,
dbo_EMPLOYEE.ZIP_1, Month([BIRTH_DATE]) AS [Month], Day([BIRTH_DATE]) AS
[Date], Year([BIRTH_DATE]) AS [Year]
FROM dbo_EMPLOYEE
WHERE (((dbo_EMPLOYEE.BIRTH_DATE) Like [Enter BirthMonth: 1 - 12]) AND
((dbo_EMPLOYEE.TERMINATE_DATE) Is Null));

I open my report and the connected query asks me to select a month (1- 12)
then I have the report displaying the current month at the top of the
report. I am trying to see all employees who have a birthday that month.
Everything works fine, except that the month shows the number that I
selected and not the alphabet month. Like, I choose 5 for May and I want May
to be displayed at the top of my report? How can I do that? Is it a simple
format somewhere that I am missing?

I know, simple issue..... but it bugs me.
_____
Rohn Everson
 
I did change my lables thanks.

Although when I try to use the formating you suggested, Access changes it to: "([BIRT"h_d"ATE], mmm)"

Any suggestions as to why?

_____
Rohn Everson
 
How are you creating this SQL: in VBA, or in the SQL view of a query? What
exactly did you type? I can't think of any reason that should have happened.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I did change my lables thanks.

Although when I try to use the formating you suggested, Access changes it
to: "([BIRT"h_d"ATE], mmm)"

Any suggestions as to why?

_____
Rohn Everson
 
Neither.....

I was trying to put it in the query field properties and I also tried the report field properties.

I just tried replacing the; Month([BIRTH_DATE]) AS [Month] in the query itself, with; Month: Format([BIRTH_DATE],"mmm") and it works great!

with me, it always come down to operator error, lol.

Thanks so much for your help.
----------
Rohn Everson

Yahoo messenger: (e-mail address removed)
How are you creating this SQL: in VBA, or in the SQL view of a query? What
exactly did you type? I can't think of any reason that should have happened.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I did change my lables thanks.

Although when I try to use the formating you suggested, Access changes it
to: "([BIRT"h_d"ATE], mmm)"

Any suggestions as to why?

_____
Rohn Everson
 
Back
Top