finds gaps in sequential numbers?

[Guest]

How do I create a query, in MS Office Access 2000, that will find gaps in
manually typed sequential numbers?

 

[Tom Ellison]

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not existing as the
start of a sequence of missing numbers, and X - 1 as the end of that
sequence of missing numbers, where X is the next larger (the MIN() of those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name having the
Number in it.

This will work much more quickly if there is an index on the column I called
Number.

Would you benefit from me breaking this down and explaining how it works?

Tom Ellison


If you post the name of the table and of the column having these numbers, I
could create the actual SQL.

 

[Guest]

Tom - thanks so much for the info! I spent a lot of time trying to find a
solution for this without much success until you responded to my post.

And yes, I think it would benefit me if you could break this down and
explain how it works.

Jennifer

 

[Tom Ellison]

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not the
greatest number in the table:

SELECT Number
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to look at the
whole table using T1 without referenced to the "outer" query based on an
instance of the table called T. The WHERE clause relates the two copies of
the table. It says, "look for the next sequential value in Number, relative
to the value in the outer query which is considering each value of Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We want to omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range of missing
numbers. Add 1 to define the start of each range. Now, what is the upper
end of each range? To find that, find the next larger Number that does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1

That's it! Any clearer? I would think it reads well, but you would
probably need to practice it. These techniques have a wide range of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison

 

[Guest]

Thanks again Tom. I definitely will practice this but I think I can follow
it. I know you have saved me a great deal of time and frustration with your
response.

Jennifer

 

[Guest]

I too am looking for a way to determine missing numbers in a sequence.

Your solution is a wonderful way to determine the ranges missing. Is there a
way to list each missing number through this solution?

Pip''n

 

[Tom Ellison]

Dear Pip:

Showing all the missing numbers would be quite a different problem. You
would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS
can be made made against this list, limited by the largest value, if that's
what you desire.

Given full details, I could work out a solution on this basis, too.

Tom Ellison

 

[Guest]

Dear Tom:

Currently I have a listing of [Work Order]s in a table called [Work Orders]
i know the naming isn't the best but it was before I learned what I know now.
This listing is used to create a bill each month. To prevent the possibility
of missing work orders I need to list all missing work orders before I create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing the min
work order for the location. and Run a count+1 on the work order numbers.
Opening a form to the desired record each time. If the record can't be found
the number is concatenated to a string that is returned once the maximum work
order is reached. Each time the code is run, the first missing work order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders (values 1,2,3)

It's a crude way of doing it, but it works. I'd just like to make it better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record upon
completion of a sale. Currently I assign the maximum number + 1 but i know
this could create problems if a number is assigned when opening this form, a
second is opened and the first is cancelled. I would end up missing an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

 

[Tom Ellison]

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to assign the number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order numbers until
the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created by deletions.
Perhaps you would consider not deleting such work orders, but just marking
them. Depends on may factors of the design.

Tom Ellison

Dear Tom:

Currently I have a listing of [Work Order]s in a table called [Work
Orders]
i know the naming isn't the best but it was before I learned what I know
now.
This listing is used to create a bill each month. To prevent the
possibility
of missing work orders I need to list all missing work orders before I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing the min
work order for the location. and Run a count+1 on the work order numbers.
Opening a form to the desired record each time. If the record can't be
found
the number is concatenated to a string that is returned once the maximum
work
order is reached. Each time the code is run, the first missing work
order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders (values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to make it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record upon
completion of a sale. Currently I assign the maximum number + 1 but i know
this could create problems if a number is assigned when opening this form,
a
second is opened and the first is cancelled. I would end up missing an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

Dear Pip:

Showing all the missing numbers would be quite a different problem. You
would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT
EXISTS
can be made made against this list, limited by the largest value, if
that's
what you desire.

Given full details, I could work out a solution on this basis, too.

Tom Ellison

 

[Guest]

Dear Tom:

No, Work orders are never deleted. They are recorded manually from printouts
so the work order number is manually entered. I just need a listing of the
missing work orders to enter any that may have been missed through the manual
entry system.

This system will only ever have one user for each location.

*******************************
This second part is for a different program.

Is there an easy way to test for a new record?

These records aren't deleted after they are completed. However there are
times that the invoice is started and then canceled for some reason to be
entered later. Once the form is finished it can not be deleted.

I'll probably use the BeforeUpdate event like you mentioned. However, how do
I assign in code the maximum(InvoiceNo) to the field. Just use an update
query called from the event? I could also call it from the 'save' button or
is that not suggested?

Cheers and thanks,

Pip''n

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to assign the number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order numbers until
the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created by deletions.
Perhaps you would consider not deleting such work orders, but just marking
them. Depends on may factors of the design.

Tom Ellison

Dear Tom:

Currently I have a listing of [Work Order]s in a table called [Work
Orders]
i know the naming isn't the best but it was before I learned what I know
now.
This listing is used to create a bill each month. To prevent the
possibility
of missing work orders I need to list all missing work orders before I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing the min
work order for the location. and Run a count+1 on the work order numbers.
Opening a form to the desired record each time. If the record can't be
found
the number is concatenated to a string that is returned once the maximum
work
order is reached. Each time the code is run, the first missing work
order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders (values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to make it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record upon
completion of a sale. Currently I assign the maximum number + 1 but i know
this could create problems if a number is assigned when opening this form,
a
second is opened and the first is cancelled. I would end up missing an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

Dear Pip:

Showing all the missing numbers would be quite a different problem. You
would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT
EXISTS
can be made made against this list, limited by the largest value, if
that's
what you desire.

Given full details, I could work out a solution on this basis, too.

Tom Ellison


I too am looking for a way to determine missing numbers in a sequence.

Your solution is a wonderful way to determine the ranges missing. Is
there
a
way to list each missing number through this solution?

Pip''n


:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not the
greatest number in the table:

SELECT Number
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to look at
the
whole table using T1 without referenced to the "outer" query based on
an
instance of the table called T. The WHERE clause relates the two
copies
of
the table. It says, "look for the next sequential value in Number,
relative
to the value in the outer query which is considering each value of
Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We want to
omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range of
missing
numbers. Add 1 to define the start of each range. Now, what is the
upper
end of each range? To find that, find the next larger Number that
does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1

That's it! Any clearer? I would think it reads well, but you would
probably need to practice it. These techniques have a wide range of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


message
Tom - thanks so much for the info! I spent a lot of time trying to
find a
solution for this without much success until you responded to my
post.

And yes, I think it would benefit me if you could break this down
and
explain how it works.

Jennifer

:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not existing
as
the
start of a sequence of missing numbers, and X - 1 as the end of
that
sequence of missing numbers, where X is the next larger (the MIN()
of
those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name having
the
Number in it.

This will work much more quickly if there is an index on the column
I
called
Number.

Would you benefit from me breaking this down and explaining how it
works?

Tom Ellison


If you post the name of the table and of the column having these
numbers,
I
could create the actual SQL.
in
message
How do I create a query, in MS Office Access 2000, that will find
gaps
in
manually typed sequential numbers?

 

[Tom Ellison]

Dear Pip:

There are usually other ways a record can be caused to be saved other than
your "save" button. You need an event that covers all cases. I'm
accustomed to using the BeforeUpdate event and testing for NewRecord.

You could use the Max(InvoiceNo), obtained from a query, to assign this, or
you could have a control table with a row for Next Invoice Number from which
you get this. In multi-user situations it is usually recommended to use
locking in either case while you do this. In extensive tests I've run, I
could never get it to foul up, but it's theoretically possible.

In a singlue user system, you have no worries with locking. Unless, of
course, it get's installed later multi-user.

Tom Ellison

Dear Tom:

No, Work orders are never deleted. They are recorded manually from
printouts
so the work order number is manually entered. I just need a listing of the
missing work orders to enter any that may have been missed through the
manual
entry system.

This system will only ever have one user for each location.

*******************************
This second part is for a different program.

Is there an easy way to test for a new record?

These records aren't deleted after they are completed. However there are
times that the invoice is started and then canceled for some reason to be
entered later. Once the form is finished it can not be deleted.

I'll probably use the BeforeUpdate event like you mentioned. However, how
do
I assign in code the maximum(InvoiceNo) to the field. Just use an update
query called from the event? I could also call it from the 'save' button
or
is that not suggested?

Cheers and thanks,

Pip''n

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to assign the
number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order numbers
until
the BeforeUpdate event. Test for NewRecord, and if true, set the Work
Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created by
deletions.
Perhaps you would consider not deleting such work orders, but just
marking
them. Depends on may factors of the design.

Tom Ellison

Dear Tom:

Currently I have a listing of [Work Order]s in a table called [Work
Orders]
i know the naming isn't the best but it was before I learned what I
know
now.
This listing is used to create a bill each month. To prevent the
possibility
of missing work orders I need to list all missing work orders before I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing the
min
work order for the location. and Run a count+1 on the work order
numbers.
Opening a form to the desired record each time. If the record can't be
found
the number is concatenated to a string that is returned once the
maximum
work
order is reached. Each time the code is run, the first missing work
order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders (values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to make it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record upon
completion of a sale. Currently I assign the maximum number + 1 but i
know
this could create problems if a number is assigned when opening this
form,
a
second is opened and the first is cancelled. I would end up missing an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

:

Dear Pip:

Showing all the missing numbers would be quite a different problem.
You
would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT
EXISTS
can be made made against this list, limited by the largest value, if
that's
what you desire.

Given full details, I could work out a solution on this basis, too.

Tom Ellison


I too am looking for a way to determine missing numbers in a
sequence.

Your solution is a wonderful way to determine the ranges missing. Is
there
a
way to list each missing number through this solution?

Pip''n


:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not
the
greatest number in the table:

SELECT Number
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to look
at
the
whole table using T1 without referenced to the "outer" query based
on
an
instance of the table called T. The WHERE clause relates the two
copies
of
the table. It says, "look for the next sequential value in Number,
relative
to the value in the outer query which is considering each value of
Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We want
to
omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range of
missing
numbers. Add 1 to define the start of each range. Now, what is
the
upper
end of each range? To find that, find the next larger Number that
does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1

That's it! Any clearer? I would think it reads well, but you
would
probably need to practice it. These techniques have a wide range
of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


message
Tom - thanks so much for the info! I spent a lot of time trying
to
find a
solution for this without much success until you responded to my
post.

And yes, I think it would benefit me if you could break this down
and
explain how it works.

Jennifer

:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not
existing
as
the
start of a sequence of missing numbers, and X - 1 as the end of
that
sequence of missing numbers, where X is the next larger (the
MIN()
of
those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name
having
the
Number in it.

This will work much more quickly if there is an index on the
column
I
called
Number.

Would you benefit from me breaking this down and explaining how
it
works?

Tom Ellison


If you post the name of the table and of the column having these
numbers,
I
could create the actual SQL.
"Jennifer@DakCo" <Jennifer@[email protected]>
wrote
in
message
How do I create a query, in MS Office Access 2000, that will
find
gaps
in
manually typed sequential numbers?

 

[Guest]

Tom,

you mentioned assigning the Max(Invoice) to the record using a query. When I
try it i get the error that the field InvoiceNo is not being used as part of
an aggregate function.

Here's the code I tried:
UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1
WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#]));


Also, how do you test for NewRecord? I've been checking to see if the key
field is Null.

Dear Pip:

There are usually other ways a record can be caused to be saved other than
your "save" button. You need an event that covers all cases. I'm
accustomed to using the BeforeUpdate event and testing for NewRecord.

You could use the Max(InvoiceNo), obtained from a query, to assign this, or
you could have a control table with a row for Next Invoice Number from which
you get this. In multi-user situations it is usually recommended to use
locking in either case while you do this. In extensive tests I've run, I
could never get it to foul up, but it's theoretically possible.

In a singlue user system, you have no worries with locking. Unless, of
course, it get's installed later multi-user.

Tom Ellison

Dear Tom:

No, Work orders are never deleted. They are recorded manually from
printouts
so the work order number is manually entered. I just need a listing of the
missing work orders to enter any that may have been missed through the
manual
entry system.

This system will only ever have one user for each location.

*******************************
This second part is for a different program.

Is there an easy way to test for a new record?

These records aren't deleted after they are completed. However there are
times that the invoice is started and then canceled for some reason to be
entered later. Once the form is finished it can not be deleted.

I'll probably use the BeforeUpdate event like you mentioned. However, how
do
I assign in code the maximum(InvoiceNo) to the field. Just use an update
query called from the event? I could also call it from the 'save' button
or
is that not suggested?

Cheers and thanks,

Pip''n

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to assign the
number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order numbers
until
the BeforeUpdate event. Test for NewRecord, and if true, set the Work
Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created by
deletions.
Perhaps you would consider not deleting such work orders, but just
marking
them. Depends on may factors of the design.

Tom Ellison


Dear Tom:

Currently I have a listing of [Work Order]s in a table called [Work
Orders]
i know the naming isn't the best but it was before I learned what I
know
now.
This listing is used to create a bill each month. To prevent the
possibility
of missing work orders I need to list all missing work orders before I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing the
min
work order for the location. and Run a count+1 on the work order
numbers.
Opening a form to the desired record each time. If the record can't be
found
the number is concatenated to a string that is returned once the
maximum
work
order is reached. Each time the code is run, the first missing work
order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders (values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to make it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record upon
completion of a sale. Currently I assign the maximum number + 1 but i
know
this could create problems if a number is assigned when opening this
form,
a
second is opened and the first is cancelled. I would end up missing an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

:

Dear Pip:

Showing all the missing numbers would be quite a different problem.
You
would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT
EXISTS
can be made made against this list, limited by the largest value, if
that's
what you desire.

Given full details, I could work out a solution on this basis, too.

Tom Ellison


I too am looking for a way to determine missing numbers in a
sequence.

Your solution is a wonderful way to determine the ranges missing. Is
there
a
way to list each missing number through this solution?

Pip''n


:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not
the
greatest number in the table:

SELECT Number
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to look
at
the
whole table using T1 without referenced to the "outer" query based
on
an
instance of the table called T. The WHERE clause relates the two
copies
of
the table. It says, "look for the next sequential value in Number,
relative
to the value in the outer query which is considering each value of
Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We want
to
omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range of
missing
numbers. Add 1 to define the start of each range. Now, what is
the
upper
end of each range? To find that, find the next larger Number that
does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1

That's it! Any clearer? I would think it reads well, but you
would
probably need to practice it. These techniques have a wide range
of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


message
Tom - thanks so much for the info! I spent a lot of time trying
to
find a
solution for this without much success until you responded to my
post.

And yes, I think it would benefit me if you could break this down
and
explain how it works.

Jennifer

:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not
existing
as
the
start of a sequence of missing numbers, and X - 1 as the end of
that
sequence of missing numbers, where X is the next larger (the
MIN()
of
those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name
having
the
Number in it.

This will work much more quickly if there is an index on the
column
I
called
Number.

Would you benefit from me breaking this down and explaining how
it
works?

Tom Ellison


If you post the name of the table and of the column having these
numbers,
I
could create the actual SQL.
"Jennifer@DakCo" <Jennifer@[email protected]>
wrote
in
message
How do I create a query, in MS Office Access 2000, that will
find

 

[Tom Ellison]

Dear Pip:

I don't understand using an update query to assign the InvoiceNo. I suggest
setting this in the BeforeUpdate event when it is a NewRecord, as I said
before.

Testing NewRecord in the code of a form is Me.NewRecord.

Tom Ellison

Tom,

you mentioned assigning the Max(Invoice) to the record using a query. When
I
try it i get the error that the field InvoiceNo is not being used as part
of
an aggregate function.

Here's the code I tried:
UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1
WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#]));


Also, how do you test for NewRecord? I've been checking to see if the key
field is Null.

Dear Pip:

There are usually other ways a record can be caused to be saved other
than
your "save" button. You need an event that covers all cases. I'm
accustomed to using the BeforeUpdate event and testing for NewRecord.

You could use the Max(InvoiceNo), obtained from a query, to assign this,
or
you could have a control table with a row for Next Invoice Number from
which
you get this. In multi-user situations it is usually recommended to use
locking in either case while you do this. In extensive tests I've run, I
could never get it to foul up, but it's theoretically possible.

In a singlue user system, you have no worries with locking. Unless, of
course, it get's installed later multi-user.

Tom Ellison

Dear Tom:

No, Work orders are never deleted. They are recorded manually from
printouts
so the work order number is manually entered. I just need a listing of
the
missing work orders to enter any that may have been missed through the
manual
entry system.

This system will only ever have one user for each location.

*******************************
This second part is for a different program.

Is there an easy way to test for a new record?

These records aren't deleted after they are completed. However there
are
times that the invoice is started and then canceled for some reason to
be
entered later. Once the form is finished it can not be deleted.

I'll probably use the BeforeUpdate event like you mentioned. However,
how
do
I assign in code the maximum(InvoiceNo) to the field. Just use an
update
query called from the event? I could also call it from the 'save'
button
or
is that not suggested?

Cheers and thanks,

Pip''n

:

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to assign the
number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order numbers
until
the BeforeUpdate event. Test for NewRecord, and if true, set the Work
Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created by
deletions.
Perhaps you would consider not deleting such work orders, but just
marking
them. Depends on may factors of the design.

Tom Ellison


Dear Tom:

Currently I have a listing of [Work Order]s in a table called [Work
Orders]
i know the naming isn't the best but it was before I learned what I
know
now.
This listing is used to create a bill each month. To prevent the
possibility
of missing work orders I need to list all missing work orders before
I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing the
min
work order for the location. and Run a count+1 on the work order
numbers.
Opening a form to the desired record each time. If the record can't
be
found
the number is concatenated to a string that is returned once the
maximum
work
order is reached. Each time the code is run, the first missing work
order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders
(values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to make it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record upon
completion of a sale. Currently I assign the maximum number + 1 but
i
know
this could create problems if a number is assigned when opening this
form,
a
second is opened and the first is cancelled. I would end up missing
an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

:

Dear Pip:

Showing all the missing numbers would be quite a different problem.
You
would need a table of numbers, perhaps thousands. A LEFT JOIN or
NOT
EXISTS
can be made made against this list, limited by the largest value,
if
that's
what you desire.

Given full details, I could work out a solution on this basis, too.

Tom Ellison


I too am looking for a way to determine missing numbers in a
sequence.

Your solution is a wonderful way to determine the ranges missing.
Is
there
a
way to list each missing number through this solution?

Pip''n


:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not
the
greatest number in the table:

SELECT Number
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to
look
at
the
whole table using T1 without referenced to the "outer" query
based
on
an
instance of the table called T. The WHERE clause relates the
two
copies
of
the table. It says, "look for the next sequential value in
Number,
relative
to the value in the outer query which is considering each value
of
Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We
want
to
omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range
of
missing
numbers. Add 1 to define the start of each range. Now, what is
the
upper
end of each range? To find that, find the next larger Number
that
does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1

That's it! Any clearer? I would think it reads well, but you
would
probably need to practice it. These techniques have a wide
range
of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


in
message
Tom - thanks so much for the info! I spent a lot of time
trying
to
find a
solution for this without much success until you responded to
my
post.

And yes, I think it would benefit me if you could break this
down
and
explain how it works.

Jennifer

:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not
existing
as
the
start of a sequence of missing numbers, and X - 1 as the end
of
that
sequence of missing numbers, where X is the next larger (the
MIN()
of
those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name
having
the
Number in it.

This will work much more quickly if there is an index on the
column
I
called
Number.

Would you benefit from me breaking this down and explaining
how
it
works?

Tom Ellison


If you post the name of the table and of the column having
these
numbers,
I
could create the actual SQL.
"Jennifer@DakCo" <Jennifer@[email protected]>
wrote
in
message
How do I create a query, in MS Office Access 2000, that
will
find

 

[Guest]

I understand using the BeforeUpdate event, how do I determine the invoice
number to use?

Pip''n

Dear Pip:

I don't understand using an update query to assign the InvoiceNo. I suggest
setting this in the BeforeUpdate event when it is a NewRecord, as I said
before.

Testing NewRecord in the code of a form is Me.NewRecord.

Tom Ellison

Tom,

you mentioned assigning the Max(Invoice) to the record using a query. When
I
try it i get the error that the field InvoiceNo is not being used as part
of
an aggregate function.

Here's the code I tried:
UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1
WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#]));


Also, how do you test for NewRecord? I've been checking to see if the key
field is Null.

Dear Pip:

There are usually other ways a record can be caused to be saved other
than
your "save" button. You need an event that covers all cases. I'm
accustomed to using the BeforeUpdate event and testing for NewRecord.

You could use the Max(InvoiceNo), obtained from a query, to assign this,
or
you could have a control table with a row for Next Invoice Number from
which
you get this. In multi-user situations it is usually recommended to use
locking in either case while you do this. In extensive tests I've run, I
could never get it to foul up, but it's theoretically possible.

In a singlue user system, you have no worries with locking. Unless, of
course, it get's installed later multi-user.

Tom Ellison


Dear Tom:

No, Work orders are never deleted. They are recorded manually from
printouts
so the work order number is manually entered. I just need a listing of
the
missing work orders to enter any that may have been missed through the
manual
entry system.

This system will only ever have one user for each location.

*******************************
This second part is for a different program.

Is there an easy way to test for a new record?

These records aren't deleted after they are completed. However there
are
times that the invoice is started and then canceled for some reason to
be
entered later. Once the form is finished it can not be deleted.

I'll probably use the BeforeUpdate event like you mentioned. However,
how
do
I assign in code the maximum(InvoiceNo) to the field. Just use an
update
query called from the event? I could also call it from the 'save'
button
or
is that not suggested?

Cheers and thanks,

Pip''n

:

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to assign the
number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order numbers
until
the BeforeUpdate event. Test for NewRecord, and if true, set the Work
Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created by
deletions.
Perhaps you would consider not deleting such work orders, but just
marking
them. Depends on may factors of the design.

Tom Ellison


Dear Tom:

Currently I have a listing of [Work Order]s in a table called [Work
Orders]
i know the naming isn't the best but it was before I learned what I
know
now.
This listing is used to create a bill each month. To prevent the
possibility
of missing work orders I need to list all missing work orders before
I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing the
min
work order for the location. and Run a count+1 on the work order
numbers.
Opening a form to the desired record each time. If the record can't
be
found
the number is concatenated to a string that is returned once the
maximum
work
order is reached. Each time the code is run, the first missing work
order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders
(values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to make it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record upon
completion of a sale. Currently I assign the maximum number + 1 but
i
know
this could create problems if a number is assigned when opening this
form,
a
second is opened and the first is cancelled. I would end up missing
an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

:

Dear Pip:

Showing all the missing numbers would be quite a different problem.
You
would need a table of numbers, perhaps thousands. A LEFT JOIN or
NOT
EXISTS
can be made made against this list, limited by the largest value,
if
that's
what you desire.

Given full details, I could work out a solution on this basis, too.

Tom Ellison


I too am looking for a way to determine missing numbers in a
sequence.

Your solution is a wonderful way to determine the ranges missing.
Is
there
a
way to list each missing number through this solution?

Pip''n


:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not
the
greatest number in the table:

SELECT Number
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to
look
at
the
whole table using T1 without referenced to the "outer" query
based
on
an
instance of the table called T. The WHERE clause relates the
two
copies
of
the table. It says, "look for the next sequential value in
Number,
relative
to the value in the outer query which is considering each value
of
Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We
want
to
omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range
of
missing
numbers. Add 1 to define the start of each range. Now, what is
the
upper
end of each range? To find that, find the next larger Number
that
does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1

That's it! Any clearer? I would think it reads well, but you
would
probably need to practice it. These techniques have a wide
range
of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


in
message
Tom - thanks so much for the info! I spent a lot of time
trying
to
find a
solution for this without much success until you responded to
my
post.

And yes, I think it would benefit me if you could break this
down
and

 

[Tom Ellison]

Dear Pip:

Use a query that returns the value, as we have discussed. Run this query
from code in the BeforeUpdate event. Grab the returned value (one row, one
column).

Tom Ellison

I understand using the BeforeUpdate event, how do I determine the invoice
number to use?

Pip''n

Dear Pip:

I don't understand using an update query to assign the InvoiceNo. I
suggest
setting this in the BeforeUpdate event when it is a NewRecord, as I said
before.

Testing NewRecord in the code of a form is Me.NewRecord.

Tom Ellison

Tom,

you mentioned assigning the Max(Invoice) to the record using a query.
When
I
try it i get the error that the field InvoiceNo is not being used as
part
of
an aggregate function.

Here's the code I tried:
UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1
WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#]));


Also, how do you test for NewRecord? I've been checking to see if the
key
field is Null.

:

Dear Pip:

There are usually other ways a record can be caused to be saved other
than
your "save" button. You need an event that covers all cases. I'm
accustomed to using the BeforeUpdate event and testing for NewRecord.

You could use the Max(InvoiceNo), obtained from a query, to assign
this,
or
you could have a control table with a row for Next Invoice Number from
which
you get this. In multi-user situations it is usually recommended to
use
locking in either case while you do this. In extensive tests I've
run, I
could never get it to foul up, but it's theoretically possible.

In a singlue user system, you have no worries with locking. Unless,
of
course, it get's installed later multi-user.

Tom Ellison


Dear Tom:

No, Work orders are never deleted. They are recorded manually from
printouts
so the work order number is manually entered. I just need a listing
of
the
missing work orders to enter any that may have been missed through
the
manual
entry system.

This system will only ever have one user for each location.

*******************************
This second part is for a different program.

Is there an easy way to test for a new record?

These records aren't deleted after they are completed. However there
are
times that the invoice is started and then canceled for some reason
to
be
entered later. Once the form is finished it can not be deleted.

I'll probably use the BeforeUpdate event like you mentioned.
However,
how
do
I assign in code the maximum(InvoiceNo) to the field. Just use an
update
query called from the event? I could also call it from the 'save'
button
or
is that not suggested?

Cheers and thanks,

Pip''n

:

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to assign
the
number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order
numbers
until
the BeforeUpdate event. Test for NewRecord, and if true, set the
Work
Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created by
deletions.
Perhaps you would consider not deleting such work orders, but just
marking
them. Depends on may factors of the design.

Tom Ellison


Dear Tom:

Currently I have a listing of [Work Order]s in a table called
[Work
Orders]
i know the naming isn't the best but it was before I learned what
I
know
now.
This listing is used to create a bill each month. To prevent the
possibility
of missing work orders I need to list all missing work orders
before
I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing
the
min
work order for the location. and Run a count+1 on the work order
numbers.
Opening a form to the desired record each time. If the record
can't
be
found
the number is concatenated to a string that is returned once the
maximum
work
order is reached. Each time the code is run, the first missing
work
order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders
(values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to make
it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record
upon
completion of a sale. Currently I assign the maximum number + 1
but
i
know
this could create problems if a number is assigned when opening
this
form,
a
second is opened and the first is cancelled. I would end up
missing
an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

:

Dear Pip:

Showing all the missing numbers would be quite a different
problem.
You
would need a table of numbers, perhaps thousands. A LEFT JOIN
or
NOT
EXISTS
can be made made against this list, limited by the largest
value,
if
that's
what you desire.

Given full details, I could work out a solution on this basis,
too.

Tom Ellison


I too am looking for a way to determine missing numbers in a
sequence.

Your solution is a wonderful way to determine the ranges
missing.
Is
there
a
way to list each missing number through this solution?

Pip''n


:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but
not
the
greatest number in the table:

SELECT Number
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to
look
at
the
whole table using T1 without referenced to the "outer" query
based
on
an
instance of the table called T. The WHERE clause relates the
two
copies
of
the table. It says, "look for the next sequential value in
Number,
relative
to the value in the outer query which is considering each
value
of
Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We
want
to
omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the
range
of
missing
numbers. Add 1 to define the start of each range. Now, what
is
the
upper
end of each range? To find that, find the next larger Number
that
does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1

That's it! Any clearer? I would think it reads well, but
you
would
probably need to practice it. These techniques have a wide
range
of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


"Jennifer@DakCo" <[email protected]>
wrote
in
message
Tom - thanks so much for the info! I spent a lot of time
trying
to
find a
solution for this without much success until you responded
to
my
post.

And yes, I think it would benefit me if you could break
this
down
and

 

[Guest]

Sorry, I asked the wrong question last time.

I know how to create the query to show the max(invoiceNo). I also know how
to run the query using the DoCmd.OpenQuery. But how do I grab the single
value in order to assign the value to the table. If i'm not to use the
DoCmd.OpenQuery what do I use?

I understand as you mentioned that you can't use an update query in the
BeforeUpdate event because you havn't updated the table with the new record.

Pip''n

Dear Pip:

Use a query that returns the value, as we have discussed. Run this query
from code in the BeforeUpdate event. Grab the returned value (one row, one
column).

Tom Ellison

I understand using the BeforeUpdate event, how do I determine the invoice
number to use?

Pip''n

Dear Pip:

I don't understand using an update query to assign the InvoiceNo. I
suggest
setting this in the BeforeUpdate event when it is a NewRecord, as I said
before.

Testing NewRecord in the code of a form is Me.NewRecord.

Tom Ellison


Tom,

you mentioned assigning the Max(Invoice) to the record using a query.
When
I
try it i get the error that the field InvoiceNo is not being used as
part
of
an aggregate function.

Here's the code I tried:
UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1
WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#]));


Also, how do you test for NewRecord? I've been checking to see if the
key
field is Null.

:

Dear Pip:

There are usually other ways a record can be caused to be saved other
than
your "save" button. You need an event that covers all cases. I'm
accustomed to using the BeforeUpdate event and testing for NewRecord.

You could use the Max(InvoiceNo), obtained from a query, to assign
this,
or
you could have a control table with a row for Next Invoice Number from
which
you get this. In multi-user situations it is usually recommended to
use
locking in either case while you do this. In extensive tests I've
run, I
could never get it to foul up, but it's theoretically possible.

In a singlue user system, you have no worries with locking. Unless,
of
course, it get's installed later multi-user.

Tom Ellison


Dear Tom:

No, Work orders are never deleted. They are recorded manually from
printouts
so the work order number is manually entered. I just need a listing
of
the
missing work orders to enter any that may have been missed through
the
manual
entry system.

This system will only ever have one user for each location.

*******************************
This second part is for a different program.

Is there an easy way to test for a new record?

These records aren't deleted after they are completed. However there
are
times that the invoice is started and then canceled for some reason
to
be
entered later. Once the form is finished it can not be deleted.

I'll probably use the BeforeUpdate event like you mentioned.
However,
how
do
I assign in code the maximum(InvoiceNo) to the field. Just use an
update
query called from the event? I could also call it from the 'save'
button
or
is that not suggested?

Cheers and thanks,

Pip''n

:

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to assign
the
number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order
numbers
until
the BeforeUpdate event. Test for NewRecord, and if true, set the
Work
Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created by
deletions.
Perhaps you would consider not deleting such work orders, but just
marking
them. Depends on may factors of the design.

Tom Ellison


Dear Tom:

Currently I have a listing of [Work Order]s in a table called
[Work
Orders]
i know the naming isn't the best but it was before I learned what
I
know
now.
This listing is used to create a bill each month. To prevent the
possibility
of missing work orders I need to list all missing work orders
before
I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing
the
min
work order for the location. and Run a count+1 on the work order
numbers.
Opening a form to the desired record each time. If the record
can't
be
found
the number is concatenated to a string that is returned once the
maximum
work
order is reached. Each time the code is run, the first missing
work
order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders
(values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to make
it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record
upon
completion of a sale. Currently I assign the maximum number + 1
but
i
know
this could create problems if a number is assigned when opening
this
form,
a
second is opened and the first is cancelled. I would end up
missing
an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

:

Dear Pip:

Showing all the missing numbers would be quite a different
problem.
You
would need a table of numbers, perhaps thousands. A LEFT JOIN
or
NOT
EXISTS
can be made made against this list, limited by the largest
value,
if
that's
what you desire.

Given full details, I could work out a solution on this basis,
too.

Tom Ellison


I too am looking for a way to determine missing numbers in a
sequence.

Your solution is a wonderful way to determine the ranges
missing.
Is
there
a
way to list each missing number through this solution?

Pip''n


:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but
not
the
greatest number in the table:

SELECT Number
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to
look
at
the
whole table using T1 without referenced to the "outer" query
based
on
an
instance of the table called T. The WHERE clause relates the
two
copies
of
the table. It says, "look for the next sequential value in
Number,
relative
to the value in the outer query which is considering each
value
of
Number
that exists."

Another subquery:

 

[Tom Ellison]

Dear Pip:

Open the query as a recordset, read the record, and reference the value:

Public Function testme() As String
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT MAX(invoiceNo) AS MaxInvNo
FROM YourTable")
rst.MoveFirst
Debug.Print rst!MaxInvNo
End Function

Replace YourTable with the actual name of your table. You don't need the
Debug.Print line, it just shows that the value you need is now available as
rst!MaxInvNo.

Tom Ellison

Sorry, I asked the wrong question last time.

I know how to create the query to show the max(invoiceNo). I also know how
to run the query using the DoCmd.OpenQuery. But how do I grab the single
value in order to assign the value to the table. If i'm not to use the
DoCmd.OpenQuery what do I use?

I understand as you mentioned that you can't use an update query in the
BeforeUpdate event because you havn't updated the table with the new
record.

Pip''n

Dear Pip:

Use a query that returns the value, as we have discussed. Run this query
from code in the BeforeUpdate event. Grab the returned value (one row,
one
column).

Tom Ellison

I understand using the BeforeUpdate event, how do I determine the
invoice
number to use?

Pip''n

:

Dear Pip:

I don't understand using an update query to assign the InvoiceNo. I
suggest
setting this in the BeforeUpdate event when it is a NewRecord, as I
said
before.

Testing NewRecord in the code of a form is Me.NewRecord.

Tom Ellison


Tom,

you mentioned assigning the Max(Invoice) to the record using a
query.
When
I
try it i get the error that the field InvoiceNo is not being used as
part
of
an aggregate function.

Here's the code I tried:
UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1
WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#]));


Also, how do you test for NewRecord? I've been checking to see if
the
key
field is Null.

:

Dear Pip:

There are usually other ways a record can be caused to be saved
other
than
your "save" button. You need an event that covers all cases. I'm
accustomed to using the BeforeUpdate event and testing for
NewRecord.

You could use the Max(InvoiceNo), obtained from a query, to assign
this,
or
you could have a control table with a row for Next Invoice Number
from
which
you get this. In multi-user situations it is usually recommended
to
use
locking in either case while you do this. In extensive tests I've
run, I
could never get it to foul up, but it's theoretically possible.

In a singlue user system, you have no worries with locking.
Unless,
of
course, it get's installed later multi-user.

Tom Ellison


Dear Tom:

No, Work orders are never deleted. They are recorded manually
from
printouts
so the work order number is manually entered. I just need a
listing
of
the
missing work orders to enter any that may have been missed
through
the
manual
entry system.

This system will only ever have one user for each location.

*******************************
This second part is for a different program.

Is there an easy way to test for a new record?

These records aren't deleted after they are completed. However
there
are
times that the invoice is started and then canceled for some
reason
to
be
entered later. Once the form is finished it can not be deleted.

I'll probably use the BeforeUpdate event like you mentioned.
However,
how
do
I assign in code the maximum(InvoiceNo) to the field. Just use an
update
query called from the event? I could also call it from the 'save'
button
or
is that not suggested?

Cheers and thanks,

Pip''n

:

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to
assign
the
number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order
numbers
until
the BeforeUpdate event. Test for NewRecord, and if true, set
the
Work
Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created
by
deletions.
Perhaps you would consider not deleting such work orders, but
just
marking
them. Depends on may factors of the design.

Tom Ellison


Dear Tom:

Currently I have a listing of [Work Order]s in a table called
[Work
Orders]
i know the naming isn't the best but it was before I learned
what
I
know
now.
This listing is used to create a bill each month. To prevent
the
possibility
of missing work orders I need to list all missing work orders
before
I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by
storing
the
min
work order for the location. and Run a count+1 on the work
order
numbers.
Opening a form to the desired record each time. If the record
can't
be
found
the number is concatenated to a string that is returned once
the
maximum
work
order is reached. Each time the code is run, the first missing
work
order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work
orders
(values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to
make
it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a
record
upon
completion of a sale. Currently I assign the maximum number +
1
but
i
know
this could create problems if a number is assigned when
opening
this
form,
a
second is opened and the first is cancelled. I would end up
missing
an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

:

Dear Pip:

Showing all the missing numbers would be quite a different
problem.
You
would need a table of numbers, perhaps thousands. A LEFT
JOIN
or
NOT
EXISTS
can be made made against this list, limited by the largest
value,
if
that's
what you desire.

Given full details, I could work out a solution on this
basis,
too.

Tom Ellison


I too am looking for a way to determine missing numbers in a
sequence.

Your solution is a wonderful way to determine the ranges
missing.
Is
there
a
way to list each missing number through this solution?

Pip''n


:

Dear Jennifer:

Step 1: find a Number where the next number is missing,
but
not
the
greatest number in the table:

SELECT Number
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Here, a subquery in the EXISTS clause looks for Number +
1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query
to
look
at
the
whole table using T1 without referenced to the "outer"
query
based
on
an
instance of the table called T. The WHERE clause relates
the
two
copies
of
the table. It says, "look for the next sequential value
in
Number,
relative
to the value in the outer query which is considering each
value
of
Number
that exists."

Another subquery:

 

[Guest]

Dear Tom,

Thanks for all your help. Your solution worked after I changed the variable
rst to an Object instead of a RecordSet.

Thanks for the Debug.Print action too. All this time i've been using msg
boxes.

Thanks Again,

Pip''n

Dear Pip:

Open the query as a recordset, read the record, and reference the value:

Public Function testme() As String
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT MAX(invoiceNo) AS MaxInvNo
FROM YourTable")
rst.MoveFirst
Debug.Print rst!MaxInvNo
End Function

Replace YourTable with the actual name of your table. You don't need the
Debug.Print line, it just shows that the value you need is now available as
rst!MaxInvNo.

Tom Ellison

Sorry, I asked the wrong question last time.

I know how to create the query to show the max(invoiceNo). I also know how
to run the query using the DoCmd.OpenQuery. But how do I grab the single
value in order to assign the value to the table. If i'm not to use the
DoCmd.OpenQuery what do I use?

I understand as you mentioned that you can't use an update query in the
BeforeUpdate event because you havn't updated the table with the new
record.

Pip''n

Dear Pip:

Use a query that returns the value, as we have discussed. Run this query
from code in the BeforeUpdate event. Grab the returned value (one row,
one
column).

Tom Ellison


I understand using the BeforeUpdate event, how do I determine the
invoice
number to use?

Pip''n

:

Dear Pip:

I don't understand using an update query to assign the InvoiceNo. I
suggest
setting this in the BeforeUpdate event when it is a NewRecord, as I
said
before.

Testing NewRecord in the code of a form is Me.NewRecord.

Tom Ellison


Tom,

you mentioned assigning the Max(Invoice) to the record using a
query.
When
I
try it i get the error that the field InvoiceNo is not being used as
part
of
an aggregate function.

Here's the code I tried:
UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1
WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#]));


Also, how do you test for NewRecord? I've been checking to see if
the
key
field is Null.

:

Dear Pip:

There are usually other ways a record can be caused to be saved
other
than
your "save" button. You need an event that covers all cases. I'm
accustomed to using the BeforeUpdate event and testing for
NewRecord.

You could use the Max(InvoiceNo), obtained from a query, to assign
this,
or
you could have a control table with a row for Next Invoice Number
from
which
you get this. In multi-user situations it is usually recommended
to
use
locking in either case while you do this. In extensive tests I've
run, I
could never get it to foul up, but it's theoretically possible.

In a singlue user system, you have no worries with locking.
Unless,
of
course, it get's installed later multi-user.

Tom Ellison


Dear Tom:

No, Work orders are never deleted. They are recorded manually
from
printouts
so the work order number is manually entered. I just need a
listing
of
the
missing work orders to enter any that may have been missed
through
the
manual
entry system.

This system will only ever have one user for each location.

*******************************
This second part is for a different program.

Is there an easy way to test for a new record?

These records aren't deleted after they are completed. However
there
are
times that the invoice is started and then canceled for some
reason
to
be
entered later. Once the form is finished it can not be deleted.

I'll probably use the BeforeUpdate event like you mentioned.
However,
how
do
I assign in code the maximum(InvoiceNo) to the field. Just use an
update
query called from the event? I could also call it from the 'save'
button
or
is that not suggested?

Cheers and thanks,

Pip''n

:

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to
assign
the
number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order
numbers
until
the BeforeUpdate event. Test for NewRecord, and if true, set
the
Work
Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created
by
deletions.
Perhaps you would consider not deleting such work orders, but
just
marking
them. Depends on may factors of the design.

Tom Ellison


Dear Tom:

Currently I have a listing of [Work Order]s in a table called
[Work
Orders]
i know the naming isn't the best but it was before I learned
what
I
know
now.
This listing is used to create a bill each month. To prevent
the
possibility
of missing work orders I need to list all missing work orders
before
I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by
storing
the
min
work order for the location. and Run a count+1 on the work
order
numbers.
Opening a form to the desired record each time. If the record
can't
be
found
the number is concatenated to a string that is returned once
the
maximum
work
order is reached. Each time the code is run, the first missing
work
order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work
orders
(values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to
make
it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a
record
upon
completion of a sale. Currently I assign the maximum number +
1
but
i
know
this could create problems if a number is assigned when
opening
this
form,
a
second is opened and the first is cancelled. I would end up
missing
an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

:

Dear Pip:

Showing all the missing numbers would be quite a different
problem.
You
would need a table of numbers, perhaps thousands. A LEFT
JOIN
or
NOT
EXISTS
can be made made against this list, limited by the largest
value,
if
that's