month name vs month number

Guest · Dec 29, 2003

I've been trying to get my report to print a month name (January, February, etc) instead of the month number that is coming from my query. I created a table with the numbers and the months and tried just adding a relationship between the two fields (one in the table and one in the query), then ran the report wizard to create the report selecting the needed fields from the query and the month field from the month table, but it gives me an error that says:"You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query." I'm confused... the months table is in no way used in the creation of the query. Can you offer any suggestions or solutions as to how I can get my report to just give me the name of the months rather than the number? Please keep in mind that I'm a reletive novice in Access. Thanks in advance!

Fons Ponsioen · Dec 29, 2003

In the query you are eluding to, in the propertties for
the month field, set the format to "mmm" without the
quotes. This will reflect the month name rather than the
number.
Hope this helps.
Happy New Year.
Fons

-----Original Message-----
I've been trying to get my report to print a month name

(January, February, etc) instead of the month number that
is coming from my query. I created a table with the
numbers and the months and tried just adding a
relationship between the two fields (one in the table and
one in the query), then ran the report wizard to create
the report selecting the needed fields from the query and
the month field from the month table, but it gives me an
error that says:"You have chosen fields from record
sources which the wizard can't connect. You may have
chosen fields from a table and from a query based on that
table. If so, try choosing fields from only the table or
only the query." I'm confused... the months table is in no
way used in the creation of the query. Can you offer any
suggestions or solutions as to how I can get my report to
just give me the name of the months rather than the
number? Please keep in mind that I'm a reletive novice in
Access. Thanks in advance!

Guest · Dec 29, 2003

The query is SQL-specific. Can you tell me where I can set this up in the following code

