reading variable "from" and "to" ZIP codes

[Dan Wasser]

How do I tell the macro to use a "from" and "to" ZIP code
range from a list of ZIP codes that reside in a worksheet?

At the bottom of this post is my macro. Part of it is:
AND (IINFO.I_ZIP>='00601' And IINFO.I_ZIP<='99999') AND

But rather than specifying 00601 and 99999 in the macro, I
want it to look at the ZIP code that resides in cells A1
(from) and B1 (to) in a worksheet called "By ZIP" So, I
imagine that Range("By ZIP!A1") and Range("By ZIP!B1") is
involved somehow, but I can't figure it out.

Thanks, in advance. Dan


With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=V1DATA;Description=Crystal Reports
Data;UID=dwasser;APP=Microsoft®
Query;WSID=DWASSER;DATABASE=V1Data;Trusted_Connection=Ye" _
), Array("s;AnsiNPW=No")), Destination:=Range
("A1"))
.CommandText = Array( _
"SELECT STAY.S_ADATE, IINFO.I_ZIP, STAY.S_STATUS,
STAY.S_NIGHTS" & Chr(13) & "" & Chr(10) & "FROM
v1data.dbo.IINFO IINFO, v1data.dbo.STAY STAY" & Chr(13)
& "" & Chr(10) & "WHERE (STAY.S_ADATE>='" & Format(Range
("Date!B1"), "mm-dd-yy") & "' And STAY.S_ADATE<='" & Format
(Range("Date!B2"), "mm-dd-yy") & "') AND
(STAY.S_STATUS='HIST') A" _
, "ND (STAY.S_NIGHTS>=1) AND (IINFO.I_ZIP>='00601'
And IINFO.I_ZIP<='99999') AND (IINFO.I_RECID =
STAY.S_IRECID)" & Chr(13) & "" & Chr(10) & "ORDER BY
IINFO.I_ZIP")
.Name = "Query from V1DATA"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

 

[bruce taylor]

Replace your actual values with variables.

Get a value to your variables BEFORE the line of code that
needs it.

To get the value use someting like ;

FromZip = 0'to re-set the value
ToZip = 0'to re-set the value

FromZip=range("A1").value
ToZip=range("B1").value

and your code that needs the from and to values .....

 

[Dan Wasser]

I still receive "compile" errors. I guess I just don't
understand how it all works. Thanks for trying but I
guess I just don't get it. Oh, well.
Dan