Between and Query

F

faxylady

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.
 
J

John Spencer

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.
 
F

faxylady

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...
 
Top