SELECT a.IDUniqueCarRecNo, Year(a.MaintDate), Month(a.MaintDate), MAX(a.OdometerRead) AS MaxofCurrentMonth, MAX(b.OdometerRead) AS MaxofPreviousMont
FROM tblMaintenanceRecord AS a LEFT JOIN tblMaintenanceRecord AS b ON (DateSerial(Year(a.MaintDate),Month(a.MaintDate),1)=DateSerial(Year(b.MaintDate),1+Month(b.MaintDate),1)) AND (a.IDUniqueCarRecNo=b.IDUniqueCarRecNo
GROUP BY a.IDUniqueCarRecNo, Year(a.MaintDate), Month(a.MaintDate)

This code creates expressions for the Month and Year. The month is displayed, of course, as the month number (1-12). Let me know if there's any way for me to do this... Thanks!!!

Ken Snell · Dec 29, 2003

Try this:

SELECT a.IDUniqueCarRecNo, Year(a.MaintDate), Format(a.MaintDate, "mmmm") AS
MonthName, MAX(a.OdometerRead) AS MaxofCurrentMonth, MAX(b.OdometerRead) AS
MaxofPreviousMonth
FROM tblMaintenanceRecord AS a LEFT JOIN tblMaintenanceRecord AS b ON
(DateSerial(Year(a.MaintDate),Month(a.MaintDate),1)=DateSerial(Year(b.MaintD
ate),1+Month(b.MaintDate),1)) AND (a.IDUniqueCarRecNo=b.IDUniqueCarRecNo)
GROUP BY a.IDUniqueCarRecNo, Year(a.MaintDate), Month(a.MaintDate);

--
Ken Snell
<MS ACCESS MVP>

cb said:
The query is SQL-specific. Can you tell me where I can set this up in the following code?

SELECT a.IDUniqueCarRecNo, Year(a.MaintDate), Month(a.MaintDate),

MAX(a.OdometerRead) AS MaxofCurrentMonth, MAX(b.OdometerRead) AS
MaxofPreviousMonth

FROM tblMaintenanceRecord AS a LEFT JOIN tblMaintenanceRecord AS b ON

(DateSerial(Year(a.MaintDate),Month(a.MaintDate),1)=DateSerial(Year(b.MaintD
ate),1+Month(b.MaintDate),1)) AND (a.IDUniqueCarRecNo=b.IDUniqueCarRecNo)

GROUP BY a.IDUniqueCarRecNo, Year(a.MaintDate), Month(a.MaintDate);

This code creates expressions for the Month and Year. The month is

displayed, of course, as the month number (1-12). Let me know if there's
any way for me to do this... Thanks!!!

Guest · Dec 29, 2003

Nope... gives me "Wrong number of arguments used with function in query expression" nice try though. sounded right to me. I tryed it as "m", "mmm", "mmmm" and all other forms of the month expression with and without the "", nothing. It all gives me the same message. Any other ideas?

Duane Hookom · Dec 29, 2003

Try:
SELECT a.IDUniqueCarRecNo, Year(a.MaintDate), Format(a.MaintDate, "mmmm") AS
MonthName, MAX(a.OdometerRead) AS MaxofCurrentMonth, MAX(b.OdometerRead) AS
MaxofPreviousMonth
FROM tblMaintenanceRecord AS a LEFT JOIN tblMaintenanceRecord AS b ON
(DateSerial(Year(a.MaintDate),Month(a.MaintDate),1)=DateSerial(Year(b.MaintD
ate),1+Month(b.MaintDate),1)) AND (a.IDUniqueCarRecNo=b.IDUniqueCarRecNo)
GROUP BY a.IDUniqueCarRecNo, Year(a.MaintDate), Format(a.MaintDate, "mmmm");

--
Duane Hookom
Microsoft Access MVP

cb said:
Nope... gives me "Wrong number of arguments used with function in query

expression" nice try though. sounded right to me. I tryed it as "m", "mmm",
"mmmm" and all other forms of the month expression with and without the "",
nothing. It all gives me the same message. Any other ideas?

Jim/Chris · Dec 29, 2003

Here are some date functions wich might help you

Month: Month([namessy]![date]) Number of month
day: Day([namessy]![date]) Numberof day
year: Year([namessy]![date]) number of year 4 digits
monthname: Format([date],"mmmm") Month alpha name complete
monthname: Format([date],"mmm") Month alpha name abbr
Dayname: Format([date],"dddd") Day alpha complete
Dayname: Format([date],"ddd") Day alpha abbr

Jim

-----Original Message-----
I've been trying to get my report to print a month name

(January, February, etc) instead of the month number that
is coming from my query. I created a table with the numbers
and the months and tried just adding a relationship between
the two fields (one in the table and one in the query),
then ran the report wizard to create the report selecting
the needed fields from the query and the month field from
the month table, but it gives me an error that says:"You
have chosen fields from record sources which the wizard
can't connect. You may have chosen fields from a table and
from a query based on that table. If so, try choosing
fields from only the table or only the query." I'm
confused... the months table is in no way used in the
creation of the query. Can you offer any suggestions or
solutions as to how I can get my report to just give me the
name of the months rather than the number? Please keep in
mind that I'm a reletive novice in Access. Thanks in advance!

Guest · Dec 29, 2003

Thanks for the try Duane! I almost thought that was going to work, but it gave me...

"The SELECT statement includes a reserved work or argument name that is mispelled or missing, or the punctuation is incorrect"

I've tried different combinations of ( ) and " " and haven't gotten it right yet. Is this really complex or am I going at this really backwards??? I feel like I'm not doing this the easy way, if there is such a thing.

Got any other suggestions... I'll keep hacking away at it. Thanks again!

Duane Hookom · Dec 29, 2003

MonthName() is probably a function in the version of Access that you are
using. Try:

SELECT a.IDUniqueCarRecNo, Year(a.MaintDate),
Format(a.MaintDate, "mmmm") AS MthName,
MAX(a.OdometerRead) AS MaxofCurrentMonth,
MAX(b.OdometerRead) AS MaxofPreviousMonth
FROM tblMaintenanceRecord AS a LEFT JOIN tblMaintenanceRecord AS b ON
(DateSerial(Year(a.MaintDate),Month(a.MaintDate),1)=DateSerial(Year(b.MaintD
ate),1+Month(b.MaintDate),1)) AND (a.IDUniqueCarRecNo=b.IDUniqueCarRecNo)
GROUP BY a.IDUniqueCarRecNo, Year(a.MaintDate), Format(a.MaintDate, "mmmm");

--
Duane Hookom
Microsoft Access MVP

cb said:
Thanks for the try Duane! I almost thought that was going to work, but it gave me...

"The SELECT statement includes a reserved work or argument name that is

mispelled or missing, or the punctuation is incorrect"

I've tried different combinations of ( ) and " " and haven't gotten it

right yet. Is this really complex or am I going at this really backwards???
I feel like I'm not doing this the easy way, if there is such a thing.

Guest · Dec 29, 2003

YOU WIN!!!!! Finally, something that you think should be so simple and just one little thing throws you into a big mess! Someday I'll understand all of this. Until then, I thank GOD for this discussion group! If I could I'd buy you a pizza!

THANK YOU , THANK YOU , THANK YOU!!!

Duane Hookom · Dec 29, 2003

I think there are pizza places around here (NW Wisconsin) that allow you to
order on-line. However, I appreciate the offer but the Atkins diet frowns on
the carbs :-(

--
Duane Hookom
Microsoft Access MVP

cb said:
YOU WIN!!!!! Finally, something that you think should be so simple and

just one little thing throws you into a big mess! Someday I'll understand
all of this. Until then, I thank GOD for this discussion group! If I could
I'd buy you a pizza!

month name vs month number

Guest

Fons Ponsioen

Guest

Ken Snell

Guest

Duane Hookom

Jim/Chris

Guest

Duane Hookom

Guest

Duane Hookom