Query to pull records between two values (1 Viewer)

cwestca

New member
Local time
Today, 08:17
Joined
Sep 25, 2018
Messages
2
I have a table called lotContents that has two fields in it; mrn(primaryKey) and partNum both are a short-text datatype. I have created a form called workOrderCreationForm that has a text box that auto-generates a work order number. Then the two other text-boxes on the form are called firstMrn and lastMrn respectively. I'm trying to find a way to query the results between two values and add the Work Order Number to each record and then place the new data in the workOrderContents table(mrn,partNum, workOrderNumber(foreign Key)).I have written a sql statement to query the results from lotContents between the two textboxes(firstMrn and lastMrn). The Sql statement looks like this:

Code:
Private Sub Command8_Click()
'set the work order number to the "woNum" variable
woNum = Me.workOrderNumber_TextBOx
'select all mrn's between the first and last mrn values
'set firstMrn_TextBox to the variable "firstMrn"
firstMrn = Me.firstMrn_TextBox
'set lastMrn_TextBox to the variable "lastMrn"
lastMrn = Me.lastMrn_TextBox
'set the sql statement
SQL = "INSERT INTO workOrderContents" _
    & "SELECT lotContent.mrn, lotContent.partNumber" _
    & "FROM lotContent" _
    & "WHERE lotContent.mrn BETWEEN '" & firstMrn & "' AND '" & lastMrn & "'" _
    & "ORDER BY lotContent.mrn"
End Sub
I have two questions, how can I get this sql statment to actually work and send the queried data of this query to the table(workOrderContents)and second how can I appended the variable workOrderNumber to each record so when viewed from the workOrderContents table you will see mrn,partNum,workOrderNumber.
 

JHB

Have been here a while
Local time
Today, 16:17
Joined
Jun 17, 2012
Messages
7,732
First, you SQL-string isn't valid because you need spaces at the end on each line, (marked with red):
Code:
  SQL = "INSERT INTO workOrderContent[B][COLOR=Red]s "[/COLOR][/B] _
    & "SELECT lotContent.mrn, lotContent.partNumbe[B][COLOR=red]r "[/COLOR][/B] _
    & "FROM lotConten[B][COLOR=red]t " [/COLOR][/B]_
    & "WHERE lotContent.mrn BETWEEN '" & firstMrn & "' AND '" & lastMrn & "[B][COLOR=red]' "[/COLOR][/B] _
    & "ORDER BY lotContent.mrn"
Execute the SQL-String using DoCmd.RunSQL.
Second, always declare your variables using DIM, and add the Option explicit at the top of each module, it Force explicit variable declaration which would save you a lot of time later, (it will alert you when you use unknown variables or controls), when you create larger and more complex procedures.
Third, don't use MS-Access reserved words, SQL is a reserved words, it can cause unforeseen errors.
Your question no. 2, just add the value to the SQL-string.
Code:
 .., lotContent.partNumber, " & woNum & " as workOrderNumber " &  _
So your sub will be:
Code:
Option Compare Database
Option Explicit    ' Force explicit variable declaration.

Private Sub Command8_Click()
  Dim SQLString As String
  Dim woNum As Long
  Dim firstMrn As String, lastMrn As String
  'set the work order number to the "woNum" variable
  woNum = Me.workOrderNumber_TextBOx
  'select all mrn's between the first and last mrn values
  'set firstMrn_TextBox to the variable "firstMrn"
  firstMrn = Me.firstMrn_TextBox
  'set lastMrn_TextBox to the variable "lastMrn"
  lastMrn = Me.lastMrn_TextBox
  'set the sql statement
  SQLString = "INSERT INTO workOrderContents " _
   & "SELECT lotContent.mrn, lotContent.partNumber, " & woNum & " as workOrderNumber " _
   & "FROM lotContent " _
   & "WHERE lotContent.mrn BETWEEN '" & firstMrn & "' AND '" & lastMrn & "' " _
   & "ORDER BY lotContent.mrn"
   DoCmd.RunSQL (SQLString)
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:17
Joined
May 7, 2009
Messages
19,169
if woNum is numeric:

SQL = "INSERT INTO workOrderContents (workordernumber, mrn, partnum) " _
& "SELECT " & woNum & ", lotContent.mrn, lotContent.partNumber " _
& "FROM lotContent " _
& "WHERE lotContent.mrn BETWEEN '" & firstMrn & "' AND '" & lastMrn & "' " _
& "ORDER BY lotContent.mrn"

if woNum is string:

SQL = "INSERT INTO workOrderContents (workordernumber, mrn, partnum) " _
& "SELECT '" & woNum & "', lotContent.mrn, lotContent.partNumber " _
& "FROM lotContent " _
& "WHERE lotContent.mrn BETWEEN '" & firstMrn & "' AND '" & lastMrn & "' " _
& "ORDER BY lotContent.mrn"
 

cwestca

New member
Local time
Today, 08:17
Joined
Sep 25, 2018
Messages
2
This worked beautifully, I really appreciate the help and tips. I'll keep them in mind going forward.
 

Users who are viewing this thread

Top Bottom