Return OrderNo/BoxNo from Total Boxes

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I have a table - tblOrderBoxes, which contains fields OrderBoxesID, OrderNo
and NoOfboxes. I want to build a query that gives me a string in the format
OrderNo/BoxNo. I can get it to work fine if it is just one box using the
following - OrderBoxNo: [OrderNo] & "/" & [NoOfBoxes], but if there is more
than one box (e.g. 3) it just outputs 1234/3, and I need 1234/1, 1234/2 and
1234/3. I think I need to call a custom function within the query. Any
pointers in the right direction? Also is it possible to enter more than one
variable into a function via a query?

thanks in advance

Mark
 
Mark said:
I have a table - tblOrderBoxes, which contains fields OrderBoxesID, OrderNo
and NoOfboxes. I want to build a query that gives me a string in the
format OrderNo/BoxNo. I can get it to work fine if it is just one box
using the following - OrderBoxNo: [OrderNo] & "/" & [NoOfBoxes], but if
there is more than one box (e.g. 3) it just outputs 1234/3, and I need
1234/1, 1234/2 and 1234/3. I think I need to call a custom function within
the query. Any pointers in the right direction? Also is it possible to
enter more than one variable into a function via a query?

thanks in advance

Mark
Click to expand...

With this, I can get the box numbers all in one cell but not on each row.

Public Function GenerateBoxNo(OrderNo As Long, Qty As Integer)
Dim i As Integer
Dim BoxNo As String

If qty > 1 Then
For i = 1 To qty
BoxNo = BoxNo & " " & OrderNo & "/" & i
Next
GenerateBoxNo = BoxNo

Else

GenerateBoxNo = OrderNo & "/" & qty

End If


End Function
 
The best way I know is to add another table that has one field consisting of
unique integers from 1 to N. Where N is the largest number you will need.

(table: MyIntegers; Field: IntNumber)

Then you add that table to your query with NO join.
Add the field IntNumbers to the query
Set the criteria for IntNumber as <= NoOfBoxes

This will generate one row for each box.
SQL would look something like

SELECT OrderNo & "/" & IntNumber
FROM tblOrderBoxes, MyIntegers
WHERE IntNumber <= NoOfBoxes
 
Exactly what I was looking for - works a treat. Many thanks for your help.


John Spencer said:
The best way I know is to add another table that has one field consisting
of unique integers from 1 to N. Where N is the largest number you will
need.

(table: MyIntegers; Field: IntNumber)

Then you add that table to your query with NO join.
Add the field IntNumbers to the query
Set the criteria for IntNumber as <= NoOfBoxes

This will generate one row for each box.
SQL would look something like

SELECT OrderNo & "/" & IntNumber
FROM tblOrderBoxes, MyIntegers
WHERE IntNumber <= NoOfBoxes




Mark said:
I have a table - tblOrderBoxes, which contains fields OrderBoxesID,
OrderNo and NoOfboxes. I want to build a query that gives me a string in
the format OrderNo/BoxNo. I can get it to work fine if it is just one box
using the following - OrderBoxNo: [OrderNo] & "/" & [NoOfBoxes], but if
there is more than one box (e.g. 3) it just outputs 1234/3, and I need
1234/1, 1234/2 and 1234/3. I think I need to call a custom function
within the query. Any pointers in the right direction? Also is it
possible to enter more than one variable into a function via a query?

thanks in advance

Mark
Click to expand...
Click to expand...
 
Back
Top