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.

[CODE=Vb]
Dim InvNum As Long
Dim InvRec As Long
ArchProgressBar .Min = 0
ArchProgressBar .Max = InvoiceRS.Recor dCount

InvDateFrom = Format(Txt_Arch iveDateFrom.Tex t, "dd/mm/yyyy")
InvDateTill = Format(Txt_Arch iveDateTill.Tex t, "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 "
OpenInvoiceArch DB (InvoiceArchSQL )
InvoiceRS.MoveF irst
InvNum = InvoiceRS.Recor dCount



Do Until InvRec = InvNum
For InvRec = 0 To InvNum

If (InvoiceRS("Inv oice_Date") >= DateValue(InvDa teFrom)) And (InvoiceRS("Inv oice_Date") <= DateValue(InvDa teTill)) Then
InvoiceArchRS.A ddNew
InvoiceArchRS(" Invoice_ID") = InvoiceRS("Invo ice_ID")
InvoiceArchRS(" Invoice_Date") = InvoiceRS("Invo ice_Date")
InvoiceArchRS(" Customer_ID") = InvoiceRS("Cust omer_ID")
InvoiceArchRS(" Customer_Name") = InvoiceRS("Cust omer_Name")
InvoiceArchRS.U pdate


ArchProgressBar .Value = ArchProgressBar .Value + 1

Arch_lblPercent .Caption = "Archiving " & Int(ArchProgres sBar.Value * 100 / ArchProgressBar .Max) & "% Done"
Arch_lblPercent .Refresh
Lbl_ArchBarCurr entRec.Caption = "Warning! This will take some time, current record:" & ArchProgressBar .Value
InvoiceRS.MoveN ext

Next InvRec
End If
Loop
[/CODE]

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


[CODE=Vb]
InvoiceSQL = "SELECT * FROM Invoice WHERE (Invoice_Date >= " & DateValue(InvDa teFrom) & ") AND (Invoice_Date <= " & DateValue(InvDa teTill) & ") ORDER BY Invoice_ID ASC"
OpenInvoiceDB (InvoiceSQL)


'OR

InvoiceSQL = "SELECT * FROM Invoice WHERE (Invoice_date BETWEEN " & DateValue(InvDa teFrom) & " AND " & DateValue(InvDa teTill) & ") ORDER BY Invoice_ID ASC"
OpenInvoiceDB (InvoiceSQL)
[/CODE]

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
[CODE=Vb]
InvoiceSQL = "Insert Into Invoice_Archive " _
& " Select * From Invoice " _
& " Where Invoice_Date BETWEEN " & InvDateFrom & " AND " & InvDateTill & ""
OpenInvoiceDB (InvoiceSQL)
[/CODE]

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

[CODE=Vb]
InvoiceSQL = "SELECT * FROM Invoice WHERE Invoice_Date BETWEEN " & InvDateFrom & " AND " & InvDateTill & ""
OpenInvoiceDB (InvoiceSQL)
testreccount = InvoiceRS.Recor dCount
MsgBox ("Count " & testreccount & "")
[/CODE]

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

[CODE=Vb]
InvoiceSQL = "SELECT * FROM Invoice WHERE Invoice_Date BETWEEN " & InvDateFrom & " AND " & InvDateTill & ""
OpenInvoiceDB (InvoiceSQL)
testreccount = InvoiceRS.Recor dCount
MsgBox ("Count " & testreccount & "")
[/CODE]

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

[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 >=, <=

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

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