Hi folks, I'm drawing a blank on what could be a simple issue. I want to
create a report that groups events by month, where each month has a
header with the month name and all records for that month are sorted by
day in ascending order. The table currently collects abbreviated month
names (Jan, Feb...).

Any suggestions are welcome!

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Neil,
Access 2007 and later.
Assuming that the report source has the date field that you wish to group by, in design view for the report, choose "Group & Sort" option in the Design menu.
Choose 'Group on' option for the date field and the 'by month' option.
Use the 'Sort by' option to sort by date field; 'oldest to newest'.
In the header for the date field group, insert a text control with a source, =Format$([DateField],"mmmm") to extract the month name. Or =Format$([DateField],"mmm") to get the abbreviated month name.


Mike P.
11/9/20

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

If you don't have a date field to draw from (maybe reconsider the table design)...
You can convert the Short name to a number by writing your own function.

This is one copied from: https://www.microsoftaccessexpert.com/Microsoft-Access-Code-Month-To-Number.aspx

Public Function ChangeToMonth(sMonth As String) As Integer
Select Case sMonth
Case "Jan"
ChangeToMonth = 1
Case "Feb"
ChangeToMonth = 2
Case "Mar"
ChangeToMonth = 3
Case "Apr"
ChangeToMonth = 4
Case "May"
ChangeToMonth = 5
Case "Jun"
ChangeToMonth = 6
Case "Jul"
ChangeToMonth = 7
Case "Aug"
ChangeToMonth = 8
Case "Sep"
ChangeToMonth = 9
Case "Oct"
ChangeToMonth = 10
Case "Nov"
ChangeToMonth = 11
Case "Dec"
ChangeToMonth = 12
Case Else
ChangeToMonth = 0
End Select
End Function




On Friday, September 11, 2020 at 10:47:18 PM UTC+10, Neil wrote:

On 9/11/2020 6:35 AM, Mike P wrote:

Hi Neil,
Access 2007 and later.
Assuming that the report source has the date field that you wish to group by, in design view for the report, choose "Group & Sort" option in the Design menu.
Choose 'Group on' option for the date field and the 'by month' option.
Use the 'Sort by' option to sort by date field; 'oldest to newest'.
In the header for the date field group, insert a text control with a source, =Format$([DateField],"mmmm") to extract the month name. Or =Format$([DateField],"mmm") to get the abbreviated month name.

Mike P.
11/9/20

Thanks for the thoughts, Mike. I did try that, but the problem is that
the month field uses the abbreviated month name (Jan, Feb, etc.), so the
sort order arranges the months alphabetically (Apr, Aug, Feb, etc.).

I also tried a few other methods to establish the month number from the month's abbreviated name, but haven't found a way to do that yet. For example, I tried to establish a field that looked up the table.month and convert it to the month number, but that failed with the message that it couldn't find the table.month field (!)

Question re: Group & Sort
Is there a way to use SQL to select records with a specific value, such
as =SELECT * FROM [table] WHERE [table.month] = "Jan" ? If so, I would
then have to leave blanks where there are no events in table.month

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/11/2020 6:35 AM, Mike P wrote:

Hi Neil,
Access 2007 and later.
Assuming that the report source has the date field that you wish to group by, in design view for the report, choose "Group & Sort" option in the Design menu.
Choose 'Group on' option for the date field and the 'by month' option.
Use the 'Sort by' option to sort by date field; 'oldest to newest'.
In the header for the date field group, insert a text control with a source, =Format$([DateField],"mmmm") to extract the month name. Or =Format$([DateField],"mmm") to get the abbreviated month name.

Mike P.
11/9/20

Thanks for the thoughts, Mike. I did try that, but the problem is that
the month field uses the abbreviated month name (Jan, Feb, etc.), so the
sort order arranges the months alphabetically (Apr, Aug, Feb, etc.).

I also tried a few other methods to establish the month number from the
month's abbreviated name, but haven't found a way to do that yet. For
example, I tried to establish a field that looked up the table.month and convert it to the month number, but that failed with the message that it couldn't find the table.month field (!)

