Creating a reference Query, I think!

[robk5787]

Well, I am back once again with another issue. I have 5 fields to describe each error that is logged. The first field is the location of the problem, 2nd is the error message that it creates, 3rd is the date, 4th is the start time, and the 5th is the end time.

I have figured out a way to create a new column or field to total entire downtime due to the great people of this forum. My next bump in the road deals with the detail in the message field. The data in this field needs to be better indentified for the users. Basically the message contains information like Diverter 11, but it represents an actual bin location. For example, the message might say DIV-11, but I need to add a column and find a way where it matches the diverter name with a bin number. The message column wuold have the DIV #, and field or column 6 would have the bin number that corresponds to the DIV #. I thought about creating a table to reference to, but I can't figure out how to query so that it looks for the DIV # in the message and match it with the proper BIN number! My knowledge on VB is very limited. Any help is greatly appreciated.

 

[WayneRyan]

Robk,

You could make a public function FindDiverter and
pass it the ErrorMessage. It could parse the Error
Message and extract the Diverter number.

If the message is consistent, has "Div #" followed
by a three-digit number, then a field in your
query could do it.

NewField: IIf(InStr(1, ErrorMessage, "Div #") > 0, 
              Mid(ErrorMessage, InStr(1, ErrorMessage, "Div #") + 6, 3),
              Null)

The Public VBA function would allow you to better
parse the ErrorMessage and extract the Diverter number.

Better yet would to break the diverter number out into
its own field and forget all the work it requires
being stored like that.

Wayne