Sum values in a text field 3

[rcreed1]

I have a database that has a field called "duty position". On my form it is a list box and has the choices: Primary Rep, Alternate Rep, Primary Warden, and Alternate Warden.I am hoping to add onto the form a box that shows the total number of Wardens (both Primary and Alternate).

My second question is in another database I have a form that uses a calculation in the control field to figure out how many wardens are requiered. This text box displays the number requiered and it's control source like I said is an expression (# of families/15 * 2). This number is displayed then in the text box on that form. I was hoping to then somehow import the number I get from my first question (the sum of the total warden) and display how many wardens they have and how many they are required to have.

I hope this explains my question. Any help would be greatly appreciated.

Thank you
SFC Reed

 

[Craig0201]

SFC Reed,

You're not after a SUM. You're after a COUNT. You can do this using the DCount function. Look it up in Help.

The rest just follows on from the above.

Number of wardens is your DCount.
Number of families is presumably a second DCount.
Number of extra wardens is the 2nd DCount minus the first.

These can be used as control sources on text boxes.

Craig

 

[rcreed1]

=[DCount]=[NEO II]![Duty Pos] In ("PRIMARY WARDEN","ALTERNATE WARDEN")


The above statment is what I have in the control source yet it is giving me "?Name " in the text box

 

[PHV]

Provided that the Wardens table columns are boolean, you may try this:
=DSum("Abs([PRIMARY WARDEN])+Abs([ALTERNATE WARDEN])", "[NEO II]", "[Some column]='" & [Some control] & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[maxhugen]

I think the DCount syntax is wrong... try:

=DCount("*","[Duty Pos]","[Duty Position] = 'PRIMARY WARDEN' OR [Duty Position] = ALTERNATE WARDEN'")

The first argument, "*" says count total number of records even if they contain null values,

The second argument is your table, and

The third arg is a WHERE expression (without the word 'where').


HTH

Max Hugen

http://www.AlphaFinancial.com.au

Sydney Australia

 

[rcreed1]

Thanks but that didn't work either. It is probably something I am doing.

For one it is showing "#Name?" in the text box NOT "?Name" like I had stated earlier.

Second - and sorry if this is a stupid question but am I suppose to replace the [Some column] and [Some Control] with something?

 

[rcreed1]

maxhugen,

I tried yours and it gave me a "#error"

thank you
Richard

 

[PHV]

rcreed1, can you please post the relevant table definition and the corresponding form's control names ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rcreed1]

I am not exactly sure what you may be asking (sorry this is all new to me) however I will try...

the table's name is NEO II and the "duty position" field is "Data" type.

The form that I placed the text box that I am trying to place this equation is named "Query for ward/reps" and it's record source is the table "NEO II

 

[PHV]

And what about this ?
=DCount("[Duty Position]", "[NEO II]", "[Duty Position] Like '*WARDEN*'")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rcreed1]

Sorry,

one other thing that may help is the text box I am trying to display this in is called "Text 19"

thank you

 

[rcreed1]

PHV,

It gave me "# Error"

Thank You

 

[PHV]

Depending of your regional settings you may have to use semi-colons ( instead of commas (,) in expressions:
=DCount("[Duty Position]"; "[NEO II]"; "[Duty Position] Like '*WARDEN*'")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rcreed1]

Sorry,

I owe you all a big apology, and a thank you. As I was double checking everything I realized that "Duty Position" was changed to "Duty Pos" so now I got it to work. I had shortened the name so it would fit better in the title bar of the spreadsheet.

Thank you for your patience and all your dedicated help.

Once again I am sorry for any confusion I may have caused.

SFC Reed

 

[PHV]

Nevermind, glad you've sorted out and thanks for the pinky.

 

[maxhugen]

Heh heh, we've probably all done the same sort of thing, especially when you've been staring at your code for ages wondering what you're still doing wrong!

Isn't it a relief when you 'crack' it!

Cheers

Max Hugen

http://www.AlphaFinancial.com.au

Sydney Australia

 

[rcreed1]

Sorry to ask more of you all after my last debacle....

but now that I got that to work (actually the only one I was able to get working was the =DCount("[Duty Pos]", "[NEO II]", "[Duty Pos] Like '*WARDEN*'")

I was wondering if there was a way I could add a where statement to this.

Another field in my database is "MSC" which is a list of different Army units. I have already created queries that I used the 'Where' [Forms]![Query for ward/reps]![Select Unit].

So on my form 'Query for ward/reps' I had a list box named 'Unit Select' that the user will select a unit from and then it will display that unit's information.

I was hoping to add a where clause to my: =DCount("[Duty Position]", "[NEO II]", "[Duty Position] Like '*WARDEN*'")

so it will only add the wardens from the selected unit in the 'Unit Select'

Note* to avoid any confusion the list box is called 'Unit Select' and the database field name is "MSC"

Thank you

 

[PHV]

Something like this ?
DCount("[Duty Pos]", "[NEO II]", "[Duty Pos] Like '*WARDEN*' AND MSC='" & Forms![Query for ward/reps]![Unit Select] & "'")
Provided that MSC is a text column in the table [NEO II]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rcreed1]

Awesome,

You have no idea how much you all just helped me.

Just so you can possibly get a little gratification...

I am in South Korea and this database keep tracks of all the people (wardens) who ensure noncombatants (civilian businessmen, military families.....) are taken care of and alerted in case of emergencies.

You may be thinking " I am glad I am not there and have this guy writing the database" but this is more just away for us to make a quick query to see where there may be problems with our manning. Or to get figures for briefings. None-the-less it will be important and you simply solved a big piece of this.

Thank you
SFC Reed

 

[maxhugen]

Hey, that's cool! Besides, you're probably doing a better job (and getting better advice when you need a bit) than the CIA etc etc.... those missing WMD that had the US, UK and us (Aust) leaping into righteous action springs to mind!

You'll always get an answer on these forums, never fails to amaze me how generous people are to share their knowledge!

Cheers

Max Hugen

http://www.AlphaFinancial.com.au

Sydney Australia