By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

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

P: 84
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_Date" 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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim InvNum As Long
  3. Dim InvRec As Long
  4. ArchProgressBar.Min = 0 
  5. ArchProgressBar.Max = InvoiceRS.RecordCount
  6.  
  7. InvDateFrom = Format(Txt_ArchiveDateFrom.Text, "dd/mm/yyyy")
  8. InvDateTill = Format(Txt_ArchiveDateTill.Text, "dd/mm/yyyy")
  9.  
  10. InvoiceSQL = "SELECT * FROM Invoice WHERE Invoice_Date >= 01/01/1996 ORDER BY Invoice_ID ASC"
  11. OpenInvoiceDB (InvoiceSQL)
  12. InvoiceArchSQL = "SELECT * FROM Invoice_Archive"
  13. OpenInvoiceArchDB (InvoiceArchSQL)
  14. InvoiceRS.MoveFirst
  15. InvNum = InvoiceRS.RecordCount
  16.  
  17.  
  18.  
  19. Do Until InvRec = InvNum
  20. For InvRec = 0 To InvNum
  21.  
  22. If (InvoiceRS("Invoice_Date") >= DateValue(InvDateFrom)) And (InvoiceRS("Invoice_Date") <= DateValue(InvDateTill)) Then
  23. InvoiceArchRS.AddNew
  24. InvoiceArchRS("Invoice_ID") = InvoiceRS("Invoice_ID")
  25. InvoiceArchRS("Invoice_Date") = InvoiceRS("Invoice_Date")
  26. InvoiceArchRS("Customer_ID") = InvoiceRS("Customer_ID")
  27. InvoiceArchRS("Customer_Name") = InvoiceRS("Customer_Name")
  28. InvoiceArchRS.Update
  29.  
  30.  
  31. ArchProgressBar.Value = ArchProgressBar.Value + 1
  32.  
  33. Arch_lblPercent.Caption = "Archiving " & Int(ArchProgressBar.Value * 100 / ArchProgressBar.Max) & "% Done"
  34. Arch_lblPercent.Refresh
  35. Lbl_ArchBarCurrentRec.Caption = "Warning! This will take some time, current record:" & ArchProgressBar.Value
  36. InvoiceRS.MoveNext
  37.  
  38. Next InvRec
  39. End If
  40. Loop
  41.  
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


Expand|Select|Wrap|Line Numbers
  1.  
  2. InvoiceSQL = "SELECT * FROM Invoice WHERE (Invoice_Date >= " & DateValue(InvDateFrom) & ") AND (Invoice_Date <= " & DateValue(InvDateTill) & ") ORDER BY Invoice_ID ASC"
  3. OpenInvoiceDB (InvoiceSQL)
  4.  
  5.  
  6. 'OR 
  7.  
  8. InvoiceSQL = "SELECT * FROM Invoice WHERE (Invoice_date BETWEEN " & DateValue(InvDateFrom) & " AND " & DateValue(InvDateTill) & ") ORDER BY Invoice_ID ASC"
  9. OpenInvoiceDB (InvoiceSQL) 
  10.  
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
Expand|Select|Wrap|Line Numbers
  1.  
  2. InvoiceSQL = "Insert Into Invoice_Archive " _
  3. & " Select * From Invoice " _
  4. & " Where Invoice_Date BETWEEN " & InvDateFrom & " AND " & InvDateTill & ""
  5. OpenInvoiceDB (InvoiceSQL)
  6.  
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.
Mar 11 '08 #1
Share this Question
Share on Google+
16 Replies


amitpatel66
Expert 100+
P: 2,367
Did you give a commit after running INSERT statement?
Mar 11 '08 #2

ck9663
Expert 2.5K+
P: 2,878
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  InvoiceSQL = "Insert Into Invoice_Archive " _
  3.       & " Select * From Invoice " _
  4.       & " Where Invoice_Date BETWEEN " & InvDateFrom &
  5.                 " AND " & InvDateTill & ""      
  6. 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
Mar 11 '08 #3

P: 84
Did you give a commit after running INSERT statement?
sorry i dont know what you meen :S


Heres an example without the INSERT

Expand|Select|Wrap|Line Numbers
  1.  
  2. InvoiceSQL = "SELECT * FROM Invoice WHERE Invoice_Date BETWEEN " & InvDateFrom & " AND " & InvDateTill & ""
  3. OpenInvoiceDB (InvoiceSQL)
  4. testreccount = InvoiceRS.RecordCount
  5. MsgBox ("Count " & testreccount & "")
  6.  
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
Mar 12 '08 #4

amitpatel66
Expert 100+
P: 2,367
sorry i dont know what you meen :S


Heres an example without the INSERT

Expand|Select|Wrap|Line Numbers
  1.  
  2. InvoiceSQL = "SELECT * FROM Invoice WHERE Invoice_Date BETWEEN " & InvDateFrom & " AND " & InvDateTill & ""
  3. OpenInvoiceDB (InvoiceSQL)
  4. testreccount = InvoiceRS.RecordCount
  5. MsgBox ("Count " & testreccount & "")
  6.  
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,UPDATE 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) ?
Mar 12 '08 #5

P: 84
After performing any INSERT,DELETE,UPDATE 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
Mar 12 '08 #6

amitpatel66
Expert 100+
P: 2,367
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??
Mar 12 '08 #7

P: 84
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
Mar 12 '08 #8

amitpatel66
Expert 100+
P: 2,367
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??
Mar 12 '08 #9

ck9663
Expert 2.5K+
P: 2,878
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:


Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Invoice
  3. WHERE Invoice_Date BETWEEN cast('01/01/1997' as datetime) AND cast('01/01/2007' as datetime)

-- CK
Mar 12 '08 #10

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


Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Invoice
  3. 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"
Mar 12 '08 #11

ck9663
Expert 2.5K+
P: 2,878
Where are you running it? I tested this on a sample table with smalldatetime field and it worked.

-- CK
Mar 12 '08 #12

P: 84
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
Mar 12 '08 #13

P: 10
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.
Mar 13 '08 #14

P: 84
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 >=, <=

Expand|Select|Wrap|Line Numbers
  1.  
  2. InvoiceSQL = " Select * From Invoice " _
  3. & " Where (Invoice_Date >= #" & InvDateFrom & "#) AND (Invoice_Date <= #" & InvDateTill & "#) ORDER BY Invoice_ID ASC"
  4. OpenInvoiceDB (InvoiceSQL)
  5.  
ill just add the INSERT now and try it out :D
Mar 13 '08 #15

P: 84
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 ?
Mar 13 '08 #16

P: 84
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
Mar 14 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.