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

[shadowsong]

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?

 

[Jason Lepack]

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

Cheers,
Jason Lepack

 

[shadowsong]

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.

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

Cheers,
Jason Lepack

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?

 

[Marshall Barton]

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

 

[shadowsong]

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?

 

[shadowsong]

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.

 

[Marshall Barton]

(e-mail address removed) 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.

 

[shadowsong]

On Jul 16, 1:17 pm, Marshall Barton wrote:
(e-mail address removed) 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.

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

 

[shadowsong]

On Jul 16, 2:58 pm, (e-mail address removed) wrote:
On Jul 16, 1:17 pm, Marshall Barton wrote:
(e-mail address removed) 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.

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.

 

[Marshall Barton]

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.

 

[Marshall Barton]

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])

 

[shadowsong]

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])

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.

 

[shadowsong]

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])

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.

 

[John W. Vinson]

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]

 

[shadowsong]

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?

 

[John W. Vinson]

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;

Odd. I don't see why it's objecting. What are the datatypes of LAST and FIRST?

It may be that it's getting upset because these are reserved words - try
putting all occurences of FIRST, LAST, DATA, NUMBER and NUM in square
brackets.

John W. Vinson [MVP]