Adding a date field for last month

[Guest]

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")

 

[Rick B]

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.

 

[Guest]

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.

 

[John Spencer (MVP)]

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.

 

[Guest]

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 -

 

[John Spencer (MVP)]

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.

 

[Guest]

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.