convert range of numbers in one record into range of single-number records

I have a table of product registrations. All entries have a product
number. Some entries have a single serial number, and some have a
range of serial numbers. I would like to remove all records containing
a range and replace them with multiple records, one per number in the
range.

For example, say I have four records:

Product     Serial     First     Last
F21           1
F21                        2          10
F11                        1          10
F11           11

I would like to wave my magic query-wand and end up with my table
looking like this:

Product     Serial     First     Last
F21           1
F21           2
F21           3
F21           4
F21           5
F21           6
F21           7
F21           8
F21           9
F21           10
F11           1
F11           2
F11           3
F11           4
F11           5
F11           6
F11           7
F11           8
F11           9
F11           10
F11           11

I know it will involve SELECT (to find the records with non-null
ranges), INSERT (to put the individual serial records in the table),
DELETE (to get rid of the range records), and x = [first] to [last]
step 1 (to generate the serial number for each record in the range -
although I'm not sure if that's the correct syntax for SQL), but I'm
not sure how to put that all together. Any ideas?

0
shadowsong
7/16/2007 6:47:43 PM
access.queries 6343 articles. 1 followers. Follow

14 Replies
1094 Views

Similar Articles

[PageSpeed] 18

Is this going to be a "one-time update and prevent this from occurring
in future" update?

Cheers,
Jason Lepack

On Jul 16, 2:47 pm, shadows...@gmail.com wrote:
> I have a table of product registrations. All entries have a product
> number. Some entries have a single serial number, and some have a
> range of serial numbers. I would like to remove all records containing
> a range and replace them with multiple records, one per number in the
> range.
>
> For example, say I have four records:
>
> Product     Serial     First     Last
> F21           1
> F21                        2          10
> F11                        1          10
> F11           11
>
> I would like to wave my magic query-wand and end up with my table
> looking like this:
>
> Product     Serial     First     Last
> F21           1
> F21           2
> F21           3
> F21           4
> F21           5
> F21           6
> F21           7
> F21           8
> F21           9
> F21           10
> F11           1
> F11           2
> F11           3
> F11           4
> F11           5
> F11           6
> F11           7
> F11           8
> F11           9
> F11           10
> F11           11
>
> I know it will involve SELECT (to find the records with non-null
> ranges), INSERT (to put the individual serial records in the table),
> DELETE (to get rid of the range records), and x = [first] to [last]
> step 1 (to generate the serial number for each record in the range -
> although I'm not sure if that's the correct syntax for SQL), but I'm
> not sure how to put that all together. Any ideas?


0
Jason
7/16/2007 6:51:22 PM
Unfortunately not, it will be recurring. The data coming into the
database comes from our customers who can't be arsed to write out
every serial number, and submit forms with ranges on them. That's why
I was planning to delete the range entries once they'd been re-entered
as single entries - so the next time around, the query would only be
looking at new range entries, and the old already-processed ones would
be gone.


On Jul 16, 11:51 am, Jason Lepack <jlep...@gmail.com> wrote:
> Is this going to be a "one-time update and prevent this from occurring
> in future" update?
>
> Cheers,
> Jason Lepack
>
> On Jul 16, 2:47 pm, shadows...@gmail.com wrote:
>
> > I have a table of product registrations. All entries have a product
> > number. Some entries have a single serial number, and some have a
> > range of serial numbers. I would like to remove all records containing
> > a range and replace them with multiple records, one per number in the
> > range.
>
> > For example, say I have four records:
>
> > Product     Serial     First     Last
> > F21           1
> > F21                        2          10
> > F11                        1          10
> > F11           11
>
> > I would like to wave my magic query-wand and end up with my table
> > looking like this:
>
> > Product     Serial     First     Last
> > F21           1
> > F21           2
> > F21           3
> > F21           4
> > F21           5
> > F21           6
> > F21           7
> > F21           8
> > F21           9
> > F21           10
> > F11           1
> > F11           2
> > F11           3
> > F11           4
> > F11           5
> > F11           6
> > F11           7
> > F11           8
> > F11           9
> > F11           10
> > F11           11
>
> > I know it will involve SELECT (to find the records with non-null
> > ranges), INSERT (to put the individual serial records in the table),
> > DELETE (to get rid of the range records), and x = [first] to [last]
> > step 1 (to generate the serial number for each record in the range -
> > although I'm not sure if that's the correct syntax for SQL), but I'm
> > not sure how to put that all together. Any ideas?


0
shadowsong
7/16/2007 7:03:29 PM
shadowsong@gmail.com wrote:

>I have a table of product registrations. All entries have a product
>number. Some entries have a single serial number, and some have a
>range of serial numbers. I would like to remove all records containing
>a range and replace them with multiple records, one per number in the
>range.
>
>For example, say I have four records:
>
>Product     Serial     First     Last
>F21           1
>F21                        2          10
>F11                        1          10
>F11           11
>
>I would like to wave my magic query-wand and end up with my table
>looking like this:
>
>Product     Serial     First     Last
>F21           1
>F21           2
>F21           3
>F21           4
>F21           5
>F21           6
>F21           7
>F21           8
>F21           9
>F21           10
>F11           1
>F11           2
>F11           3
>F11           4
>F11           5
>F11           6
>F11           7
>F11           8
>F11           9
>F11           10
>F11           11


First create a table(named Numbers) with one field (named
Num). Populate the table with the values 0, 1, 2, ... up to
more than you will ever have a range.

Then use a query like:
SELECT Product, Num
FROM tblproducts, Numbers
WHERE Num Between Nz([First], Serial) AndNz([Last], Serial)
Order By Num

