Incrementing a month value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have written a module in Access 2000 with DAO 3.6 referenced, that creates a Table, then runs a loop that the user inputs a number of months that depreciation will be applied, and a starting Transaction Date.

This loop writes a SQL statement to populate the table with two fields: a Transaction Number, and a Transaction Date: strSQL = "INSERT INTO Ass2Depcn ( [TA#], TADate )SELECT " & strTA & " AS [TA#], " & Format(datTADate, "Short Date") & " AS TADate;

With each loop I have used the stored procedure DateAdd to increase the next statement by one month:
datTADate = DateAdd("m", 1, datTADate

When I run this in the immediate window I get the correct result (NB I am an Aussie), ie
1 31/12/200
2 30/1/200
3 28/2/200

But when I run the module into the table I get the date field decreasing in value starting at 1/1/1900 and by seconds at a time. Even though the table is formatted to display in Short Date, it displays time values

Hope you can help, thanks in advance
 
Mark White said:
I have written a module in Access 2000 with DAO 3.6 referenced, that
Click to expand...
creates a Table, then runs a loop that the user inputs a number of months
that depreciation will be applied, and a starting Transaction Date.
This loop writes a SQL statement to populate the table with two fields: a
Click to expand...
Transaction Number, and a Transaction Date: strSQL = "INSERT INTO
Ass2Depcn ( [TA#], TADate )SELECT " & strTA & " AS [TA#], " &
Format(datTADate, "Short Date") & " AS TADate;"
With each loop I have used the stored procedure DateAdd to increase the next statement by one month:
datTADate = DateAdd("m", 1, datTADate)


When I run this in the immediate window I get the correct result (NB I am an Aussie), ie.
1 31/12/2003
2 30/1/2004
3 28/2/2004

But when I run the module into the table I get the date field decreasing
Click to expand...
in value starting at 1/1/1900 and by seconds at a time. Even though the
table is formatted to display in Short Date, it displays time values.
Hope you can help, thanks in advance
Click to expand...

Hi Mark

try (text will wrap)

strSQL = "INSERT INTO Ass2Depcn ( [TA#], TADate ) VALUES (" & strTA & ", #"
& Format(Date, "d mmmm yyyy") & "#);"

if TA# is a text field, enclose strTA with single quotes as below (I
wondered about this since you store the TA# in strTA (string?) which is
strange if TA# is a numeric field)
strSQL = "INSERT INTO Ass2Depcn ( [TA#], TADate ) VALUES ('" & strTA & "',
#" & Format(Date, "d mmmm yyyy") & "#);"

Regards - Joe
 
Thanks Joe

The inclusion of the Value procedure was a great help. Fixe

cheer

Mark
----- Joe Black wrote: ----


Mark White said:
I have written a module in Access 2000 with DAO 3.6 referenced, tha
Click to expand...
creates a Table, then runs a loop that the user inputs a number of month
that depreciation will be applied, and a starting Transaction DateTransaction Number, and a Transaction Date: strSQL = "INSERT INT
Ass2Depcn ( [TA#], TADate )SELECT " & strTA & " AS [TA#], "
Format(datTADate, "Short Date") & " AS TADate;
next statement by one month
datTADate = DateAdd("m", 1, datTADate an Aussie), ie
1 31/12/200
2 30/1/200
3 28/2/200
Click to expand...
in value starting at 1/1/1900 and by seconds at a time. Even though th
table is formatted to display in Short Date, it displays time values
Hi Mar

try (text will wrap

strSQL = "INSERT INTO Ass2Depcn ( [TA#], TADate ) VALUES (" & strTA & ", #
& Format(Date, "d mmmm yyyy") & "#);

if TA# is a text field, enclose strTA with single quotes as below (
wondered about this since you store the TA# in strTA (string?) which i
strange if TA# is a numeric field
strSQL = "INSERT INTO Ass2Depcn ( [TA#], TADate ) VALUES ('" & strTA & "'
#" & Format(Date, "d mmmm yyyy") & "#);

Regards - Jo
 
Back
Top