Select query returns non-existing data

Hello everybody,

I'm trying to match entries in a table up against each other based on a number of criteria (Quantity, Date). Three types of entries exist:
Those from source 1, 2, and 3. I'm trying to match data from source 1 with data from source 2 and 3. Some is identical, some is not.

In a VBA Script I first select every entry from source 1 (indicated by the field "Datasource"). I then run a While loop. For each entry I run a second select query on the fields I want to match (Quantity, Date). It works OK, ie. as expected, in some cases. In other cases, the quantity is completely off - it's as if Access just invents some random quantity instead of using the one from select query one (the one with source 1 data).

What on Earth is wrong

Thank you for your assistance.

Answer this question

Select query returns non-existing data

Steven D
It seems that running the above code several times (5+) finally matches every entry in the table. Have I hit some kind of limit or am is some kind of problem with memory allocation
Kirill Tropin
Here is query no. 1:
MyDB.OpenRecordset("SELECT * FROM Table WHERE Source =
'BEC' and Level=2")

And query no. 2 run for each entry returned from query no. 1:
"SELECT * FROM Table WHERE Source <> 'BEC' and
Quantitity = " & MyRec.Fields("Quantitity") & " and Registrationnumber
= '" & MyRec.Fields("Registrationnumber") & "' and Exdate=#" &
MyRec.Fields("ExDate") & "#"

Here is the entire 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
SoulSolutions
Yes. I have this small example of what happens. However, it seems to happen randomly so if I ran the script again I would probably get another ID that was left unmatched.
Here it matches ID 30195 with ID 32116 properly with this SQL query:
SELECT * FROM Table WHERE Source <> 'BEC' and Quantity = 350000 and
Registrationnumber = '005700785' and Exdate=#18-03-2003#

(Note: I do dates the European way)
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 (like at 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 Quantity Registrationnumber 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
Chimme

epimp wrote:
It seems that running the above code several times (5+) finally matches every entry in the table. Have I hit some kind of limit or am is some kind of problem with memory allocation

Neither one of those seems like it would be the problem. Could you post the SQL queries
imshally81
Cannot see a problem in the logic!

Have you actually spelt the field name "Quantitity" the same in both tables, or is one Quantity
Sune Henriksen
No, that's a spelling error. I translated the field names from my native language and simply hit i and t too many times.
Select query returns non-existing data

Answer this question