show the count of a query in form text box

[megatronixs]

Hi all,

I made a query to count how many new cases where yesterday.
When I run the query, I get the result showint the total. I wanted to actually get that result into a form text box, just can't find a way to do this. The below query gives me the rusult "1"

SELECT Count(*) AS [Cases Started]
FROM Table1
GROUP BY Table1.Date1, Table1.Date2, Table1.Status
HAVING (((Table1.Date1)=(Date()-1)) AND ((Table1.Date2)<>(Date())) AND ((Table1.Status)<>"New"));

The textbox I use is called "New"

Any help would be great.

Greetings.

 

[moke123]

why not use dcount() and use that as the controlsource of the textbox

 

[megatronixs]

Hi Moke123,

I'm not that expirienced with it, do you have an example?

Greetings.

 

[moke123]

heres a link to some info ... http://www.techonthenet.com/access/functions/domain/dcount.php

basically you would have something like
= DCount ( "*","Table1","whatever your criteria goes here" )

i dont know enough about your data to give a more exact answer. your criteria arguement should read like a where clause- dcount("*","MyTable","MyID = " & Me.txtID) If you have a saved query that returns what you want you could try something like =dcount("*","MySavedQueryName")

 

[arnelgp]

you can also use your saved query:
set your "New" textbox controlsource to:

=DLookup("[Cases Started]", "yourQueryName")

 

[megatronixs]

Hi Arnelgp,

Thanks, that did the trick
Can I use one query to get more than one result based on other criteria and then put that one in the field, using the [(cases started)] and maybe like [(New Cases)].
just to avoid having 200 queries for one report in the form.

Greetings.

 

[arnelgp]

this is your original query:

SELECT Count(*) AS [Cases Started]
FROM Table1
GROUP BY Table1.Date1, Table1.Date2, Table1.Status
HAVING (((Table1.Date1)=(Date()-1)) AND ((Table1.Date2)<>(Date())) AND ((Table1.Status)<>"New"));

modify it into:

SELECT Count(*) AS [Cases Started]
FROM Table1
GROUP BY Table1.Date1, Table1.Date2, Table1.Status
HAVING (((Table1.Date1)=(Date()-1)) AND ((Table1.Date2)<>(Date()));

now you can use these on your textboxes controlsource.
to show only "New Cases"

=DLookup("[Cases Started]", "yourQueryName", "[Status]='New'")

for other "status":

=DLookup("[Cases Started]", "yourQueryName", "[Status]='whatEverStatusYouLikeToShow'")

 

[megatronixs]

Hi,

That did the trick

Thanks a lot.

Greetings.