Find missing numbers is a sequence qry

[veale1009]

I got this example for sql in a query to find missing numbers in a table field



SELECT ([MyNumber]+1) AS MissingFrom, DMin("MyNumber","MyTable","MyNumber>" & [MyNumber]) AS MissingUntil
FROM MyTable
WHERE (((DMin("MyNumber","MyTable","MyNumber>" & [MyNumber]))<>([MyNumber]+1)));.

My table is COLLECTABLE TABLE and the number field is MODEL NUMBER FOR BOX

I created this:

SELECT ([MODEL NUMBER FOR BOX]+1) AS MissingFrom, DMin("MODEL NUMBER FOR BOX","COLLECTABLE TABLE","MODEL NUMBER FOR BOX>" & [MODEL NUMBER FOR BOXyNumber]) AS MissingUntil


FROM COLLECTABLE TABLE


WHERE (((DMin("MODEL NUMBER FOR BOX","MyTable","MODEL NUMBER FOR BOX>" & [MODEL NUMBER FOR BOX]))<>([MODEL NUMBER FOR BOX]+1)));

I get Syntax error in From clause COLLECTABLE TABLE and TABLE is highlighted.

I suspect there is a problem with COLLECTABLE TABLE name but, have a lot of qrys, forms, reports using taht table. Any ideas? Thanks in advance.

[Welshgasman]

Surround it with [ and ]

Bad idea having spaces in field and object names.

You are the second person today, to fall into that trap.

[veale1009]

Kinda new at this When you say Surrond it with [ and ] what is "it"?
I tried
SELECT ([MODEL NUMBER FOR BOX]+1) AS MissingFrom, DMin("MODEL NUMBER FOR BOX","COLLECTABLE TABLE","MODEL NUMBER FOR BOX>" & [MODEL NUMBER FOR BOX]) AS MissingUntil
FROM [COLLECTABLE TABLE]
WHERE (((DMin("MODEL NUMBER FOR BOX","COLLECTABLE TABLE","MODEL NUMBER FOR BOX>" & [MODEL NUMBER FOR BOX]))<>([MODEL NUMBER FOR BOX]+1)));
Now get

??

[Welshgasman]

it is anything that has spaces in it.
That would be COLLECTABLE TABLE, MODEL NUMBER FOR BOX

You have done it in some places, but not others?
You need to be consistent.

So surround fields/object names with square brackets as I have already mentioned.
Even if there is one case where you do not have to do it (I cannot think of any right now) it isn't going to hurt, and again you become consistent.

[Micron]

2 cents:
Adopt a naming convention and don't use reserved words. Access life will be smoother.
Naming conventions -
http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

[veale1009]

it is anything that has spaces in it.
That would be COLLECTABLE TABLE, MODEL NUMBER FOR BOX

You have done it in some places, but not others?
You need to be consistent.

So surround fields/object names with square brackets as I have already mentioned.
Even if there is one case where you do not have to do it (I cannot think of any right now) it isn't going to hurt, and again you become consistent.

This is original code to follow
SELECT([MyNumber]+1) AS MissingFrom,DMin("MyNumber","MyTable","MyNumber>" &[MyNumber]) AS MissingUntil
FROM MyTable
WHERE(((DMin("MyNumber","MyTable","MyNumber>" &[MyNumber]))<>([MyNumber]+1)));

this is my code

SELECT ([[MODEL NUMBER FOR BOX]]+1) AS MissingFrom, DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX]>" & [[MODEL NUMBER FOR BOX]]) AS MissingUntil
FROM [COLLECTABLE TABLE]
WHERE (((DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[ MODEL NUMBER FOR BOX]>" & [[MODEL NUMBER FOR BOX]]))<>([[ MODEL NUMBER FOR BOX]]+1)));

syntax error in qry expression
SELECT ([[MODEL NUMBER FOR BOX]]+1 it indicates the right brackets before +1. tried everything over 2 hours and still doesn't work. I inherited this DB but don't want to change table. fld names now. thanks.

[Welshgasman]

Try

SELECT [MODEL NUMBER FOR BOX] + 1 AS MissingFrom, DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX]) AS MissingUntil
FROM [COLLECTABLE TABLE]
WHERE DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX]) & " <> [MODEL NUMBER FOR BOX] + 1";

if that does not work, put it all into a string variable and Debug.Print that vraible and post the result back.

[veale1009]

Try

SELECT [MODEL NUMBER FOR BOX] + 1 AS MissingFrom, DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX]) AS MissingUntil
FROM [COLLECTABLE TABLE]
WHERE DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX]) & " <> [MODEL NUMBER FOR BOX] + 1";

if that does not work, put it all into a string variable and Debug.Print that vraible and post the result back.

I tried your code, and at first I thought it worked. I got
missing from missing until
3 3
2 2
4 4
5 5 etc I got error "Syntax error (missing operation) in query expression '[MODEL NUMBER FROM BOX]>' my DB was then locked up.
I do appreciate you're trying to help me. Not sure what and how to create a string variable. Could not find info on Google.

[Welshgasman]

Post back what you have now within code tags. # icon in post toolbar

It's complicated to put it into a string as you are using those DMin arguments that also use "

Another attempt as the last portion of the criteria looked wrong

SELECT [MODEL NUMBER FOR BOX] + 1 AS MissingFrom, DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX]) AS MissingUntil
FROM [COLLECTABLE TABLE]
WHERE DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX]) & " AND <> [MODEL NUMBER FOR BOX] + 1";

Also post the link where you got that starting sql from, as the premise is that is correct?

[veale1009]

Post back what you have now within code tags. # icon in post toolbar

It's complicated to put it into a string as you are using those DMin arguments that also use "

Another attempt as the last portion of the criteria looked wrong

SELECT [MODEL NUMBER FOR BOX] + 1 AS MissingFrom, DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX]) AS MissingUntil
FROM [COLLECTABLE TABLE]
WHERE DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX]) & " AND <> [MODEL NUMBER FOR BOX] + 1";

Also post the link where you got that starting sql from, as the premise is that is correct?

LAST USED

SELECT [MODEL NUMBER FOR BOX] + 1 AS MissingFrom, DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX]) AS MissingUntil
FROM [COLLECTABLE TABLE]
WHERE DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX]) & " <> [MODEL NUMBER FOR BOX] + 1";
SITE I GOT IT FROM

Access Database. How to find missing numbers in a sequence of numbers. - Microsoft Community

Thanks for your help. If I was a quitter, I'd just give up but, even thought not my code, I don't want to give in....

[Welshgasman]

Try this. I just tried to compare like for like. I do not mind admitting I do not understand all of it

SELECT ([MODEL NUMBER FOR BOX] + 1) AS MissingFrom, DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX]) AS MissingUntil
FROM [COLLECTABLE TABLE]
WHERE (((DMin("[MODEL NUMBER FOR BOX]","[COLLECTABLE TABLE]","[MODEL NUMBER FOR BOX] > " & [MODEL NUMBER FOR BOX])) <> ([MODEL NUMBER FOR BOX] + 1)));

Access puts far too many () brackets in for my liking, but as I do not fully understand this, I just put them all in, as is.