Between and Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Would someone please decipher this answer for me? I need to get this table
split into 4 parts and need to know what to do.

Here is the code sent to one of the MVPs. I obviously do not understand how
to decipher this.

John Vinson 8/21/2006 10:19 AM PST



On Sun, 20 Aug 2006 23:43:01 -0700, faxylady
ZC.FIRSTNAME, ZC.FAXNUMBER, ZC.ZipCode, >ZC.Categories >FROM ZC >WHERE
(((ZC.ZCID) Between [Enter starting number:] And [Enter end number:]) >AND
((([ZC].[ZCID])>="zc1")<="zc1000")); > Leave off the second set of criteria:
SELECT ZC.ZCID, ZC.LASTNAME, ZC.FIRSTNAME, ZC.FAXNUMBER, ZC.ZipCode,
ZC.Categories FROM ZC WHERE (((ZC.ZCID) Between [Enter starting number:] And
[Enter end number:]); What you're doing with AND
((([ZC].[ZCID])>="zc1")<="zc1000")); is creating a logical expression
[ZC].[ZCID])>="zc1" which will be either TRUE or FALSE, which will be
evaluated as -1 or 0 respectively. You're then comparing that -1 or 0 with
the text string "ZC1000". That comparison will be FALSE, I'd guess. My
suggestion to use BETWEEN with the paramters was intended to be an
*alternative* to your incorrect expression - not something that you would
*add* to your incorrect expression! John W. Vinson[MVP]


I think what has complicated things is that I added my own ID prefixes here.
 
From other threads in this discussion, I believe that ZCID consists of the
letters ZC followed by numeric characters. If this is always the case, then
you could use the following.

SELECT ZC.ZCID, ZC.LASTNAME, ZC.FIRSTNAME
, ZC.FAXNUMBER, ZC.ZipCode, ZC.Categories
FROM ZC
WHERE Val(Mid(ZC.ZCID,3)) Between CLng( [Enter starting number:]) And
CLng([Enter end number:])

By the way if ZCID always starts with "ZC" then there is probably no good
reason to store the letters in the first place.
 
Thank you. The reason ZC or the prefix to the ID was added in the first
place is because this table will be appended to a much larger table
containing all the entries from various other tables. All the tables now
have a customized ID to identify each one in the BIG table.

Your response was quite helpful.

John Spencer said:
From other threads in this discussion, I believe that ZCID consists of the
letters ZC followed by numeric characters. If this is always the case, then
you could use the following.

SELECT ZC.ZCID, ZC.LASTNAME, ZC.FIRSTNAME
, ZC.FAXNUMBER, ZC.ZipCode, ZC.Categories
FROM ZC
WHERE Val(Mid(ZC.ZCID,3)) Between CLng( [Enter starting number:]) And
CLng([Enter end number:])

By the way if ZCID always starts with "ZC" then there is probably no good
reason to store the letters in the first place.


faxylady said:
Would someone please decipher this answer for me? I need to get this
table
split into 4 parts and need to know what to do.

Here is the code sent to one of the MVPs. I obviously do not understand
how
to decipher this.

John Vinson 8/21/2006 10:19 AM PST



On Sun, 20 Aug 2006 23:43:01 -0700, faxylady
ZC.FIRSTNAME, ZC.FAXNUMBER, ZC.ZipCode, >ZC.Categories >FROM ZC >WHERE
(((ZC.ZCID) Between [Enter starting number:] And [Enter end number:]) >AND
((([ZC].[ZCID])>="zc1")<="zc1000")); > Leave off the second set of
criteria:
SELECT ZC.ZCID, ZC.LASTNAME, ZC.FIRSTNAME, ZC.FAXNUMBER, ZC.ZipCode,
ZC.Categories FROM ZC WHERE (((ZC.ZCID) Between [Enter starting number:]
And
[Enter end number:]); What you're doing with AND
((([ZC].[ZCID])>="zc1")<="zc1000")); is creating a logical expression
[ZC].[ZCID])>="zc1" which will be either TRUE or FALSE, which will be
evaluated as -1 or 0 respectively. You're then comparing that -1 or 0 with
the text string "ZC1000". That comparison will be FALSE, I'd guess. My
suggestion to use BETWEEN with the paramters was intended to be an
*alternative* to your incorrect expression - not something that you would
*add* to your incorrect expression! John W. Vinson[MVP]


I think what has complicated things is that I added my own ID prefixes
here.
Click to expand...
Click to expand...
 
Back
Top