merge fields

[dneidhart]

I am looking for a way to combine fields in Access 2002 in an append query. I’m current using Table.Field1 & Table.Field2 and getting nothing close to what I need. Both fields are numeric. Field 1 is 4 characters in length and Field 2 is 1 to 4 characters in length. I want a new field of 8 characters. So I need something that combines the 2 and adds the leading zeros on the second field.



Is there an easy way to do this?

 

[Rob.Mills]

I would create a public function to use as the value of the field

Public Function fValue(lngOne As Long, lngTwo As Long) As Long

Dim strComplete As String
Dim strTwo as String

Select Case len(lngTwo)

Case 1

strTwo="000" & lngTwo

Case 2

strTwo="00" & lngTwo

Case 3

strTwo="0" & lngTwo

Case Else

strTwo=lngtwo

End Select

strComplete=lngOne & strTwo

fValue=CLng(strComplete)




Then call this function in your query by typing

fValue([Field1],[Field2])

Open it in a select query to make sure it works before appending it.

 

[dcx693]

I'm not sure why you want this in an append query, since you can always calculate it, but since you ask....

You use an expression like this:
[Field1] & Right("0000" & [Field2],4)

Let me explain how it works. Clearly, it starts off with Field1. The next part takes the string "0000" and combines it with Field2. Now, since you say you'd like the second half of that string to be four characters and that Field2 maxes out at four characters, combining "0000" with Field2 will yield a max of 8 characters, or a min of 4. In any case, you'd like it to be 4 characters long. Using the Right function and just taking the right-most 4 characters gives you what you need. Concatentating that to the Field1 gets you the total merge you wanted.

Now, if you want to store the result as a number, you can use the CLng function to convert it.

 

[dcx693]

Good suggestion Pat. I think the user said the first field was already 4-digit, so perhaps he/she can simplify it to:
[fld1] & Format(fld2,"0000")

 

[dneidhart]

Thanks for your help. Each of the responses were very insightful.

I liked your response the best. However, I could not get the public function to run without an error.

 

[dneidhart]

Format(fld1,"0000") & Format(fld2,"0000")

Thanks this worked great!

 

[dneidhart]

Thanks, this worked great also.

I'm not sure why you want this in an append query, since you can always calculate it, but since you ask....

You use an expression like this:
[Field1] & Right("0000" & [Field2],4)

Let me explain how it works. Clearly, it starts off with Field1. The next part takes the string "0000" and combines it with Field2. Now, since you say you'd like the second half of that string to be four characters and that Field2 maxes out at four characters, combining "0000" with Field2 will yield a max of 8 characters, or a min of 4. In any case, you'd like it to be 4 characters long. Using the Right function and just taking the right-most 4 characters gives you what you need. Concatentating that to the Field1 gets you the total merge you wanted.

Now, if you want to store the result as a number, you can use the CLng function to convert it.