Form Formula Question

[rciolkosz]

A column in my table can be only one of four words. On my data entry form
how can I add a field to identify a running count of each of the four words
used in the table? Example: Green 24 records, red 18 records, blue 4 records
etc.

 

[Ray C]

1) Add a textbox for each of the four words in your data entry form
2) For each textbox, go into the properties window and set its recordsource
property to :

=DCount("YourField", "YourTable", "YourField = 'TheColor' ")

Be careful with the double-quotes and the single quotes.

There are other more elegant ways to do this but this is the simplest.
Please note that this function (DCOUNT) returns NULL if it doesn't find any
records that match your criteria. If you want to show a zero (0) then you
need to incorporate another function (IIF) :

=IIf(IsNull(DCount("YourField", "YourTable", "YourField = 'TheColor'
")),0,DCount("YourField", "YourTable", "YourField = 'TheColor' "))

Hope this helps

Ray

 

[Klatuu]

An okay idea, with a couple of exceptions.
Using a field name in a DCount is pointless and only slows it down.

=DCount("*", "YourTable", "YourField = ""TheColor""")
would be better, but this doesn't account for any filtering that may occur
on the form.

Using the IIf causes the DCount to execute twice. Not good, it will make
the form sluggish because now you have 6 instead of 3. It would be better to
use the Nz function:
=Nz(DCount("*", "YourTable", "YourField = ""TheColor"""),0)

 

[fredg]

A column in my table can be only one of four words. On my data entry form
how can I add a field to identify a running count of each of the four words
used in the table? Example: Green 24 records, red 18 records, blue 4 records
etc.

The table is the record source for the form?
All in the same control?
On different lines?
Add an Unbound text control to the form.
Set it's control source to:
="Green .. " & Abs(Sum([FieldName] = "Green")) & chr(13) & chr(10) &
" Red .. " & Abs(Sum([FieldName]= "red")) & chr(13) & chr(10) & "Blue
... " & Abs(...... etc ....

Size it tall enough to display all the data.

 

[Lars Brownie]

Other option would be to create a query that calculates the desired values
and drag the query into the form. Requery the sub'form'(query) in the
afterupdate event of YourField.

Lars

 

[rciolkosz]

Let me ask you this. Can you data entry into a table through a query? yes or
no.