Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL not populateing recordset in code (Access 2000)

    Hello Everyone

    I've been trying to use some code to automate the production of quality reports from a database. I've used similar code before and it's given me the results, I even had this working before I left work last night!!!!

    The code should collect all the records as per the SQL, I know the records exist as I've put them there specifically in the 'test' data I'm using. It should then create the relevant number of Word reports. These are then attached to an email generated later along with Excel charts.

    The OutLook and Excel automation work fine, the only thing I can see when i set break points and scan the code is the the rstWord recordset is always empty. I've used the SQL code in a query (once modified suitably) and it does work, unless the code is stopped on a breakpoint. I also use similar SQL strings to populate sections of the Excel and OutLook code.

    The code is attached, if you can work out where I'm wrong please tell me, if not recommend a good Psychiatrist as Access is winning in the sanity wars...

    Thanks

    Ian

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL not populateing recordset in code (Access 2000)

    Put a breakpoint on the line : Set rstWord = dBs.OpenRecordset(strWordSQL)
    When the code stop, go to the immediate window (CTRL-G in the VBE window)
    Enter : ? strWordSQL
    Copy the sql string into a new query.
    Did the new query returns records ? Can you find why not ?
    If you want, you can attach a stripped version of the db so we can have a look at it. Have a look at Hans's <post#=401925>post 401925</post#> to get it under the 100k limit.
    Francois

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

    Re: SQL not populateing recordset in code (Access 2000)

    I don't see how that SQL can work when you would wind up with spaces between the hash marks and the dates they enclose.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL not populateing recordset in code (Access 2000)

    Thanks for the replies.

    OK, i've taken out the spaces.

    I then did the immediate window bit, thanks that's going to be a useful tip [img]/forums/images/smilies/wink.gif[/img]

    Pasted the code into the Query SQL window, nothing is returned (I was expecting this but at least I now KNOW). I then had a quick 'play' with the Query in Design view and it returned the correct results. On viewing it in SQL it now looks like this: -

    SELECT *
    FROM PrimaryDataTbl
    WHERE (((PrimaryDataTbl.DateReceived) Between #1/1/2000# And #9/1/2004#) AND ((PrimaryDataTbl.VendorNumber)=17999));

    However, if I try that in my VBA it doesn't like it at all, not that I really expected it to, but desperation drives you to wierd things....

    I then tried defining my fields in the query (PrimaryDataTbl.FieldName) to see if that helped, still no luck.

    So, is there something in my SQL statement that is causing the problem? I could create a Query and use QueryDefs, but that seems to be overkill for something this simple.

    I'll look into stripping the database out to get it to < 100k.

    Thanks again.

    Ian

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL not populateing recordset in code (Access 2000)

    Hope a zip file is OK?

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL not populateing recordset in code (Access 2000)

    Charlotte is right.
    All I have to do was changing the line where you build the strWordSQL to :
    strWordSQL = "SELECT * FROM PrimaryDataTbl WHERE Primarydatatbl.DateReceived BETWEEN #" & Me.StartDateTxt & "# AND #" & Me.EndDateTxt & "# And primarydatatbl.VendorNumber = " & Me.Recordset!VendorNumber
    The difference with yours :
    BETWEEN #" & Me.StartDateTxt & "# AND #" & Me.EndDateTxt & "# And (new)
    BETWEEN # " & Me.StartDateTxt & " # AND # " & Me.EndDateTxt & " # And (yours)

    Attached the modified db
    Francois

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL not populateing recordset in code (Access 2000)

    It's the dates. For some reason the date is being changed to US style in the SQL ouptput in my immediate window. I changed the day and mopnth back to how they should be and now have a result from a query, by setting the EndDateTxt control to September next year I get results.

    I've just gone through my PC's settings and they haven't been changed as far as I can tell, the date format is still dd/mm/yy style, is thre a way of forcing SQL, or Access to accept the dates in a certain format? Or, is there anywhere in Windows 2000 that controls the date format outside of the control panel?

    Francois

    Sorry, just noticed the one I'd posted still had the spaces in, bu tonly after you pointed it out to me....... Charlotte will be thinking I'm a bit thick...

    Thanks for the time and trouble.

    Ian

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL not populateing recordset in code (Access 2000)

    In SQL statements you have always to use us date format mm/dd/yyyy.
    You can try to insert a format function in your sql statement :
    strWordSQL = "SELECT * FROM PrimaryDataTbl WHERE Primarydatatbl.DateReceived BETWEEN #" & Format(Me.StartDateTxt,"mm/dd/yyyy") & "# AND #" & Format(Me.EndDateTxt,"mm/dd/yyyy") & "# And primarydatatbl.VendorNumber = " & Me.Recordset!VendorNumber
    Francois

Posting Permissions

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