custom field numbering sequence

[Guest]

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

 

[Guest]

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) & "'")

 

[Guest]

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

 

[Guest]

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

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

 

[Guest]

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

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

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

 

[Guest]

Perfect!

Thank you 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

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

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