-- 
Marsh
MVP [MS Access]
0
Marshall
7/16/2007 8:17:56 PM
On Jul 16, 1:17 pm, Marshall Barton <marshbar...@wowway.com> wrote:
> shadows...@gmail.com wrote:
> >I have a table of product registrations. All entries have a product
> >number. Some entries have a single serial number, and some have a
> >range of serial numbers. I would like to remove all records containing
> >a range and replace them with multiple records, one per number in the
> >range.
>
> >For example, say I have four records:
>
> >Product     Serial     First     Last
> >F21           1
> >F21                        2          10
> >F11                        1          10
> >F11           11
>
> >I would like to wave my magic query-wand and end up with my table
> >looking like this:
>
> >Product     Serial     First     Last
> >F21           1
> >F21           2
> >F21           3
> >F21           4
> >F21           5
> >F21           6
> >F21           7
> >F21           8
> >F21           9
> >F21           10
> >F11           1
> >F11           2
> >F11           3
> >F11           4
> >F11           5
> >F11           6
> >F11           7
> >F11           8
> >F11           9
> >F11           10
> >F11           11
>
> First create a table(named Numbers) with one field (named
> Num). Populate the table with the values 0, 1, 2, ... up to
> more than you will ever have a range.
>
> Then use a query like:
> SELECT Product, Num
> FROM tblproducts, Numbers
> WHERE Num Between Nz([First], Serial) AndNz([Last], Serial)
> Order By Num
>
> --
> Marsh
> MVP [MS Access]

These are 7 digit serial numbers - that would make a rather large
table.

Isn't there a SQL equivalent to the VB way to do this?

vRecordset.AddNew
For x = [first] to [last] step 1
vRecordSet("PRODUCT") = .query("PRODUCT")
tblSERIAL = x
Next x

Does SQL have anything that works the same way as For...Next,
While...End While, or Do...Loop?

0
shadowsong
7/16/2007 9:58:39 PM
On Jul 16, 2:58 pm, shadows...@gmail.com wrote:
> On Jul 16, 1:17 pm, Marshall Barton <marshbar...@wowway.com> wrote:
>
>
>
> > shadows...@gmail.com wrote:
> > >I have a table of product registrations. All entries have a product
> > >number. Some entries have a single serial number, and some have a
> > >range of serial numbers. I would like to remove all records containing
> > >a range and replace them with multiple records, one per number in the
> > >range.
>
> > >For example, say I have four records:
>
> > >Product     Serial     First     Last
> > >F21           1
> > >F21                        2          10
> > >F11                        1          10
> > >F11           11
>
> > >I would like to wave my magic query-wand and end up with my table
> > >looking like this:
>
> > >Product     Serial     First     Last
> > >F21           1
> > >F21           2
> > >F21           3
> > >F21           4
> > >F21           5
> > >F21           6
> > >F21           7
> > >F21           8
> > >F21           9
> > >F21           10
> > >F11           1
> > >F11           2
> > >F11           3
> > >F11           4
> > >F11           5
> > >F11           6
> > >F11           7
> > >F11           8
> > >F11           9
> > >F11           10
> > >F11           11
>
> > First create a table(named Numbers) with one field (named
> > Num). Populate the table with the values 0, 1, 2, ... up to
> > more than you will ever have a range.
>
> > Then use a query like:
> > SELECT Product, Num
> > FROM tblproducts, Numbers
> > WHERE Num Between Nz([First], Serial) AndNz([Last], Serial)
> > Order By Num
>
> > --
> > Marsh
> > MVP [MS Access]
>
> These are 7 digit serial numbers - that would make a rather large
> table.
>
> Isn't there a SQL equivalent to the VB way to do this?
>
> vRecordset.AddNew
> For x = [first] to [last] step 1
> vRecordSet("PRODUCT") = .query("PRODUCT")
> tblSERIAL = x
> Next x
>
> Does SQL have anything that works the same way as For...Next,
> While...End While, or Do...Loop?

Actually, now that I think about it, can this just be done as a VB
macro in Access? I just need some way to tell the macro to repeat that
"For x" loop for each record in the query results. Is it possible to
define the query results as a collection and each record in the query
as an element? I'm not very familiar with how collections work - it
might be that the fields are items in a collection, not each record.

0
shadowsong
7/16/2007 11:09:41 PM
shadowsong@gmail.com wrote:

>On Jul 16, 2:58 pm, shadows...@gmail.com wrote:
>> On Jul 16, 1:17 pm, Marshall Barton  wrote:
>> > shadows...@gmail.com wrote:
>> > >I have a table of product registrations. All entries have a product
>> > >number. Some entries have a single serial number, and some have a
>> > >range of serial numbers. I would like to remove all records containing
>> > >a range and replace them with multiple records, one per number in the
>> > >range.
>>
>> > >For example, say I have four records:
>>
>> > >Product     Serial     First     Last
>> > >F21           1
>> > >F21                        2          10
>> > >F11                        1          10
>> > >F11           11
>>
>> > >I would like to wave my magic query-wand and end up with my table
>> > >looking like this:
>>
>> > >Product     Serial     First     Last
>> > >F21           1
>> > >F21           2
>> > >F21           3
>> > >F21           4
>> > >F21           5
>> > >F21           6
>> > >F21           7
>> > >F21           8
>> > >F21           9
>> > >F21           10
>> > >F11           1
>> > >F11           2
>> > >F11           3
>> > >F11           4
>> > >F11           5
>> > >F11           6
>> > >F11           7
>> > >F11           8
>> > >F11           9
>> > >F11           10
>> > >F11           11
>>
>> > First create a table(named Numbers) with one field (named
>> > Num). Populate the table with the values 0, 1, 2, ... up to
>> > more than you will ever have a range.
>>
>> > Then use a query like:
>> > SELECT Product, Num
>> > FROM tblproducts, Numbers
>> > WHERE Num Between Nz([First], Serial) AndNz([Last], Serial)
>> > Order By Num
>>
>>
>> These are 7 digit serial numbers - that would make a rather large
>> table.
>>
>> Isn't there a SQL equivalent to the VB way to do this?
>>
>> vRecordset.AddNew
>> For x = [first] to [last] step 1
>> vRecordSet("PRODUCT") = .query("PRODUCT")
>> tblSERIAL = x
>> Next x
>>
>> Does SQL have anything that works the same way as For...Next,
>> While...End While, or Do...Loop?
>
>Actually, now that I think about it, can this just be done as a VB
>macro in Access? I just need some way to tell the macro to repeat that
>"For x" loop for each record in the query results. Is it possible to
>define the query results as a collection and each record in the query
>as an element? I'm not very familiar with how collections work - it
>might be that the fields are items in a collection, not each record.



