Move and Delete Records from Tables

[rj51cxa]

I have two queries which run consecutively from a command button. The first query, which opens with a message box asking me to select the months in question, copies the selected records from TblTranasctions to TblTranactions_Old. The second query, which opens with the same message box, deletes the selected records from TblTransactions.

The two codes are as follows:

QryMoveData:

INSERT INTO TblTransactions_Old ( ShtMonth, Type, Details, Amount, [Date] )
SELECT TblTransactions.ShtMonth, TblTransactions.Type, TblTransactions.Details, TblTransactions.Amount, TblTransactions.Date
FROM TblTransactions
WHERE (((IIf([ShtMonth]="Jan",1,IIf([ShtMonth]="Feb",2,IIf([ShtMonth]="Mar",3,IIf([ShtMonth]="Apr",4,IIf([ShtMonth]="May",5,IIf([ShtMonth]="Jun",6,IIf([ShtMonth]="Jul",7,IIf([ShtMonth]="Aug",8,IIf([ShtMonth]="Sep",9,IIf([ShtMonth]="Oct",10,IIf([ShtMonth]="Nov",11,IIf([ShtMonth]="Dec",12,""))))))))))))) Between [Enter Month 1] And [Enter Month 2]))
ORDER BY TblTransactions.Date;

QryDeleteData:

DELETE TblTransactions.TransactionNumber, TblTransactions.ShtMonth, TblTransactions.Type, TblTransactions.Details, TblTransactions.Amount, TblTransactions.Date, TblTransactions.Statement, TblTransactions.Notes, IIf([ShtMonth]="Jan",1,IIf([ShtMonth]="Feb",2,IIf([ShtMonth]="Mar",3,IIf([ShtMonth]="Apr",4,IIf([ShtMonth]="May",5,IIf([ShtMonth]="Jun",6,IIf([ShtMonth]="Jul",7,IIf([ShtMonth]="Aug",8,IIf([ShtMonth]="Sep",9,IIf([ShtMonth]="Oct",10,IIf([ShtMonth]="Nov",11,IIf([ShtMonth]="Dec",12,"")))))))))))) AS Expr1
FROM TblTransactions
WHERE (((IIf([ShtMonth]="Jan",1,IIf([ShtMonth]="Feb",2,IIf([ShtMonth]="Mar",3,IIf([ShtMonth]="Apr",4,IIf([ShtMonth]="May",5,IIf([ShtMonth]="Jun",6,IIf([ShtMonth]="Jul",7,IIf([ShtMonth]="Aug",8,IIf([ShtMonth]="Sep",9,IIf([ShtMonth]="Oct",10,IIf([ShtMonth]="Nov",11,IIf([ShtMonth]="Dec",12,""))))))))))))) Between [Enter Month 1] And [Enter Month 2]));

Can anyone please tell me if there is a way of combining these two queries into one piece of code so that I only have to enter the months to copy and delete once.

I would also like to know if the whole of the final code can be run as an event from a command button rather than having two queries which are run from the command button.

Best Regards
John

 

[traingamer]

Can anyone please tell me if there is a way of combining these two queries into one piece of code so that I only have to enter the months to copy and delete once.

No, but you can add two text boxes to your form and reference the form values in each query. e.g.

INSERT INTO TblTransactions_Old ( ShtMonth, Type, Details, Amount, [Date] )
...
WHERE (((IIf([ShtMonth]="Jan",1,IIf([ShtMonth]="Feb",2,IIf([ShtMonth]="Mar",3,IIf([ShtMonth]="Apr",4,IIf([ShtMonth]="May",5,IIf([ShtMonth]="Jun",6,IIf([ShtMonth]="Jul",7,IIf([ShtMonth]="Aug",8,IIf([ShtMonth]="Sep",9,IIf([ShtMonth]="Oct",10,IIf([ShtMonth]="Nov",11,IIf([ShtMonth]="Dec",12,""))))))))))))) Between [b][Forms]![frmSelectMonth]![txtMonth1].[Value] And [Forms]![frmSelectMonth]![txtMonth2].[Value]))[/b]
...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[rj51cxa]

Thanks Traingamer, I'll give that a try.

Best Regards
John