custom field numbering sequence

J

Jae Hood

Hi,
I have a Load Details table that looks like this:
OrderID
pk LoadID
ShipperID
Status

The LoadID's for each order always correspond to the OrderID and are
followed by a - then the sequential number.
Example: the first 3 loads for OrderID number 54321 are as follows:
54321-1
54321-2
54321-3
Is there anyway to automate this process, so that everytime a new record is
created, the LoadID is generated based on the OrderID it belongs to and the
previous LoadID's which exist in that order.

Thanks,
Justin
 
O

Ofer

Create a query based on the table that take the LoadID and breaks it into to
fields
Order Number and counter

Select Cdbl(mid([FieldName],instr([FieldName],"-")+1) as MyCount,
Left([FieldName],instr([FieldName],"-")-1) as OrderId From TableName

On the After update event of the OrderId insert the code
Me.LoadIdFieldName = Dmax("MyCount","QueryName","OrderId = '" &
cstr(Me.OrderIdFieldName) & "'")
 
O

Ofer

In the query I missed one thing
Select Cdbl(mid([FieldName],instr([FieldName],"-")+1)) as MyCount,
Left([FieldName],instr([FieldName],"-")-1) as OrderId From TableName
 
J

Jae Hood

The SQL code looks like this:
Select Cdbl(mid([LoadID],instr([OrderID],"-")+1)) as MyCount,
Left([LoadID],instr([OrderID],"-")-1) as OrderID From [Load Details]

When I View it gives me "Circular reference caused by alias "OrderID" in
query definition's SELECT list."


Thanks for the help,
Justin

Ofer said:
In the query I missed one thing
Select Cdbl(mid([FieldName],instr([FieldName],"-")+1)) as MyCount,
Left([FieldName],instr([FieldName],"-")-1) as OrderId From TableName


--
In God We Trust - Everything Else We Test


Jae Hood said:
Hi,
I have a Load Details table that looks like this:
OrderID
pk LoadID
ShipperID
Status

The LoadID's for each order always correspond to the OrderID and are
followed by a - then the sequential number.
Example: the first 3 loads for OrderID number 54321 are as follows:
54321-1
54321-2
54321-3
Is there anyway to automate this process, so that everytime a new record is
created, the LoadID is generated based on the OrderID it belongs to and the
previous LoadID's which exist in that order.

Thanks,
Justin
Click to expand...
Click to expand...
 
O

Ofer

Try this

Select Cdbl(mid([LoadID],instr([LoadID],"-")+1)) as MyCount,
Left([LoadID],instr([LoadID],"-")-1) as OrderID From [Load Details]

Replace the OrderID with LoadID in the sql

--
In God We Trust - Everything Else We Test


Jae Hood said:
The SQL code looks like this:
Select Cdbl(mid([LoadID],instr([OrderID],"-")+1)) as MyCount,
Left([LoadID],instr([OrderID],"-")-1) as OrderID From [Load Details]

When I View it gives me "Circular reference caused by alias "OrderID" in
query definition's SELECT list."


Thanks for the help,
Justin

Ofer said:
In the query I missed one thing
Select Cdbl(mid([FieldName],instr([FieldName],"-")+1)) as MyCount,
Left([FieldName],instr([FieldName],"-")-1) as OrderId From TableName


--
In God We Trust - Everything Else We Test


Jae Hood said:
Hi,
I have a Load Details table that looks like this:
OrderID
pk LoadID
ShipperID
Status

The LoadID's for each order always correspond to the OrderID and are
followed by a - then the sequential number.
Example: the first 3 loads for OrderID number 54321 are as follows:
54321-1
54321-2
54321-3
Is there anyway to automate this process, so that everytime a new record is
created, the LoadID is generated based on the OrderID it belongs to and the
previous LoadID's which exist in that order.

Thanks,
Justin
Click to expand...
Click to expand...
Click to expand...
 
J

Jae Hood

Perfect!

Thank you Ofer!

Ofer said:
Try this

Select Cdbl(mid([LoadID],instr([LoadID],"-")+1)) as MyCount,
Left([LoadID],instr([LoadID],"-")-1) as OrderID From [Load Details]

Replace the OrderID with LoadID in the sql

--
In God We Trust - Everything Else We Test


Jae Hood said:
The SQL code looks like this:
Select Cdbl(mid([LoadID],instr([OrderID],"-")+1)) as MyCount,
Left([LoadID],instr([OrderID],"-")-1) as OrderID From [Load Details]

When I View it gives me "Circular reference caused by alias "OrderID" in
query definition's SELECT list."


Thanks for the help,
Justin

Ofer said:
In the query I missed one thing
Select Cdbl(mid([FieldName],instr([FieldName],"-")+1)) as MyCount,
Left([FieldName],instr([FieldName],"-")-1) as OrderId From TableName


--
In God We Trust - Everything Else We Test


:

Hi,
I have a Load Details table that looks like this:
OrderID
pk LoadID
ShipperID
Status

The LoadID's for each order always correspond to the OrderID and are
followed by a - then the sequential number.
Example: the first 3 loads for OrderID number 54321 are as follows:
54321-1
54321-2
54321-3
Is there anyway to automate this process, so that everytime a new record is
created, the LoadID is generated based on the OrderID it belongs to and the
previous LoadID's which exist in that order.

Thanks,
Justin
Click to expand...
Click to expand...
Click to expand...
 
Top