Moving Records from one table to another that meet a date between critiria

[metalheadstorm]

ok i think this question should go here seeming its more based around SQl code rather than the vb code.

Heres my problem i have a program in vb6 with a acees backend, the table "Invoice" in the Db has about 38500 records 9 and will go up)which seems to be over the limit that a vb data grid can display. As when i view the data grid it starts out invoice_Id 84 rather than 1, but that is now fixed as i just did an Order BY ASC so thats ok

The problem is i want to archive all the invoices with the "Invoice_Da te" less than or greater than two dates a user inputs eg 01/01/1997 -> 01/01/2007 in dd/mm/yyyy format

i have tried many different ways of doing this and one that works is my using a loop and searching each record if its invoice_date matches the criteria.

 
Dim InvNum As Long
Dim InvRec As Long
ArchProgressBar.Min = 0 
ArchProgressBar.Max = InvoiceRS.RecordCount
 
InvDateFrom = Format(Txt_ArchiveDateFrom.Text, "dd/mm/yyyy")
InvDateTill = Format(Txt_ArchiveDateTill.Text, "dd/mm/yyyy")
 
InvoiceSQL = "SELECT * FROM Invoice WHERE Invoice_Date >= 01/01/1996 ORDER BY Invoice_ID ASC"
OpenInvoiceDB (InvoiceSQL)
InvoiceArchSQL = "SELECT * FROM Invoice_Archive"
OpenInvoiceArchDB (InvoiceArchSQL)
InvoiceRS.MoveFirst
InvNum = InvoiceRS.RecordCount
 
 
 
Do Until InvRec = InvNum
For InvRec = 0 To InvNum
 
If (InvoiceRS("Invoice_Date") >= DateValue(InvDateFrom)) And (InvoiceRS("Invoice_Date") <= DateValue(InvDateTill)) Then
InvoiceArchRS.AddNew
InvoiceArchRS("Invoice_ID") = InvoiceRS("Invoice_ID")
InvoiceArchRS("Invoice_Date") = InvoiceRS("Invoice_Date")
InvoiceArchRS("Customer_ID") = InvoiceRS("Customer_ID")
InvoiceArchRS("Customer_Name") = InvoiceRS("Customer_Name")
InvoiceArchRS.Update
 
 
ArchProgressBar.Value = ArchProgressBar.Value + 1
 
Arch_lblPercent.Caption = "Archiving " & Int(ArchProgressBar.Value * 100 / ArchProgressBar.Max) & "% Done"
Arch_lblPercent.Refresh
Lbl_ArchBarCurrentRec.Caption = "Warning! This will take some time, current record:" & ArchProgressBar.Value
InvoiceRS.MoveNext
 
Next InvRec
End If
Loop
 

this is very long-winded and im sure can be done must faster with a SQl statement so this is what i came up with

 
InvoiceSQL = "SELECT * FROM Invoice WHERE (Invoice_Date >= " & DateValue(InvDateFrom) & ") AND (Invoice_Date <= " & DateValue(InvDateTill) & ") ORDER BY Invoice_ID ASC"
OpenInvoiceDB (InvoiceSQL)
 
 
'OR 
 
InvoiceSQL = "SELECT * FROM Invoice WHERE (Invoice_date BETWEEN " & DateValue(InvDateFrom) & " AND " & DateValue(InvDateTill) & ") ORDER BY Invoice_ID ASC"
OpenInvoiceDB (InvoiceSQL) 
 

These both produce a recordcount of 0

but there is deffinatly records that meet that critria.

I also found a way so use INSERT INTO

such as

 
InvoiceSQL = "Insert Into Invoice_Archive " _
& " Select * From Invoice " _
& " Where Invoice_Date BETWEEN " & InvDateFrom & " AND " & InvDateTill & ""
OpenInvoiceDB (InvoiceSQL)
 

But this time the code runs but doesnt actually move anything :S

So can someone give me a few pointers or correct my code plz :P
Thx in advance.

[metalheadstorm]

Try converting those dates into a date datatype. Something like:

SELECT *
FROM Invoice
WHERE Invoice_Date BETWEEN cast('01/01/1997' as datetime) AND cast('01/01/2007' as datetime)

-- CK

says error in syntax (missing operator) and highlights the first "as"

[ck9663]

Where are you running it? I tested this on a sample table with smalldatetime field and it worked.

-- CK

[metalheadstorm]

Where are you running it? I tested this on a sample table with smalldatetime field and it worked.

-- CK

in the query builder for access, in sql view

my Invoice_Date field in the table Invoice is date/time, format short date

[FredSovenix]

Try this:

InvoiceSQL = "Insert Into Invoice_Archive " _
& " Select * From Invoice " _
& " Where Invoice_Date BETWEEN #" & InvDateFrom & "# AND #" & InvDateTill & "#"

In Access, dates must be surrounded by "#" or your date will get parsed as a division statement (1 divided by 1 divided by 2007) which returns a numeric value, which will compare the date against the numeric value of the date (dates can be cast to numbers such that 1/1/1900 12:00 AM = 1.00000).

Check to see whether BETWEEN...AND is inclusive, as well (I don't remember), and if not, use >= and <= instead.

[metalheadstorm]

Try this:

InvoiceSQL = "Insert Into Invoice_Archive " _
& " Select * From Invoice " _
& " Where Invoice_Date BETWEEN #" & InvDateFrom & "# AND #" & InvDateTill & "#"

In Access, dates must be surrounded by "#" or your date will get parsed as a division statement (1 divided by 1 divided by 2007) which returns a numeric value, which will compare the date against the numeric value of the date (dates can be cast to numbers such that 1/1/1900 12:00 AM = 1.00000).

Check to see whether BETWEEN...AND is inclusive, as well (I don't remember), and if not, use >= and <= instead.

HAzAHH! Ty FredSovenix the # worked perfectly :P

and yes the BETWEEN... AND inst inclusive so i used >=, <=

 
InvoiceSQL = " Select * From Invoice " _
& " Where (Invoice_Date >= #" & InvDateFrom & "#) AND (Invoice_Date <= #" & InvDateTill & "#) ORDER BY Invoice_ID ASC"
OpenInvoiceDB (InvoiceSQL)
 

ill just add the INSERT now and try it out :D

[metalheadstorm]

Ok the insert works now, the last thing is that i need to delete all the records that i sent to the Invoice_Archive from the Invoice table :D

how do i go about doing this ?

[metalheadstorm]

Ok the insert works now, the last thing is that i need to delete all the records that i sent to the Invoice_Archive from the Invoice table :D

how do i go about doing this ?

Nvm i think i know how