Select query returns non-existing data

Hot Topic

Question for Windows wizards VBA/VBScript
retrieve data from e-mails to tables
error in formula
MS Office document and image writer print driver
fight with me
Available Resource Error on SaveAs method
[Access SQL]: Have a function similar Oracle SQL function Minus use in MS Access SQL?
creating hyperlink through vba
what is wrong with this formula? *9 Views & no one knows?*
Getting Data of HTML page

Microsoft ISV

Validation with List drop down?
Creating and Editing Microsoft Access Reports using Visual Basic
Select query returns non-existing data
Dynamicaly enter Data in Microsoft Access
"How to" Programatically hide or display pivot items of pivot field
troubleshooting saving versions with macro in Word
LBound and UBound functions
CRM 3.0 Outlook Client
Refresh excel external data - then save macro
Live Communications Server tab does not show up

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

  • Kunk

    No, that's a spelling error. I translated the field names from my native language and simply hit i and t too many times.

  • james_cline_

    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


  • ropley

    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

  • rgauba

    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


  • Chris Honcoop

    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



  • MaseYo

    Cannot see a problem in the logic!

    Have you actually spelt the field name "Quantitity" the same in both tables, or is one Quantity


  • Select query returns non-existing data
Answer this question