Concatenate with Dmax

[Thomas Rector]

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
(e-mail address removed)

 

[Joe]

Have you tried converting the DMax function to a String?

 

[Steve Schapel]

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

 

[Thomas 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

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
(e-mail address removed)

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