Seems you're using VB/VBA instead of VB.NET...
If i'm right, than you're in troulble, because
MS Access engine does NOT support regular expressions. There's few alternatives:
1. You can use
string functions[
^] to split string into parts, for example:
LEFT()[
^]
INSTR()[
^]
RIGHT()[
^]
2. You can write custom function in MS Access database (using VBA), then use it in your query. See:
Hack 54. Use Regular Expressions in Access Queries[
^]
Regular Expressions in MS Access[
^]
Note: a code which refer to custom function should be called and executed on a database level!
But(!) it would be much easier to use VB.NET...
[EDIT]
As to the 1. part of my answer. Here's a way how you can achieve that:
SELECT t.itemnumber, LEFT(t.itemnumber, IIF(InStr(1, t.itemnumber, "*", 1) =0, InStr(1, t.itemnumber, "-", 1), InStr(1, t.itemnumber, "*", 1))) As itemalphabet, RIGHT(t.itemnumber, LEN(t.itemnumber) - IIF(InStr(1, t.itemnumber, "*", 1) =0, InStr(1, t.itemnumber, "-", 1), InStr(1, t.itemnumber, "*", 1))) As itemnumeric
FROM test As t
WHERE t.itemnumber LIKE "*[0-9]";