righteo.......

i created a booking system for my business and i want to create an sql query so that when an appointment has passed [using system date as todays date] it is discarded and moved to another table which is the history table.

heres my coding for it

Dim conn
Dim strsql As String
Set conn = CreateObject("ADODB.Connection")
conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\Mydatabase.mdb;Persist Security Info=False")
strsql = "INSERT INTO [historytable] ([field1], [field2], [field3], [dateofappointment], [field5], [field6]) SELECT [field1], [field2], [field3], [dateofappointment], [field5], [field6] From [originialtable] Where [dateofappointment] < Date"
conn.Execute strSql
End Sub



i want to click a commannd button then original table is scanned for any dates which have passed - and then the ones which have passed - those records are then inserted into history.

but some reason i get problem and it says no value given to one or more required parameters.

so im a tad stuck any help?

Just a possibility, you may have to use Date() for the system date, as opposed to just plain Date.

Date isn't being used as a function because this is done in VB and not the Query Builder

strSQL = "...Where [dateofappointment] < #" & Date$ & "#"

I think that's the correct way, not sure about the pounds though. I'd try them without it first. Hope this helps.

Date isn't being used as a function because this is done in VB and not the Query Builder

But if Date is used as part of SQL (left inside the quotes like in the code example), the parenthesis are needed.