Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DAO Record Set (2002)

    If I run the query below, I receive one row of data, which should be my expected result. When I try to select the same data as a record set, I receive three rows of data. What do I not understand about DAO recordsets or defining the string to be used as a recordset? I don't get any errors, just too much data. Any help would be appreciated.

    Query:
    SELECT *
    FROM tblSalesTO
    WHERE SMDA_ID = "010B017 06200"
    AND Dt_Sale1 = DateValue("10/3/1996")
    AND Vld_Sl_Rsn_Cd = "Y"

    Returns one record.

    As recordset:
    strQrySalesTO = "SELECT * FROM tblSalesTO WHERE SMDA_ID = " & Chr(34)
    strQrySalesTO = strQrySalesTO + rsSalesTOMax.Fields("SMDA_ID") & Chr(34)
    strQrySalesTO = strQrySalesTO + " AND Dt_Sale1 = DateValue(" & Chr(34) & rsSalesTOMax.Fields("LastSaleDate") & Chr(34) & ")"
    strQrySalesTO = strQrySalesTO + " AND Vld_Sl_Rsn_Cd = " & Chr(34) & "Y" & Chr(34)
    Set rsSalesTO = db.OpenRecordset(strQrySalesTO)

    If rsSalesTO.RecordCount = 1 Then

    dtDt_Sale1 = rsSalesTO.Fields("Dt_Sale1")
    lngFull_Cnsdrtn = rsSalesTO.Fields("Full_Cnsdrtn")
    lngInstrmnt_Type = rsSalesTO.Fields("Instrmnt_Type")
    .
    .
    .

    The record count variable returns three records, all the records in the table for the selected SMDA_ID.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO Record Set (2002)

    To test, insert the following line immediately above the line <code>Set rsSalesTO = db.OpenRecordset(strQrySalesTO)</code>:

    <code>Debug.Print strQrySalesTO</code>

    When you run the code, the SQL string should be displayed in the Immediate window. Please copy it to the clipboard, then paste it into a reply.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Record Set (2002)

    Hans,

    Here's the query from the immediate window:

    SELECT * FROM tblSalesTO WHERE SMDA_ID = "010B017 06200" AND Dt_Sale1 = DateValue("10/3/1996") AND Vld_Sl_Rsn_Cd = "Y"

    I executed the query, it returned one record. Any suggestions?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DAO Record Set (2002)

    Don't use the + sign to concatenate in a SQL string like that. Use & instead. The + operator is actually an arithmetic operator and behaves differently from &. That my be part of your problem., but the big problem I see is that you are referring to rsSalesTOMax in the SQL but there is no indication of what it is. If rsSalesToMax is yet another recordset and already populated, change your code to this:

    strQrySalesTO = "SELECT * FROM tblSalesTO WHERE SMDA_ID = " & Chr(34)
    strQrySalesTO = strQrySalesTO & rsSalesTOMax.Fields("SMDA_ID") & Chr(34)
    strQrySalesTO = strQrySalesTO & " AND Dt_Sale1 = #" & DateValue( rsSalesTOMax.Fields("LastSaleDate"))
    strQrySalesTO = strQrySalesTO & "# AND Vld_Sl_Rsn_Cd = " & Chr(34) & "Y" & Chr(34)
    Set rsSalesTO = db.OpenRecordset(strQrySalesTO)
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Record Set (2002)

    Charlotte,

    I changed the + to an &, still same result as before. The rsSalesTOMax is another record set that contains the last valid sale date for each SMDA_ID. I can run the query and it works fine. Any help would be appreciated.

    Thanks...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO Record Set (2002)

    I don't think we'll be able to solve this without seeing the database. Could you post a stripped down version of the database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
    <LI>Attach the zip file to a reply.[/list]That will enable Loungers to look at the problem directly.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Record Set (2002)

    Hans,

    When I was stripping down my .mdb, I tested it and it worked in the new version. I think it might be something with the DAO object reference. When I first ran this in the original version, I forgot to add the reference and it crashed. I added the reference, ran it, but got the wrong number of records returned. I added the reference to the stripped down version before I ran it and it worked as expected. I've tried removing the reference in the original database and adding it back in, but I still get too many records. Is there anyway to reset the DAO reference or am I way off base here? I attached the stripped down version.


    Thanks...
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO Record Set (2002)

    I just get one record, so I can't tell you what's wrong. There were a few things that struck my eye:
    <LI>You don't have Option Explicit. This is dangerous. Please select Tools | Options in the Visual Basic Editor, and tick the check box "Require Variable Declaration", then click OK. This will add Option Explicit at the top of new modules. You will have to insert it by hand in existing modules.
    <LI>You declare a recordset rsPrpts As DAO.Recordset, but the rest of the code uses the undeclared variable rsprprts. This is one of the things Option Explicit will warn you against.
    <LI>Later in the code, you have an instruction lngFull_Cnsdrtn = rsSalesTO.Fields("Full_Cnsdrtn"), but the field in tblSalesTO is named Full_Cnsdrtcn (notice the extra c)
    I don't think "resetting" the DAO reference, whatever that may be, will help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •