DMIN in a VBA SQL Statement (1 Viewer)

[Kiwiman]

Howzit

I am trying to convert an update query into vba. This update query includes the use of the DMIN function. The query works, but I can't get the vba method to use.

The query is:

UPDATE tblSOVStaging SET tblSOVStaging.SOVYear = DMin("[SOVYear]","tblSOV","[AuditNo]='" & tblSOVStaging!AuditNo & "'");

My current vba method is

strSQL = "UPDATE tblSOVStaging "
strSQL = strSQL & "SET tblSOVStaging.SOVYear = DMin(" & Qte & [SOVYear] & Qte & "," & Qte & tblSOV & Qte & "," & Qte & [AuditNo] & Qte & "= tblSOVStaging!AuditNo );"
CurrentDb.Execute strSQL

QTE is a Public Const """"

I have also tried the chr(34).

The error message I am getting is...

Microsoft Access can not find the field 'l' referred to in your expression

The fields and the tables are correct, but I can't seem to get the syntax corect.

Any help would be appreciated.

 

[kenwarthen]

I've had similar problems with SQL statements. For me it's usually a syntax issue that my bad eyes missed. particularly with quote marks nested within double quote marks. I like to put a Debug.Print statement after the creation of any SQL statement in my code. That helps me spot syntax errors. In your sample it looks to me like you might be missing a space following the comma in the two "," sections of your statement.

strSQL = strSQL & _
"SET tblSOVStaging.SOVYear = DMin(" & Qte & [SOVYear] & Qte & _
"," & Qte & tblSOV & Qte & _
"," & Qte & [AuditNo] & Qte & _
"= tblSOVStaging!AuditNo );"

Ken

 

[LPurvis]

The spaces after parameters aren't required - just for we humans to better scan the expression. ;-)

It looks to me like you might not have Option Explicit set in your module header?
Otherwise it should be identifying missing variables for SOVYear, tblSOV and AuditNo - unless you've identified those as variables/constants containing the exact same text as their name.
Otherwise the VBA SQL needs to be...

strSQL = strSQL & "SET tblSOVStaging.SOVYear=DMin(" & Qte & "[SOVYear]" & Qte & "," & Qte & "tblSOV" & Qte & "," & Qte & "[AuditNo] = " & Qte & " & tblSOVStaging.AuditNo)"
Notice I've changed tblSOVStaging!AuditNo to tblSOVStaging.AuditNo as I hate the use of Bangs in SQL data objects (refering to Access expressions within a query string sure - fair enough ;-)
Also delimited the field and table names.
Personally - I think this looks harder to read than my normal preference.

strSQL = strSQL & "SET tblSOVStaging.SOVYear=DMin(""[SOVYear]"",""tblSOV"",""[AuditNo] = "" & tblSOVStaging.AuditNo)"

Or switch to single quotes for complete triviality

strSQL = strSQL & "SET tblSOVStaging.SOVYear=DMin('[SOVYear]','tblSOV','[AuditNo] = ' & tblSOVStaging.AuditNo)"

Cheers.

 

[Kiwiman]

Howzit

Thanks guys for the replies.

I thought I had tried the single quotes as I also find it easier to read without the QTE etc.

I only put the QTE in there to try ans solve my problem...

I'll give them another go with the doubles, singles etc to see if I make any headway. Fingers crossed ...

 

[LPurvis]

All three of the above should be fine.
(Though it's just aircode - hard to be sure in this little posting windows :-s)

Having tried the single quote method - you might have just had a misplaced quote and ampersand (there was one out of place in the criteria parameter).

Cheers.

 

[Kiwiman]

Thanks Leigh

Job done - double quotes worked a treat.

Final code re this that I am using:

strSQL = "Update tblSOVStaging "
strSQL = strSQL & "SET tblSOVStaging.SOVYear=DMin(""[SOVYear]"",""tblSOV"",""[AuditNo] = '"" & [tblSOVStaging]![AuditNo] & ""'"")"

Which gives me this - in the immediate window

Update tblSOVStaging SET tblSOVStaging.SOVYear=DMin("[SOVYear]","tblSOV","[AuditNo] = '" & [tblSOVStaging]![AuditNo] & "'")

Thanks once again.