Select query returns non-existing data

Hot Topic

Adding a date from a specific cell into the DateAdd function
Microsoft Ecosystem
obtain data of next 10 days
Address Book Issues
Reading PDF files from VBA
How we can get Exchange VSAPI?
Excel Chart: How to change chart data source formula when object "Series.Formula" is too short?
Error while editing Tasks\Announcements etc. in Sharepoint 2007
bracketing "ifs" and "ands" and "ors" in VBA over Excel
WebPart Problem (?) / BSM2005

Microsoft ISV

combine 3 combo boxes values into one string
vba create and add a macro for excel programmatically
Excel status loader/counter?
usrfrm initialize
Problem passing a range as argument
Excel: Email Macro help
Count and display in cell
Hyperlink in text (or anywhere). Use a LinkLabel >>
Digital Certificate Error
Delete Unwanted rows based on value in cell

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

  • ray_newbie_SSIS

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

  • AmirAlis

    Cannot see a problem in the logic!

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


  • jeff357

    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

  • GreenStone90

    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


  • GrandpaB

    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



  • Rhubarb

    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


  • Select query returns non-existing data
Answer this question