SQL statement "=" doesn't work in VBA

[dfarland]

Have an Access DB "StoreDB.mdb, with a table called "data" and a date field called "BusDate". If you replace the {#" & DateRange & "#"} with {#9/30/2003#"}, as an example, it works. The value shown in the locals view is correct. What is up?

This code should work, shouldn't it?

Sub GetTable()
Dim DbEngine As Database
Dim rs As Recordset
Dim strSQL As String
Dim DateRange As String
DateRange = Range("Date&quot
Set DbEngine = OpenDatabase("C:\Sales\StoreDB.mdb&quot
strSQL = "SELECT * FROM Data WHERE BusDate = #" & DateRange & "#"
Set rs = DbEngine.OpenRecordset(strSQL)
Range("A1&quot.CopyFromRecordset rs
End Sub

Dean

 

[Zathras]

Just saying "doesn't work" doesn't give a lot to go on. Things "don't work" in several possible ways. What happens? No data? Wrong data? Error message?

Put a break point at

 strSQL = "SELECT...

and use the cursor to hover over

 DateRange

-- what do you see?

What is the definition of the range named "Date"?

What is the contents of the upper-left hand cell of the range named "Date"?

 

[dfarland]

Sorry, this sample code will run however returns no data.

May be a stupid question, but "break"?

 

[Zathras]

To set a break point, click in the empty column at the left of the code window in line with any statement. When done correctly, it toggles a break point with the appearance of a dot where you clicked, and a highlight color for the line. Then when the macro runs, it stops on that line, just before executing it, to allow you to do stuff. The line appears with yellow highlighting. (Just the same as when an error happens.)

Very useful technique. You definitely should learn how to use it.

 

[krinid]

Zathras is right; you should check the value of DateRange at execution time, and the easiest way to do that is to put in a breakpoint in your code after DateRange has been assigned (not before!). The {strSQL = "SELECT * FROM Data WHERE BusDate = #" & DateRange & "#"} line itself is a good spot to put in a break. Or you could put in a STOP statement just before the statement and check it's value then. As Zathras mentioned, during break mode (or 'debug mode') put the cursor over the variable and it's value with be displayed. Or you can enter ?DateRange in the debug window and get its value. It's a very useful technique.

 

[dfarland]

I do use the break mode a lot to step through code. It really is a nice feature. The value of DateRange is "9/30/2003". Which is what the value of the cell/range named "Date".

 

[xlbo]

Should you really be passing a date as a string ???
You are enclosing the date with # which Im asume is the appropriate method ('cos no-one else has said anything) but you have daterange dimmed as string, which requires ' ' around it. Not only may this cause a syntax error but you are unlikely to get any data unless "BusDate" is defined as a string in your db also....

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Zathras]

dfarland: You've got me stumped. Try putting in

  MsgBox strSQL

after the

 strSQL = "SELECT...

and run it both ways:

 "#" & DateRange & "#"

vs.

 "#9/30/2003#"

The resulting display should be identical and both should work the same way, or you haven't told us the whole story.

Geoff: Access is funny that way. It takes the # sign as a date delimiter. (And of course it should be a string since the process is building up a SQL string from string bits.)

 

[TonyJollans]

Hi Dean,

Just a thought but I have had problems in the past with date formats differing between Excel, Access, VBA, etc. Silly as it seems it may be worth trying 30/9/2003 instead of 9/30/2003. Other than that (and the fact that I hate date literals in Access) your code looks good (apart from a missing semicolon at the end of the SQL which shouldn't be a problem).

Enjoy,
Tony

 

[dfarland]

I got it working, not sure how, but it is working. I renamed anything that I had simply named date. That is the only thing that makes any sense to me.

Thanks for the help.