Now I remember.
Line: [System Number] & Format([Seq No],"000")
3acks
Last Activity: Mar 17 '08, 02:50 PM
Joined: May 31 '07
Location:
-
Simple concatenation
I know I've done this before, but for some reason the answer alludes my brain this Monday morning...
When I concatenate two number fields line:[system] & [sequence], it is dropping the leading zeros.
ex: system = 72 and sequence = 001
concatenate as line:[system] & [sequence= 721
I want to see line = 72001
Thanks -
oops
. .Leave a comment:
-
Just a follow up for anyone who refers to this at a later date. I moved the code to the AfterInsert update because on BeforeUpdate it would re-calculate the number with any change to any field in the record and I only want a number calculated when a new record is added. I also added an If statement to handle if the search returned no current value, so that the first record would get a value = 1.
Private Sub Form_AfterInser t()...Leave a comment:
-
Finally got it to work right. I think one of my problems is that System Number is a number format, so no (')s go around the string.
Thanks for the help.
Private Sub Form_BeforeUpda te(Cancel As Integer)
Dim Pspec As String
Dim Fcode As String
Dim Synum As Integer
Dim STR As String
If Not IsNull(Forms![line test]!PipeSpec) And Not IsNull(Forms![line test]!FluidCode)...Leave a comment:
-
I've gotten past that and I'm getting the unknown function 'A' again. I've tried repacing the three variables with a predefined string, but it's not reading it correctly. It's giving me the next available number for the entire database and not for the criteria.
Dim Pspec As String
Dim Fcode As String
Dim Synum As String
Dim STR As String
Pspec = Me![Pipe Spec].Value...Leave a comment:
-
It keeps telling me I've canceled the previous operation and won't go on.Leave a comment:
-
This is the line that is highlighting.
[txtseqno] = DMax("[seq no]", "Line_List" , "[Pipe spec] = " & Forms![line test]!PipeSpec & " And [fluid code] = " & Forms![line test]!FluidCode & " And [system number] = " & Forms![line test]!SystemNumber) + 1
It is giving the correct value from the form for each field.Leave a comment:
-
Is this some reference file that I need to load? Just a thought...Leave a comment:
-
Undefined function 'A' in expression???? No number generated.Leave a comment:
-
yeah, just a 3 digit number is all. I'll concatenate at times for reports and things to create a longer number, but this field is just a 3 digit number.Leave a comment:
-
When I got out and got back in, this time the error was different. Run-time error 3085, undefined function 'A' in expression.
?????Leave a comment:
-
Alright, I got it to work using a simple multiple criteria inside a query. Now when I try to transfer this concept to my form where I actually have 3 criteria, it fails. Here is the code.
Private Sub Form_BeforeUpda te(Cancel As Integer)
If Not IsNull(Forms![line test]!PipeSpec) And Not IsNull(Forms![line test]!FluidCode) And Not IsNull(Forms![line test]!SystemNumber) Then
[txtseqno] = DMax("[seq...Leave a comment:
-
”[CustomerName] = ‘” & sCustName & “’ AND [ProductType] = ‘“ & sProdType & “’”
I can not get this to work with two criteria... even if I use specific values for sCustName and sProdType...Leave a comment:
-
Where do I place the code? I notice you have it on the BeforeUpdate of the form, but there needs to be multiple values selected first and they have not had the chance to choose the customer or product yet??Leave a comment:
-
Auto increment number based off of other values
I've read a lot of articles and forums trying to figure this one and have yet to come up with a solution I can get to work. I need to create a unique id. I can do this using Dmax to find the largest value and add 1. The problem is that I need the unique ID to be based on the value of two other fields. So, in table of hundreds of records I want to look at only certain values to obtain the next available number. For example, if I have a table...
No activity results to display
Leave a comment: