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.

[amitpatel66]

Did you give a commit after running INSERT statement?

[ck9663]

I think this is an Access question, not an SQL-Server. But t-sql are almost identical so I'll try to give my two cents.

You have this code:

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

Find a way display the value of InvoiceSQL variable, like MessageBox maybe. Paste it in your query builder (i don't know how it's called in Access).

Try executing the SELECT first. Remove or comment-out the insert part first. If there are rows returned by your SELECT, then include the insert. If there are no rows returned by your select, then check your data, the WHERE part is not being fulfilled.

-- CK

[metalheadstorm]

Did you give a commit after running INSERT statement?

sorry i dont know what you meen :S


Heres an example without the INSERT

 
InvoiceSQL = "SELECT * FROM Invoice WHERE Invoice_Date BETWEEN " & InvDateFrom & " AND " & InvDateTill & ""
OpenInvoiceDB (InvoiceSQL)
testreccount = InvoiceRS.RecordCount
MsgBox ("Count " & testreccount & "")
 

This also produces no records

Heres a example of some data

Invoice_Id Invoice_Date Customer_ID Customer_Name
1 24/10/2001 OLEADE01 MR DENIS O LEARY

InvDateFrom = 01/01/1997
InvDateTill = 01/01/2007

TF 24/10/2001 is between InvDateFrom and InvDateTill

[amitpatel66]

sorry i dont know what you meen :S


Heres an example without the INSERT

 
InvoiceSQL = "SELECT * FROM Invoice WHERE Invoice_Date BETWEEN " & InvDateFrom & " AND " & InvDateTill & ""
OpenInvoiceDB (InvoiceSQL)
testreccount = InvoiceRS.RecordCount
MsgBox ("Count " & testreccount & "")
 

This also produces no records

Heres a example of some data

Invoice_Id Invoice_Date Customer_ID Customer_Name
1 24/10/2001 OLEADE01 MR DENIS O LEARY

InvDateFrom = 01/01/1997
InvDateTill = 01/01/2007

TF 24/10/2001 is between InvDateFrom and InvDateTill

After performing any INSERT,DELETE,U PDATE on database, COMMIT needs to be done in order to make the changes permanent.

ANyways your SELECT itself does not return any record even you have the data, so it is the problem with the select query. Are all the dates being passed in proper format (DD/MM/YYYY) ?

[metalheadstorm]

After performing any INSERT,DELETE,U PDATE on database, COMMIT needs to be done in order to make the changes permanent.

ANyways your SELECT itself does not return any record even you have the data, so it is the problem with the select query. Are all the dates being passed in proper format (DD/MM/YYYY) ?

Yes it is in DD/MM/YYYY InvDateFrom/Till is in DD/MM/YYYY

and in access it is in short date format so they both match

[amitpatel66]

Yes it is in DD/MM/YYYY InvDateFrom/Till is in DD/MM/YYYY

and in access it is in short date format so they both match

Did you try to execute your select query in Access DB instead of from your VB application? Does that give you the records??

[metalheadstorm]

this is what i have

SELECT *
FROM Invoice
WHERE Invoice_Date BETWEEN 01/01/1997 AND 01/01/2007;

and that doesnt return any either

[amitpatel66]

this is what i have

SELECT *
FROM Invoice
WHERE Invoice_Date BETWEEN 01/01/1997 AND 01/01/2007;

and that doesnt return any either

You need to enclose your dates with single quotes or something??

[ck9663]

this is what i have

SELECT *
FROM Invoice
WHERE Invoice_Date BETWEEN 01/01/1997 AND 01/01/2007;

and that doesnt return any either

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