DCount() not working (1 Viewer)

[gbil]

Good day.

i am trying to count records on table based on criteria, then show it in unbound text box. i try this code for it:

Me.myText = DCount("tFName", "tblMembers", "[tOrgNick] = 'Me.lstOrg.Column(0)'")

it does not work. then i try this one:

Dim nShow As Integer
Dim tShow As String

tShow = Me.lstOrg.Column(0)
nShow = DCount("*", "tblMembers", "[tOrgNick] = 'tShow'")

Me.myText = nShow //edit: forgot this line

it also did not work. in watch window, tShow = "Lfarmer", nShow = 0

there are 16 records for "Lfarmer" in the table.

please enlighten me.

btw, i remember there is a "North Wind" (or something) sample database application in previous version of access but i cannot find it in help section. where can i find it?

 

[CJ_London]

try

Me.myText = DCount("tFName", "tblMembers", "[tOrgNick] = '"[COLOR=red] &[/COLOR] Me.lstOrg.Column(0) [COLOR=red]& "[/COLOR]'")

Note the way the criteria is now constructed

 

[Cronk]

CJ

A typo here. Should be
Me.myText = DCount("tFName", "tblMembers", "[tOrgNick] = '" & Me.lstOrg.Column(0) & "'")ISingle quotes required if column(0) contains text, no quotes if it is an index number.

 

[CJ_London]

well spotted! Have corrected my post

 

[gbil]

thank you cj and cronk.

it now works perfectly.