Date Formatting

[Guest]

I have a query that sums daily oil production and groups it by month. This is
a make table query. I use DateValue(Format([Date],"mm/yyyy")) to group the
date by month. In the table the field for the date still shows a day value
(i.e. 1/1/2006). This is okay but I need the field to show the last day of
each month (i.e. 1/31/2006) not the first day. Anyone know how to do this?

 

[Neil Sunderland]

I have a query that sums daily oil production and groups it by month. This is
a make table query. I use DateValue(Format([Date],"mm/yyyy")) to group the
date by month. In the table the field for the date still shows a day value
(i.e. 1/1/2006). This is okay but I need the field to show the last day of
each month (i.e. 1/31/2006) not the first day. Anyone know how to do this?

DateValue(DateAdd("m",1,DateValue(Format([date_set],"mm/yyyy")))-1)

(In other words, the day before the first day of the next month)

 

[Guest]

Thanks Neil. I don't know that I would ever have come up with that solution.

I have a query that sums daily oil production and groups it by month. This is
a make table query. I use DateValue(Format([Date],"mm/yyyy")) to group the
date by month. In the table the field for the date still shows a day value
(i.e. 1/1/2006). This is okay but I need the field to show the last day of
each month (i.e. 1/31/2006) not the first day. Anyone know how to do this?

DateValue(DateAdd("m",1,DateValue(Format([date_set],"mm/yyyy")))-1)

(In other words, the day before the first day of the next month)

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

 

[fredg]

I have a query that sums daily oil production and groups it by month. This is
a make table query. I use DateValue(Format([Date],"mm/yyyy")) to group the
date by month. In the table the field for the date still shows a day value
(i.e. 1/1/2006). This is okay but I need the field to show the last day of
each month (i.e. 1/31/2006) not the first day. Anyone know how to do this?

1) It really shouldn't matter. No one should be 'looking' at the table
anyway. A control on a form can show just the month/year.
Set the Format property of the control to:
mm/yyyy

2) A Date datatype must b a valid date, containing month, day, and
year. You can use VBA to change a date to the last day of the month
whenever it is entered into your database.

Day 0 of the following month is the last day of the current month.

Code the AfterUpdate event of the control used for data entry:

Me![DateField] = DateSerial(Year([DateField]),Month([DateField])+1,0)

To change existing entered dates, create an Update query:
Update YourTable Set [DateField] = DateSerial(Year([DateField]),
Month(DateField])+1,0);

3) Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[Dale Fye]

Another, much cleaner, option would be:

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

This will give you the last day of the month for your [DateField].

I hope you don't actually have a field in your table called [Date]. If you
do, I would change it to something else. "Date" is a reserved word in
Access and using fields with that name may eventually lead to problems as
Access/JET attempt to interpret your SQL.

BTW, If you feel changing the field name would be difficult, you should
look into getting Speed Ferret.

HTH
Dale

 

[Guest]

Dale,

Thanks. I like this solution much better. It's easier to understand.

Dan

Another, much cleaner, option would be:

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

This will give you the last day of the month for your [DateField].

I hope you don't actually have a field in your table called [Date]. If you
do, I would change it to something else. "Date" is a reserved word in
Access and using fields with that name may eventually lead to problems as
Access/JET attempt to interpret your SQL.

BTW, If you feel changing the field name would be difficult, you should
look into getting Speed Ferret.

HTH
Dale

I have a query that sums daily oil production and groups it by month. This
is
a make table query. I use DateValue(Format([Date],"mm/yyyy")) to group the
date by month. In the table the field for the date still shows a day value
(i.e. 1/1/2006). This is okay but I need the field to show the last day of
each month (i.e. 1/31/2006) not the first day. Anyone know how to do
this?