SQL using VBA

[T Best]

Hi all,

I ran a query in Access 97 and got the correct # of records. When I run the
same query in my VBA code it seem to ignore the 2nd and 3rd conditions in my
WHERE statement. I copied and pasted the SQL from Access and it still didn't
work. Any ideas? Here is my code(I have the SQL code on one line, no line
continuations):

Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String

strSQL = "SELECT [1#_CLM#] FROM RCACMB WHERE ([1#_CLM#] Like 'SN*' Or
[1#_CLM#] Like 'NS*') AND ([5#_D-REPTD] >= #05/1/2005# And [5#_D-REPTD]
<=#05/31/2005# AND([9#_SECT])=2)"

Set rst = dbs.OpenRecordset(strSQL)

TIA
Ted

 

[Albert D.Kallal]

If that code snip is as you have, you never setup the "dbs" var

Either go

Set rst = currentdb.OpenRecordset(strSQL)

or

set dbs = currentdb
Set rst = dbs.OpenRecordset(strSQL)

 

[T Best]

yeah i did that. i should have included all of my code before. here it is...

Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Dim NewClaims As Integer

Set dbs = CurrentDb

strSQL = "SELECT [1#_CLM#], [9#_SECT] FROM RCACMB WHERE ((([1#_CLM#]) Like
'SN*') AND (([9#_SECT])=2));"

Set rst = dbs.OpenRecordset(strSQL)

NewClaims = rst.RecordCount
MsgBox NewClaims

strSQL = ""
rst.Close
Set dbs = Nothing

I copied the SQL from the SQL view of the query. when i run the
query(qryJoesMonthlyRecap) i get 182 records but when i run the SQL i get
373 record. i even tried running it this way...
Set rst = dbs.OpenRecordset("qryJoesMonthlyRecap")

and got 373 records. the SQL code is exactly the same in my code above and
the query. this is killing me. is there something about the recordcount
property that i don't know? why am i getting different record counts when i
run the query and when i execute the SQL code thru VBA?

 

[Albert D.Kallal]

Well, it was a long shot.

So, now go:

strSQL = "SELECT [1#_CLM#], [9#_SECT] FROM RCACMB WHERE ((([1#_CLM#]) Like
'SN*') AND (([9#_SECT])=2));"

debug.print strSql

Add the above line of code. After you run, and the code fails, you can open
up the debug window (ctrl-g), and then look at the sql.

in fact, you can cut/past the sql into a blank new query in the query
builder..and test it....

Does it run?

 

[T Best]

i was able to get it working with DCount instead. i still don't know why
that wasn't working.
thanks for the help
Ted

Well, it was a long shot.

So, now go:

strSQL = "SELECT [1#_CLM#], [9#_SECT] FROM RCACMB WHERE ((([1#_CLM#]) Like
'SN*') AND (([9#_SECT])=2));"

debug.print strSql

Add the above line of code. After you run, and the code fails, you can open
up the debug window (ctrl-g), and then look at the sql.

in fact, you can cut/past the sql into a blank new query in the query
builder..and test it....

Does it run?


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal