Function ParseString

[NOEL71]

hello all!



I am trying to understand the programming behind an expression in access..

I trying to build a Iif function in a query if the value of the field have one or any of the value will define the definition from the table that I have created.

If the value is A then it would spell the defintion to "Apple"

if the value is A, B then it would spell the defintion "Apple, Grapes"

If the value is B, C then it would spell the defintion "Grapes, Cherry"

If the value is A, B, C then it would spell the definition "Apple, Grapes, Cherry"


I have created a table with a list definition that would corresponde with the value. Thanks for the help.....

[JoeM]

Work from the most restrictive (A,B,C) first, down to the least restrictive.
But note, you can only nest up to 7 levels with IIF.

Quite frankly, I would probably create a Custom User Defined Function to do something like this that has a lot of conditions. It is much easier to maintain.
See this link here: http://www.fontstuff.com/vba/vbatut01.htm, and note how they use Select Case in one of the examples instead of IF to handle a bunch of conditions. That is how I would recommend handling it.

[lfpm062010]

Replace function might work as well.

http://www.techonthenet.com/access/f...ng/replace.php

The trick is not to replace what you have already done.

[June7]

This looks like saving multiple values as a text string into a single field. Usually a bad idea.

If you have the table of values and associated definitions, why do you need IIf? Join tables in query.

[rpeare]

I agree with june7, looks like a multi value field he's trying to re-parse to get the original items.

[NOEL71]

I'm sorry i know a little bit about access, i have tried join the table. tbl_main and tbl_codes

tbl_codes has two fields codes and description

codes description

AB DENTAL
MD MEDICAL
LI LEGAL
LR LEGAL
TP TEMPORARY
PG PREGNANT
AD ADMINSTRATIVE
LO NON AVAIL

The problem I'm having is everyweek the codes change with multiple codes, sometime one to four codes: MD, TP, PG, LO. Unless I know the combination I cant join them. I would like to have them define it on a query If one codes meets the criteria it will show the description. Thanks for the help.....

[June7]

So you have 2-letter codes in a lookup table but the weekly data has multiple codes as a string in a single field. If this were a multi-value field could actually be dealt with rather easily. But since this is just a string of characters, not so easy. If the number of codes were always the same, might be able to handle without VBA but the inconsistency adds complication. Options I see for manipulating the string require code. Code will parse the string and then do something with the elements.

Options:

1. save into a normalized table structure

2. function called in query will return a string of the equivalent descriptions - example code (not tested):

Function ParseString(strInput)
Dim strAry As Variant, i As Integer, strOutput As String
strAry = Split(strInput, ",")
For i = 0 To UBound(strAry) - 1
    strOutput = strOutput & DLookup("Description", "tblCodes", "Codes='" & strAry(i) & "'") & ", "
Next
End Function

Calling the function from query:
SELECT *, ParseString([MultiCodeField]) AS Description FROM dataInputTable;

[NOEL71]

So you have 2-letter codes in a lookup table but the weekly data has multiple codes as a string in a single field. If this were a multi-value field could actually be dealt with rather easily. But since this is just a string of characters, not so easy. If the number of codes were always the same, might be able to handle without VBA but the inconsistency adds complication. Options I see for manipulating the string require code. Code will parse the string and then do something with the elements.

Options:

1. save into a normalized table structure

2. function called in query will return a string of the equivalent descriptions - example code (not tested):

Function ParseString(strInput)
Dim strAry As Variant, i As Integer, strOutput As String
strAry = Split(strInput, ",")
For i = 0 To UBound(strAry) - 1
    strOutput = strOutput & DLookup("Description", "tblCodes", "Codes='" & strAry(i) & "'") & ", "
Next
End Function

Calling the function from query:
SELECT *, ParseString([MultiCodeField]) AS Description FROM dataInputTable;

I sorry June7 is this VBA code or am nesting this as a function in my query? Sorry for my confusion....

[June7]

It is VBA code in a general module and then the function can be called from query. Or the function can be behind a form and call the function from a textbox ControlSource.

[NOEL71]

Below is database. Again thanks!!!!

[June7]

Sorry, the function I suggested wasn't quite complete. I was also in error about the UBound(strAry)-1.

Also, your field names have space so must be enclosed in [].
DLookup("Definition", "tblMRC_Code", "[MRC Rsn]='" & strAry(i) & "'")
This is main reason to avoid spaces and special characters/punctuation (underscore is exception) in naming convention. They are just annoying.

However, even the corrected function is not working. In first iteration of the loop the DLookup returns definition and then returns only Null for subsequent iterations in the loop. This is frustrating. I've never actually tried a DLookup in a function like this and this is unexpected. The result of this can be seen by calling the function from textbox:

=ParseString([MRC RSN])

Here is the code that runs without errors but just doesn't produce the desired output:

Public Function ParseString(strInput)
Dim strAry As Variant, i As Integer, strOutput As String
strAry = Split(strInput, ",")
For i = 0 To UBound(strAry)
    strOutput = strOutput & DLookup("Definition", "tblMRC_Code", "[MRC Rsn]='" & strAry(i) & "'") & ","
Next
ParseString = strOutput
End Function

Not sure how to fix. Still thinking about it.

The ultimate final solution might require normalizing the imported data into a related table.

[June7]

Okay, thought I would go nuts. Issue is the MRC Rsn string has space after each comma. Change function to include a space after comma:

strAry = Split(strInput, ", ")

or in case inclusion of space in the string is inconsistent:

strAry = Split(Replace(strInput, " ", ""), ",")

Consistency in string structure is critical to this. If inclusion of comma is not reliable then that will change whole approach.

And if you want to eliminate the trailing comma:

ParseString = Left(strOutput, Len(strOutput) - 1)

A better name for the function might be GetDefinition instead of ParseString.

[NOEL71]

June7

Thanks! for the help. It works perfect, the output is what I was looking for.