Hi,

I was having some problem using DbFunctionCall with the Replace() function in MS Access, but I kept getting "Undefined function 'Replace' in expression." error. I wasn't sure what I did wrong. Thanks in advance for all of your help~!

LLBLGen version: 2.0.0.0 Final Database: Access 2003

Query trying to achieve:

SELECT Code, SupplierName
FROM [_Product] INNER JOIN [Supplier] ON [_Product].[Supplier] = [Supplier].[Id]
WHERE REPLACE([_Product].Code, "-", "") LIKE "*somecode*"

LLBLGen code that I used:

DbFunctionCall fnReplace = new DbFunctionCall("Replace", new object[] {ProductFields.Code, "-", string.Empty});

IPredicateExpression mainCriteria = new PredicateExpression();

mainCriteria.Add(
     ProductFields.Code.SetExpression(fnReplace) % 
     string.Format("%{0}%", somecode.Replace("-", "")));

ResultsetFields rs = new ResultsetFields(2);
rs.DefineField(ProductFields.Code, 0);
rs.DefineField(SupplierFields.SupplierName, 1);

IRelationCollection relation = new RelationCollection();
relation.Add(ProductEntity.Relations.SupplierEntityUsingSupplier, JoinHint.Inner);

TypedListDao list = new TypedListDAO();
DataTable result = new DataTable();

list.GetMultiAsDataTable(rs, result, 0, null, mainCriteria, relations, true, null, null, 0, 0);

Code generated in the visualizer. I tried copy-and-paste them into Access with usual modification, (adding quotes, changing Entity name into table name, change % to *, etc) and it work fine, so I'm not sure how come it's still giving me error..=(

Visualizer result:

PredicateExpression mainCriteria:

(
    (
        REPLACE
        (
            ProductEntity.Code, @LOcdcab7832, @LOcdcab7bf3
        )
        LIKE @Code1
    )
)
@LOcdcab7832, -
@LOcdcab7bf3, 
@Code1, somecode

RelationCollection relation:

(
    Suppliers  INNER JOIN _Product  ON  Supplier.Id=_Product.Supplier
)

Thanks again for your help!

Sorry~

So it seems like the problem is I can't call Replace outside of Access.... Guess I'll try to find other ways without using Replace...=(