I am in the process of creating a databases to track various cables that are run for a television station. All of our cables are given an alphanumeric label based on how the cable is categorized by the function of the cable. For example if we run a Cat6 cable for a network connection, the cable would be labeled NET1234 and a cable that we run for carrying a high definition video signal would be labeled HDV1234.
We will be deleting the records of the cables that are being removed as equipment is decommissioned and removed. This will result in gaps our records of the various categories of cable label. We would go from having a list of cables like:
NET1234
NET1235
NET1236
NET1237
NET1238
to a list of cable labels like:
NET1234
NET1236
NET1237
I am trying make a query that would find the gaps in the cable labels so that we can reuse the alphanumeric labels. We have 13 categories of wire that we will need to search through to find gaps. After the query is run we would get something like:
NET1235
NET1238
The data for the alphanumeric labels resides on a field labeled wireNumber on a table called wireInfo.
I though that the best way to accomplish this would be to grab the the last 4 characters of the strings in the wireNumber field on the wireInfo table and compare those 4 characters against the 4 characters in the checkNum field of the tlbWireNumCheck table.
The checkNum field on the tlbWireNumCheck is currently being populated with numbers from 1 to 10000 by a function called AddNumbers()
Code:
Public Function AddNumbers() Dim i As Long
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tblWireNumCheck", dbOpenDynaset)
For i = 1 To 10000
rs.AddNew
rs!checkNum = i
rs.Update
Next
rs.Close
Set rs = Nothing
Then I have a form called rtpWireNumberGaps where a user will select the category of wire in which they are trying to find gaps. Once the user has selected the from the drop down menu, the user with click a button that will open a query labeled qryWireNumGap_TEST.
This is the code for what happens when the Print Button on the rtpWireNumberGaps form is pressed:
Code:
Private Sub ptnWireNumGap_Click()Dim wireCatSelect As Integer
Dim wireCatString As String
wireCatSelect = Me.wireNumGapDropMenu
If wireCatSelect = 1 Then
wireCatString = "AES"
ElseIf wireCatSelect = 2 Then
wireCatString = "ASI"
ElseIf wireCatSelect = 3 Then
wireCatSring = "AUD"
Else
End If
'I used the following line to see what I was getting
'when a uses makes a selection from the dropdown menu
'that is why I assigned wireCatSelect as an integer
'and created the if statment to assign the string variable wireCatString.
'MsgBox wireCatSelect, vbOKOnly, wireCatString
'I am calling the AddNumbers Function because
'the only way I know how to exicute a function is by calling the function
Call AddNumbers
DoCmd.OpenQuery qryWireNumGap_TEST, acViewNormal, acEdit
End Sub
This is the code for the SQL query that I have been trying:
Code:
SELECT checkNum
FROM tblWireNumCheck
WHERE checkNum Not In
(Select CLng(Mid([wireNumber],4))
From wireInfo
Where Left([wireNumber],3) = From "rtpWireNumberGaps=" & wireCatString & "'" );
Code:
= From "rtpWireNumberGaps=" & wireCatString & "'" );
is my attempt at passing the variable wireCatString to the qryWireNumGap_TEST
So the process as I imagine it would go like this:
A user navigates to the rptWireNumberGaps form and selects a category from the drop down menu and clicks print.
Based on what catergory the user selects a string is assigned to a variable called wireCatString.
Then that variable is passed on to the qryWireNumGap_TEST.
The qryWireNumGap test pulls the last four characters from the strings that are in the wireNumber field on the wireInfo table and compares them against the strings that are in the checkNum field on the tblWireNumCheck table.
Then the user would have a list containing all the gaps that are in that wire category.
Now when I hard code what wire category I want to check, I get back a list of gaps that I would expect when I run open the query.
For example
Code:
SELECT checkNum
FROM tblWireNumCheck
WHERE checkNum Not In
(Select CLng(Mid([wireNumber],4))
From wireInfo
Where Left([wireNumber],3) = "AES")
gives me a list of gaps that would be what I expect based on the wire numbers that are categorized as AES.
Can I pass a variable to SQL query from a form?
I would include a copy of my database, but I just accidently wiped it out so I will need to add all of this stuff over again and then post my database.