Adding a date field for last month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex - 25 May 2005 21:22 GMT

I have an append query that adds a date field then appends it to a table. It
works fine for every month except Jan. In Feb., when I ran the query, it
added Jan/2005 instead of Jan/2004. Do you know what I'm doing wrong? Thanks

Date: Format(DateSerial(Year(Date()),Month(Date())-1,1),"mm/yy")

Reply to this Message

Rick B - 25 May 2005 21:31 GMT

Just FYI, you would not typically store a calcualted field in a table. What
are you trying to accomplish?

Normally, you would store the current date and then perform any needed
calcualtions in your queries, reports, or forms.

Signature

Rick B

> I have an append query that adds a date field then appends it to a table. It
> works fine for every month except Jan. In Feb., when I ran the query, it
> added Jan/2005 instead of Jan/2004. Do you know what I'm doing wrong? Thanks
>
> Date: Format(DateSerial(Year(Date()),Month(Date())-1,1),"mm/yy")

Reply to this Message

Alex - 25 May 2005 21:51 GMT

I have to store it in a table because I'm importing .txt files every month
that don't have a date. So, I have to import the text file, give it a date
and append it to the main table that keeps a year's worth of data.

> Just FYI, you would not typically store a calcualted field in a table. What
> are you trying to accomplish?
[quoted text clipped - 9 lines]
> >
> > Date: Format(DateSerial(Year(Date()),Month(Date())-1,1),"mm/yy")

Reply to this Message

John Spencer (MVP) - 26 May 2005 01:50 GMT

I don't understand. IF you want the previous month to the current month, then
why isn't Jan 2005 the previous month to Feb 2005?

Also, note that Date is a dangerous name to use for a field. IT can be confused
with the DATE function. And in addition Jan 2005 is NOT a date since there is
not day component. You appear to be storing a string consisting of a two-digits
for the month, a slash, and two-digits for the year.

Reply to this Message

Alex - 02 Jun 2005 17:37 GMT

I'm sorry John. Let me start over - I understand that using Date as the name
is dangerous; I'll change that.

I need to import .txt and .xls files at the beginning of each month. The
files do not include a date field. I import the .txt and .xls files into
TableNew. I then have an append query that add the records from TableNew to
TableMain (which contains records from several previous months). There is a
field in TableMain called Date.

In my append query I have a column: Date:
Format(DateSerial(Year(Date()),Month(Date())-1,1),"mm/yy"), which will add
last month's date to the Date field in TableMain.

It works fine, except if the append query is run in the month of Jan. When
I ran it in Jan 2005, for example, the Date field should have been populated
with Dec. 2004, but instead it was populated with Dec 2005.

I can see in my expression that I haven't subtracted a year like I
subtracted the month. I'm not sure how to re-write the expression
subtracting the year if the current month happens to be January.

I hope that makes more sense. Thanks again -

> I don't understand. IF you want the previous month to the current month, then
> why isn't Jan 2005 the previous month to Feb 2005?
[quoted text clipped - 9 lines]
> >
> > Date: Format(DateSerial(Year(Date()),Month(Date())-1,1),"mm/yy")

Reply to this Message

John Spencer (MVP) - 03 Jun 2005 00:03 GMT

Well, you don't need to subtract anything from the year. DateSerial will handle
subtracting months and moving to the previous year as required. Something is
wrong here, but I'm not sure what.

Am I correct in my assumption that your field named Date is a text field? If
not, and it is a field of type Date, then the problem is that Access is trying
to interpret "12/05" as a date. When it does, the algorithm apparently goes.
12 - a valid month, 05 a valid day, and there is no year, so I'll give it the
current year - 2005. So it will store 12/05/2005.

I would store the ENTIRE Date without applying the format. I would guess that
IF your field is date type, you will actually find all the data is stored as a
date on the 4th day of the month.

> I'm sorry John. Let me start over - I understand that using Date as the name
> is dangerous; I'll change that.
[quoted text clipped - 32 lines]
> > >
> > > Date: Format(DateSerial(Year(Date()),Month(Date())-1,1),"mm/yy")

Reply to this Message

Alex - 09 Jun 2005 21:18 GMT

Thanks John, I think you've put me on the right track. I'm guessing that I
have the format as 12/05 and it's seeing 12 as a valid month, 05 as a valid
day and no year, therefore giving it the current year (as you stated). I'm
way too tired to work on this today, but will look at it tomorrow. I really
appreciate your help.

> Well, you don't need to subtract anything from the year. DateSerial will handle
> subtracting months and moving to the previous year as required. Something is
[quoted text clipped - 46 lines]
> > > >
> > > > Date: Format(DateSerial(Year(Date()),Month(Date())-1,1),"mm/yy")

Reply to this Message