Quick help with SQL command in VB

[psuaudi]

I am having problems with using the IIf/dlookup command in my recordsource query. Is it possible to use this within VB? it works as a regular query. I tried to fix the quotes, etc, but it didn't seem to fix the problem.

me.form.recordsource = "SELECT [Table].[Number], [Table].[SubNumber], " & _
        "IIf(nz(DLookUp('[ID]','Revisions','[Number] = '" & _
        "[Table]![Number] & "''),False)<>False,True,False) AS Test " & _
        ....and so on

Thanks!

[MMcCarthy]

Firstly, you are trying to impose a boolean value of False into the ID field if null value exists. You can't do this.

If [Table]![Number] is a number datatype then remove the quotes and change the code as follows:

me.recordsource = "SELECT [Table].[Number], [Table].[SubNumber], IIf(nz(DLookUp('[ID]','Revisions','[Number]=" & [Table]![Number]),0) <>0,True,False) AS Test " & _ ....and so on
 

However, if it is a string datatype you will need the following:

me.recordsource = "SELECT [Table].[Number], [Table].[SubNumber], IIf(nz(DLookUp('[ID]','Revisions','[Number]='" & [Table]![Number] & "'')," ")<>" ",True,False) AS Test " & _ ....and so on
 

[MMcCarthy]

BTW, you don't need to use ...

Me.Form.Recordsource=

Me. designates the form so you only need ...

Me.Recordsource=

[NeoPa]

I think you may be looking for something more like this, if I'm right in suspecting that you're trying to build up the DLookup function call from within the SQL code.
You had a single-single-quote after ('[Number] = ) rather than a double-single-quote which would have been required.

Me.RecordSource =
    "SELECT [Table].[Number], [Table].[SubNumber], " & _
    "IIf(Nz(DLookUp('[ID]','Revisions','[Number] = " & _
    "'' & [Table]![Number] & ''),False)<>False,True,False) AS Test " & _
    ....and so on

A better way would probably be to link the tables with a LEFT JOIN (Table & Revisions).
Let me know if I'm off track, but I'm guessing this is what you were trying to do.