adding custom string on certain field in query design 1

[davyre]

Hi,

I have this query using the Query Wizard to append records to a table called Orders_Status, the records are from other tables such as TblUnits, TblOrderUnits, TblVendor.
What I want is in a particular field in Orders_Status, a prefix to be added before the value of the record.

Example:

--------------------------------------------------------------------
Order     | Date        | Vendor      |  ETD      |  UDD           |
--------------------------------------------------------------------
123123    | 11/15/2012  | A           | 11/16/2012| 11/22/2012     |
123141    | 11/15/2012  | B           | 11/16/2012| 1/11/2013      |
--------------------------------------------------------------------

thats my current table. What I want is like this:

--------------------------------------------------------------------
Order     | Date        | Vendor      |  ETD      |  UDD           |
--------------------------------------------------------------------
[COLOR=#EF2929]Ord[/color]123123 | 11/15/2012  | A           | 11/16/2012| 11/22/2012     |
[COLOR=#EF2929]Ord[/color]123141 | 11/15/2012  | B           | 11/16/2012| 1/11/2013      |
--------------------------------------------------------------------

I want to add word "Ord" in front of the order number. How can I do that in Append Query Design?
Thanks!

 

[dhookom]

What is the SQL view of your append query that adds the records? You should be able to simple concatenate "Ord" to the Order field.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

INSERT INTO Orders_Status ( OrderDate, Supplier, ETD, UnitDeliveryDate, [Order], Unit )
SELECT TblOrder.OrderDate, TblVendor.VendorDesc, TblOrderUnit.ETD, TblOrderUnit.UnitDeliveryDate, TblOrder.OrderDate, TblUnits.BSUnitID
FROM TblUnits INNER JOIN ((TblOrder INNER JOIN ((TblVendor INNER JOIN TblParts ON TblVendor.[VendorID] = TblParts.[VendorID]) INNER JOIN TmpTblOrderUnitPartStatus ON TblParts.[PartID] = TmpTblOrderUnitPartStatus.[PartID]) ON TblOrder.[OrderID] = TmpTblOrderUnitPartStatus.[OrderID]) INNER JOIN TblOrderUnit ON TblOrder.[OrderID] = TblOrderUnit.[OrderID]) ON (TblUnits.UnitID = TmpTblOrderUnitPartStatus.UnitID) AND (TblUnits.UnitID = TblOrderUnit.UnitID);

in the field Order, I want to add "Ord" in front of the date so it will distinguish between Order field and OrderDate field. I don't know why but thats the requirement that my boss wants

 

[dhookom]

How do you get "Ord123123" from a date field? Is the field you want to append to a text field so that you can insert a value with text?

You might try something like:

INSERT INTO Orders_Status ( OrderDate, Supplier, ETD, UnitDeliveryDate, [Order], Unit )
SELECT "Ord" & TblOrder.OrderDate, TblVendor.VendorDesc, TblOrderUnit.ETD, 
TblOrderUnit.UnitDeliveryDate, TblOrder.OrderDate, TblUnits.BSUnitID
FROM TblUnits
 INNER JOIN ((TblOrder
 INNER JOIN ((TblVendor
 INNER JOIN TblParts ON TblVendor.[VendorID] = TblParts.[VendorID])
 INNER JOIN TmpTblOrderUnitPartStatus

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

yap that is a text field. Okay now I got it, thanks!