Solved Use of DLookup in SQL expressions? (1 Viewer)

[Teggun]

Hi guys, I'm having a trouble when using DLOOKUP function and combine it together in the SELECT clause of an SQL expression, i wonder if this is actually possible to be done, and if so, I would appreciate help from you, because I can't come up with the correct syntax to make this work.

I'm doing it this way, because some of the registers in the table "tblBobins" do not have any "IDMachine" assigned, and when opening a recordset using INNER JOIN to bring in the other table, it excludes the registers that do not have it.

Dim SQLB as String

SQLB = "SELECT tblBobins.IDBobin, IIF(NOT ISNULL([tblBobins]![IDMachine_FK]), DLookup('MachineNumber', 'tblMachines', 'IDMachine = [tblBobins]![IDMachine_FK]'), '') AS [Machine] FROM tblBobins"

Set rsBobins = CurrentDb.OpenRecordset(SQLB, dbOpenDynaset)

If there is any better way to achieve so, I'll be happy to learn from you guys. I'm still learning about VBA.

Thanks in advance for your help.

 

[Isaac]

Could you change it to a left join?

 

[pbaldy]

You would typically do it by joining the two tables in the query, and it would be far more efficient. In design view of a query, add both tables and join them on the appropriate fields. Once you get the query working you can copy the SQL to your code.

 

[Teggun]

Could you change it to a left join?

There you go, thanks a lot, didn't know about this feature.

 

[pbaldy]

Apparently I'm blind and didn't see the second paragraph. Luckily Isaac did.

 

[Isaac]

There you go, thanks a lot, didn't know about this feature.

Studying the concepts of joins is highly recommended....right up there with normalization and entity/relational modeling. Sounds a lot harder than it is, but worth at least spending an hour reading each topic.

Apparently I'm blind and didn't see the second paragraph. Luckily Isaac did.

I've had so many blind moments this week I can't count them any more. : )