If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Concatenate eliminating blank cells | Neil G | Worksheet Functions | 6 | May 17th, 2004 04:25 PM |
CONCATENATE within VLOOKUP | Frank Kabel | Worksheet Functions | 3 | March 31st, 2004 10:12 PM |
Concatenate | Crystal | Worksheet Functions | 2 | February 26th, 2004 04:33 AM |
Concatenate Conditions | Harlan Grove | Worksheet Functions | 0 | January 22nd, 2004 12:21 AM |
Date formats & Concatenate function | Sue | Worksheet Functions | 2 | November 30th, 2003 04:43 AM |