How to replace a date in an insert query?

[Leo Hamal]

Hello again,

I am trying to insert th last date and last balance for a given month/year,
from one table to another with the following query :

INSERT INTO TempBalance4 ( Date4, Balance4 )
SELECT TOP 1 Q.date AS Date4, Q.NewBal AS Balance4
FROM TbSav2 AS Q
WHERE (((Month([Q].[date]))<=[Forms]![FMonthParm]![Mth])
AND ((Year([Q].[date]))<=[Forms]![FYearParm]![Yr]))
ORDER BY Q.date DESC;

This works fine but now I would like to change the date4 to to-days date
with the last date of the month when the data I get is from the previous
month(s).

Example :

1. At the end of january I get the result 8/1/2008 & 1000.

2. At the end of february I get the same result 8/1/2008 & 1000, but would
like it to insert like 29/2/2008 & 1000 instead.

Any ideas out there to accomplish this.

Thanks,
Leo.

 

[Klatuu]

DateSerial(Year(date),Month(date)+1,0) AS Date4

Will return the last day of the current month.

 

[Leo Hamal]

Thanks Dave,

But is there any way I can get that in the query?

Leo.

DateSerial(Year(date),Month(date)+1,0) AS Date4

Will return the last day of the current month.
--
Dave Hargis, Microsoft Access MVP

Hello again,

I am trying to insert th last date and last balance for a given month/year,
from one table to another with the following query :

INSERT INTO TempBalance4 ( Date4, Balance4 )
SELECT TOP 1 Q.date AS Date4, Q.NewBal AS Balance4
FROM TbSav2 AS Q
WHERE (((Month([Q].[date]))<=[Forms]![FMonthParm]![Mth])
AND ((Year([Q].[date]))<=[Forms]![FYearParm]![Yr]))
ORDER BY Q.date DESC;

This works fine but now I would like to change the date4 to to-days date
with the last date of the month when the data I get is from the previous
month(s).

Example :

1. At the end of january I get the result 8/1/2008 & 1000.

2. At the end of february I get the same result 8/1/2008 & 1000, but would
like it to insert like 29/2/2008 & 1000 instead.

Any ideas out there to accomplish this.

Thanks,
Leo.

 

[Klatuu]

Yes:

INSERT INTO TempBalance4 ( Date4, Balance4 )
SELECT TOP 1 DateSerial(Year(date),Month(date)+1,0) AS Date4
, Q.NewBal AS Balance4
FROM TbSav2 AS Q
WHERE (((Month([Q].[date]))<=[Forms]![FMonthParm]![Mth])
AND ((Year([Q].[date]))<=[Forms]![FYearParm]![Yr]))
ORDER BY Q.date DESC;

--
Dave Hargis, Microsoft Access MVP

Thanks Dave,

But is there any way I can get that in the query?

Leo.

DateSerial(Year(date),Month(date)+1,0) AS Date4

Will return the last day of the current month.
--
Dave Hargis, Microsoft Access MVP

Hello again,

I am trying to insert th last date and last balance for a given month/year,
from one table to another with the following query :

INSERT INTO TempBalance4 ( Date4, Balance4 )
SELECT TOP 1 Q.date AS Date4, Q.NewBal AS Balance4
FROM TbSav2 AS Q
WHERE (((Month([Q].[date]))<=[Forms]![FMonthParm]![Mth])
AND ((Year([Q].[date]))<=[Forms]![FYearParm]![Yr]))
ORDER BY Q.date DESC;

This works fine but now I would like to change the date4 to to-days date
with the last date of the month when the data I get is from the previous
month(s).

Example :

1. At the end of january I get the result 8/1/2008 & 1000.

2. At the end of february I get the same result 8/1/2008 & 1000, but would
like it to insert like 29/2/2008 & 1000 instead.

Any ideas out there to accomplish this.

Thanks,
Leo.

 

[John W. Vinson]

Thanks Dave,

But is there any way I can get that in the query?

Ummm... yes. Put it in the query as a calculated field - just put Dave's
expression in a vacant Field cell in the query design grid.

 

[Leo Hamal]

Dave,

Thanks again this solved my problem.

Never knew you can put a field in a select statement that doesn't belong to
a table.

Leo.

Yes:

INSERT INTO TempBalance4 ( Date4, Balance4 )
SELECT TOP 1 DateSerial(Year(date),Month(date)+1,0) AS Date4
, Q.NewBal AS Balance4
FROM TbSav2 AS Q
WHERE (((Month([Q].[date]))<=[Forms]![FMonthParm]![Mth])
AND ((Year([Q].[date]))<=[Forms]![FYearParm]![Yr]))
ORDER BY Q.date DESC;

--
Dave Hargis, Microsoft Access MVP

Thanks Dave,

But is there any way I can get that in the query?

Leo.

DateSerial(Year(date),Month(date)+1,0) AS Date4

Will return the last day of the current month.
--
Dave Hargis, Microsoft Access MVP


:

Hello again,

I am trying to insert th last date and last balance for a given month/year,
from one table to another with the following query :

INSERT INTO TempBalance4 ( Date4, Balance4 )
SELECT TOP 1 Q.date AS Date4, Q.NewBal AS Balance4
FROM TbSav2 AS Q
WHERE (((Month([Q].[date]))<=[Forms]![FMonthParm]![Mth])
AND ((Year([Q].[date]))<=[Forms]![FYearParm]![Yr]))
ORDER BY Q.date DESC;

This works fine but now I would like to change the date4 to to-days date
with the last date of the month when the data I get is from the previous
month(s).

Example :

1. At the end of january I get the result 8/1/2008 & 1000.

2. At the end of february I get the same result 8/1/2008 & 1000, but would
like it to insert like 29/2/2008 & 1000 instead.

Any ideas out there to accomplish this.

Thanks,
Leo.