Access SQL Left function 1

[Hambone321]

This Works:
strSQL = "SELECT CnAdrPrf_ZIP
FROM Alive_Alumni_Address
WHERE LEFT(CnAdrPrf_ZIP,5)
BETWEEN'"& ZipNeg &"' AND'"& ZipAdd &"'
ORDER BY CnAdrPrf_ZIP;"

This Doesn't Work:
strSQL = "SELECT LEFT(CnAdrPrf_ZIP,5)
FROM Alive_Alumni_Address
WHERE LEFT(CnAdrPrf_ZIP,5)
BETWEEN'"& ZipNeg &"' AND'"& ZipAdd &"'
ORDER BY CnAdrPrf_ZIP;"

I need to only select the first 5 from the zip code because the codes are in different formats some are 17745 and some are 17745-3748

And I'm stuck... Thanks in advance guys

 

[PHV]

Please, define "Doesn't Work".
What about this ?

strSQL = "SELECT LEFT(CnAdrPrf_ZIP,5) AS Zip5" _
 & " FROM Alive_Alumni_Address" _
 & " WHERE LEFT(CnAdrPrf_ZIP,5)" _
 & " BETWEEN '" & ZipNeg & "' AND '" & ZipAdd & "'" _
 & " ORDER BY 1"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hambone321]

Okay it seems the SQL is "working" with all the SQL statements, however the program is failing on the use the first Record when I use the second statement and PHV's suggested statement.

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/QueryDatabaseDebug.asp, line 88

Line 88 is:

myDynArray(0)=rsAlumni("CnAdrPrf_ZIP")

Setting a Dynamic Array(0) equal to the first Zip code I retrieve.

Note all of this works fine with the First SQL statement, just not the second one or PHV's suggested SQL statement.

Any help much appreciated

 

[PHV]

with my suggestion, use either this:
myDynArray(0)=rsAlumni("Zip5")
or this:
myDynArray(0)=rsAlumni.Fields(0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hambone321]

Phenomenal its working perfectly. Thanks a lot, you made my life a lot easier. Cheers!

 

[lespaul]

the reason for that is because when you were doing this:

myDynArray(0)=rsAlumni("CnAdrPrf_ZIP")

with the first query it worked because you were returning a field in the SQL with the label 'CnAdrPrf_Zip'

in the second query where you were getting the left, 5 access renames your field to something like 'Expr0001'. You would need to have code looking for 'Expr0001'

It works with PHV's correction because he named the result:

SELECT LEFT(CnAdrPrf_ZIP,5) AS Zip5"

so the code

myDynArray(0)=rsAlumni("Zip5")

references the correct label for that field. The other option,

myDynArray(0)=rsAlumni.Fields(0)

indicates that it should retrieve the first column of the result set (using a 0 based array for the fields) so that you are not required to give the new field a name.

HTH

Leslie

Come join me at New Mexico Linux Fest!

 

[LarrySteele]

Leslie, I gave you a star for taking the time to explain what was going on and why the fix works. Very helpful for OP and anyone who reads this thread.

 

[BigRed1212]

And I gave PHV a star for posting the fix that was so well explained.