You don't want to use code to do things that a query can do.
After all, you are working in a database here.  Since SQL
uses set based logic, it does not include looping
constructs.

I should have suggested this first.  Just modify the query a
little to work within the range in the records instead of
for all serial numbers:

SELECT Product, Num
FROM tblproducts, Numbers
WHERE Nz([First], Serial) + Num <= Nz([Last], Serial)
Order By Num

This way, the Numbers table only needs to go up to the
number of serials in the range.

-- 
Marsh
MVP [MS Access]
0
Marshall
7/16/2007 11:50:30 PM
On Jul 16, 4:50 pm, Marshall Barton <marshbar...@wowway.com> wrote:
> shadows...@gmail.com wrote:
> >On Jul 16, 2:58 pm, shadows...@gmail.com wrote:
> >> On Jul 16, 1:17 pm, Marshall Barton  wrote:
> >> > shadows...@gmail.com wrote:
> >> > >I have a table of product registrations. All entries have a product
> >> > >number. Some entries have a single serial number, and some have a
> >> > >range of serial numbers. I would like to remove all records containing
> >> > >a range and replace them with multiple records, one per number in the
> >> > >range.
>
> >> > >For example, say I have four records:
>
> >> > >Product     Serial     First     Last
> >> > >F21           1
> >> > >F21                        2          10
> >> > >F11                        1          10
> >> > >F11           11
>
> >> > >I would like to wave my magic query-wand and end up with my table
> >> > >looking like this:
>
> >> > >Product     Serial     First     Last
> >> > >F21           1
> >> > >F21           2
> >> > >F21           3
> >> > >F21           4
> >> > >F21           5
> >> > >F21           6
> >> > >F21           7
> >> > >F21           8
> >> > >F21           9
> >> > >F21           10
> >> > >F11           1
> >> > >F11           2
> >> > >F11           3
> >> > >F11           4
> >> > >F11           5
> >> > >F11           6
> >> > >F11           7
> >> > >F11           8
> >> > >F11           9
> >> > >F11           10
> >> > >F11           11
>
> >> > First create a table(named Numbers) with one field (named
> >> > Num). Populate the table with the values 0, 1, 2, ... up to
> >> > more than you will ever have a range.
>
> >> > Then use a query like:
> >> > SELECT Product, Num
> >> > FROM tblproducts, Numbers
> >> > WHERE Num Between Nz([First], Serial) AndNz([Last], Serial)
> >> > Order By Num
>
> >> These are 7 digit serial numbers - that would make a rather large
> >> table.
>
> >> Isn't there a SQL equivalent to the VB way to do this?
>
> >> vRecordset.AddNew
> >> For x = [first] to [last] step 1
> >> vRecordSet("PRODUCT") = .query("PRODUCT")
> >> tblSERIAL = x
> >> Next x
>
> >> Does SQL have anything that works the same way as For...Next,
> >> While...End While, or Do...Loop?
>
> >Actually, now that I think about it, can this just be done as a VB
> >macro in Access? I just need some way to tell the macro to repeat that
> >"For x" loop for each record in the query results. Is it possible to
> >define the query results as a collection and each record in the query
> >as an element? I'm not very familiar with how collections work - it
> >might be that the fields are items in a collection, not each record.
>
> You don't want to use code to do things that a query can do.
> After all, you are working in a database here.  Since SQL
> uses set based logic, it does not include looping
> constructs.
>
> I should have suggested this first.  Just modify the query a
> little to work within the range in the records instead of
> for all serial numbers:
>
> SELECT Product, Num
> FROM tblproducts, Numbers
> WHERE Nz([First], Serial) + Num <= Nz([Last], Serial)
> Order By Num
>
> This way, the Numbers table only needs to go up to the
> number of serials in the range.
>
> --
> Marsh
> MVP [MS Access]

I'm having a brain block - what does Nz() refer to?

I think this should do what I need: find all serial range records,
convert into one record for each serial contained in the range, and
insert those new records into the original table. now that all the
ranges have unique records, take range-only records out of the table
entirely, and delete range info from records that also have single
serial numbers.

INSERT INTO tblproducts
(product, serial)
SELECT Product, tblproducts.[First] + numbers.[Num] as serial
FROM tblproducts, Numbers
WHERE tblproducts.[Last] Is Not Null AND serial <= tblproducts.[Last]
Order By Num

UPDATE tblproducts
SET [First] = "", [Last] = ""
WHERE [Serial] Is Not Null AND [Last] Is Not Null

DELETE FROM tblproducts
WHERE [Serial] Is Null


Any syntax that I've messed up, or anything that will otherwise go
horribly wrong with the code above?

Thanks for your help,
Joanna

