1

I'm currently setting up a label on Bartender. Currently I'm on Database setup screen and already have connected an excel sheet to it.

Inside an excel sheet, I have a column that is prefixed with a barcode number starting digits are '5011'. Inside an excel sheet I can select few records and drag down to generate more barcodes. e.g. if you have numbers going down a cell 1,2,3... and you select all three and drag down you have numbers following up with4,5,6,7,8 etc... same principle with my barcodes.

But.. the '5011' needs to start with a '0', so it becomes '05011'. If I format my barcodes with an '0' and drag down, cells do not update to what they should to. e.g. 01,02,03.. will continue like this: 03,03,03,03,03 repetitively.. even when refreshing the workbook with F9

I need an SQL query that just simply adds an extra '0' at the start for the barcode column and shows all results within the sheet.

I have managed to use this query to run on excel sheet:

`SELECT * FROM [Sheet1$];

just not sure how to add that extra 0 for column named Outer Barcodes

and have tried this:

UPDATE `Sheet1$` SET `Outer Barcode` = Concat('0',`Outer Barcode`)

but an error message comes up with: Undefined function 'Concat' in expression.

raina77ow
  • 103,633
  • 15
  • 192
  • 229
  • 1
    What is the underlying DBMS product you are using? –  Mar 25 '19 at 10:29
  • I have spoken to a support team on Bartender, I have followed their instuctions and given me this link: https://www.microsoft.com/en-us/download/details.aspx?id=13255 I believe this should say which DBMS product im using –  Mar 25 '19 at 10:30
  • Possible duplicate of [CONCAT equivalent in MS Access](https://stackoverflow.com/questions/20403870/concat-equivalent-in-ms-access) – raina77ow Mar 25 '19 at 10:32
  • As mentioned in that other question, try ... `SET [Outer Barcode] = '0' & [Outer Barcode]` – raina77ow Mar 25 '19 at 10:33
  • Error: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. this is what it comes up with –  Mar 25 '19 at 10:35

1 Answers1

0

The concat() operator in MS Access is &:

UPDATE [Sheet1$]
    SET [Outer Barcode] = '0' & [Outer Barcode];
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi Gordon! this is the error message:Error: Operation must use an updateable query. –  Mar 25 '19 at 10:37
  • select '0' & [Outer Barcode] from [Sheet1$] this is what I have used. Its working now. Thanks for the MS Access operator, helped me alot! –  Mar 25 '19 at 10:42