If it is possible? some of different fields merge in a calculation filed on a query

[SahilSS]

Hello all,

I know normal fields merge calculation in a filed but how to do different fields data is merge in a calculation filed on a query?

For example in a filed data is text and number like as (ABC111) or (222DEF) so in this case how can we do merge calculation without text or number. I need as per below shown details so please can anyone replay how to do this merge calculations ???

Field1 - ABC111
Field2 - 222DEF
MergeField - ABC DEF
or
MergeField - 111DEF

Thanks.

[Seth Schrock]

You would use a combination of the Left, Right, and Mid functions and possibly the Instr function. Is the pattern for Field1 always three letters and then three numbers? Is the pattern for Field2 always three numbers and then three letters?

[Kara Hewett]

The Microsoft Visual Basic string class includes numerous methods that could be used for the merge of fields. The complete list is at the URL below. In addition to Left, Right and Mid, you could use Split, RSet, LSet. Also Join can be used to concatenate strings once they have been split.

http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.strings(v=vs.110).aspx

[Seth Schrock]

Keep in mind that the list provided is for VB.Net, not MS Access VBA, so while some of the ones in the list aren't available by default in MS Access VBA.

[SahilSS]

Not only follow always three letters and three numbers for both fields. Some times more than letters some times more than numbers like as below shown fields. I just used Left, Right functions also but not workout & it is taken numbers also.
Ex: 1
Field1: Samsung 2245
Filed2: Mobile
MergeField: Samsung Mobile (how to do this?)

Ex: 2
Filed1: Logitech 220
Field2: Mouse
MergeField: Nokia Mobile

Filed1 is Brand name and model number Field2 is Item so I need letters only in the filed1 without model number in the merged filed

[twinnyfo]

Sahil,

You will have to come up with rules for just about every possibility. You may have to play with functions line Split (as mentioned earlier) or InStr to determine breaking points and then combine with the Left/Right/Mid Functions.

However, you may encounter problems with your code. First, if you only look for a space in between the Name and model number, you may have a brand name that has two words (or a Brand that has a Word and a Number in it).

Is there a reason the brand and model are already concatenated instead of being separate fields (you may just get the data that way, which explains your desire to split it).

This could get very complex depending on the type of data you have.

[Seth Schrock]

I did some research online and I found a way to remove all your numbers from the text. However, this will add a lot of overhead to the program, so I wouldn't run this on very many records at the same time.

Function ParseNumber(strIn) As String
'-----------------------------------------------------------
' Arvin Meyer 8/13/1995
'-----------------------------------------------------------
On Error Resume Next

Dim strTmp As String
Dim lngPtr As Long 'pointer

For lngPtr = 1 To Len(strIn)
If IsText(Mid(strIn, lngPtr, 1)) Then
strTmp = strTmp & Mid(strIn, lngPtr, 1)
End If
Next lngPtr

ParseNumber = strTmp

End Function

(Link to Original Post, modified to get text instead of numbers.) You would just have to perform this function on both fields and concatenate the results.