Concatenate Issue

[Guest]

Hi I am having issues concatenating a text field. The field contains details
of when a call back has happened. I want to concatenate into one field. I
have tried using the following SQL statement (Thanks Duane) but with little
sucess.

Contact Details: Concatenate("SELECT Details of contact made FROM
tblDebtors WHERE Customer Name =""" & tblCustomerDetails![Customer Name])

I get the following error:

Run Time Error: Method 'Open' of object '_Recordset' failed

When I debug it relates to the following line of code:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Can anyone help?

 

[John W. Vinson]

Contact Details: Concatenate("SELECT Details of contact made FROM
tblDebtors WHERE Customer Name =""" & tblCustomerDetails![Customer Name])

Blanks are meaningful. If you (unwisely IMO) insist on using them in your
fieldnames, you must use brackets - you see "Details of contact made" and
"Customer name" as two things, but Access sees them as six. You also need a
closing quote.

Try

Contact Details: Concatenate("SELECT [Details of contact made] FROM
tblDebtors WHERE [Customer Name] =""" & tblCustomerDetails![Customer Name] &
"""")


John W. Vinson [MVP]

 

[Guest]

I am assuming you are working in Access. Standard Access does not have a
Concatenate function. To concatenate text fields in Access just put field
name in brackets, apersand, and next field name in brackets.
[MyFieldName] & [MyFieldName1]
Sometimes you want the data separated by a dash then you do this --
[MyFieldName] & " - " & [MyFieldName1]
If you want three fields but one may be null then this --
[MyFieldName] & " - " & IIF([MyFieldName1] Is Null,
Null,[MyFieldName1] & " - " &) IIF([MyFieldName2] Is Null,
Null,[MyFieldName2])

 

[Guest]

Thanks John that worked perfectly.

 

[Guest]

Sorry John one more thing. If I add in an extra field (Invoice Number) before
Details of contact made. Is there a way to seperate it with a hyphen?

Thanks John that worked perfectly.

Hi I am having issues concatenating a text field. The field contains details
of when a call back has happened. I want to concatenate into one field. I
have tried using the following SQL statement (Thanks Duane) but with little
sucess.

Contact Details: Concatenate("SELECT Details of contact made FROM
tblDebtors WHERE Customer Name =""" & tblCustomerDetails![Customer Name])

I get the following error:

Run Time Error: Method 'Open' of object '_Recordset' failed

When I debug it relates to the following line of code:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Can anyone help?

 

[John Spencer]

Contact Details: Concatenate("SELECT [Invoice Number] & "" - "" &[Details of
contact made] FROM
tblDebtors WHERE [Customer Name] =""" & tblCustomerDetails![Customer Name])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sorry John one more thing. If I add in an extra field (Invoice Number)
before
Details of contact made. Is there a way to seperate it with a hyphen?

Thanks John that worked perfectly.

Hi I am having issues concatenating a text field. The field contains
details
of when a call back has happened. I want to concatenate into one field.
I
have tried using the following SQL statement (Thanks Duane) but with
little
sucess.

Contact Details: Concatenate("SELECT Details of contact made FROM
tblDebtors WHERE Customer Name =""" & tblCustomerDetails![Customer
Name])

I get the following error:

Run Time Error: Method 'Open' of object '_Recordset' failed

When I debug it relates to the following line of code:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Can anyone help?

 

[John W. Vinson]

Sorry John one more thing. If I add in an extra field (Invoice Number) before
Details of contact made. Is there a way to seperate it with a hyphen?

You can insert a literal hyphen by concatenating it as a text string:

SELECT [Invoice Number] & "-", [Details of contact made], ...

However, it should generally NOT be necessary to include such punctuation in a
query. Use the query to select the data to be presented; present it on a Form
for onscreen use, or a Report for printing. You can put punctuation, hyphens,
etc. as Labels on the form or report.

John W. Vinson [MVP]

 

[Guest]

Thanks for all you help John. Working fine.

Sorry John one more thing. If I add in an extra field (Invoice Number) before
Details of contact made. Is there a way to seperate it with a hyphen?

You can insert a literal hyphen by concatenating it as a text string:

SELECT [Invoice Number] & "-", [Details of contact made], ...

However, it should generally NOT be necessary to include such punctuation in a
query. Use the query to select the data to be presented; present it on a Form
for onscreen use, or a Report for printing. You can put punctuation, hyphens,
etc. as Labels on the form or report.

John W. Vinson [MVP]