473,322 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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
16 2038
amitpatel66
2,367 Expert 2GB
Did you give a commit after running INSERT statement?
Mar 11 '08 #2
ck9663
2,878 Expert 2GB
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
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
2,367 Expert 2GB
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
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
2,367 Expert 2GB
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
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
2,367 Expert 2GB
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
2,878 Expert 2GB
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
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
2,878 Expert 2GB
Where are you running it? I tested this on a sample table with smalldatetime field and it worked.

-- CK
Mar 12 '08 #12
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
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
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
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
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

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

Similar topics

0
by: RT | last post by:
Hello; I'm trying to create a updates pages that I can move either to the previous record or the next record within a recordset without having to return to the repeating list of records after...
2
by: M Wells | last post by:
Hi All, I need to change a column value in several thousand records in a table and output a list of the record ids of the records I've updated into another table. The table, however, is being...
6
by: Matt K. | last post by:
Hi there, I have a form in an Access project that contains a subform which displays the results of a query of the style "select * from where = #a certain date#". In the main part of the form...
2
by: No Spam | last post by:
To fellow Access 2K users: I have run into a newbie situation where I have been assigned to create an Access database to act as a schedule. Basically, it is a very simple setup: the end...
6
by: Robin S. | last post by:
**Eric and Salad - thank you both for the polite kick in the butt. I hope I've done a better job of explaining myself below. I am trying to produce a form to add products to a table (new...
2
by: Jason | last post by:
I have a table of 650,000 records associated to contracts. Some of the contracts have multiple records but each contract only has one Active Record (there might be several inactive records). ...
9
by: Sharktyyfa | last post by:
Hi, hoping someone can help. Access 2003, WinXP. I have built a database that uses synchro to co-ordinate with the mothership. All is well. The person i built it for wants the satellites...
11
by: shriil | last post by:
Hi I have this database that calculates and stores the incentive amount earned by employees of a particular department. Each record is entered by entering the Date, Shift (morn, eve, or night)...
3
by: c0l0nelFlagg | last post by:
I have a moving dispatcher database. There are 99 drivers, 99 loaders, and 50 different vehicles. The scheduler database is built on a 13 4 week month year so that it can be used repeatedly in any...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.