SQL not populateing recordset in code (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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 to get it under the 100k limit.

Reply | Quote

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 to get it under the 100k limit.

Reply | Quote

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.

Reply | Quote

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

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

Reply | Quote

Hope a zip file is OK?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hope a zip file is OK?

Reply | Quote

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

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

Reply | Quote

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.

Reply | Quote