How do I have Access automatically display what type of device something is, based on what's input in another field on the same table?

Question

I'm currently working on building a radio inventory database for a charity I work with. I have a table called "Radio Info" where I am putting in the info about the individual radios and related accessories. Right now, I have just two different fields in the table. One is Barcode, and the other is Type. For Barcode, it will have one of three options. A radio has a barcode with the format AXXXX, where each X can be a 0 - 9. Batteries have BAXXX, and headsets have CXXXX. What I'm trying to accomplish is, based on whatever barcode I put in, Access can look at the value, and automatically determine what type of device it is. So if I put in A0001, A0150, or A1039, it will automatically fill the type field with Radio. If I put in B004, B025, or B439, it will fill it in with Battery, and so on. There may be more options added into the Type field later, but they will always have a different starting letter(s) than any of the other options. I'm pretty sure I need to use a SWITCH statement to pull this off, but all of my attempts have resulted in an error.

I've tried to google how to use the switch expression, and attempted to copy/paste and modify it to work with my data, but it results in various errors depending on where I put different things like quotes. I'm pretty sure my issues is how I'm referencing the Barcode field, but I have no clue what should be done to reference the field. Here is my latest attempt, and it returns the error "The expression you entered contains invalid syntax.

=Switch(Barcode=A*,"Radio",[Barcode=BA*,"Battery"],[Barcode=C*,"Headset"])

Any help with this would be greatly appreciated.

Answer 1

You could use your Switch() approach in a query if you quote the pattern strings, use Like instead of =, and discard the square brackets.

SELECT
    Barcode,
    Switch(
        Barcode Like 'A*', 'Radio',
        Barcode Like 'BA*', 'Battery',
        Barcode Like 'C*', 'Headset'
        ) AS [Type]
FROM [Radio Info];

But if you were hoping to use the Switch() expression to define a field in table design, I don't believe it can work there.

If you don't want to use a query, then consider building a form based on your table to manage the table's data.

Answer 2

Asc will return the ASCII code of the first character which is 65 for "A".

Then you can use Choose for a more obtuse syntax:

=Choose(Asc([Barcode])-64,"Radio","Battery","Headset","Speaker")