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!

"ant1983" <(E-Mail Removed)> wrote in message
news5F58B33-CFBD-4284-8DC8-(E-Mail Removed)...
> 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)

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" <(E-Mail Removed)> wrote in message
news5F58B33-CFBD-4284-8DC8-(E-Mail Removed)...
> 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!

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

"Dirk Goldgar" wrote:

> "ant1983" <(E-Mail Removed)> wrote in message
> news5F58B33-CFBD-4284-8DC8-(E-Mail Removed)...
> > 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)
>

The other two could have a zero-length string.

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

"ant1983" <(E-Mail Removed)> wrote in message
news:6C99F9D1-9E49-4FB1-AB54-(E-Mail Removed)...
> Mmm.... Well it now brings up a number but the wrong number. It counts
> 12
> and there is actually 14
>
> "Dirk Goldgar" wrote:
>
>> "ant1983" <(E-Mail Removed)> wrote in message
>> news5F58B33-CFBD-4284-8DC8-(E-Mail Removed)...
>> > 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" <David.C.Holley> wrote in message
news:(E-Mail Removed)...
> 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.

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

(please reply to the newsgroup)

When i copy and paste that it returns a 0

"David C. Holley" wrote:

> The other two could have a zero-length string.
>
> DCount("*", "tblTrainingSession", "Len(txtVenue) = 0") should pull the other
> 2.
>
> "ant1983" <(E-Mail Removed)> wrote in message
> news:6C99F9D1-9E49-4FB1-AB54-(E-Mail Removed)...
> > Mmm.... Well it now brings up a number but the wrong number. It counts
> > 12
> > and there is actually 14
> >
> > "Dirk Goldgar" wrote:
> >
> >> "ant1983" <(E-Mail Removed)> wrote in message
> >> news5F58B33-CFBD-4284-8DC8-(E-Mail Removed)...
> >> > 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)
> >>
>
>
> .
>

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" wrote:

> "David C. Holley" <David.C.Holley> wrote in message
> news:(E-Mail Removed)...
> > 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.
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>
> .
>

"Clifford Bass via AccessMonster.com" <u48370@uwe> wrote in message
news:a1c4cb86e159a@uwe...
>
> This may be more efficient:
>
> =DCount("*", "tblTrainingSession", "txtVenue Is Null or txtVenue = """"")


I agree.

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

(please reply to the newsgroup)

"ant1983" <(E-Mail Removed)> wrote in message
news:F88D7D36-13B5-409F-8D59-(E-Mail Removed)...
> When i copy and paste that it returns a 0
>
> "David C. Holley" wrote:
>
>> The other two could have a zero-length string.
>>
>> DCount("*", "tblTrainingSession", "Len(txtVenue) = 0") should pull the
>> other
>> 2.


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, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)