none
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


    Thursday, April 19, 2012 3:10 PM

All replies

  • 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

    Thursday, April 19, 2012 5:23 PM
  • 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


    Friday, April 20, 2012 8:43 AM
  • Hi MorrisStarling,

    did you try with eval so:

    getGBL_CK = eval(strresult)

    HTH Paolo

    Friday, April 20, 2012 9:41 AM
  • 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

    Friday, April 20, 2012 12:30 PM
  • 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

    Friday, April 20, 2012 1:14 PM
  • 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

    Friday, April 20, 2012 1:16 PM