vba SQL not equalling query in Access

T

T Best

I ran a query in Access 97 and got the correct # of records. When I run the
same query in my VBA code it seems 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
 
D

Duane Hookom

We can't see your results or how you determined something didn't work or
what you mean by "still didn't work".
How are you counting the number of records in your recordset?
 
T

T Best

i'm sorry you're right here is all of my code...

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?


Duane Hookom said:
We can't see your results or how you determined something didn't work or
what you mean by "still didn't work".
How are you counting the number of records in your recordset?
--
Duane Hookom
MS Access MVP

T Best said:
I ran a query in Access 97 and got the correct # of records. When I run the
same query in my VBA code it seems 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
Click to expand...
Click to expand...
 
O

OfficeDev18 via AccessMonster.com

If the SQL in your first posting is the most current, I see some
possibilities. To copy it,

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)"

First of all I would change

[5#_D-REPTD] >= #05/1/2005# And [5#_D-REPTD] <=#05/31/2005#

to

(Month([5#_D-REPTD]) = 5) And (Year([5#_D-REPTD]) = 2005)

Besides that, though, I think I spot an error in the parenthises.

I think that

([5#_D-REPTD] >= #05/1/2005# And [5#_D-REPTD] <=#05/31/2005# AND([9#_SECT])=2)


should be slightly changed to

([5#_D-REPTD] >= #05/1/2005# And [5#_D-REPTD] <=#05/31/2005#) AND ([9#_SECT]
=2)

or, with my change above,

(Month([5#_D-REPTD]) = 5) And (Year([5#_D-REPTD]) = 2005) AND ([9#_SECT]=2)

in order to isolate each term in the WHERE clause.

Hope this helps,

Sam

T said:
i'm sorry you're right here is all of my code...

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?
We can't see your results or how you determined something didn't work or
what you mean by "still didn't work".
Click to expand...
[quoted text clipped - 19 lines]
Click to expand...
 
J

John Spencer

Getting the recordcount accurately means that you must force Access to move
to the end of the recordset before you get the count.

Set rst = dbs.OpenRecordset(strSQL)

If rst.RecordCount > 0 then rst.moveLast
NewClaims = rst.RecordCount
MsgBox NewClaims
End if

Although I would use DCount or an aggregate query if all you want is this
limited bit of data
With DCount there is no need for creating a recordset at all.

Your entire code snippet would be:

NewClaims = DCount("*","RCACMB","[1#_CLM#]) Like 'SN*' AND [9#_SECT])=2")
MsgBox NewClaims

With an aggregate query
Dim ...

Set dbs = CurrentDb
strSQL = "SELECT Count(*) as CountClaims FROM RCACMB WHERE ((([1#_CLM#])
Like
'SN*') AND (([9#_SECT])=2));"
Set rst = dbs.OpenRecordset(strSQL)

NewClaims = rst!CountClaims
MsgBox NewClaims

Using DCount is the simplest method and it may be the fastest.

T Best said:
i'm sorry you're right here is all of my code...

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?


Duane Hookom said:
We can't see your results or how you determined something didn't work or
what you mean by "still didn't work".
How are you counting the number of records in your recordset?
--
Duane Hookom
MS Access MVP

T Best said:
I ran a query in Access 97 and got the correct # of records. When I run the
same query in my VBA code it seems 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
Click to expand...
Click to expand...
Click to expand...
 
T

T Best

unfortunately that didn't work. i changed my SQL statement to read...

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)"

when i ran that it gave me all claims that met the criteria of the first
WHERE clause and ignored the rest. does anyone know why the result thru code
is giving me a different record count than when i run the query? i pasted
this SQL statement in the SQL view of my query and ran it and got a
different record count.

OfficeDev18 via AccessMonster.com said:
If the SQL in your first posting is the most current, I see some
possibilities. To copy it,

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)"

First of all I would change

[5#_D-REPTD] >= #05/1/2005# And [5#_D-REPTD] <=#05/31/2005#

to

(Month([5#_D-REPTD]) = 5) And (Year([5#_D-REPTD]) = 2005)

Besides that, though, I think I spot an error in the parenthises.

I think that

([5#_D-REPTD] >= #05/1/2005# And [5#_D-REPTD] <=#05/31/2005# AND([9#_SECT])=2)


should be slightly changed to

([5#_D-REPTD] >= #05/1/2005# And [5#_D-REPTD] <=#05/31/2005#) AND ([9#_SECT]
=2)

or, with my change above,

(Month([5#_D-REPTD]) = 5) And (Year([5#_D-REPTD]) = 2005) AND ([9#_SECT]=2)

in order to isolate each term in the WHERE clause.

Hope this helps,

Sam

T said:
i'm sorry you're right here is all of my code...

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?
We can't see your results or how you determined something didn't work or
what you mean by "still didn't work".
Click to expand...
[quoted text clipped - 19 lines]
TIA
Ted
Click to expand...
Click to expand...
Click to expand...
 
T

T Best

AWESOME...thanks John. That did the trick! I didn't think of using DCount.
I really appreciate your help!

John Spencer said:
Getting the recordcount accurately means that you must force Access to move
to the end of the recordset before you get the count.

Set rst = dbs.OpenRecordset(strSQL)

If rst.RecordCount > 0 then rst.moveLast
NewClaims = rst.RecordCount
MsgBox NewClaims
End if

Although I would use DCount or an aggregate query if all you want is this
limited bit of data
With DCount there is no need for creating a recordset at all.

Your entire code snippet would be:

NewClaims = DCount("*","RCACMB","[1#_CLM#]) Like 'SN*' AND [9#_SECT])=2")
MsgBox NewClaims

With an aggregate query
Dim ...

Set dbs = CurrentDb
strSQL = "SELECT Count(*) as CountClaims FROM RCACMB WHERE ((([1#_CLM#])
Like
'SN*') AND (([9#_SECT])=2));"
Set rst = dbs.OpenRecordset(strSQL)

NewClaims = rst!CountClaims
MsgBox NewClaims

Using DCount is the simplest method and it may be the fastest.

T Best said:
i'm sorry you're right here is all of my code...

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?


Duane Hookom said:
We can't see your results or how you determined something didn't work or
what you mean by "still didn't work".
How are you counting the number of records in your recordset?
--
Duane Hookom
MS Access MVP

I ran a query in Access 97 and got the correct # of records. When I
Click to expand...
run
the
same query in my VBA code it seems to ignore the 2nd and 3rd
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL using VBA 4
calling a value from Aggregate Recordset?!?! 3
this should be quick 3
Transposing data in Access 2007 Query 1
Parameters in a query in run time 1
Type Mismatch in Recordset sql 1
Query works, SQL in VBA does not 4
query on query 1

Top