How to use SELECT COUNT on form

[Ben2k]

I have a form and a text box

I want to use the following SQL command to populate the textbox

=(SELECT COUNT FROM [qry_summary_getalldeals])

qry_summary_getalldeals is a query

When I run the form it just displays #Name?, presume syntax must be wrong?

Thanks

 

[Norie]

You can't just put a query into a textbox like that.

Try looking at the DCOUNT, DSUM etc functions.

e.g. =DCOUNT("*", "qry_summary_getdeals")

 

[Ben2k]

Sorry I meant a listbox

SELECT COUNT Qry_Summary_GetAllDeals.Type FROM Qry_Summary_GetAllDeals;

This gives an error "syntax error (missing operator)"

 

[Norie]

I'm sorry but I don't quite understand what you are trying to do?

What is it you actually want in the listbox?

Why not try using the Query Builder to create a query to populate it?

i.e. On the Property sheet for the control goto the Data tab, select Row Source Type =Table/Query and then goto Row Source...

 

[Ben2k]

Thanks Norie, just sussed it out myself, was getting confused as I didn't realise you could bind individual queries to controls

This syntax works

SELECT COUNT([Qry_Summary_GetAllDeals.Type]) FROM Qry_Summary_GetAllDeals

However when I try to add a where clause as below it says "invalid bracketing"

SELECT COUNT([Qry_Summary_GetAllDeals.Type]) FROM Qry_Summary_GetAllDeals WHERE ([qry_summarygetalldeals.type]='trade');

 

[Norie]

try using the Query Builder

Have you done this?

Are you just typing in the SQL?

 

[Ben2k]

I was using the query builder, but then switching to SQL view to add the COUNT bit, not sure what problem was but I rebooted PC and it worked fine then.

 

[Norie]

was using the query builder, but then switching to SQL view to add the COUNT bit

Why did you do that?

You can do counts, sums etc in the query builder.

Just right click and select Totals.