Share via

Access 2003 Query to search text

Anonymous
2011-09-24T17:34:10+00:00

Hello, I am looking for a solution to search for text in a field and have a query display a different line of text in a new field when the search term has been found.  This query is to be used to clean up an alarm report for viewing by operators.  An example of what the text field might look like is: "Wrote new value [0] to Rslinxopcserver/cp007/pump11hoastatus"  What I would like to show when this value is found is "Wrote new value [0] to Clearwell Pump 1 status”.   The second part of the text string is constant, meaning that “Rslinxopcserver/cp007/pumphoastatus” will repeat itself every time a process change is made to that pump and “Wrote new value [0]” is variable.

I have several records to search through which all have different strings of text but which follow the rules I have defined above and I am looking for advice on how to accomplish this in an efficient manner.

Thank you

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2011-09-24T21:48:27+00:00

    Not sure I understand, but try using a calculated field with an expression something like:

    NewField: IIf(textfield Like [Enter search string] & "*", "different line of text", Null)

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-10-05T04:57:38+00:00

    Thank you for the replies.  I was able to get this figured out through another thread.  What I ended up doing was omitting the variable part of the string which left the constant string.  I then defined my constants in another table so the end result of the formula would say something that made sense. 

    This is the formula I used to remove the "constant":

    RemoveText: Left([_ConditionName],InStr([_ConditionName],"(")-1) & Mid([_ConditionName],InStr([_ConditionName],")")+2)

    And this is the formula to remove the "variable":

    WroteValue: Val(Mid([_ConditionName],InStr([_ConditionName],"(")+1))

    Thank you again,

    0 comments
  2. Anonymous
    2011-09-26T04:16:51+00:00

    Hi Rick

    I'm with marshal on this one - not sure what you really want to achieve.

    so we need some more info . . you say a few records . .how many.

    the rules for text strings . .

    how many strings are you trying to match against how many records

    can the "constant:" bit of the string be isolated

    can you then link the 2 lists of text on a constant list

    maybe use a key that looks like Rslinxopcserver/cp007/pumphoastatus -

    which has a tranlator field like Clearwell Pump 1 status

    how many different strings are you searching for in any one opperation ?

    sounds like you are trying to process records created by some infernal machine . .that was nor designed to be DB friendly.

    can you supply  more examples of the records you are manipulating

    cheers

    PaulG

    0 comments