Changing VBA Code from an InputBox

[Phresh10]

Hi All<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Please kindly assist with this problem. <o></o>
<o> </o>
Below is an SQL query in a VBA module that pulls data out of a database <o></o>
<o> </o>
=============================================================<o></o>
Sub CompassExtract()<o></o>
<o> </o>
sSQLDB = "PrivateDataBase"<o></o>
mstrOLEDBConnect = "Provider=SQLOLEDB.1;" & _<o></o>
"Data Source=FNS-PDBSQL-01;" & _<o></o>
"Initial Catalog=" & sSQLDB & ";" & _<o></o>
"Integrated Security=SSPI"<o></o>
<o> </o>
Set gcnn = New ADODB.Connection<o></o>
gcnn.ConnectionString = mstrOLEDBConnect<o></o>
gcnn.ConnectionTimeout = 0<o></o>
gcnn.CommandTimeout = 0<o></o>
gcnn.Open<o></o>
<o> </o>
myPriData = "SELECT PriJComCode, PriGroupCode, PriZd, PriItmWinsorK "<o></o>
myPriData = myPriData & "FROM ReportData "<o></o>
myPriData = myPriData & "WHERE PriScrID BETWEEN 721 AND 725 "<o></o>
myPriData = myPriData & "AND PriItmCode = 0 "<o></o>
myPriData = myPriData & "ORDER BY PriJComCode, PriGroupCode, PriZd DESC "<o></o>
<o> </o>
Set rst = gcnn.Execute(myPriData)<o></o>
<o> </o>
Worksheets("Sheet1").Range("a2").CopyFromRecordset rst<o></o>
<o> </o>
=============================================================<o></o>
<o> </o>
I am working (but its getting very difficult) on a VBA code that will change the SQL code through an input box i.e.<o></o>
<o> </o>
For example, I may want to change the SQL query from:<o></o>
<o> </o>
myPriData = myPriData & "WHERE PriScrID BETWEEN 721 AND 725 "<o></o>
<o> </o>
to:<o></o>
<o> </o>
myPriData = myPriData & "WHERE PriScrID BETWEEN 1000 AND 2000 "<o></o>
<o> </o>
<o> </o>
by using an input box instead of going the the VBA code to change.<o></o>
<o> </o>
Please kindly assist.<o></o>
<o> </o>
<o> </o>
Kind Regards


Phresh10

 

[Derek Brown]

Perhaps something like:

Dim strFirst As String
Dim strLast As String
Dim Message As String
Dim Title As String
Dim Default As String
' replace the following line:
'myPriData = myPriData & "WHERE PriScrID BETWEEN 721 AND 725 "
' with the following lines:
Message = "Enter lowest number (between 500 and 1000)"
Default = "721"
Title = "Input number"
strFirst = InputBox(Message, Title, Default)
Message = "Enter highest number (between " & Str(CInt(strFirst) + 1) & " 1000)"
Default = Str(CStr(strFirst) + 1)
Title = "Input number"
strLast = InputBox(Message, Title, Default)
mypridata = mypridata & "WHERE PriScrID BETWEEN " & strFirst & " AND " & strLast & " "

With the wording changed as necessary.
You will also need to add some validation to make sure that the first number is in fact lower than the second number and that it is numbers that were entered etc.