insert query

[madrav72]

hi all

have a table containing records of rents which are paid on a monthly basis from the first day til the last day what i need to do is to initiate some query to find the latest months transaction sat Feb then do an INSERT into next month which is March

to make a demonstration of what i mean say we have the latest record for Joe's Octobers rent which is from 01/10/02 to 31/10/02

then i press a button to inittiate automation to run next months rents on all records - bear in mind that this query needs to locate all the latest months records then work with that. so for example the query should not pick out January of February for Joe cos his latest record is October. get it?

then Joes latest record will now be November 01 to 30th
and all other info such as rent is always the same as in previous months record.

sounds fairly straight forward but iaam having some probs with it.

I am doing in Access 2000 but will need it to work in VB to Access 2000 and hence ADO.

i need to know how to do this thing in SQL please !

thanks all

 

[simongallop]

Presuming that you want to show all records from the previous month so:

SELECT * FROM MyTable WHERE [StartDateFieldName] = format("01/" & format(dateadd('m',-1,Now()),"mm/yy"),"dd/mm/yy");

HTH