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. -
-
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.
16 2038
Did you give a commit after running INSERT statement?
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
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
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,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) ?
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
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??
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
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??
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
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"
Where are you running it? I tested this on a sample table with smalldatetime field and it worked.
-- CK
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
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.
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
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 ?
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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). ...
|
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...
|
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)...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
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...
| |