Code Help..... (1 Viewer)

Ashfaque

Student
Local time
Today, 16:47
Joined
Sep 6, 2004
Messages
759
Hi,

I have a table wherein Cno (Number) and Criteria(Text) are recorded for multiple times.
Lets say Cno 1034 has 3 times violation record (LEAVING WORK AREA) in this table so it should count
I am trying to display number of records based on above 2 fields. No error but record showing zero.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim TotRec As Integer

Set rs = CurrentDb.OpenRecordset("SELECT COUNT(*) as TotRec FROM T_IssuedWarnings " & _
"WHERE CNo = '" & CboSearchWarn.Column(1) & "' AND Criteria = 'Criteria'")


Me.TxtWarningRecords = rs!TotRec
rs.Close
End if

Why rs!TotRec resulting 0 records while there are 3 records in the table for this 1034 Cno number.

Thanks,
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:17
Joined
Feb 19, 2013
Messages
13,093
and do each of these records have the 'criteria' field populated with the word 'criteria'?
 

jocph

Member
Local time
Today, 19:17
Joined
Sep 12, 2014
Messages
57
If CNo is number data type, no quotes needed and if Criteria is an entry box in the form, try this:

Code:
" SELECT COUNT(*) as TotRec  FROM T_IssuedWarnings "  & _
" WHERE CNo = " & CboSearchWarn.Column(1) & " AND Criteria = '" & Criteria & "'"
 

arnelgp

once i caught a fish alive...
Local time
Today, 19:17
Joined
May 7, 2009
Messages
13,202
why are you using Column(1)? is CNO on that column?
you can just use DCount():

Me.TxtWarningRecords=DCount("1", "T_IssuedWarnings", "CNo = " & [CboSearchWarn] & " And [Criteria] = '" & [Criteria] & "'")
 

Ashfaque

Student
Local time
Today, 16:47
Joined
Sep 6, 2004
Messages
759
I think I didnt put my question clearly.

let us say again the Cno # 1034 has following record in table
1034 LATES
1034 VIOLATIONS
1034 LATES
1034 ABSENT
1034 ABSENT
1034 LATES
I am trying to count total record of LATES only for 1034. So in a combo on a blank form (combo contains only Cno), once selected 1034 it should count from tbl T_IssuedWarnings that "Cno 1034 has 3 LATES" and display in unbound text box called TxtWarningRecords.

Please note that source of combo that generates CNo is not based on tbl T_IssuedWarnings. It is generated from other table that has CNo data.
Thanks,
 

moke123

AWF VIP
Local time
Today, 07:17
Joined
Jan 11, 2013
Messages
2,470
Dont know your combo or field name but should be something like this
Code:
TxtWarningRecords = DCount("*","tbl T_IssuedWarnings","Cno = " & YourComboBox  & " and  YourFieldName = 'LATES'")
 

Ashfaque

Student
Local time
Today, 16:47
Joined
Sep 6, 2004
Messages
759
Yes Moke,

It worked well. I thank you all the crew...Arnep, Jocph CJ as well.

Thanks for your support...

Ashfaque
 

Users who are viewing this thread

Top Bottom