Split int in query

[Carloj]

Hi there,



I want to split a order in seprate boxes. In order 12345 is defined box 50 until 100 . Now i want this in a query like:

Order	BOX
12345	51
12345	52
12345	53
12345	54

(until 100)

Is this possible in a query? or is it possible in a new box table?

[ranman256]

start at 51?
end at 100? even with more records after 100?

you can do it easy in a report,
or
append the data to a 'table' then use vb code to number them
but
doing it in a query is not easy or stable.

(unless someone else has a query method?)

[CJ_London]

June recently posted some code on another forum which can be adapted

SELECT PONumber, Counter AS Box
FROM tblPOs, (SELECT DISTINCT 100*Abs([Hundreds].[id] Mod 10)+10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1 AS Counter
FROM MSysObjects AS Ones, MSysObjects AS Tens, MSysObjects AS Hundreds
WHERE Ones.Id<0 AND Tens.Id<0 AND Hundreds.Id<0)  AS Counts
WHERE Counter Between 51 And 100 AND PONumber=12345

the code in red provides a number range from 1 to 999

[Carloj]

June recently posted some code on another forum which can be adapted

SELECT PONumber, Counter AS Box
FROM tblPOs, (SELECT DISTINCT 100*Abs([Hundreds].[id] Mod 10)+10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1 AS Counter
FROM MSysObjects AS Ones, MSysObjects AS Tens, MSysObjects AS Hundreds
WHERE Ones.Id<0 AND Tens.Id<0 AND Hundreds.Id<0)  AS Counts
WHERE Counter Between 51 And 100 AND PONumber=12345

the code in red provides a number range from 1 to 999

Do you got a link to the thread?

[Carloj]

start at 51?
end at 100? even with more records after 100?

you can do it easy in a report,
or
append the data to a 'table' then use vb code to number them
but
doing it in a query is not easy or stable.

(unless someone else has a query method?)

It can go up to a million.

do you got an example of append the data and a vb code?

[CJ_London]

have to ask, why would you want to create 1m boxes for 1 order in one go? I suspect there is a better way to go. Creating 1m records in one go will take time, and if happens frequently may need consider a sql server backend due to space requirements for all those records

re link - see post #3

https://www.access-programmers.co.uk...d.php?t=302140

if you need to go to a million, you will need to add further msysobjects for thousands, 10's thousands, etc