Reformat date to the first of the month

[FL610]

I have a list of maintenance events for several years, the events have taken
place on random days of every month, i need to count the events for each
month. With the current date format MM/DD/YYYY i get groupings of events for
that day of the month, and not just the month. I was thinking i'd reformat
the event date to the first of every month, any ideas on how to accomplish
this? Thank you in advance!

 

[Wayne-I-M]

Hi

You don't need to change the date. Just calculate on the month the date is in

In A form
=Month([DateField])

In A query
SELECT Month([YourTable]![Datefield]) AS MonthAsNumber,
MonthName(Month([YourTable]![Datefield])) AS MonthAsText
FROM YourTable;

Use these in a report to "sort by"

Good luck

 

[Jerry Whittle]

Something like the following in a query will convert your dates to the first
day of that month.

FOM: [DateField] - Day([DateField]+1

 

[Jerry Whittle]

Mistake!

FOM: [DateField] - Day([DateField])+1

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Something like the following in a query will convert your dates to the first
day of that month.

FOM: [DateField] - Day([DateField]+1
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a list of maintenance events for several years, the events have taken
place on random days of every month, i need to count the events for each
month. With the current date format MM/DD/YYYY i get groupings of events for
that day of the month, and not just the month. I was thinking i'd reformat
the event date to the first of every month, any ideas on how to accomplish
this? Thank you in advance!

 

[John W. Vinson]

Mistake!

FOM: [DateField] - Day([DateField])+1

Or (alternatively)

DateSerial(Year([DateField]), Month([DateField]), 1)

 

[FL610]

Thank you for the quick response! I tried the code below and it changed the
dates to the end of each month, which was still better than any progress i
was making. Using the shotgun approach I changed the '+' at the end to a '-'
and it works great !

Mistake!

FOM: [DateField] - Day([DateField])+1

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Something like the following in a query will convert your dates to the first
day of that month.

FOM: [DateField] - Day([DateField]+1
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a list of maintenance events for several years, the events have taken
place on random days of every month, i need to count the events for each
month. With the current date format MM/DD/YYYY i get groupings of events for
that day of the month, and not just the month. I was thinking i'd reformat
the event date to the first of every month, any ideas on how to accomplish
this? Thank you in advance!

 

[Jose armando Lopez]

Thank you for the quick response! I tried the code below and it changed the
dates to the end of each month, which was still better than any progress i
was making. Using the shotgun approach I changed the '+' at the end to a '-'
and it works great !

Mistake!

FOM: [DateField] - Day([DateField])+1

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Something like the following in a query will convert your dates to the first
day of that month.

FOM: [DateField] - Day([DateField]+1
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a list of maintenance events for several years, the events have taken
place on random days of every month, i need to count the events for each
month. With the current date format MM/DD/YYYY i get groupings of events for
that day of the month, and not just the month. I was thinking i'd reformat
the event date to the first of every month, any ideas on how to accomplish
this? Thank you in advance!