0
shadowsong
7/20/2007 5:04:05 PM
On Jul 20, 10:04 am, shadows...@gmail.com wrote:
> On Jul 16, 4:50 pm, Marshall Barton <marshbar...@wowway.com> wrote:
>
>
>
> > shadows...@gmail.com wrote:
> > >On Jul 16, 2:58 pm, shadows...@gmail.com wrote:
> > >> On Jul 16, 1:17 pm, Marshall Barton  wrote:
> > >> > shadows...@gmail.com wrote:
> > >> > >I have a table of product registrations. All entries have a product
> > >> > >number. Some entries have a single serial number, and some have a
> > >> > >range of serial numbers. I would like to remove all records containing
> > >> > >a range and replace them with multiple records, one per number in the
> > >> > >range.
>
> > >> > >For example, say I have four records:
>
> > >> > >Product     Serial     First     Last
> > >> > >F21           1
> > >> > >F21                        2          10
> > >> > >F11                        1          10
> > >> > >F11           11
>
> > >> > >I would like to wave my magic query-wand and end up with my table
> > >> > >looking like this:
>
> > >> > >Product     Serial     First     Last
> > >> > >F21           1
> > >> > >F21           2
> > >> > >F21           3
> > >> > >F21           4
> > >> > >F21           5
> > >> > >F21           6
> > >> > >F21           7
> > >> > >F21           8
> > >> > >F21           9
> > >> > >F21           10
> > >> > >F11           1
> > >> > >F11           2
> > >> > >F11           3
> > >> > >F11           4
> > >> > >F11           5
> > >> > >F11           6
> > >> > >F11           7
> > >> > >F11           8
> > >> > >F11           9
> > >> > >F11           10
> > >> > >F11           11
>
> > >> > First create a table(named Numbers) with one field (named
> > >> > Num). Populate the table with the values 0, 1, 2, ... up to
> > >> > more than you will ever have a range.
>
> > >> > Then use a query like:
> > >> > SELECT Product, Num
> > >> > FROM tblproducts, Numbers
> > >> > WHERE Num Between Nz([First], Serial) AndNz([Last], Serial)
> > >> > Order By Num
>
> > >> These are 7 digit serial numbers - that would make a rather large
> > >> table.
>
> > >> Isn't there a SQL equivalent to the VB way to do this?
>
> > >> vRecordset.AddNew
> > >> For x = [first] to [last] step 1
> > >> vRecordSet("PRODUCT") = .query("PRODUCT")
> > >> tblSERIAL = x
> > >> Next x
>
> > >> Does SQL have anything that works the same way as For...Next,
> > >> While...End While, or Do...Loop?
>
> > >Actually, now that I think about it, can this just be done as a VB
> > >macro in Access? I just need some way to tell the macro to repeat that
> > >"For x" loop for each record in the query results. Is it possible to
> > >define the query results as a collection and each record in the query
> > >as an element? I'm not very familiar with how collections work - it
> > >might be that the fields are items in a collection, not each record.
>
> > You don't want to use code to do things that a query can do.
> > After all, you are working in a database here.  Since SQL
> > uses set based logic, it does not include looping
> > constructs.
>
> > I should have suggested this first.  Just modify the query a
> > little to work within the range in the records instead of
> > for all serial numbers:
>
> > SELECT Product, Num
> > FROM tblproducts, Numbers
> > WHERE Nz([First], Serial) + Num <= Nz([Last], Serial)
> > Order By Num
>
> > This way, the Numbers table only needs to go up to the
> > number of serials in the range.
>
> > --
> > Marsh
> > MVP [MS Access]
>
> I'm having a brain block - what does Nz() refer to?
>
> I think this should do what I need: find all serial range records,
> convert into one record for each serial contained in the range, and
> insert those new records into the original table. now that all the
> ranges have unique records, take range-only records out of the table
> entirely, and delete range info from records that also have single
> serial numbers.
>
> INSERT INTO tblproducts
> (product, serial)
> SELECT Product, tblproducts.[First] + numbers.[Num] as serial
> FROM tblproducts, Numbers
> WHERE tblproducts.[Last] Is Not Null AND serial <= tblproducts.[Last]
> Order By Num
>
> UPDATE tblproducts
> SET [First] = "", [Last] = ""
> WHERE [Serial] Is Not Null AND [Last] Is Not Null
>
> DELETE FROM tblproducts
> WHERE [Serial] Is Null
>
> Any syntax that I've messed up, or anything that will otherwise go
> horribly wrong with the code above?
>
> Thanks for your help,
> Joanna

okay, making this simpler. with numbers 0-500 in the num column, for a
record where first is 0102049 and last is 0102050,

SELECT DATA.MODEL, DATA.FIRST, DATA.LAST, [DATA].[FIRST]+[NUMBER].
[NUM] AS SN
FROM DATA, [NUMBER]
WHERE (((DATA.FIRST)="0102049") AND ((DATA.LAST) Is Not Null));

returns 501 results, and

SELECT DATA.MODEL, DATA.FIRST, DATA.LAST, [DATA].[FIRST]+[NUMBER].
[NUM] AS SN
FROM DATA, [NUMBER]
WHERE (((DATA.FIRST)="0102049") AND ((DATA.LAST) Is Not Null) AND
(([DATA].[FIRST]+[NUMBER].[NUM])<=[data].[last]));

returns 0 instead of returning 2.

what am I doing wrong? obviously it's something with the calculated
field constraint, but i'm not sure what.

0
shadowsong
7/20/2007 7:00:31 PM
shadowsong@gmail.com wrote:
>I'm having a brain block - what does Nz() refer to?

Nz(x, y) returns x when x is not Null.  If z is Null, then
it returns y

You can find all of the built-in functions in VBA Help.


>I think this should do what I need: find all serial range records,
>convert into one record for each serial contained in the range, and
>insert those new records into the original table. now that all the
>ranges have unique records, take range-only records out of the table
>entirely, and delete range info from records that also have single
>serial numbers.
>
>INSERT INTO tblproducts
>(product, serial)
>SELECT Product, tblproducts.[First] + numbers.[Num] as serial
>FROM tblproducts, Numbers
>WHERE tblproducts.[Last] Is Not Null AND serial <= tblproducts.[Last]
>Order By Num
>
>UPDATE tblproducts
>SET [First] = "", [Last] = ""
>WHERE [Serial] Is Not Null AND [Last] Is Not Null
>
>DELETE FROM tblproducts
>WHERE [Serial] Is Null
>
>
>Any syntax that I've messed up, or anything that will otherwise go
>horribly wrong with the code above?


I think that look ok, except that I thought that the First
and Last fields were a numeric type.  You should be setting
them to Null instead of ""

Be sure to test all that on copies of the tables so you can
quicky recover from any mistakes.

-- 
Marsh
MVP [MS Access]
0
Marshall
7/20/2007 7:32:14 PM
shadowsong@gmail.com wrote:

>okay, making this simpler. with numbers 0-500 in the num column, for a
>record where first is 0102049 and last is 0102050,
>
>SELECT DATA.MODEL, DATA.FIRST, DATA.LAST, [DATA].[FIRST]+[NUMBER].
>[NUM] AS SN
>FROM DATA, [NUMBER]
>WHERE (((DATA.FIRST)="0102049") AND ((DATA.LAST) Is Not Null));
>
>returns 501 results, and
>
>SELECT DATA.MODEL, DATA.FIRST, DATA.LAST, [DATA].[FIRST]+[NUMBER].
>[NUM] AS SN
>FROM DATA, [NUMBER]
>WHERE (((DATA.FIRST)="0102049") AND ((DATA.LAST) Is Not Null) AND
>(([DATA].[FIRST]+[NUMBER].[NUM])<=[data].[last]));
>
>returns 0 instead of returning 2.
>
>what am I doing wrong? obviously it's something with the calculated
>field constraint, but i'm not sure what.

I guess some/all of these fields are Text fields.  In that
case, doing arithmetic on them is at best an iffy
proposition.

Let's try converting them to numbers:

SELECT DATA.MODEL, Clng([DATA].[FIRST])+[NUMBER].[NUM] AS SN
FROM DATA, [NUMBER]
WHERE DATA.FIRST)="0102049"
	AND DATA.LAST Is Not Null
	AND
Clng([DATA].[FIRST])+[NUMBER].[NUM]<=Clng([data].[last])

-- 
Marsh
MVP [MS Access]
0
Marshall
7/20/2007 7:59:19 PM
On Jul 20, 12:59 pm, Marshall Barton <marshbar...@wowway.com> wrote:
> shadows...@gmail.com wrote:
> >okay, making this simpler. with numbers 0-500 in the num column, for a
> >record where first is 0102049 and last is 0102050,
>
> >SELECT DATA.MODEL, DATA.FIRST, DATA.LAST, [DATA].[FIRST]+[NUMBER].
> >[NUM] AS SN
> >FROM DATA, [NUMBER]
> >WHERE (((DATA.FIRST)="0102049") AND ((DATA.LAST) Is Not Null));
>
> >returns 501 results, and
>
> >SELECT DATA.MODEL, DATA.FIRST, DATA.LAST, [DATA].[FIRST]+[NUMBER].
> >[NUM] AS SN
> >FROM DATA, [NUMBER]
> >WHERE (((DATA.FIRST)="0102049") AND ((DATA.LAST) Is Not Null) AND
> >(([DATA].[FIRST]+[NUMBER].[NUM])<=[data].[last]));
>
> >returns 0 instead of returning 2.
>
> >what am I doing wrong? obviously it's something with the calculated
> >field constraint, but i'm not sure what.
>
> I guess some/all of these fields are Text fields.  In that
> case, doing arithmetic on them is at best an iffy
> proposition.
>
> Let's try converting them to numbers:
>
> SELECT DATA.MODEL, Clng([DATA].[FIRST])+[NUMBER].[NUM] AS SN
> FROM DATA, [NUMBER]
> WHERE DATA.FIRST)="0102049"
>         AND DATA.LAST Is Not Null
>         AND
> Clng([DATA].[FIRST])+[NUMBER].[NUM]<=Clng([data].[last])
>
> --
> Marsh
> MVP [MS Access]

