Concatenating and then exporting to excel

[canfish]

Hi all,



I am running into a specific problem, namely:

I am exporting many of my queries into excel tables. I have information entered into separate fields for the address of each facility (I am basing the database around facilities), but I want to export into excel so that the address is all in one field. How do I draw information from each field all into one field in my query, so that I can export it and have a nice simple "address" field?

Thank you very much for your help.

[RuralGuy]

Just concatenate them in the query As FieldName.

[canfish]

Hi ruralguy,

Thanks for the reply! Unfortunately, I'm not sure quite how to follow your instructions. What does it mean to concatenate them "in the query As Fieldname"? I've tried using the expression builder, but it confuses me. What am I supposed to do, exactly?

Thanks!

canfish

[RuralGuy]

Switch the Query Builder to SQL mode and copy it to a post along with what fields you want concatenated.

[canfish]

SELECT tblOffices.Ministry, tblOffices.OfficeName, tblOffices.CostCentre, tblOffices.AddressLine1, tblOffices.AddressLine2, tblOffices.City, tblOffices.PostalCode, tblOffices.SiteManager, tblOffices.SiteManagerPhone, tblOffices.SiteManagerEmail, tblEquipment.SpecialShippingInstructions, tblEmployees.FirstName, tblEmployees.LastName, tblEmployees.Email, tblEquipment.EquipmentType, tblEquipment.EquipmentMake, tblEquipment.EquipmentManufacturer, tblEquipment.EquipmentModel, tblEquipment.EquipmentSerialNumber, tblEquipment.AssetTagNumber, tblEquipment.ComputerName, tblEquipment.LeasedOwnedDesignation, tblEquipment.Lessor, tblEquipment.LeaseSupplementNumber, tblEquipment.LeaseEndDate, tblEquipment.HardwareIntent, tblEquipment.AdditionalInformation
FROM (tblOffices INNER JOIN (tblEquipment INNER JOIN tblAssetts ON tblEquipment.PKEquipmentItemID = tblAssetts.FKEquipmentItemID) ON tblOffices.PKOfficeID = tblEquipment.FKOfficeID) INNER JOIN (tblEmployees INNER JOIN tblOfficeEmployees ON tblEmployees.PKEmployeeID = tblOfficeEmployees.FKEmployeeID) ON (tblEmployees.PKEmployeeID = tblAssetts.FKEmployeeID) AND (tblOffices.PKOfficeID = tblOfficeEmployees.FKOfficeID);

I need the following to be concatenated:

tblOffices.AddressLine1
tblOffices.AddressLine2
tblOffices.City
tblOffices.PostalCode


Thank you for your help!

[RuralGuy]

Try this:

SELECT tblOffices.Ministry, tblOffices.OfficeName, tblOffices.CostCentre, tblOffices.AddressLine1 & vbCrLf &  tblOffices.AddressLine2 & vbCrLf & tblOffices.City & " " & tblOffices.PostalCode As FullAddress, tblOffices.SiteManager, tblOffices.SiteManagerPhone, tblOffices.SiteManagerEmail, tblEquipment.SpecialShippingInstructions, tblEmployees.FirstName, tblEmployees.LastName, tblEmployees.Email, tblEquipment.EquipmentType, tblEquipment.EquipmentMake, tblEquipment.EquipmentManufacturer, tblEquipment.EquipmentModel, tblEquipment.EquipmentSerialNumber, tblEquipment.AssetTagNumber, tblEquipment.ComputerName, tblEquipment.LeasedOwnedDesignation, tblEquipment.Lessor, tblEquipment.LeaseSupplementNumber, tblEquipment.LeaseEndDate, tblEquipment.HardwareIntent, tblEquipment.AdditionalInformation
FROM (tblOffices INNER JOIN (tblEquipment INNER JOIN tblAssetts ON tblEquipment.PKEquipmentItemID = tblAssetts.FKEquipmentItemID) ON tblOffices.PKOfficeID = tblEquipment.FKOfficeID) INNER JOIN (tblEmployees INNER JOIN tblOfficeEmployees ON tblEmployees.PKEmployeeID = tblOfficeEmployees.FKEmployeeID) ON (tblEmployees.PKEmployeeID = tblAssetts.FKEmployeeID) AND (tblOffices.PKOfficeID = tblOfficeEmployees.FKOfficeID);

If vbCrLf does not work in the query then use Chr(13) & Chr(11) instead.

[canfish]

Got it working. Thanks!