How do I count the nulls in a field on a table not related to my f

[ant1983]

Hi,

I have a table (tblTrainingSession) and amongst other fields there is a
txtVenue field.

Then i have a amongst all I forms a frmMainMenu. On this form i have all my
buttons etc but i want to do a section on the forms of a glimpse of stats (so
various ad-hoc things)

One of the things i want to do is to do a count of the txtVenue field (Nulls
only) in the tblTrainingSession. How do i do this?

I thought i could add a txtbox to the frmMainMenu and then right-click and
go to Expression Builder and build the following: "=
[tblTrainingSession]![txtVenue] = Null"

But that results in nothing! LOL

Ta!

 

[Dirk Goldgar]

Hi,

I have a table (tblTrainingSession) and amongst other fields there is a
txtVenue field.

Then i have a amongst all I forms a frmMainMenu. On this form i have all
my
buttons etc but i want to do a section on the forms of a glimpse of stats
(so
various ad-hoc things)

One of the things i want to do is to do a count of the txtVenue field
(Nulls
only) in the tblTrainingSession. How do i do this?

I thought i could add a txtbox to the frmMainMenu and then right-click and
go to Expression Builder and build the following: "=
[tblTrainingSession]![txtVenue] = Null"

But that results in nothing! LOL

Ta!

Try:

=DCount("*", "tblTrainingSession", "txtVenue Is Null")

 

[David C. Holley]

DCount("txtVenue","tblTrainingSession", "IsNull([txtVenue])")

I personally despise the idea of switchboards and instead of using them will
go with an alternative that might work for you.

I create a table USysMenuFunction with two fields, menu function and sort
order

0 Trailer Manifests
0 Trailer Dispatch
0 Load Lists
9 Quit

Then I create a list box on the Main Menu/Switchboard to display the menu
functions sorted by SORT ORDER then MENU FUNCTION. (The sort order field in
the table is used for a custom sort as opposed to alphabetica.)

In the onClick event of the list box, I have a sub that uses a select
statement to execute whatever code is associated with the menu function as
in.

Select [Forms]![frmMainMenu]![cboMenuFunctions]
Case "Trailer Manifests"
Case "Trailer Dispatch"
Case "Quit"
end select

This eliminates issues when you have a large number of menu options. You can
even add grouping if needed as in

100 -Accounting
101 Process Payments
102 Receive Payments
103 Maintain Accounts
199 Blank Space]
200 -Client Accounts
201 Create Order
202 View Order
203 Invoicing

Since the the Select Case works on the value in the list box, you simply
don't include a case for "-Accounting", "[Blank Space]", or "-Client
Accounts". The SORT ORDER field is then used to group the functions under
the headers.

In your scenario, this helps to save real estate because you can place the
list box into a TAB CONTROL with a tab for the menu options and one for the
statistics. Or just place the stats elsewhere on the form.

You can also expand this technique to control which users have access to
which options.

 

[ant1983]

Mmm.... Well it now brings up a number but the wrong number. It counts 12
and there is actually 14

Hi,

I have a table (tblTrainingSession) and amongst other fields there is a
txtVenue field.

Then i have a amongst all I forms a frmMainMenu. On this form i have all
my
buttons etc but i want to do a section on the forms of a glimpse of stats
(so
various ad-hoc things)

One of the things i want to do is to do a count of the txtVenue field
(Nulls
only) in the tblTrainingSession. How do i do this?

I thought i could add a txtbox to the frmMainMenu and then right-click and
go to Expression Builder and build the following: "=
[tblTrainingSession]![txtVenue] = Null"

But that results in nothing! LOL

Ta!

Try:

=DCount("*", "tblTrainingSession", "txtVenue Is Null")


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[David C. Holley]

The other two could have a zero-length string.

DCount("*", "tblTrainingSession", "Len(txtVenue) = 0") should pull the other
2.

Mmm.... Well it now brings up a number but the wrong number. It counts
12
and there is actually 14

