Microsoft Access - Convert Dlookup Query to VBA coding

I'm a novice when working with VBA but I know it's suitable for my current project. I'm trying to convert a opertional query statement that works in SQL to function properly in a  VBA module: Sv_first: IIf(IsNull([First_name]),DLookUp("First_name","bos"," Group_ID='" & [Group_ID] & "' AND First_name"),""). I made an attempt to run it in VBA but it failed with errors: DoCmd.RunSQL "Update Bos SET ” & SV & ” = IIf(IsNull([First_name]),DLookUp("First_name","bos"," Group_ID='" & [Group_ID] & "' AND First_name"),"") FROM bos" When you get a chance, can you modify this code for me run in VBA? Thanks

The following is an example that shows the syntax of what you are trying to do.  Rather than using DLookup() or any other domain function which runs an independent query, it is better to use a join to the lookup table.  That gives the query engine the opportunity to optimize the query

I didn't code yours because it doesn't make any sense.what is SV?  Is that a variable that contains some column name and why is it a variable?  Why isn't the column name "Sv_first"?  Also, the DLookUP is referencing the same table you are trying to update.  You have no Where clause on the update query so all rows would be updated with the same value which doesn't sound like a good plan.  And finally, because you are using an IIf() rather than the Where clause, you have to update all records and so it looks like some will be set to the lookup value and others will be set to a ZLS, also not good.

Examine the sample I posted.  It joins a table to a query on two columns and selects only qty's that are null to be updated.

A helpful hint - assign SQL strings that you build in code to variables and reference the variable in the RunSQL statement.  I know it seems like an extra step but when you are debugging it is a big help because you can stop the code and examine the string you are trying to execute.  You can also print it to the debug window and copy it into the SQL editor to run it where you will get better error messages and syntax help.

Hi Pat,

This code works only works if the value I'm trying to capture for my null field is located in the first row.
I need to capture the value if its located anywhere within the group.

Thanks,
Charles

The join I used came directly from your DLookup() example.  If the join is not pulling the correct record then the DLookup() won't either.  Please post some example data that shows the problem.