vba SQL syntax help

[supmktg]

I'm having trouble building an update query in vba.

I need an Inner join between 2 tables where the joined field in the 2nd table is formatted by the function fncFormat. The table name and field name of the 2nd table are variables selected on the form that runs the query.

Here is my code:

Dim strTbl2 as string
Dim strFld2 as string
Dim sql as string

strTbl = me.cboTbl
strFld = me.cboFld

sql = "UPDATE [" & strTlb2 & "] INNER JOIN Table1 "
sql = sql & "ON fncFormat([" & strTlb2 & "].[" & strFld2 & "]) = Table1.JoinFld "
sql = sql & "SET Table1.UpdateFld = True, Table1.Desc = 'New' "
sql = sql & "Where Table1.UpdateFld <> True;"
currentdb.execute(sql)

The problem I'm having is including the fncFormat in the join. The following sql works without the fncFormat. (and the fncFormat function works everywhere else)

sql = "UPDATE [" & strTlb2 & "] INNER JOIN Table1 ON [" & strTlb2 & "].[" & strFld2 & "] = Table1.JoinFld "
sql = sql & "SET Table1.UpdateFld = True, Table1.Desc = 'New' "
sql = sql & "Where Table1.UpdateFld <> True;"
currentdb.execute(sql)

Any help would be appreciated!

Thanks,
Sup

 

[AccessMSSQL]

What is your fnFormat doing? and what error do you get?

 

[VilaRestal]

I wonder if the fnc makes the view uneditable.

Try:

sql = "UPDATE Table1 "
sql = sql & "SET UpdateFld = True, Desc = 'New' "
sql = sql & "WHERE UpdateFld <> True "
sql = sql & "AND EXISTS (SELECT * FROM [" & strTlb2 & "] WHERE "
sql = sql & "fncFormat([" & strTlb2 & "].[" & strFld2 & "]) = Table1.JoinFld);"

 

[supmktg]

The fncFormat function removes some characters and makes the result a string. My initial error was datatype mismatch, which didn't make sense because Table1.joinfield is definitely a string. Then I tried altering the sql quotes and bracketing and got syntax errors.

VilaRestal your workaround "Exists" code actually worked, but it was exceptionally slow. When I ran it in the QBE, I noticed a bunch of errors in the result.

It turns out that strTbl2.strFld2 had some unexpected null or zero length strings which was causing the datatype mismatch. I added the following function to deal with null and zero length and now my sql works:

Function nzlTxt(varInput As Variant, varOutput As Variant) As String
If IsNull(varInput) Or Len(varInput) = 0 Then
nzlTxt = varOutput
Else
nzlTxt = varInput
End If
End Function

This new sql now works:

sql = "UPDATE [" & strTlb2 & "] INNER JOIN Table1 "
sql = sql & "ON fncFormat(nzlTxt([" & strTlb2 & "].[" & strFld2 & "],0)) = Table1.JoinFld "
sql = sql & "SET Table1.UpdateFld = True, Table1.Desc = 'New' "
sql = sql & "Where Table1.UpdateFld <> True;"

Thanks to both of you for your help!

Sup