SQL to VBA (with a concatenated alias)

[Mr_Si]

Hi all,

I'm using VBA for a search query to search for a site address.
The way I'm doing it means that the form which is opened as a result of the search that shows the found entries includes a field which has multiple table fields under a single aliased heading.

My SQL "Select" section of Code is as follows:

SELECT tblSite.idsSiteID, [COLOR=Red][chrBuildingName] & " " & [chrAddress1] & ", " & [chrAddress2] & ", " & [chrTown] & ", " & [chrPostcode] AS Sitename[/COLOR], tblAddress.chrBuildingName, tblAddress.chrAddress1, tblAddress.chrAddress2, tblAddress.chrAddress3, tblAddress.chrTown, tblAddress.chrCounty, tblAddress.chrPostcode, tblAddress.chrCountry, tblSiteType.chrSiteType, tblSite.memSiteComments

Now, it's the the bit of code that I've coloured RED that I need to know how to transfer into VBA code, as it's trying to concatenate multiple fields into 1.

Now, I tried to make a start in VBA, but it only outputted the postcode for the "Sitename" field.


It works fine in normal SQL, but becuase I need a search criterion from the user, inputted into a form, I have to do it in VBA.


I hope the above makes sense and I look forward to replies.

if you need more information, then I'll be happy to try and give it.


Blessings,

Si

P.S. My db is working well and I am upto 1137 entries so far, all inputted manually!!! 600 & something to go

 

[Mr_Si]

As an update,

I've done it!

I continued searching the net for a solution, and only non-other but Allen Browne gave a solution on the Eggheadcafe forum.

I had to use double quotes for each field and thus the select section looked like:

BeginningSQL = "SELECT tblSite.idsSiteID, tblAddress.chrBuildingName & "" "" & tblAddress.chrAddress1 & "" "" & tblAddress.chrAddress2 & "", "" & tblAddress.chrTown & "", "" & tblAddress.chrPostcode AS Sitename, tblAddress.chrBuildingName, tblAddress.chrAddress1, tblAddress.chrAddress2, tblAddress.chrAddress3, tblAddress.chrTown, tblAddress.chrCounty, tblAddress.chrPostcode, tblAddress.chrCountry, tblSiteType.chrSiteType, tblSite.memSiteComments