Count function

[bobjackson]

Hi,

I want to use the count function to count how many records matching combo7 and Insp_Cat = 1, then calculate 20% of the total records. then using the Update statement update the field Insp_Type to "C" limiting the number of records to 20% of total records previously calculated.

This is as far as I've got, but if I can crack this I'm almost there, not quite referring to the separate table but I'm sure with further tinkering I'll get there.

This is the code I have to date but getting a syntax error on the count line.

Private Sub Command9_Click()
Dim strSql As String
Dim Rec_Qty As Integer
Dim Rec_Perc As Integer
'Copy all records matching WO_ID in Combo7 into Tbl_Inspections
strSql = "INSERT INTO Tbl_Inspections (WO_ID, SHT, PIN, Insp_Cat, Insp_Rnd) SELECT "
strSql = strSql & "WO_ID, SHT, PIN, Insp_Cat, Insp_Rnd FROM Qry_WO_Selection WHERE [Qry_WO_Selection].[WO_ID] = " & Me.[Combo7]
Debug.Print "strSQL value: " & strSql
CurrentDb.Execute strSql
'Return record count for all records in Tbl_Inspections matching WO_ID in Combo7 and Insp_Cat =1
Rec_Qty = Count (WO_ID & Insp_Cat) Where [WO_ID]= Me.[Combo7]& [Insp_Cat]=1 From Tbl_Inspections
Rec_Per = Rec_Qty * 0.2
'Update records for "C" 20% records using Rec_Per value in limit function of Update command
strSql = "Update Tbl_Inspections"
strSql = strSql & "Set Insp_Type = 'C' WHERE WO_ID = Me.Combo7 & Insp_Cat = 1 & Limit = Rec_Per"
Debug.Print "strSQL value: " & strSql
CurrentDb.Execute strSql
End Sub

Thanks

Rob

 

[Andrzejek]

First of all, please use TGML tags to show your code.
Second, Command9 and Combo7 are very bad names for your controls.

As far as your Count problem, please refer to Count Function information here of how to use it.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

The "&" is not used in SQL where clauses.
Which 20%? What determines if a record is chosen or not?
There are lots of errors in your code. You need to take these one step at a time and then build.
Have you looked at the results of your debug.print statements?

Your code on TGML:

Private Sub Command9_Click()
    Dim strSql As String
    Dim Rec_Qty As Integer
    Dim Rec_Perc As Integer
    [COLOR=#4E9A06]'Copy all records matching WO_ID in Combo7 into Tbl_Inspections[/color]
    strSql = "INSERT INTO Tbl_Inspections (WO_ID, SHT, PIN, Insp_Cat, Insp_Rnd) SELECT "
    strSql = strSql & "WO_ID, SHT, PIN, Insp_Cat, Insp_Rnd FROM Qry_WO_Selection "
    strSql = strSql & " WHERE [Qry_WO_Selection].[WO_ID] = " & Me.[Combo7]
    Debug.Print "strSQL value: " & strSql
    CurrentDb.Execute strSql
    [COLOR=#4E9A06]'Return record count for all records in Tbl_Inspections matching WO_ID in Combo7 and Insp_Cat =1[/color]
    Rec_Qty = Count (WO_ID & Insp_Cat) Where [WO_ID]= Me.[Combo7]& [Insp_Cat]=1 From Tbl_Inspections
    Rec_Per = Rec_Qty * 0.2
   [COLOR=#4E9A06] 'Update records for "C" 20% records using Rec_Per value in limit function of Update command[/color]
    strSql = "Update Tbl_Inspections"
    strSql = strSql & "Set Insp_Type = 'C' WHERE WO_ID = Me.Combo7 & Insp_Cat = 1 & Limit = Rec_Per"
    Debug.Print "strSQL value: " & strSql
    CurrentDb.Execute strSql
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016