Joining two fields in a query

[AllusiveKitten]

Hi

I am hoping someone can assist me, I have just created a query in Access where I have joined 2 fields ie FirstName & Surname to bring back the value "FirstName Surname". I am then trying to be able run a query for the full value. This is all working fine in the query until I copy the SQL statement to Visual basic.

Set db = CurrentDb()
lsql = "SELECT [TblList_RecipientDetails]![FirstName] & " " & [TblList_RecipientDetails]![Surname] AS Expr1, TblList_RecipientDetails.FirstName, TblList_RecipientDetails.Surname, TblList_RecipientDetails.AddressName, TblList_RecipientDetails.[Project/OfficeName], TblList_Addresses.Address1, TblList_Addresses.Address2, TblList_Addresses.Address3, TblList_Addresses.City, TblList_Addresses.State, TblList_Addresses.PostCode, TblList_Addresses.Country, TblList_RecipientDetails.Current, TblList_RecipientDetails.DeativateDate FROM TblList_RecipientDetails INNER JOIN TblList_Addresses ON TblList_RecipientDetails.[Project/OfficeName] = TblList_Addresses.[Project/OfficeName] WHERE ((([TblList_RecipientDetails]![FirstName] & " " & [TblList_RecipientDetails]![Surname])='" & Txt_RecipientName & "')));"
Set lrs = db.OpenRecordset(lsql)

It is here that I get an error "Expected end of statement" and the bolded area hightlighted.

Can anyone see where I have made the error, or left something off.

Thank you for your help

AK

[hariharanmca]

lsql = "SELECT [TblList_RecipientDetails]![FirstName]  [TblList_RecipientDetails]![Surname] AS Expr1, TblList_RecipientDetails.FirstName, TblList_RecipientDetails.Surname, TblList_RecipientDetails.AddressName, TblList_RecipientDetails.[Project/OfficeName], TblList_Addresses.Address1, TblList_Addresses.Address2, TblList_Addresses.Address3, TblList_Addresses.City, TblList_Addresses.State, TblList_Addresses.PostCode, TblList_Addresses.Country, TblList_RecipientDetails.Current, TblList_RecipientDetails.DeativateDate FROM TblList_RecipientDetails INNER JOIN TblList_Addresses ON TblList_RecipientDetails.[Project/OfficeName] = TblList_Addresses.[Project/OfficeName] WHERE ((([TblList_RecipientDetails]![FirstName]  [TblList_RecipientDetails]![Surname])='" & Txt_RecipientName & "')));"
Set lrs = db.OpenRecordset(lsql)
 

Try this query

[QVeen72]

Hi

I am hoping someone can assist me, I have just created a query in Access where I have joined 2 fields ie FirstName & Surname to bring back the value "FirstName Surname". I am then trying to be able run a query for the full value. This is all working fine in the query until I copy the SQL statement to Visual basic.

Set db = CurrentDb()
lsql = "SELECT [TblList_RecipientDetails]![FirstName] & " " & [TblList_RecipientDetails]![Surname] AS Expr1, TblList_RecipientDetails.FirstName, TblList_RecipientDetails.Surname, TblList_RecipientDetails.AddressName, TblList_RecipientDetails.[Project/OfficeName], TblList_Addresses.Address1, TblList_Addresses.Address2, TblList_Addresses.Address3, TblList_Addresses.City, TblList_Addresses.State, TblList_Addresses.PostCode, TblList_Addresses.Country, TblList_RecipientDetails.Current, TblList_RecipientDetails.DeativateDate FROM TblList_RecipientDetails INNER JOIN TblList_Addresses ON TblList_RecipientDetails.[Project/OfficeName] = TblList_Addresses.[Project/OfficeName] WHERE ((([TblList_RecipientDetails]![FirstName] & " " & [TblList_RecipientDetails]![Surname])='" & Txt_RecipientName & "')));"
Set lrs = db.OpenRecordset(lsql)

It is here that I get an error "Expected end of statement" and the bolded area hightlighted.

Can anyone see where I have made the error, or left something off.

Thank you for your help

AK

Hi,

Some problem with ur Where Condition , there is no operator after Firstname...

Try this where condition :
" Where [TblList_RecipientDetails]![FirstName] & ' ' & [TblList_RecipientDetails]![Surname])='" & Txt_RecipientName & "' ;"


Change Expr1 also this Way:

SELECT [TblList_RecipientDetails]![FirstName] & ' ' & [TblList_RecipientDetails]![Surname] AS Expr1

This is may avoid confusion of double quotes...

Regards
Veena

[hariharanmca]

lsql = "SELECT ([TblList_RecipientDetails]![FirstName] & [TblList_RecipientDetails]![Surname]) AS fldRecipientName, TblList_RecipientDetails.FirstName, TblList_RecipientDetails.Surname, TblList_RecipientDetails.AddressName, TblList_RecipientDetails.[Project/OfficeName], TblList_Addresses.Address1, TblList_Addresses.Address2, TblList_Addresses.Address3, TblList_Addresses.City, TblList_Addresses.State, TblList_Addresses.PostCode, TblList_Addresses.Country, TblList_RecipientDetails.Current, TblList_RecipientDetails.DeativateDate FROM TblList_RecipientDetails INNER JOIN TblList_Addresses ON TblList_RecipientDetails.[Project/OfficeName] = TblList_Addresses.[Project/OfficeName] WHERE ((([TblList_RecipientDetails]![FirstName] & [TblList_RecipientDetails]![Surname]) like '" & Txt_RecipientName & "')));"

Can you explain why in this field name

TblList_RecipientDetails.[Project/OfficeName] = TblList_Addresses.[Project/OfficeName]

[jrtox]

Hello,

you said that you created a query in Access where you joined 2 fields, Right?

Maybe my step might help.

1.
1.A. You dont need to paste that very long Set of SQL STATEMENT to your VB..
1.B. Just save youre query, example:QUIRYNAME.

2.When youre Using ADODC object then Setup a connection between ur VB and ACCESS.
3.then try the code below
Adodc1.CommandType = adCmdText
Adodc1.RecordSource = "SELECT * FROM QUIRYNAME" ' Calls directly to your quiry.
Set DataGrid1.DataSource = Adodc1' Optional

4. if Using ADODB then

dim CONN as NEW ADODB.Connection
dim RS. as NEw ADODB.Recordset

'Establish CONN as A Connection

then,

RS.Open "SELECT * FROM QUIRYNAME",CONN,,
Set Datagrid1.datasource=RS

5. Goodluck!!

Regards
Ervin