converting to numbers is what i was missing, thanks. however, now i
need to know how to put leading zeros back in on the ones that had
them originally.
IF LEFT([FIRST] = "0"
THEN [sn] = 0 & Clng([DATA].[FIRST])+[NUMBER].[NUM]
ELSE [sn] = Clng([DATA].[FIRST])+[NUMBER].[NUM]

only i don't think if statements work in the "select" part of the
query, and left( seems to not be available in this version of sql.

0
shadowsong
7/20/2007 8:31:22 PM
On Jul 20, 1:31 pm, shadows...@gmail.com wrote:
> On Jul 20, 12:59 pm, Marshall Barton <marshbar...@wowway.com> wrote:
>
>
>
> > shadows...@gmail.com wrote:
> > >okay, making this simpler. with numbers 0-500 in the num column, for a
> > >record where first is 0102049 and last is 0102050,
>
> > >SELECT DATA.MODEL, DATA.FIRST, DATA.LAST, [DATA].[FIRST]+[NUMBER].
> > >[NUM] AS SN
> > >FROM DATA, [NUMBER]
> > >WHERE (((DATA.FIRST)="0102049") AND ((DATA.LAST) Is Not Null));
>
> > >returns 501 results, and
>
> > >SELECT DATA.MODEL, DATA.FIRST, DATA.LAST, [DATA].[FIRST]+[NUMBER].
> > >[NUM] AS SN
> > >FROM DATA, [NUMBER]
> > >WHERE (((DATA.FIRST)="0102049") AND ((DATA.LAST) Is Not Null) AND
> > >(([DATA].[FIRST]+[NUMBER].[NUM])<=[data].[last]));
>
> > >returns 0 instead of returning 2.
>
> > >what am I doing wrong? obviously it's something with the calculated
> > >field constraint, but i'm not sure what.
>
> > I guess some/all of these fields are Text fields.  In that
> > case, doing arithmetic on them is at best an iffy
> > proposition.
>
> > Let's try converting them to numbers:
>
> > SELECT DATA.MODEL, Clng([DATA].[FIRST])+[NUMBER].[NUM] AS SN
> > FROM DATA, [NUMBER]
> > WHERE DATA.FIRST)="0102049"
> >         AND DATA.LAST Is Not Null
> >         AND
> > Clng([DATA].[FIRST])+[NUMBER].[NUM]<=Clng([data].[last])
>
> > --
> > Marsh
> > MVP [MS Access]
>
> converting to numbers is what i was missing, thanks. however, now i
> need to know how to put leading zeros back in on the ones that had
> them originally.
> IF LEFT([FIRST] = "0"
> THEN [sn] = 0 & Clng([DATA].[FIRST])+[NUMBER].[NUM]
> ELSE [sn] = Clng([DATA].[FIRST])+[NUMBER].[NUM]
>
> only i don't think if statements work in the "select" part of the
> query, and left( seems to not be available in this version of sql.

i tried defining the field as
 LEFT("0000000", LEN([DATA].[FIRST])-LEN(CLNG([DATA].[FIRST])) &
CLng([DATA].[FIRST])+[NUMBER].[NUM] AS SN

but that tells me there's a missing operator and highlights the "as",
for some reason. in theory i should be able to use left and len but it
doesn't seem to be working in practice.

0
shadowsong
7/20/2007 9:29:24 PM
On Fri, 20 Jul 2007 20:31:22 -0000, shadowsong@gmail.com wrote:

>converting to numbers is what i was missing, thanks. however, now i
>need to know how to put leading zeros back in on the ones that had
>them originally.
>IF LEFT([FIRST] = "0"
>THEN [sn] = 0 & Clng([DATA].[FIRST])+[NUMBER].[NUM]
>ELSE [sn] = Clng([DATA].[FIRST])+[NUMBER].[NUM]

Use the Format() function instead to convert the calculated result to a
string:

SELECT DATA.MODEL, 
Format(Clng([DATA].[FIRST])+[NUMBER].[NUM], "0000000") AS SN
FROM DATA, [NUMBER]
WHERE DATA.FIRST)="0102049"
	AND DATA.LAST Is Not Null
	AND
Clng([DATA].[FIRST])+[NUMBER].[NUM]<=Clng([data].[last])


             John W. Vinson [MVP]
0
John
7/20/2007 9:54:18 PM
On Jul 20, 2:54 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Fri, 20 Jul 2007 20:31:22 -0000, shadows...@gmail.com wrote:
> >converting to numbers is what i was missing, thanks. however, now i
> >need to know how to put leading zeros back in on the ones that had
> >them originally.
> >IF LEFT([FIRST] = "0"
> >THEN [sn] = 0 & Clng([DATA].[FIRST])+[NUMBER].[NUM]
> >ELSE [sn] = Clng([DATA].[FIRST])+[NUMBER].[NUM]
>
> Use the Format() function instead to convert the calculated result to a
> string:
>
> SELECT DATA.MODEL,
> Format(Clng([DATA].[FIRST])+[NUMBER].[NUM], "0000000") AS SN
> FROM DATA, [NUMBER]
> WHERE DATA.FIRST)="0102049"
>         AND DATA.LAST Is Not Null
>         AND
> Clng([DATA].[FIRST])+[NUMBER].[NUM]<=Clng([data].[last])
>
>              John W. Vinson [MVP]

This worked for a few weeks until I opened the query in SQL mode to
look at the CLng() syntax. I didn't change anything, or so I thought,
but now it doesn't work. When trying to run this query I get an error
that says, "Data type mismatch in criteria expression."

Here is the query:

INSERT INTO DATA
SELECT DATA.DLRNAME AS DLRNAME, DATA.DLRNUM AS DLRNUM, DATA.SALESNAME
AS SALESNAME, DATA.CUSTNAME AS CUSTNAME, DATA.ADDR1 AS ADDR1,
DATA.ADDR2 AS ADDR2, DATA.CITY AS CITY, DATA.STATE AS STATE, DATA.ZIP
AS ZIP, DATA.PHONE AS PHONE, DATA.EMAIL AS EMAIL, DATA.SOLD AS SOLD,
DATA.MODEL AS MODEL, DATA.UPC AS UPC, Format(Clng(DATA.FIRST)
+NUMBER.NUM,"0000000") AS SN, DATA.EXTWARR AS EXTWARR, DATA.ENTERED AS
ENTERED
FROM DATA, [NUMBER]
WHERE (CLng(DATA.FIRST)+NUMBER.NUM)<=CLng(DATA.LAST) And (DATA.LAST)
Is Not Null;

All the CLng()s are where they're supposed to be, there aren't any non-
number characters in FIRST or LAST... What am I missing? Why isn't it
working?

0
shadowsong
7/27/2007 7:21:22 PM
Reply:

Similar Artilces:

SFO sync of records
We installed the sales for outlook client on a laptop and then replicated with the central database without any issues. The new user is a part of the business unit that owns all the records in the central database. After the replication there are no records being transferred to this laptop. We tried replicating a few times with no errors provided but no records being transferred. ...

Font with Monospaced Numbers
Can someone recommmend a readily available sans serif font, similar to Arial, that has monospaced numbers, so that a column of numbers will line up. When I use Arial in an Excel worksheet, the "1" occupies a narrow space, and causes a column of numbers to misalign. I don't want a totally monospaced font (I have one of those). Only the numbers, not text/letters should be monospaced. Thanks for your help. Fred Holmes Try using Courier. HTH Jason Atlanta, GA >-----Original Message----- >Can someone recommmend a readily available sans serif font, similar to >Arial, ...

Merging two fields into one?
Il give you an example of what I want to do - will explain it much easier. I want to merge two fields from the same table which is about some people. How do I merge one field from the table (such as their firstname) with another (such as surname) to make it look like one field in a database form? SilverSword, You can use concatenation to combine two (or more) text values. This is not done in your table, but on a form, or in a query, or on a report. On a form, based on your names table, or a query against that table, place a text control with a ControlSource of... ...

Erratic behaviour when rapidly scrolling through form records
I am developing an Access 2003 application associated with a photographic competition. My ScoringForm permits the selection of image classes and a subform displays all the images in the selected class. This subform is bound to a query and contains an option group of 14 buttons - each button representing one of the possible scores to be awarded during the judging process. If an image has been allotted a score, then the text on the selected button (and only the selected button) is made bolder and has its colour changed from black to red. This makes the chosen score stand out very clearly...

Special Random Numbers' Distribution
How to insert random numbers between 1-50 in cells A1:A10, with a condition of using a number once only, i.e. no number repeating. -- Thanx & Best Regards, Faraz! Check out http://www.youtube.com/watch?v=SoK9kq-0uXg -- Jacob "Faraz A. Qureshi" wrote: > How to insert random numbers between 1-50 in cells A1:A10, with a condition > of using a number once only, i.e. no number repeating. > -- > Thanx & Best Regards, > > Faraz! Unfortunately utube's blocked at our OFFICE! :-( -- Thanx & Best Regards, Faraz! &q...

one code to do multiple things
hi, i have the code below which takes the value in cell U17 everytime it changes, and places it in column A on a sheet called graphs thereby forming a list down the sheet of all the values that appear in cell U17, for charting purposes. What i would like to do is to be able to duplicate this for different cells. ie have the cell values of W25 appear in a list in column K on the graphs sheet, and so on. this code was written for me by someone else as, could you please give me some code that will enable me to capture the values for two or three cells in sheet1 into the sheet cal...

Excel 2007 convert into tab delimeter text file with extra tab spa
Hi , Sub: I have excel 2007 trying to save as tab delimeted text file having an issue. I have saved my excel 2007 as separate Tab delimited Text files. I see extra tabs stored in the tab delimited text files. But when I do the same with excel 2003 it works fine there is not extra tabs stored in my tab delimited text file. My excel 2007 conveted to tab delimited text file looks like as shown below(Extra two tabs at the end of the each record). Data0<Tab>data1<Tab><Tab> Data2<Tab>data3<Tab><Tab> Appreciate your help in advance. Rega...

Item reorder number
The "item reorder number" is not a field included in the build of a new item. Therefore, there is no item reorder number on a PO for the item. It is especially painful when one deals with matrix items. Is it possible to include the field "item reorder number" in the new item wizard on the next upgrade? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open th...

how do make a row non numbered?
Trying to make a row not have a numbered value on the left side. I don't know where you're goinng with this, but.... Those numbers are used for reference purposes by excel. If you'd like to display something different, maybe this will work for you: Use Col_A to display the "row numbers" you'd prefer. Then, Tools|Options|View tab|Uncheck: Row & Column Headers If you need to display the Column Headers, you'll could put them in Row_1. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Removing number value from a row" wrote: > Tryi...

Count Records for this week, this month, this quarter, this year
Hello, I am using the code-- =DCount("[ClaimID]","tblClaims","DateValue([DateCreated])=Date()") --to count the number of record created today. I would like to use a similar code to count the number of record created: 1) This week 2) This Month 3) This Quarter 4) This Year When I say “this”, I mean the current week, month, Quarter and Year based on NOW. For example, if I have 5 record for December 2009 and 6 records for December 2008, since we are in 2009 the total for “This Month” will be 5 (not 5 + 6 = 11). Also, my record CreatedDate has a dat...

