SQL expression for txt box on a Form

[eggwater]

hello

can anyone tell me how I can implement this SQL expression in a text box on a form

SELECT Accom_record_card.Export_ctrl, Count(Accom_record_card.Export_ctrl) AS CountOfExport_ctrl
FROM Accom_record_card
GROUP BY Accom_record_card.Export_ctrl
HAVING (((Accom_record_card.Export_ctrl)="2"));

I can't find anything similar anywhere.

Alternatively how can I implement a Count function to count all the occurances of Export_ctrl="2" - I keep getting syntax and type mismatch errors.

 

[FoFa]

I would try to develop this in a query first as your SQL appears incorrect. I think you are trying to do this:
SELECT Accom_record_card.Export_ctrl, Count(Accom_record_card.Export_ctrl) AS CountOfExport_ctrl
FROM Accom_record_card
GROUP BY Accom_record_card.Export_ctrl
HAVING Count(Accom_record_card.Export_ctrl) = 2;

But if you are really looking for Accom_record_card.Export_ctrl = "2" than you need this instead:
SELECT Accom_record_card.Export_ctrl, Count(Accom_record_card.Export_ctrl) AS CountOfExport_ctrl
FROM Accom_record_card
WHERE Accom_record_card.Export_ctrl = "2"
GROUP BY Accom_record_card.Export_ctrl ;

However since this in theory will return multiple rows, why would you want it in a text box?

 

[eggwater]

more info

the 2 represents a particular flag.

I'm making a stats form/report where I want to show 4 flags:

I want 4 text boxes that will show the count values of flag 1, flag 2, flag 3, flag 4 - namely Exported Record, INCOMPLETE Record, UnExported Record and Amended Record

I thought it would be possible have Text boxes with subqueries in - I've tried writing a single query but can't get it to work because I obviously need to be able to show the same field 4 times having their results based on the 4 different criteria.

I may be over complicating this.

I could run a find duplicates query and have my form be continuous but this is not really what I want.

 

[FoFa]

If you have 4 different text boxes, than have thought about something like dcount for each text box (like 4 seperate queries) each with it's own criteria?

 

[eggwater]

i tried it

but couldn't get it to work:

it nearlly worked - I got a value but it was wrong

how would the expression look?

 

[FoFa]

Domain agregate functions are setup as follows:
function (dcount, dsum, dlookup, etc)
Dlookup("columnName","table/queryname","where calause without where")

dcount("Export_ctrl","Accom_record_card","Export_ctrl = '2'")
Should return a count of Export_ctrl from table Accom_record_card
where export_ctrl = '2' (you have to single quotes for strings since it is inside double quotes)

In the text box you need to put an = sign in front of it, and if external changes are made, you need to requery the control to update the value.

 

[eggwater]

thank you

i will give it a go - think i was probably using dbl quotes like the doofas I am.

cheers