Code Help..... (1 Viewer)

[Ashfaque]

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]

and do each of these records have the 'criteria' field populated with the word 'criteria'?

 

[jocph]

If CNo is number data type, no quotes needed and if Criteria is an entry box in the form, try this:

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

 

[arnelgp]

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]

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]

Dont know your combo or field name but should be something like this

TxtWarningRecords = DCount("*","tbl T_IssuedWarnings","Cno = " & YourComboBox  & " and  YourFieldName = 'LATES'")

 

[Ashfaque]

Yes Moke,

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

Thanks for your support...

Ashfaque

 

[moke123]

We're all happy to help.
Good luck with your project.