how to define derived field

[masoud_sedighy]

before i have a question about searching the fields in the table and answer in this address (​

http://www.access-programmers.co.uk/forums/showthread.php?t=246966) was concatenate the fields as a derived field and search in that.​

now i like to do that in the vba like below but i have error (enter parameter x1) please help how i have to define that.​

  [LEFT]Dim X1 As Variant[/LEFT]
  [LEFT]X1 = [First Name] & "-" & [Last Name] & "-" & [ID Number] & "-" &  _[/LEFT]
  [LEFT]        "-" & [MobileNo][/LEFT]
    [LEFT]varWhere = "X1 LIKE '" & _[/LEFT]
  [LEFT]        strWild & Me.txtsearch & strWild & "'"[/LEFT]

 

[pr2-eugin]

Is it possible if you could edit your post again? There seems to be some formatting issue with your post.. Just select the entire text and click the remove Text Formatting button..

 

[masoud_sedighy]

i edited my post

 

[pr2-eugin]

So what are you trying to do.. Forget about your previous posts.. Just a fresh start..

What are you trying to do in simple plain English?

 

[masoud_sedighy]

thanks
i like to concatenate fiels [fistname]&[lastname] ,..and then save in the variable x1 and then use it in

x1=[fistname]&[lastname] &.....

[LEFT]varWhere = "X1 LIKE '" & _[/LEFT]
  [LEFT]        strWild & Me.txtsearch & strWild & "'"[/LEFT]

and then filter my form with below code, but i have error (enter parameter value) about x1, it seems i have not uses x1 variable correctly in varwhere variable, may be problem of quotation or double quotation but i do not know how to solve that.

Set frm = Forms![Fardi List]
        ' In an MDB,
      
            ' Use the Filter property
            frm.Filter = varWhere
            frm.FilterOn = True
        
            ' In and ADP,
            ' Clear the Filter property, if any
          
        Set frm = Nothing
  '  Not open - just open filtered
        ' If 5 or fewer records found,
       
            ' Open Contacts filtered
            DoCmd.OpenForm "Fardi List", WhereCondition:=varWhere
            ' Make sure focus is on contacts
            Forms![Fardi List].SetFocus
       
    ' Clean up recordset
End Sub

 

[pr2-eugin]

Try

varWhere = X1[COLOR=Red][B] & " [/B][/COLOR]LIKE '[COLOR=Red][B]" &[/B][/COLOR] strWild & Me.txtsearch & strWild & "'"

 

[masoud_sedighy]

it works,
just one more question if i like to have also vertical bars like below:

LastName & "|" & FirstName & "|" & etc

i receive error "invalid use of vertical bars in query expression"

is it possible with vertical bars?

 

[pr2-eugin]

Why?? What is the need of using vertical bars here??

 

[masoud_sedighy]

now it seems all the fields are mixed , just i like to separate them part by part and can search part by part

for example

[first name]=masoud
[last name]=sedighy
[mobile no]=1234
[malicode]=3323

after concatenating is "masoudsedighy12343323"
now if search for example txtsearch=433
it finds records actually it should not find because there is not melicode or mobileno with value 433

 

[masoud_sedighy]

thanks pr2-eugin

it works now with below code:

x1 = "[First Name]" & "&" & "[Last Name]" & "&" & "[ID Number]" & "&" & "[MobileNo]" & "&" & "[Mahale_Tavalod]" & _
"&" & "[MeliCode]"

MsgBox fd

' varWhere = "[First Name]&[Last Name]&[ID Number] LIKE '" & strWild & Me.txtsearch & strWild & "'"


varWhere =x1 & " LIKE '" & strWild & Me.txtsearch & strWild & "'"