Add more than one series to a pivot chart using VB MS Access continued...
I'm trying to programmatically create a stacked bar pivot chart. Using "Programming Microsoft Office Access 2003" by Rick Dobson, I've created the chart. However, it doesn't distinguish between the different values for the series. Does anyone have any suggestions on how to create a chart using one column containing three values for the series? Essentially, this is a continuation of a previous post: http://groups.google.com/group/microsoft.public.access.formscoding/browse_thread/thread/e23f3506a6d561a0/674295d410a71cf9%23674295d410a71cf9" Any help is greatly appre...

Splitting a single row into multiple copies
I have a very weird situation that I'm hoping some of you might have seen (and possibly resolved). We're migrating data from one system to another and the business rules have changed. System 1 allowed a user to create a record for an item with a quantity > 1. System 2 doesn't permit a quantity > 1 for certain items. Is there a way to create multiple rows in the source table (or an intermediate table) based on a list of item types which are not allowed to have a quantity > 1? Any help would be greatly appreciated. Joel j_racicot@hotmail.com wrote: >I have a very...

Pulling Data from one worksheet into another
Hi, I have a question regarding pulling text from one worksheet into another. Please can you help me? I need to duplicate the information (text only) on one worksheet to another one as it is typed. Is there a formula I can use that will allow the data typed in one worksheet to appear in the other one immediatley? I have a formula that the person before was using but I don't know how to apply it to the whole sheet. Any advice would be greatly appreciated. The formula is =IF(ISBLANK(OFFSET(OFFSET('Design Schedule'!$B$8,$A15,$B15),0,H$7)),"",OFFSET(OFFSET(&...

UNION query taking 5 minutes for approx 2000 records
I am dealing with data from two hospitals, each with their version of the database. I am trying to group records of admissions and discharges from each hospital by patient (URN), date of admission (DOA), date of discharge (DISCH) and time of admission (TOA) where at least one patient record represents a transfer from hosp A (KEMH) to hosp B (PMH) ie where demogr_urPM.ADMIT_SOURCE Like "KEMH*". The individual SELECT queries run quickly enough but the UNION query I am using takes approx 5 min to return about 2000 records. The source tables, demogr_ur (for hosp A) and demogr...

Critique a function to convert time, please
I have a bunch of data representing time intervals that come from a database application. The intervals vary from a few seconds to several years. The database (MySQL) stores time intervals as fixed point numbers in units of seconds. Reading the values in seconds is unnatural (a year is ~31.5E6 seconds), so I wrote a little UDF to convert the seconds to more natural units. The conversion is: Value Units <60 seconds Seconds <60 minutes Minutes <24 hours Hours <99 days Days Otherwise Years I chose 99 days as the threshold betwe...

How do i get excel to add a range of times & display as total as .
Hi I'm trying to get excel to add a range of times together and display the total as the sum of all of these hours. I can enter the times but keep getting the strangest of totals. Any help please. Use a custom format [hh]:mm for example otherwise it will always start over after 24 hours so 30:00 shows as 06:00, later if you need to apply a pay rate note that you have to convert to decimals by multiplying with 24, i.e. =time*24*payrate Regards, Peo Sjoblom "pm_faery" wrote: > Hi > I'm trying to get excel to add a range of times together and display the > ...

in Excel 2000 how do I convert text to numbers?
I imported files from Wells Fargo and I want to convert them from text to numbers...so far nothing is working! thanks Jo Dee One method...... Format all as General. Copy an empty cell(also formatted as General). Select range of numbers. Edit>Paste Special>Add>OK>Esc. One other method......... Select range of numbers and Data>Text to Columns>Finish Gord Dibben Excel MVP On Sun, 27 Feb 2005 11:53:06 -0800, Jo Dee <Jo Dee@discussions.microsoft.com> wrote: >I imported files from Wells Fargo and I want to convert them from text to >numbers...so far nothi...

Can't see row numbers or left scroll arrow in Excel spreadsheet
One of my excel spreadsheets won't display the row numbers (half of column "A" also can't be seen); left scroll arrow is not on screen. Other spreadsheets are OK. What to do? Thanks Jim Try Ctrl+F10 to maximize the workbook? "Jim Freund" wrote: > One of my excel spreadsheets won't display the row numbers (half of column > "A" also can't be seen); left scroll arrow is not on screen. Other > spreadsheets are OK. What to do? > Thanks > > Jim ...

Apply a mutiple to a range of cells?
I have a range of cells spanning multiple columns and rows. I would like, in my fantasy, to just hightlight all of them and say "multiply by x" where x is an integer. Now, I know I can't do that because my microphone doesn't work, so, other then a macro, is there a way to do it? Thanks, -Jim In an unused cell enter the numnber that you want to multiply by - it can be an integer or a floating point number - then highlight the range to you want to mutiply and then right-click and select Paste Special > Multiply > Ok Don't forget to say "Multipy by x"...

Calculate average of a range, but exclude errors
Hello! In my workbook I have several worksheets. In cell B2 of every worksheet, there is an average from the data in that worksheet. As this is a template, some data is not entered yet, and some B2 cells show error (div by 0). Now, I wanted to calculate the average from B2 cells of all worksheets, but exclude all cells with errors. My formula =Average(Sheet1:Sheet12!B2) How do I modify this to work? Thakks Peter Hi Peter, Change the formulas of the cells that you want to average to =IF(ISERROR(YourFormula),"",YourFormula) or, even better, check for the divider being zero...

Convert Excel records to text documents in arbitary formats
Hi, A newbie here (groan). I need a tool to take data in excel tables and generate text documents with the data rearranged in new forms, eg to look somewhat like mailing labels (but with more data items, like email address). Is there a tool that will take Excel data and generate documents in arbitrary formats? Also, is there a tool to edit column data, eg. the Name field I have is all uppercase, and I wanted to cap the first and make the others lower case (eg: JONES -> Jones), a pain in the ass if done manually. Generally, an editing tool for column data. thanks in advance Don't kno...

Message box warns for duplicate use of named ranges when copying s
I created an Exel/VBA2003 application in which a copy of a worksheet is added when the user pushes some button on the main form. In the worksheet which is copied, I use a few named ranges. This works fine in 2003. In 2007, however, a message box pops up that warns for duplicate use of the named range in the new sheet. Since there are 5 ranges, it is quite annoying to cancel these message boxes every time. Can I programmatically prevent these msg boxes from popping up ? Thanks ! Found one answer on pcreview (gracias a Hector Miguel): add Application.DisplayAlerts = False a...

Record macro tool bar
Please can someone tell me how I can restore the macro toolbar that is usually offered when recording a macro; without it I do not know how to switch from relative to absolute references. Thanks Tim Select the reference, press F4 Tim Wheeler wrote: > Please can someone tell me how I can restore the macro toolbar that is > usually offered when recording a macro; without it I do not know how to > switch from relative to absolute references. > > Thanks > Tim If you manually close the Recording toolbar when recording a macro it does not appear on its own the next time...

can you single click to open files in word and excell?
Is it possible to set the default to a single click when opening word and excell files? There's a windows setting (at least in WinXP) that you can toggle to make a single click open the file. Windows Start button|settings|control panel|Folder Options|General Tab At the bottom of that dialog. lvinning wrote: > > Is it possible to set the default to a single click when opening word and > excell files? -- Dave Peterson I have it set to that level, but once I'm inside of word or excell, you're required to double click on a file name before it will open. Some other ...

convert text to number format
Hello, I have a column of numbers that have the comment "The number in this cell is formatted as text or preceded by an apostrophe". I'd like to convert all these these values to numbers so I can get a summation. By mousing over the cell I can get a menu that allows me to convert the numbers one at a time, but how can I convert them all at once? I've tried converting with the Format/Cells and Edit/Copy/PasteSpecial/values. Thanks in advance, Ellen I found the answer: In an empty cell, enter the number 1. Select the cell, and on the Edit menu, click Copy. Select the...