Use COUNT in text box Control Source... 2

[NFI]

Hello,

I'm trying to return a count of a number of records from a table in a text box on a form...

If you followed that, here's what I'm trying in the text box control source;

=(SELECT count([crew].[id]) AS [Complement] FROM [crew])

I get the error #Name? from this, though.

This works;

=count((crew.id))

although why I need to double bracket it, I've no idea - Access removes the Count keyword if I don't...

What I actually want to do is this;

=Count((SELECT [crew].[id] WHERE [crew].[ship] = [ship].[value]))

Where the last [ship] is a combobox on the form...this just returns #Error though...

In a fit of desperation, I tried this:

=COUNT((SELECT [crew].[id] FROM [crew] INNER JOIN [ship] ON [crew].[ship] = [ship].[value]))

...but I got #Error for my trouble

Has anybody got any ideas?

I can supply any further info if needed, but I didn't know what people might want...

Thanks,

Paul

 

[Remou]

You seem to be looking for DCount:

[tt]=DCount("id","crew","[ship] = " & [value])[/tt]

This is assuming you have a ship value as a control on your form. if you need something more complicated, you can DLookUp a query, or get the value from SQL run in VBA.

http://lessthandot.com

 

[dhookom]

You can't use a SQL statement as a control source. You can use DCount() or other domain aggregate functions.
If Ship is numeric try:

=DCount("[id]","[crew]","[ship] = " & [ship])

If Ship is text try:

=DCount("[id]","[crew]","[ship] = """ & [ship] & """")

Duane
Hook'D on Access
MS Access MVP

 

[NFI]

Thanks very much you chaps,

I've only started writing these God-awful queries Access uses today and I was fast approaching the end of my teather with them...

Many thanks, that works a treat.

Paul