My function returns the string containing field names rather than underlying values.

Question

Hi all,

here is a setup: My setup table has 4 fields:
1) DW_Extract_Name
2) DW_Extract_ID
3) PP_mask
4) GBL_CONCAT_KEY

and here are the values:

1) DW_APAC_GCG_INDIA
2) 12
3) APAC~India~GCG~(OASIS|OASIS_NRI)
4) 'APAC~GCG~356~'&format([GBL_CODE],
"0000000000")&'~'&[GBL_FILTER_FIELD_2_value]&'~'&[GBL_FILTER_FIELD_3_VALUE]

The idea is that I'm using a string like e.g. "APAC~India~GCG~OASIS"
and RegExp to match it to a PP_mask in the setup table.

that part works fine, so my function 'getGBL_CONCAT_KEY" returns the
correct GBL_CONCAT_KEY value from the setup table.

The problem is it delivers the string literally, without performing
the Format() function, or subsitituting the field names like
[GBL_FILTER_FIELD_2_VALUE] with its underlying value.

So currently it returns:
'APAC~GCG~356~'&format([GBL_CODE],
"0000000000")&'~'&[GBL_FILTER_FIELD_2_value]&'~'&[GBL_FILTER_FIELD_3_VALUE]

whereas I'm expecting:

'APAC~GCG~356~0000000053~BR~4'

Any help or direction?

Thanks,
Morris

Answer 1

If

'APAC~GCG~356~'&format([GBL_CODE],
"0000000000")&'~'&[GBL_FILTER_FIELD_2_value]&'~'&[GBL_FILTER_FIELD_3_VALUE]

is the value being retrieved from your setup table -- this value is being treated as one string constant.  If this is the case -- you need to separate 'APAC~GCG~356~' from &format([GBL_CODE],
"0000000000")&'~'&[GBL_FILTER_FIELD_2_value]&'~'&[GBL_FILTER_FIELD_3_VALUE]

Is your function being called in a sql statement?  Whether or not -- create a test sub to call your function and add a breakpoint where the function gets called -- hold the mouse over the var / parameter  to see what value is being passed to the function.   Do a debug.print within the function to see what value your passed in parameter contains.  Could you show how your function is being called?  like in a sql statement or a VBA statement?

---

Rich P

Answer 2

Yes, the function is saved in the Module (getGBL_CK)

I invoke it from a simple SQL query via normal Access functionality:

SELECT [PP], getGBL_CK("APAC","GCG","APAC~INDIA~GCG~OASIS") AS Expr1
FROM Archer_CR_Report;

The getGBL_CK function is:

• Public Function getGBL_CK(strRegion, strSector, strPP_routing As String) As String
• strSQL = "select * from [setup_GBL_CONCAT_KEY] WHERE [GBL_REGION] = '" & strRegion & "' AND [GBL_BUSINESS_SECTOR] LIKE '" & strSector & "';"
  rsData.Open strSQL, CurrentProject.Connection, adOpenDynamic
• Do While Not rsData.EOF
      strBuffer = rsData("PP_mask")
      blMatched = fncRegexpMatch(strBuffer, strPP_routing)
     
      If blMatched = True Then
          strResult = rsData("GBL_CONCAT_KEY")
          Exit Do
      End If
      rsData.MoveNext
  Loop
• rsData.Close
• Set rsData = Nothing
• getGBL_CK = strResult
• End Function

and to make it complete, here is a row from setup table for this particular PP:

GBL_REGION	GBL_BUSINESS_SECTOR	PP_mask	GBL_CONCAT_KEY
APAC	GCG	APAC~India~GCG~(OASIS|OASIS_NRI)	'APAC~GCG~356~' & format([GBL_CODE], "0000000000") & '~' & [GBL_FILTER_FIELD_2_value] & '~' & [GBL_FILTER_FIELD_3_VALUE]&'~'&[GBL_ACCT_NATURE]

I agree that to me it looks like that since the function returns the value of 'String' type, Access just displays it, instead of evaluating the value while executing the query.

To be honest I have since managed to achieve my result by using a time consuming workaround - I'm analysing the string before returning the value, stripping out the field names ([GBL_CODE] and so on) using more regExp, looking up their values in a separate Recordset and returning the value, rather than the field name.. but I've got a feeling that's like reinventing a wheel and there must be a way of forcing Access to treat the value returned by the function as SQL syntax...

It will bug me, so still - any help appreciated!

Morris

Answer 3

Hi MorrisStarling,

did you try with eval so:

getGBL_CK = eval(strresult)

HTH Paolo

Answer 4

Yes I tried.

Eval(strResult) worked fine as long as strresult was equal to:

'APAC~GCG~356~'&format("26", "0000000000")

but as soon as I added the field name:

'APAC~GCG~356~'&format([GBL_CODE], "0000000000")

I was getting the error:

Run-time error '2482':

Microsoft Office Access can't find the name 'GBL_CODE' you entered in the expression

Answer 5

Where you have the field names...&[GBL_FILTER_FIELD_2_value]&'~'&[GBL_FILTER_FIELD_3_VALUE]

How are you telling it to look at the Table the fields exit in?

i.e. if the Table name were My_Data then would this work?

&My_Data.[GBL_FILTER_FIELD_2_value]&'~'&My_Data.[GBL_FILTER_FIELD_3_VALUE]

or this?

&My_Data![GBL_FILTER_FIELD_2_value]&'~'&My_Data![GBL_FILTER_FIELD_3_VALUE]

I don't really understand your situation I think.

Are you putting formulas in your table fields?

Are you using Access 2010?

---

Chris Ward

Answer 6

Well, to reference a field in a table with the eval you have to insert it in a form and refer to it via the field in the form.

let's say you have a form called test in which you insert a field called GBL_CODE and with controlsource GBL_CODE you strresult become

'APAC~GCG~356~' & format(forms!test.form![GBL_CODE], "0000000000")

if you eval this string you'll have your desired result.

HTH Paolo