INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Count function

Count function

(OP)
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

RE: Count function

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.

RE: Count function

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:

CODE --> vba

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 "
    strSql = strSql & " 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 

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close