Dcount field in form 1

[ottograham]

I have a table with vehicle information ("Vehicles&quot. The vehicles have 5 digit class code strings ([Class]), with the first character identifying gross vehicle weight. For instance, 0**** is a light truck, while 7**** is a private passenger vehicle.

How best to count the number of vehicles of each type and place the total on the form:
Light: __xx_____
Medium: _xx____
Heavy: __xx__
Private Passenger: _xx___

Do I use text boxes and put expressions in the Control Source property? I've tried following the Domain Function help screens, with no success. Would you include the proper syntax?

Thank you in advance for your help.

 

[LonnieJohnson]

You can put a text box on your form. In the text box's control source property you can put:

=DCount("Class",[RecordSource],"Class Like '0*'&quot

This expression says to count the number occurances of the field class in the form's recordsource where Class is like any string that begins with a zero (0*...zero plus the wild card). This will count all your light trucks.

Make another text box and put a 7 in the expression:

=DCount("Class",[RecordSource],"Class Like '7*'&quot

This will count your private passenger vehicles.

Good luck...B-)
ljprodev@yahoo.com
ProDev
MS Access Applications

 

[ottograham]

Lonnie:

It works! - One more thing. I use an 8 character string Account code. Can I add a second condition where Account="Accountcode" to the class like '7*'?

I've used this:

=DCount("Class","Vehicles","Class Like '7*' and Account = 'Account' &quot but it returns 0 when there are 2 cars to count. I'm sure its the syntax, but I've tried it many different ways. Where am I going wrong?

Thank you.

Scott

 

[LonnieJohnson]

Yes, you can have more than one condition. If this AccountCode is on your form then you will have to concantenate it's field name to the SQL statement like so...

=DCount("Class","Vehicles","Class Like '7*' And Account = '" & AccountFieldName & "'&quot

This will work provided that Account is a field in your table.
Also note that the above statement is all one line.

Good luck.
ljprodev@yahoo.com
ProDev
MS Access Applications

 

[ottograham]

Lonnie:

That is so cool! I need to figure out how all the quotes work so I can get it right myself, but thanks again for your help.