concatenate problem 1

[mavog]

Hi All,
I've come across a little problem wit concatenating memo fields(address). They are truncated to a text fields ,which only holds 255 characters.Any ideas on how to keep all the txt?

Here's the code i'm using:

Private Sub btnSQL7_Click()
Dim SQLstr As String
'concat into ETE

SQLstr = "SELECT Available.[Total size], Available.Description, [Available.Address] & ',' & [Available.Address2] & ',' & [Available.Address3] & ',' & [Available.Town] & ',' & [Available.Postcode] AS PropertyAddress, Available.AgentOne INTO ETE FROM Available"

DoCmd.RunSQL SQLstr

End Sub

 

[CajunCenturion]

I think you can use the "+" sign to concatenate within an SQL statement. The following example also inserts a space between the fields.

SQLstr = "SELECT Available.[Total size], Available.Description, [Available.Address] + ' ' + [Available.Address2] + ' ' + [Available.Address3] + ' ' + Available.Town] + ' ' + [Available.Postcode] AS PropertyAddress, Available.AgentOne INTO ETE FROM Available"

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[GComyn]

ok... here's how I understand it....

With a select into query, you can create a memo field only by having a memo field to put into it.

What you are doing is creating a string (using either & or +), then putting it into a new table.

What I would do would be to have the table already created with the memo field required, then append the information into the table.

If you want to have an empty table each time you append to it, just delete all records before appending.


If I am incorrect about the creation of a memo field with the select into query, PLEASE speak up, anyone....

GComyn