Concatenate with Dmax

I apoligize in advance, I have been going through the postings for
over 5 hours, and I have not found a solution I can make work.

I want to concatenate a listbox value with a sequencial number
defined as
DMax([ctrnlnumber])+1, that should look something like this:
=[ctrnltext] & "-" & DMax([ctrnlnumber])+1.

The output should look like:
JFSD-1001 then on each subsequent record:
JFSD-1002
JFSD-1003

"JFSD" is the listbox value from a query on a table with one record
set as JFSD

I want these number(s) to be "written" to a text field called
SmwRef in the same table as the other fields on the form, the table
name is SmwMain.

If possible I would like the solution to be handled in the
properties box, since I know even less about VBA or code.

Thanks Again, You guys as always are the greatest at making us
'want-a-bes' look good !!

Tom Rector

Have you tried converting the DMax function to a String?
-----Original Message-----
I apoligize in advance, I have been going through the
postings for
over 5 hours, and I have not found a solution I can make
work.

I want to concatenate a listbox value with a sequencial
number
defined as
DMax([ctrnlnumber])+1, that should look something like
this:
=[ctrnltext] & "-" & DMax([ctrnlnumber])+1.

The output should look like:
JFSD-1001 then on each subsequent record:
JFSD-1002
JFSD-1003

"JFSD" is the listbox value from a query on a table
with one record
set as JFSD

I want these number(s) to be "written" to a text
field called
SmwRef in the same table as the other fields on the
form, the table
name is SmwMain.

If possible I would like the solution to be handled
in the
properties box, since I know even less about VBA or code.

Thanks Again, You guys as always are the greatest at
making us
'want-a-bes' look good !!

Tom Rector

.

Tom,

It is not 100% clear to me what you are trying to do, but hopefully
these few comments will help...

First off, your usage of the DMax function is not correct.
DMax([ctrnlnumber]) does not make sense. The syntax for DMax is...
DMax("[NameOfField]","NameOfTableOrQuery","optional condition expression")
So you see, you have not got any ""s which are required, and you have
not indicated the table or query name.

Second point is that it is highly inadvisable to "write" this
concatenated data to a table field. Just stick with the values in the
[ctrnltext] and [ctrnlnumber] fields, and generate the concatenated
value whenever you need it for your purposes of form or report.

Do you mean that the [ctrnlnumber] field is presently blank? Why not
try it like this... temporarily add a new Autonumber field to the table,
and then run an Update Query on the table to update the [ctrnlnumber] to:
[TheAutoNumberField]+1000

--
Steve Schapel, Microsoft Access MVP

Thomas Rector wrote:
I apoligize in advance, I have been going through the postings for
over 5 hours, and I have not found a solution I can make work.

I want to concatenate a listbox value with a sequencial number
defined as
DMax([ctrnlnumber])+1, that should look something like this:
=[ctrnltext] & "-" & DMax([ctrnlnumber])+1.

The output should look like:
JFSD-1001 then on each subsequent record:
JFSD-1002
JFSD-1003

"JFSD" is the listbox value from a query on a table with one record
set as JFSD

I want these number(s) to be "written" to a text field called
SmwRef in the same table as the other fields on the form, the table
name is SmwMain.

If possible I would like the solution to be handled in the
properties box, since I know even less about VBA or code.

Thanks Again, You guys as always are the greatest at making us
'want-a-bes' look good !!

Tom Rector

Steve Schapel wrote in message ...
Tom,

It is not 100% clear to me what you are trying to do, but hopefully
these few comments will help...

First off, your usage of the DMax function is not correct.
DMax([ctrnlnumber]) does not make sense. The syntax for DMax is...
DMax("[NameOfField]","NameOfTableOrQuery","optional condition expression")
So you see, you have not got any ""s which are required, and you have
not indicated the table or query name.

Second point is that it is highly inadvisable to "write" this
concatenated data to a table field. Just stick with the values in the
[ctrnltext] and [ctrnlnumber] fields, and generate the concatenated
value whenever you need it for your purposes of form or report.

Do you mean that the [ctrnlnumber] field is presently blank? Why not
try it like this... temporarily add a new Autonumber field to the table,
and then run an Update Query on the table to update the [ctrnlnumber] to:
[TheAutoNumberField]+1000

--
Steve Schapel, Microsoft Access MVP

Thomas Rector wrote:
I apoligize in advance, I have been going through the postings for
over 5 hours, and I have not found a solution I can make work.

I want to concatenate a listbox value with a sequencial number
defined as
DMax([ctrnlnumber])+1, that should look something like this:
=[ctrnltext] & "-" & DMax([ctrnlnumber])+1.

The output should look like:
JFSD-1001 then on each subsequent record:
JFSD-1002
JFSD-1003

"JFSD" is the listbox value from a query on a table with one record
set as JFSD

I want these number(s) to be "written" to a text field called
SmwRef in the same table as the other fields on the form, the table
name is SmwMain.

If possible I would like the solution to be handled in the
properties box, since I know even less about VBA or code.

Thanks Again, You guys as always are the greatest at making us
'want-a-bes' look good !!

Tom Rector



Thanks Steve, I took your advice and just used the "ID" number and the
'default" field entry of "JFSD" in a report field.