Hi JennEx,
Where did this SQL statement come from:
Quote:
|
SQLStatement:="SELECT * FROM `CONTROL_1$` where 'Type$'='FR' And 'SubResp'='WPL1' ORDER BY 'Start' ASC, 'Facility B$' ASC, 'Unit$' ASC"
|
Does it work correctly? I'd have expected something like:
Quote:
|
SQLStatement:="SELECT * FROM `CONTROL_1$` WHERE [Type$]='FR' AND [SubResp]='WPL1' ORDER BY [Start] ASC, [Facility B$] ASC, [Unit$] ASC"
|
Regarding:
The asterisks shouldn't be there and, as indicated above, I'd have expected:
As for the variable handling, you could approach that like:
Code:
Dim StrType As String, StrSub As String
StrSrc = ActiveDocument.Path & "\GBContributions 2013.xls"
StrType = Trim(InputBox("Please input the Type #, as one of:" & vbCr & _
vbTab & "1: DR" & vbTab & "2: DT" & vbTab & "3: FR" & vbCr & _
vbTab & "4: FT" & vbTab & "5: CR" & vbTab & "6: CT"))
If Not IsNumeric(StrType) Then Exit Sub
If StrType < 1 Or StrType > 6 Then Exit Sub
StrSub = Trim(InputBox("Please input the SubResp #, as one of:" & vbCr & _
"1: CUE1" & vbTab & "2: CUL1" & vbTab & "3: WPE1" & vbTab & "4: WPL1" & vbCr & _
"5: RPE1" & vbTab & "6: RPL1" & vbTab & "7: HPE1" & vbTab & "8: HPL1"))
If Not IsNumeric(StrSub) Then Exit Sub
If StrSub < 1 Or StrSub > 8 Then Exit Sub
StrType = Split(",DR,DT,FR,FT,CR,CT", ",")(StrType)
StrSub = Split(",CUE1,CUL1,WPE1,WPL1,RPE1,RPL1,HPE1,HPL1", ",")(StrSub)
...
SQLStatement:="SELECT * FROM `CONTROL_1$` WHERE [Type$]='" & StrType & "' AND [SubResp]='" & StrSub & "'"