Formula for form!!! Urgent plzzzz!

[senna]

Hi there,

I need a formula such as =Sum(IIf([claim sighted]=2,1,0)) for a form in my database. This formula i used for another form and is only an example of the type of solution i am looking for as it worked for my other requirement. Would a formula like this work for the following requirement?

The values i want to count are frequently duplicated text IE: A99999 or A88888 and i only want to count the number of instances each of these values come up when [sighted]=2.

Example.

TAG --------------- SIGHTED
A77777 ----------------2
A77777 ----------------2
A88888 ----------------1
A88888 ----------------1
A88888 ----------------1
A99999 ----------------2
A99999 ----------------2


A99999 ----------------2

In this example, the final count should be 2. As there are 2 different TAGS which HAVE BEEN sighted.

Can anyone help? URGENT! Thank you, any help greatly appreciated!

[TheShabz]

Are Tag and Sighted stored in a table? If so you can use a query.

SELECT count(TAG)
FROM myTable
GROUP BY TAG, SIGHTED
HAVING SIGHTED = 2;

^^I think that's good syntax. Dont have Access right now to check.

Then you can use the DLookup() function to pull the count into the form from the query.

[senna]

Thanks Shabz, ill give it a try. Not too sure where to put this coding though.
At the moment yes they are in the table
and I have a query for the form, which contains these fields in them.

WHere do I go from here?

[TheShabz]

Create a separate query for that. Then, create an unbound textbox that holds:
=DLookup("FieldName", "QueryName")

Keep the quotes

[senna]

I am not sure where to put that first code though...
if i create a new query, where do i place that first code?

[TheShabz]

You probably have the query in design view. Open it in SQL view and paste. remember to change the myTable to the proper table name.

[senna]

Ok... I have pasted this into SQL
SELECT count(tag)
FROM [Sales Table]
GROUP BY tag, sighted
HAVING sighted=2;


and this onto my form

=DLookUp("tag","COUNT QUERY")


i get an #error

[TheShabz]

Does the query return a result when you switch from sql view to datasheet view? My SQL syntax could be off.

Also, it could be that "COUNT QUERY" should be "[COUNT QUERY]"