E
epimp702
I have a table with lots of entries (2000+). Some of them are related -
they have the same value in Quantity, Registrationnumber and Exdate but
differ in Source. I want to mark the related rows with a "Matched =
True".
Using the code below some match as expected whereas others return
completely different values of Quantity than the row has in the table.
It appears as if Access loses track of where it is at and just picks
another value of quantity from the table. I don't get why.
I hope this makes sense to you. I certainly understand if it does not.
I have now spent the entire day trying to figure out what's wrong - it
makes no sense to me at all.
Thank you in advance!
I have the following code:
Dim MyDB As DAO.Database, MyRec As DAO.Recordset, MySubRec As
DAO.Recordset
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset("SELECT * FROM Table WHERE Source =
'BEC' and Level=2")
While Not MyRec.EOF
sqlselectquery = "SELECT * FROM Table WHERE Source <> 'BEC' and
Quantitity = " & MyRec.Fields("Quantitity") & " and Registrationnumber
= '" &
MyRec.Fields("Registrationnumber") & "' and Exdate=#" &
MyRec.Fields("ExDate") & "#"
Set MySubRec = MyDB.OpenRecordset(sqlselectquery)
While Not MySubRec.EOF
sqlupdatequery = "UPDATE Table SET MatchFound = True WHERE
TableID = " & MySubRec.Fields("TableID")
DoCmd.SetWarnings False
DoCmd.RunSQL sqlupdatequery
DoCmd.SetWarnings True
MySubRec.MoveNext
Wend
MyRec.MoveNext
Wend
Here it matches 30195 with 32116 properly with this SQL query:
SELECT * FROM Table WHERE Source <> 'BEC' and Quantity = 350000 and
Registrationnumber = '005700785' and Exdate=#18-03-2003#
TableID QuantityRegistrationnumber CurrencyID ExDate PayDate YieldType Matched Source
30195 350000 005700785 EUR 18-03-2003 28-03-2003 UDB No BEC
31790 350000 005700785 EUR 18-03-2003 08-03-2005 RUS No BEC
32115 005700785 NLG 15-03-2004 No DAB
32116 350000 005700785 NLG 18-03-2003 Yes DAB
But here it makes the faulty notion that the Quantitiy of the row with
TableID 30319 is 7454 (as row with TableID 31762) thus resulting in
this query:
SELECT * FROM Table WHERE Source <> 'BEC' and Quantity = 7454 and
Registrationnumber = '005501202' and Exdate=#16-04-2003#
TableID QuantityRegistrationnumber CurrencyID ExDate PayDate YieldType Matched Source
30319 6581 005501202 EUR 16-04-2003 01-05-2003 UDB No BEC
32056 6581 005501202 FRF 16-04-2003 No DAB
31762 7454 005501202 EUR 16-04-2003 22-12-2004 RUS No BEC
YieldType RUS is present in both extractions of the table.
I only have little experience with VBA as I've mostly been working in
PHP/MySQL environments. My Linux using friends tell me that MS Access
is suffering from a data overload, that Microsoft software can't deal
with this kind of stuff. Is that really the case or is it me who's got
faulty code (I, personally, expect the latter to be the case).
I really hope you can help me out.
they have the same value in Quantity, Registrationnumber and Exdate but
differ in Source. I want to mark the related rows with a "Matched =
True".
Using the code below some match as expected whereas others return
completely different values of Quantity than the row has in the table.
It appears as if Access loses track of where it is at and just picks
another value of quantity from the table. I don't get why.
I hope this makes sense to you. I certainly understand if it does not.
I have now spent the entire day trying to figure out what's wrong - it
makes no sense to me at all.
Thank you in advance!
I have the following code:
Dim MyDB As DAO.Database, MyRec As DAO.Recordset, MySubRec As
DAO.Recordset
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset("SELECT * FROM Table WHERE Source =
'BEC' and Level=2")
While Not MyRec.EOF
sqlselectquery = "SELECT * FROM Table WHERE Source <> 'BEC' and
Quantitity = " & MyRec.Fields("Quantitity") & " and Registrationnumber
= '" &
MyRec.Fields("Registrationnumber") & "' and Exdate=#" &
MyRec.Fields("ExDate") & "#"
Set MySubRec = MyDB.OpenRecordset(sqlselectquery)
While Not MySubRec.EOF
sqlupdatequery = "UPDATE Table SET MatchFound = True WHERE
TableID = " & MySubRec.Fields("TableID")
DoCmd.SetWarnings False
DoCmd.RunSQL sqlupdatequery
DoCmd.SetWarnings True
MySubRec.MoveNext
Wend
MyRec.MoveNext
Wend
Here it matches 30195 with 32116 properly with this SQL query:
SELECT * FROM Table WHERE Source <> 'BEC' and Quantity = 350000 and
Registrationnumber = '005700785' and Exdate=#18-03-2003#
TableID QuantityRegistrationnumber CurrencyID ExDate PayDate YieldType Matched Source
30195 350000 005700785 EUR 18-03-2003 28-03-2003 UDB No BEC
31790 350000 005700785 EUR 18-03-2003 08-03-2005 RUS No BEC
32115 005700785 NLG 15-03-2004 No DAB
32116 350000 005700785 NLG 18-03-2003 Yes DAB
But here it makes the faulty notion that the Quantitiy of the row with
TableID 30319 is 7454 (as row with TableID 31762) thus resulting in
this query:
SELECT * FROM Table WHERE Source <> 'BEC' and Quantity = 7454 and
Registrationnumber = '005501202' and Exdate=#16-04-2003#
TableID QuantityRegistrationnumber CurrencyID ExDate PayDate YieldType Matched Source
30319 6581 005501202 EUR 16-04-2003 01-05-2003 UDB No BEC
32056 6581 005501202 FRF 16-04-2003 No DAB
31762 7454 005501202 EUR 16-04-2003 22-12-2004 RUS No BEC
YieldType RUS is present in both extractions of the table.
I only have little experience with VBA as I've mostly been working in
PHP/MySQL environments. My Linux using friends tell me that MS Access
is suffering from a data overload, that Microsoft software can't deal
with this kind of stuff. Is that really the case or is it me who's got
faulty code (I, personally, expect the latter to be the case).
I really hope you can help me out.