Reformat date to the first of the month

  • Thread starter Thread starter FL610
  • Start date Start date
F

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!
 
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
 
Something like the following in a query will convert your dates to the first
day of that month.

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

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

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


Jerry Whittle said:
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.

FL610 said:
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!
Click to expand...
Click to expand...
 
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 !

Jerry Whittle said:
Mistake!

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

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


Jerry Whittle said:
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.

FL610 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
FL610 said:
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 !

Jerry Whittle said:
Mistake!

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

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


Jerry Whittle said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top