Question re: Group & Sort
Is there a way to use SQL to select records with a specific value, such
as =SELECT * FROM [table] WHERE [table.month] = "Jan" ? If so, I would
then have to leave blanks where there are no events in table.month

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, September 11, 2020 at 11:06:13 PM UTC+10, mal....@gmail.com wrote: Sorry - hit POST a bit quick.

I would have this function happening at the Query that feeds the report, not on the report itself.
Then you will have a field to group/sort on in the usual way.

If you don't have a date field to draw from (maybe reconsider the table design)...
You can convert the Short name to a number by writing your own function.

This is one copied from: https://www.microsoftaccessexpert.com/Microsoft-Access-Code-Month-To-Number.aspx

Public Function ChangeToMonth(sMonth As String) As Integer
Select Case sMonth
Case "Jan"
ChangeToMonth = 1
Case "Feb"
ChangeToMonth = 2
Case "Mar"
ChangeToMonth = 3
Case "Apr"
ChangeToMonth = 4
Case "May"
ChangeToMonth = 5
Case "Jun"
ChangeToMonth = 6
Case "Jul"
ChangeToMonth = 7
Case "Aug"
ChangeToMonth = 8
Case "Sep"
ChangeToMonth = 9
Case "Oct"
ChangeToMonth = 10
Case "Nov"
ChangeToMonth = 11
Case "Dec"
ChangeToMonth = 12
Case Else
ChangeToMonth = 0
End Select
End Function
On Friday, September 11, 2020 at 10:47:18 PM UTC+10, Neil wrote:

On 9/11/2020 6:35 AM, Mike P wrote:

Hi Neil,
Access 2007 and later.
Assuming that the report source has the date field that you wish to group by, in design view for the report, choose "Group & Sort" option in the Design menu.
Choose 'Group on' option for the date field and the 'by month' option. Use the 'Sort by' option to sort by date field; 'oldest to newest'.
In the header for the date field group, insert a text control with a source, =Format$([DateField],"mmmm") to extract the month name. Or =Format$([DateField],"mmm") to get the abbreviated month name.

Mike P.
11/9/20

Thanks for the thoughts, Mike. I did try that, but the problem is that
the month field uses the abbreviated month name (Jan, Feb, etc.), so the sort order arranges the months alphabetically (Apr, Aug, Feb, etc.).

I also tried a few other methods to establish the month number from the month's abbreviated name, but haven't found a way to do that yet. For example, I tried to establish a field that looked up the table.month and convert it to the month number, but that failed with the message that it couldn't find the table.month field (!)

Question re: Group & Sort
Is there a way to use SQL to select records with a specific value, such
as =SELECT * FROM [table] WHERE [table.month] = "Jan" ? If so, I would
then have to leave blanks where there are no events in table.month

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/11/2020 9:06 AM, mal....@gmail.com wrote:

If you don't have a date field to draw from (maybe reconsider the table design)...
You can convert the Short name to a number by writing your own function.

This is one copied from: https://www.microsoftaccessexpert.com/Microsoft-Access-Code-Month-To-Number.aspx

Public Function ChangeToMonth(sMonth As String) As Integer
Select Case sMonth
Case "Jan"
ChangeToMonth = 1
Case "Feb"
ChangeToMonth = 2
Case "Mar"
ChangeToMonth = 3
Case "Apr"
ChangeToMonth = 4
Case "May"
ChangeToMonth = 5
Case "Jun"
ChangeToMonth = 6
Case "Jul"
ChangeToMonth = 7
Case "Aug"
ChangeToMonth = 8
Case "Sep"
ChangeToMonth = 9
Case "Oct"
ChangeToMonth = 10
Case "Nov"
ChangeToMonth = 11
Case "Dec"
ChangeToMonth = 12
Case Else
ChangeToMonth = 0
End Select
End Function

Thanks... I've used this technique in other situations, but then what?
If the Group & Sort allowed it, I could also do something like:

SELECT * FROM table WHERE table.month = "Jan"

But I haven't been able to get that to work, either.

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/11/2020 9:09 AM, mal....@gmail.com wrote:

On Friday, September 11, 2020 at 11:06:13 PM UTC+10, mal....@gmail.com wrote: Sorry - hit POST a bit quick.

I would have this function happening at the Query that feeds the report, not on the report itself.
Then you will have a field to group/sort on in the usual way.

Thanks again for the idea, I'll look into this.

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

You could try:-
SELECT * , datepart("m","1 " & month ) as MonthNumber FROM [table]
to give the month number. Then group on MonthNumber in the report.
The group header would just need the month field.

Mike P.
11/9/20

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/11/2020 10:09 AM, Mike P wrote:

You could try:-
SELECT * , datepart("m","1 " & month ) as MonthNumber FROM [table]
to give the month number. Then group on MonthNumber in the report.
The group header would just need the month field.

Mike P.
11/9/20

Thanks, I'll look into datepart() to see how that works.

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

To get the numerical value of a month, 1 ... 12, use
month(aDate)

Put this into your query and then sort on it. You may need to place on the report in which case make it invisible

Jim


On Friday, 11 September 2020 16:08:15 UTC+1, Neil wrote:

On 9/11/2020 10:09 AM, Mike P wrote:

You could try:-
SELECT * , datepart("m","1 " & month ) as MonthNumber FROM [table]
to give the month number. Then group on MonthNumber in the report.
The group header would just need the month field.

Mike P.
11/9/20

Thanks, I'll look into datepart() to see how that works.

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, September 11, 2020 at 8:06:13 AM UTC-5, mal....@gmail.com wrote:

If you don't have a date field to draw from (maybe reconsider the table design)...
You can convert the Short name to a number by writing your own function.

This is one copied from: https://www.microsoftaccessexpert.com/Microsoft-Access-Code-Month-To-Number.aspx

Public Function ChangeToMonth(sMonth As String) As Integer
Select Case sMonth
Case "Jan"
ChangeToMonth = 1
Case "Feb"
ChangeToMonth = 2
Case "Mar"
ChangeToMonth = 3
Case "Apr"
ChangeToMonth = 4
Case "May"
ChangeToMonth = 5
Case "Jun"
ChangeToMonth = 6
Case "Jul"
ChangeToMonth = 7
Case "Aug"
ChangeToMonth = 8
Case "Sep"
ChangeToMonth = 9
Case "Oct"
ChangeToMonth = 10
Case "Nov"
ChangeToMonth = 11
Case "Dec"
ChangeToMonth = 12
Case Else
ChangeToMonth = 0
End Select
End Function
On Friday, September 11, 2020 at 10:47:18 PM UTC+10, Neil wrote:

On 9/11/2020 6:35 AM, Mike P wrote:

Hi Neil,
Access 2007 and later.
Assuming that the report source has the date field that you wish to group by, in design view for the report, choose "Group & Sort" option in the Design menu.
Choose 'Group on' option for the date field and the 'by month' option. Use the 'Sort by' option to sort by date field; 'oldest to newest'.
In the header for the date field group, insert a text control with a source, =Format$([DateField],"mmmm") to extract the month name. Or =Format$([DateField],"mmm") to get the abbreviated month name.

Mike P.
11/9/20

Thanks for the thoughts, Mike. I did try that, but the problem is that
the month field uses the abbreviated month name (Jan, Feb, etc.), so the sort order arranges the months alphabetically (Apr, Aug, Feb, etc.).

I also tried a few other methods to establish the month number from the month's abbreviated name, but haven't found a way to do that yet. For example, I tried to establish a field that looked up the table.month and convert it to the month number, but that failed with the message that it couldn't find the table.month field (!)

Question re: Group & Sort
Is there a way to use SQL to select records with a specific value, such
as =SELECT * FROM [table] WHERE [table.month] = "Jan" ? If so, I would
then have to leave blanks where there are no events in table.month

--
best regards,

Neil

This will also return month