Hi,

I have a table (tblTrainingSession) and amongst other fields there is a
txtVenue field.

Then i have a amongst all I forms a frmMainMenu. On this form i have
all
my
buttons etc but i want to do a section on the forms of a glimpse of
stats
(so
various ad-hoc things)

One of the things i want to do is to do a count of the txtVenue field
(Nulls
only) in the tblTrainingSession. How do i do this?

I thought i could add a txtbox to the frmMainMenu and then right-click
and
go to Expression Builder and build the following: "=
[tblTrainingSession]![txtVenue] = Null"

But that results in nothing! LOL

Ta!

Try:

=DCount("*", "tblTrainingSession", "txtVenue Is Null")


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

The other two could have a zero-length string.

DCount("*", "tblTrainingSession", "Len(txtVenue) = 0") should pull the
other 2.

If you want to count both zero-length strings and Nulls, that would need to
be:

=DCount("*", "tblTrainingSession", "Len(txtVenue & '') = 0")

Note that the '' in Len(txtVenue & '') is two single-quotes ('), not a
double-quote.

Note also that such an expression is going to be relatively inefficient.
The inefficiency may very well not matter in your application.

 

[ant1983]

When i copy and paste that it returns a 0

The other two could have a zero-length string.

DCount("*", "tblTrainingSession", "Len(txtVenue) = 0") should pull the other
2.

Mmm.... Well it now brings up a number but the wrong number. It counts
12
and there is actually 14

Hi,

I have a table (tblTrainingSession) and amongst other fields there is a
txtVenue field.

Then i have a amongst all I forms a frmMainMenu. On this form i have
all
my
buttons etc but i want to do a section on the forms of a glimpse of
stats
(so
various ad-hoc things)

One of the things i want to do is to do a count of the txtVenue field
(Nulls
only) in the tblTrainingSession. How do i do this?

I thought i could add a txtbox to the frmMainMenu and then right-click
and
go to Expression Builder and build the following: "=
[tblTrainingSession]![txtVenue] = Null"

But that results in nothing! LOL

Ta!


Try:

=DCount("*", "tblTrainingSession", "txtVenue Is Null")


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

.

 

[ant1983]

Thanks Dirk. I just realised thevalue it returned was actually the correct
one (I was counting the wrong field) so it works perfectly...

I wana go one further thought - how do i count the nulls in txtVenue that
has a tick-mark in another field called blnCurrent Training?

So currently i have:

=DCount("*","tblTrainingSession","numRoom Is Null")

And i tried changing it to: "=DCount("*","tblTrainingSession","numRoom Is
Null", blnCurrentTraining" = Yes)"

(apparently i cant do that )

 

[Dirk Goldgar]

This may be more efficient:

=DCount("*", "tblTrainingSession", "txtVenue Is Null or txtVenue = """"")

I agree.

 

[Dirk Goldgar]

When i copy and paste that it returns a 0

That suggests that either there is something else in the field that looks
blank, other than a Null or a zero-length string, or else you are mistaken
in thinking that there should be 14, not 12, records with Null in the field.
How did you determine that number? Were you looking at the table itself, or
at a query that maybe joined that table to another?

 

[Dirk Goldgar]

Thanks Dirk. I just realised thevalue it returned was actually the
correct
one (I was counting the wrong field) so it works perfectly...

I wana go one further thought - how do i count the nulls in txtVenue that
has a tick-mark in another field called blnCurrent Training?

So currently i have:

=DCount("*","tblTrainingSession","numRoom Is Null")

And i tried changing it to: "=DCount("*","tblTrainingSession","numRoom Is
Null", blnCurrentTraining" = Yes)"

(apparently i cant do that )

Close. Try this:

=DCount("*", "tblTrainingSession", "numRoom Is Null And
blnCurrentTraining <> 0")

Please note that the above expression should be entered all on one line,
although it may have been broken onto two lines by the newsreader.