assign query results to a tempvar

[TexRanger]

The Query

'SELECT Count(clubbox) AS MTSingles
FROM moves WHERE (((moves.cmrdate)>[Forms]![CPanel]![Text44] And (moves.cmrdate)<[Forms]![CPanel]![Text46]) AND ((moves.driverid)=[Forms]![CPanel]![Text38]) AND ((moves.move)=35) AND ((moves.clubbox)=32));'

returns about 60 records, how do i assign the results to a tempvars
any help in this matter would be appreciated.

many thanks

 

[Mihail]

You wish to store into a variable... 60 records ?!?!?!?
What mean query results ?

 

[pr2-eugin]

Don't think you can assign a Recordset to TempVars.. AFAIK, it can only be String/Number.. I might be wrong though !

If you want to use the Recordset, why not simply create Queries on RunTime (Dynamically) and assign it to a Recordset object and use it then and there? Recordset For beginners might be a good place to start..

 

[TexRanger]

ooops, terribly sorry and my apologies,
the query returns a count of 60 records, i wish to store this value of count into tempvars.

 

[pr2-eugin]

Use DCount instead.. Does not matter if the Query returns only one value.. It still is considered a Recordset..

Be careful of Dates, Texts when using DCount.. General Usage of Domain Function -> http://access.mvps.org/access/general/gen0018.htm

 

[TexRanger]

can i use 6 or 7 conditions, in the dcount[] criteria part, are there any restrictions with regards to total number of criteria that may be used.
many thanks

 

[Mihail]

Of course.
If you can build a query you can build as well the Where clause for aggregate functions (like DCount, DLookup etc).

In fact, the Where for DCount is the same as for the Query but without the "where" word.
For guys that don't know very well SQL (I am one) the approach is to design a query then, from SQL view, to copy the string after the word "Where".

For now, because you already have a query, you can count the resulted records by using:

Dim Counter As Long
Counter = DCount("*" , "QueryName")

 

[TexRanger]

The Original Query was
---
SELECT Count(clubbox) AS LoadSingles
FROM moves
WHERE (((moves.cmrdate)>[Forms]![CPanel]![Text44] And (moves.cmrdate)<[Forms]![CPanel]![Text46]) AND ((moves.driverid)=[Forms]![CPanel]![Text38]) AND ((moves.move)<>35) AND ((moves.clubbox)=32));

and this returned a count of 38 as a result, but when the same was converted to dcount() --

TempVars.Add "CLSingle", DCount("clubbox", "moves", "moves.cmrdate >#" & Forms!CPanel!Text44 & "# AND moves.cmrdate <#" & Forms!CPanel!Text46 & "# AND moves.driverid = " & Forms!CPanel!Text38 & " AND moves.move <> 35 AND moves.clubbox = 32")

the result returned was 0, am i missing something here, i guess YES, be kind enough to help
--many thanks and much obliged.

 

[Mihail]

As I said, I'm not very comfortable with SQL but this part
& " AND moves.move <> 35 AND moves.clubbox = 32"
I think that should be
& " AND " & moves.move & " <> 35 AND " & moves.clubbox & " = 32"

The best is to store the string into a variable:
Dim WhereSQL As String
WhereSQL = "Here construct the Where string"
Debug.Print WhereSQL
Stop
TempVars.Add "CLSingle, DCount("clubbox", "moves", WhereSQL)

This way you can see in the Immediate window how WhereSQL string looks.

PS:
Name controls with suggestive names.
What Text44 store ???? From your SQL I think that here should be a Date.
So, something like txtStartDate or txtEndDate is a lot better in order to debug and/or to maintain the application.

 

[pr2-eugin]

Okay, I will go down Mihail's Route. Do the following, the Query you currently have..

SELECT Count(clubbox) AS LoadSingles
FROM moves
WHERE (((moves.cmrdate)>[Forms]![CPanel]![Text44] And  (moves.cmrdate)<[Forms]![CPanel]![Text46]) AND  ((moves.driverid)=[Forms]![CPanel]![Text38]) AND  ((moves.move)<>35) AND ((moves.clubbox)=32));

Save this Query by giving it a Name say getCount_Qry. Then in the Code use..

TempVars.Add "CLSingle", Nz(DLookUp("LoadSingles", "[B]getCount_Qry[/B]"), 0)

That's it ! Job done !