Interesting Access query question

[matthewwhaley]

I am trying to create a query that will update a new field with a
value from a table (tbl1) using another field in tb1 as a subject of a
"like" statement. Ex

Lets say I have 20 errors codes (groups of individual record error
codes) that come through a system (SysError) with record specific
information

SysError:
RVF error: [PreferredStock:17178U202] Tier2
product_new..PreferredStock row doesn't exists for this product so
preferred stock information (parValue, dividendRate, frequencyCode) is
missing

The above has many record specific variables that populate the string
(exreferredStock might be Bond, Equity,etc)

I created an errocode in one field in tbl1 called ErrCode and the
above looks like this
-RVF error: Tier2 product information is missing

For new records coming in I would like to use this criteria/ object of
a like statement to update the errorcode by record

Tbl1 field 1 errorstring =
-"RVF error: * Tier2 product * information * is missing"

In my query, I want to switch the syserror with the errorcode in tbl1.

This is where it falls down

Switch(a.syserror LIKE "&In(Select errorstring From
tbl1)&",tbl1.errorcode)

I only get blanks.

Question 1, can you nest a in(select )statement as an object of a
LIKE? If so, syntax would be greatly appreciated

Questions 2. If you cannot nest the above, any solutions on how to
group the errorcodes from the system?

Thanks in advance

 

[Lord Kelvan]

soooo

Switch(a.syserror LIKE "&In(Select errorstring From
tbl1)&",tbl1.errorcode)

that syntax looks completly off why are you using the concatinate
symbol & there or is that a typo

form that sounds of things you have

xxxxxxxx ###### xxxxxxxxxx as the syserror field
and the errorstring is the ###### you are trying to switch them where
the ###### is the same correct

first of all the switch function is essentionally a case statement and
does not 'switch' values arround it is basically a multilevel iif
statement

now i may be missunderstanding but are your rows of data like
id syserror errorstring
1 xxxxxxxx ###### xxxxxxxxxx ######

and you want
id syserror errorstring
1 ###### xxxxxxxx ###### xxxxxxxxxx

if this is what you want look below if not please explain what you ae
trying to do more clearly

the best method would be to write some vba code in a form to read the
values then update them back in each of the different fields

first you would need to create the query in a dao recordset in a form
and then take the syserror and the errorstring and place them in
seperate variables then write update recordset code to save each of
the variables back to the different fields

something like

dim db as database
dim rst as dao.recordset
dim strsyserror as string
dim strerrorstring as string
set db = currentdb()
set rst = openrecordset("select * from tbl1")
rst.bof
Do Until rst.EOF
strsyserror = rst!syserror
strerrorstring = rst!errorstring

rst.edit
rst!syserror = strsyserror
rst!errorstring = strerrorstring
rst.update
rst.movenext
loop

i havent tested that code but it should work

hope this helps

regards
kelvan