Use of MonthName (1 Viewer)

[Rmaster2022]

I've created a birthday report that groups birthdays by month, then sorts by day. I have a heading for each month written: =MonthName(DatePart("m",[DOB]))
DOB is date of birth. However I have a few null values for DOB, so, of course, the null records are grouped under the heading: #type!
Is there a way to write an expression so the heading is "Unknown" for a group of null values in the DOB field.

 

[theDBguy]

You could try:

=IIf(IsNull([DOB]),"Unknown",MonthName(DatePart("m",[DOB])))

 

[Galaxiom]

You could try:

=IIf(IsNull([DOB]),"Unknown",MonthName(DatePart("m",[DOB])))

IsNull is a VBA function. Better to use a database engine expression Is Null.

=IIf([DOB] Is Null,"Unknown",MonthName(DatePart("m",[DOB])))

BTW An alternative to MonthName(DatePart("m", DOB)) is

Format(DOB, "mmmm")

"mmmm" returns the full name
"mmm" returns the three letter abbreviation.

 

[ebs17]

I think a header should always have meaningful content. Since Monthname(null) generates an error, you could make sure up front that valid numbers from 1 to 12 are always passed.

To do this, you can use an auxiliary table to get the missing values into the query, like ...

SELECT
   Monthname(N.Number) AS Header,
   D.*
FROM
   NumberTable AS N
      LEFT JOIN Data AS D
      ON N.Number = Month(D.DOB)

 

[Rmaster2022]

Thank you all for your answers and help. They all worked, now is choosing a preference. I guess there is more than one way to skin a cat! (Apologies to our cat Gracie!)