Combing fields

[mike02]

Hello,
I have a Query the i need to update a table. Now my one table has the
following fields:
ID
Name
Date
Return

Now i need the date and


return to update from another table with these fields:
ID

Name
Month
Year (I know year is an important word in
access)
Return

I need to group the Month and year into one field from
this past table. I have been trying to use a query but i cant get them to group
to give the month end date. I am able to get it to group like this, for example.
May, 2004.
Is there a way to get the year, and month to group in this format
dd/mm/yyyy? It needs to be the last day of the every month. any ideas, thanks!

[JoeM]

Typically, any calculation which can be derived off of existing table values should NOT be stored back to the field. There is usually no reason to, because it can be calculated by the query, and you can usually use a query for most anything that you use a table for (reports, exports, etc).

To get the Month End Date of any Date field, you could use a Calculated Field in a query like this:

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

[mike02]

I dont understand how the would grab the data from the fields already, I doesnt work btw. Ive tried to modify Using the DateSerial Function, but i am unable to retrieve the month, and year from the current fields. The reason i have it appending to another table is I have to break it up into month and year to create column table. and i need it in dd/mm/yyyy form in the other form to creat my graphs, I have to cross tab query to creae my column chart and i had to break up the months as columns and years as rows. i need the dat for the graphs i have create.

This is what i want.
Name..Month..Year..Expr
aaaa..June....2004...30/06/2004

The expr field will be the expresssion. I need it to combine the month and year, as well as use the last day of each month. thanks for helping much appreciated,

Michael

[JoeM]

So I think I misunderstood your original question. Are you saying that you currently have a "Month" field and a "Year" field, and from those you want to create a date that is the Month End of those two fields? If so, then that should be pretty easy:

MonthEndDate:DateSerial([YearField],[MonthField]+1,0)

The logic is this. By adding one to the Month field, you are going in to the next month. But by setting the Day to be zero, is goes back to the last day of the previous month. That way you don't need to worry about how many days each months has, the system will figure that out on its own.

[mike02]

Thank you i really appreciate your assistance.

[mike02]

It was working great, all i did was save and close the query, then i opened it again to append data to a new table and I reciece #error in my field, I dont see how its dividing anything by zero:S

[JoeM]

How are the fields you are trying to write the data to formatted?
Do you have any blanks, zeroes, or invalid entries in your Month/Year fields?

[mike02]

They are formatted as text for month, and number for year. When i first strarted this project i inputted them as that, now that i ve been doing it for a few months i feel that i need to update somethings to make the database better. do you think this is necesarry in the instance?

[JoeM]

You need to make sure that the format of the fields in your Append Query matches the format of each field in the table you are writing them to.
It is very important to consider each field format carefully. Note that if you format numeric fields as text, it is going to make it much harder to do mathematical computations on them.

If you see some issues with your structure, yes, I would recommend making changes it. It might be a bit of burden now, but it will save you headaches down the road.