here is a setup: My setup table has 4 fields:
and here are the values:
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:
whereas I'm expecting:
Any help or direction?
- Edited by MorrisStarling Thursday, April 19, 2012 3:10 PM
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],
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?
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
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")
- Set rsData = Nothing
- getGBL_CK = strResult
- End Function
and to make it complete, here is a row from setup table for this particular PP:
'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!
- Edited by MorrisStarling Friday, April 20, 2012 8:46 AM
Yes I tried.
Eval(strResult) worked fine as long as strresult was equal to:
but as soon as I added the field name:
I was getting the error:
Run-time error '2482':
Microsoft Office Access can't find the name 'GBL_CODE' you entered in the expression
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?
I don't really understand your situation I think.
Are you putting formulas in your table fields?
Are you using Access 2010?
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.