Month(Datevalue(sMonth & " 01 2020")

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/14/2020 8:05 AM, Ron Paii wrote:

On Friday, September 11, 2020 at 8:06:13 AM UTC-5, mal....@gmail.com wrote:

If you don't have a date field to draw from (maybe reconsider the table design)...
You can convert the Short name to a number by writing your own function.

This is one copied from:
https://www.microsoftaccessexpert.com/Microsoft-Access-Code-Month-To-Number.aspx

Public Function ChangeToMonth(sMonth As String) As Integer
Select Case sMonth
Case "Jan"
ChangeToMonth = 1
Case "Feb"
ChangeToMonth = 2
Case "Mar"
ChangeToMonth = 3
Case "Apr"
ChangeToMonth = 4
Case "May"
ChangeToMonth = 5
Case "Jun"
ChangeToMonth = 6
Case "Jul"
ChangeToMonth = 7
Case "Aug"
ChangeToMonth = 8
Case "Sep"
ChangeToMonth = 9
Case "Oct"
ChangeToMonth = 10
Case "Nov"
ChangeToMonth = 11
Case "Dec"
ChangeToMonth = 12
Case Else
ChangeToMonth = 0
End Select
End Function
On Friday, September 11, 2020 at 10:47:18 PM UTC+10, Neil wrote:

On 9/11/2020 6:35 AM, Mike P wrote:

Hi Neil,
Access 2007 and later.
Assuming that the report source has the date field that you wish to group by, in design view for the report, choose "Group & Sort" option in the Design menu.
Choose 'Group on' option for the date field and the 'by month' option. >>>> Use the 'Sort by' option to sort by date field; 'oldest to newest'.
In the header for the date field group, insert a text control with a source, =Format$([DateField],"mmmm") to extract the month name. Or =Format$([DateField],"mmm") to get the abbreviated month name.


Mike P.
11/9/20

Thanks for the thoughts, Mike. I did try that, but the problem is that
the month field uses the abbreviated month name (Jan, Feb, etc.), so the >>> sort order arranges the months alphabetically (Apr, Aug, Feb, etc.).

I also tried a few other methods to establish the month number from the
month's abbreviated name, but haven't found a way to do that yet. For
example, I tried to establish a field that looked up the table.month and >>> convert it to the month number, but that failed with the message that it >>> couldn't find the table.month field (!)

Question re: Group & Sort
Is there a way to use SQL to select records with a specific value, such
as =SELECT * FROM [table] WHERE [table.month] = "Jan" ? If so, I would
then have to leave blanks where there are no events in table.month

--
best regards,

Neil

This will also return month

Month(Datevalue(sMonth & " 01 2020")

These return the month name given a date value, which is the reverse of
what I need to do: given the abbreviated month name, I need to return
the month number. There are no date values involved.

The VB function described above by Mal is a familiar solution, but I
haven't found a way to insert it in a report without Access tossing some
error.

Thanks to all for the thoughts!

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, September 15, 2020 at 4:33:39 PM UTC+1, Neil wrote:

On 9/14/2020 8:05 AM, Ron Paii wrote:

On Friday, September 11, 2020 at 8:06:13 AM UTC-5, mal....@gmail.com wrote:

If you don't have a date field to draw from (maybe reconsider the table design)...
You can convert the Short name to a number by writing your own function. >>
This is one copied from:
https://www.microsoftaccessexpert.com/Microsoft-Access-Code-Month-To-Number.aspx

Public Function ChangeToMonth(sMonth As String) As Integer
Select Case sMonth
Case "Jan"
ChangeToMonth = 1
Case "Feb"
ChangeToMonth = 2
Case "Mar"
ChangeToMonth = 3
Case "Apr"
ChangeToMonth = 4
Case "May"
ChangeToMonth = 5
Case "Jun"
ChangeToMonth = 6
Case "Jul"
ChangeToMonth = 7
Case "Aug"
ChangeToMonth = 8
Case "Sep"
ChangeToMonth = 9
Case "Oct"
ChangeToMonth = 10
Case "Nov"
ChangeToMonth = 11
Case "Dec"
ChangeToMonth = 12
Case Else
ChangeToMonth = 0
End Select
End Function
On Friday, September 11, 2020 at 10:47:18 PM UTC+10, Neil wrote:

On 9/11/2020 6:35 AM, Mike P wrote:

Hi Neil,
Access 2007 and later.
Assuming that the report source has the date field that you wish to group by, in design view for the report, choose "Group & Sort" option in the Design menu.
Choose 'Group on' option for the date field and the 'by month' option. >>>> Use the 'Sort by' option to sort by date field; 'oldest to newest'.
In the header for the date field group, insert a text control with a source, =Format$([DateField],"mmmm") to extract the month name. Or =Format$([DateField],"mmm") to get the abbreviated month name.


Mike P.
11/9/20

Thanks for the thoughts, Mike. I did try that, but the problem is that >>> the month field uses the abbreviated month name (Jan, Feb, etc.), so the >>> sort order arranges the months alphabetically (Apr, Aug, Feb, etc.).

I also tried a few other methods to establish the month number from the >>> month's abbreviated name, but haven't found a way to do that yet. For
example, I tried to establish a field that looked up the table.month and >>> convert it to the month number, but that failed with the message that it >>> couldn't find the table.month field (!)

Question re: Group & Sort
Is there a way to use SQL to select records with a specific value, such >>> as =SELECT * FROM [table] WHERE [table.month] = "Jan" ? If so, I would >>> then have to leave blanks where there are no events in table.month

--
best regards,

Neil

This will also return month

Month(Datevalue(sMonth & " 01 2020")

These return the month name given a date value, which is the reverse of
what I need to do: given the abbreviated month name, I need to return
the month number. There are no date values involved.

The VB function described above by Mal is a familiar solution, but I
haven't found a way to insert it in a report without Access tossing some error.

Thanks to all for the thoughts!

--
best regards,

Neil

Hi Neil,

Have you overlooked my suggestion, datepart("m","1 " & month ) ?

Mike P.
15/9/20

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/15/2020 12:36 PM, Mike P wrote:

Hi Neil,

Have you overlooked my suggestion, datepart("m","1 " & month ) ?

Mike P.
15/9/20

Hi Mike,

Thanks for your suggestion. I didn't overlook it, however, I'm not clear
about its use. In your example it appears that the [datefield] has to
supply the number "1", but the table contains no such field.

When looking into it at:

<https://support.microsoft.com/en-us/office/datepart-function-26868a79-5505-4e5a-8905-6001372223fa>

I didn't see an example of its use where the [datefield] did not contain
a value in date format or a portion thereof.

If I've misinterpreted your suggestion, please enlighten me!

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, September 15, 2020 at 6:46:41 PM UTC+1, Neil wrote:

On 9/15/2020 12:36 PM, Mike P wrote:

Hi Neil,

Have you overlooked my suggestion, datepart("m","1 " & month ) ?

Mike P.
15/9/20

Hi Mike,

Thanks for your suggestion. I didn't overlook it, however, I'm not clear about its use. In your example it appears that the [datefield] has to
supply the number "1", but the table contains no such field.

When looking into it at:

<https://support.microsoft.com/en-us/office/datepart-function-26868a79-5505-4e5a-8905-6001372223fa>

I didn't see an example of its use where the [datefield] did not contain
a value in date format or a portion thereof.

If I've misinterpreted your suggestion, please enlighten me!

--
best regards,

Neil

Hi Neil,

Adapt the source query for your report as follows:-
SELECT * , datepart("m","1 " & [table.month] ) as MonthNumber FROM [table]
to give the month number (value 1 to 12) in the column, MonthNumber.
Then group on MonthNumber in the report.
The group header would just need the month field.

DatePart Function Explanation - the first argument ("m") dictates the part of the date to be returned; the month number in this case.
The second argument ("1 " & month) is a date, but if the year part of the date is not supplied, then the current year is assumed. So the date argument is formed by concatenating 1 (literal in quotes) and the name of the month (Jan through to Dec) held
in the month field and the current year assumed. For example, "1 Sep 2020" where the value of month in the report source is "Sep".
You can test the function with any arguments in the Immediate Window (Ctrl+G) in the Development Environment (VBA).
Type ?datepart("m","1 Jan") and press Enter in the Immedite Window. This will return 1.
Type ?datepart("m","1 Dec") and press Enter in the Immedite Window. This will return 12.

Mike P.
15/9/20

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/15/2020 2:46 PM, Mike P wrote:

On Tuesday, September 15, 2020 at 6:46:41 PM UTC+1, Neil wrote:

On 9/15/2020 12:36 PM, Mike P wrote:

Hi Neil,

Have you overlooked my suggestion, datepart("m","1 " & month ) ?

Mike P.
15/9/20

Hi Mike,

Thanks for your suggestion. I didn't overlook it, however, I'm not clear
about its use. In your example it appears that the [datefield] has to
supply the number "1", but the table contains no such field.

When looking into it at:

<https://support.microsoft.com/en-us/office/datepart-function-26868a79-5505-4e5a-8905-6001372223fa>

I didn't see an example of its use where the [datefield] did not contain
a value in date format or a portion thereof.

If I've misinterpreted your suggestion, please enlighten me!

--
best regards,

Neil

Hi Neil,

Adapt the source query for your report as follows:-
SELECT * , datepart("m","1 " & [table.month] ) as MonthNumber FROM [table]
to give the month number (value 1 to 12) in the column, MonthNumber.
Then group on MonthNumber in the report.
The group header would just need the month field.

DatePart Function Explanation - the first argument ("m") dictates the part of the date to be returned; the month number in this case.
The second argument ("1 " & month) is a date, but if the year part of the date is not supplied, then the current year is assumed. So the date argument is formed by concatenating 1 (literal in quotes) and the name of the month (Jan through to Dec) held

in the month field and the current year assumed. For example, "1 Sep 2020" where the value of month in the report source is "Sep".

You can test the function with any arguments in the Immediate Window (Ctrl+G) in the Development Environment (VBA).
Type ?datepart("m","1 Jan") and press Enter in the Immedite Window. This will return 1.
Type ?datepart("m","1 Dec") and press Enter in the Immedite Window. This will return 12.

Mike P.
15/9/20

Thanks again, Mike. I'll explore this approach and let you know how it goes!

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/15/2020 3:30 PM, Neil wrote:

On 9/15/2020 2:46 PM, Mike P wrote:

On Tuesday, September 15, 2020 at 6:46:41 PM UTC+1, Neil wrote:

On 9/15/2020 12:36 PM, Mike P wrote:

Hi Neil,

Have you overlooked my suggestion, datepart("m","1 " & month ) ?

Mike P.
15/9/20

Hi Mike,

Thanks for your suggestion. I didn't overlook it, however, I'm not clear >>> about its use. In your example it appears that the [datefield] has to
supply the number "1", but the table contains no such field.

When looking into it at:

<https://support.microsoft.com/en-us/office/datepart-function-26868a79-5505-4e5a-8905-6001372223fa>


I didn't see an example of its use where the [datefield] did not contain >>> a value in date format or a portion thereof.

If I've misinterpreted your suggestion, please enlighten me!

--
best regards,

Neil

Hi Neil,

Adapt the source query for your report as follows:-
SELECT * , datepart("m","1 " & [table.month] ) as MonthNumber FROM
[table]
to give the month number (value 1 to 12) in the column, MonthNumber.
Then group on MonthNumber in the report.
The group header would just need the month field.

DatePart Function Explanation - the first argument ("m") dictates the
part of the date to be returned; the month number in this case.
The second argument ("1 " & month) is a date, but if the year part of
the date is not supplied, then the current year is assumed.  So the
date argument is formed by concatenating 1 (literal in quotes) and the
name of the month (Jan through to Dec) held in the month field and the
current year assumed. For example, "1 Sep 2020" where the value of
month in the report source is "Sep".
You can test the function with any arguments in the Immediate Window
(Ctrl+G) in the Development Environment (VBA).
Type ?datepart("m","1 Jan") and press Enter in the Immedite Window.
This will return 1.
Type ?datepart("m","1 Dec") and press Enter in the Immedite Window.
This will return 12.

Mike P.
15/9/20

Thanks again, Mike. I'll explore this approach and let you know how it
goes!

Thanks for the clarification, Mike, your approach worked perfectly!

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)