Update Top "n" query (1 Viewer)

[dtx]

I'm trying to write a query that takes the job number (existing field) and the number of records field, NbrRec (typed in number on form) and updates ATT to 0 and Verizon to 1. I have 704 records that match the job number and I enter 624 in NbrRec. I would like only 624 of the ATT records updated to 0 and 624 of the Verizon records to 1. (Essentially I don't have enough ATT air cards but I do have enough verizon.) I tried breaking it down step by step, but when I put it together, it doesn't work. This is my code:

Private Sub btn_ATT_VZN_Click()
On Error GoTo ATT_VZN_Err

Dim strSQL1, Q1 As String
Dim strSQL2, Q2 As String
Dim strSQL3, Q3 As String

strVar = [Forms]![frmScheduleNav]![NavigationSubform].[Form]![NbrRec]

strSQL1 = "SELECT tblScheduleReport.ATT, tblScheduleReport.Verizon, tblScheduleReport.[Datascan Job No#] FROM tblScheduleReport WHERE (((tblScheduleReport.[DATASCAN JOB NO#]) = [Forms]![frmScheduleNav]![NavigationSubform].[Form]![FrmtblSchedRep]![DATASCAN JOB NO#])) ORDER BY tblScheduleReport.ATT DESC;"

strSQL2 = "SELECT TOP" & strVar & "Q1.ATT, Q1.Verizon FROM Q1;"

strSQL3 = "UPDATE Q2 SET Q2.ATT = 0, q2.Verizon = 1"

ATT_VZN_EXIT:
Exit Sub
ATT_VZN_Err:
MsgBox Err.Number & Err.Description
Resume ATT_VZN_EXIT
End Sub

 

[June7]

Need a space after TOP and before Q1.ATT. The constructed string is running together without spaces.

Use a breakpoint and examine the string during code execution or use a Debug.Print strSQL2 in your code and you will see that.

Variables must be concatenated, not included within quote marks. Reference to form control is a variable.

Do you need filter criteria in the UPDATE to restrict records?

You build query statements but then don't do anything with them. SELECT statements are used to open recordset objects. Action SQL (UPDATE, INSERT, DELETE) must be executed. I use:
CurrentDb.Execute strSQL3

 

[dtx]

I'm not good with vba. It's just the only way to move the records from one field to another. What I have is 704 records with fields Job No#, ATT and Verizon. The Job No# is isolated by in a subform and ATT has a number 1 in it and Verizon has a number 0. I need to change 624 records to be the opposite. ATT, 0 and Verizon 1. Since the number of records can change I added a field to the form to pass the Top 'N'. I know this has to be a whole lot easier than I making it.

Job no# ATT Verizon
JOB10842 |1 | 0
JOB10842 |1 | 0

 

[June7]

Your declaration lines don't do what you think. Each variable data type must be explicitly defined or they default to Variant:

Dim strSQL1 As String, Q1 As String

Q1, Q2, Q3 aren't used, why declare them?

Need a unique identifier field in table. Autonumber should serve.

The statements reference Q1 and Q2 objects. Are these table names or query names?

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. JobNum or JobNbr or JobNo would be better.

 

[dtx]

I added the space after Top.
I use Q2 and Q3 in the following queries. I wasn't getting what I wanted and was trying to break the queries into step by step..... The logic I attempted was: Q1 filters to Job No# (stuck with that field, not my choice). Q2 selects the top 624 from Q1. Q3 updates the data in Q2. Currently I have a query hard coding in the 624 and a query that updates it on click, but that number could change.

 

[June7]

So all are concerned with just 1 table - tblScheduleReport? Consider:

CurrentDb.Execute "UPDATE tblScheduleReport SET ATT = 0, Verizon = 1 WHERE ID IN" & _
    " (SELECT TOP " & strVar & " ID FROM tblScheduleReport WHERE [Job No#] = " & _
          [Forms]![frmScheduleNav]![NavigationSubform].[Form]![FrmtblSchedRep]![DATASCAN JOB NO#] & _
          " ORDER BY ATT DESC)"

 

[dtx]

Much more elegant. I'm getting a Run-time error 3061. Too few parameters. Expected 3. Thanks for though. It gives me a good jump off point to work with!

 

[June7]

Do you have unique identifier field in table? Example assumes an autonumber field named ID.

If Job No# is a text field, need apostrophe delimiters.

I know the nested query will work - I tested - but I used constants, not variable input.

 

[Galaxiom]

I'm not good with vba. It's just the only way to move the records from one field to another. What I have is 704 records with fields Job No#, ATT and Verizon. The Job No# is isolated by in a subform and ATT has a number 1 in it and Verizon has a number 0. I need to change 624 records to be the opposite. ATT, 0 and Verizon 1. Since the number of records can change I added a field to the form to pass the Top 'N'. I know this has to be a whole lot easier than I making it.

Job no# ATT Verizon
JOB10842 |1 | 0
JOB10842 |1 | 0

"Moving records from one field to another" rings alarm bells.

If a job can only either be ATT or Verizon you have a normalization error since it is possible to store a 1 in both fields at the same time.

Normally this data would be stored with a single field with a code to represent either ATT or Verizon. This also supports any number of other companies you might add later without needing another field and a bigger problem with updating.

BTW If JOB is part of every Job Number then you would not store it but format the displayed value. Very much easier to increment a number than a string with mixed alpha and numeric.

And definitely never ever use the hash or any other special character in a field name. (Some qualify this with "except the underscore" but I recommend against it too.)

 

[dtx]

Thank you all so much for your help. The ID in this case is the job number unfortunately. The fields arrive the way they are and sadly have to stay that way. There are many other air card brands with their own fields, but Verizon can be substituted for ATT and visa versa and quite often are. Previously, if a change was entered for ATT to Verizon in the update field I had a query that did that. They through me a loop when they sent one asking for only 624 of the 704 ATT cards be converted. I can easily change for this one time, but I suspect it will happen again.

 

[June7]

But [Job No#] is not unique in the dataset. A unique identifier is needed to 'break the tie' otherwise all 704 records will match 'TOP N' values.

What do you mean by 'fields arrive the way they are'? Are you linking to a backend you have no control over?

The alternative is a VBA procedure.

 

[dtx]

Hmm...I could use No# as a unique identifier. It unfortunately, also has text. Example of that data is "SO0725982". Yes the data is from another system I have no control over.

 

[June7]

Doesn't matter if unique identifier is text. Just must be unique in the dataset.