Value in VB from SQL without cearting query

  • Thread starter Thread starter David Lauberts
  • Start date Start date
D

David Lauberts

Dear all

Looking four some pearls of wisdom.

I am using access 2002 vba

Is there any way running a query constructed in SQL, holding the value and
assigning it to a constant.

I can do it by creating and using a parameter query (CreateQueryDef()
function), this creates a 'hard' copy of the query in the data base a seems
to take a couple of seconds.

Is there another way of

Dim intX as integer
Dim stSQL as string

stSQL = "SELECT DISTINCTROW Count(tblTable.Number) FROM tblTable WHERE
(((tblTable .Number) Between 135 And 144));

intX = 'result of running parameter query stSQL'

Currently I seem to have to use CreateQueryDef() function to create a query,
run a another query to append the result to a table then look up the value
in the table to get 'intX'! This is long winded and I am sure there is a
better way.

Any help welcome and appreciated

Dave
 
Hi David,

One typical method would be to
use a domain function:

Dim lngX As Long 'if count ever goes > 35,xxx,
'you will get Overflow error
'assigning to "intX"

'assuming you are using easy example field name
'and you would never use reserved word "Number"
'for a field name

lngX = DCount("[Number]","tblTable", _
"[Number]>=135 AND [Number]<=144")

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Thanks Gary
Never even thought of using that !, silly.
Regards
Dave
Gary Walter said:
Hi David,

One typical method would be to
use a domain function:

Dim lngX As Long 'if count ever goes > 35,xxx,
'you will get Overflow error
'assigning to "intX"

'assuming you are using easy example field name
'and you would never use reserved word "Number"
'for a field name

lngX = DCount("[Number]","tblTable", _
"[Number]>=135 AND [Number]<=144")

Please respond back if I have misunderstood.

Good luck,

Gary Walter

David Lauberts said:
I am using access 2002 vba

Is there any way running a query constructed in SQL, holding the value and
assigning it to a constant.

I can do it by creating and using a parameter query (CreateQueryDef()
function), this creates a 'hard' copy of the query in the data base a seems
to take a couple of seconds.

Is there another way of

Dim intX as integer
Dim stSQL as string

stSQL = "SELECT DISTINCTROW Count(tblTable.Number) FROM tblTable WHERE
(((tblTable .Number) Between 135 And 144));

intX = 'result of running parameter query stSQL'

Currently I seem to have to use CreateQueryDef() function to create a query,
run a another query to append the result to a table then look up the value
in the table to get 'intX'! This is long winded and I am sure there is a
better way.

Any help welcome and appreciated

Dave
Click to expand...
Click to expand...
 
Back
Top