Dlookup - blank record

[learning_codes]

Hi,

I create a simple lookup and it runs good. I only get error "Null"
from the table.

Dim strProject As String
Dim strSpecialCD As String

strDivMgr = DLookup("[Report]", "tblDivname", "[Report] Like ""*"
& DivMgr!Report.Value & "*""")

strProject = DLookup("[Project]", "tbl_List", "[Report] like ""*"
& DivMgr!Report.Value & "*""")
strSpecialCD = DLookup("[SpecialCD]", "tbl_List", "[Report] like
""*" & DivMgr!Report.Value & "*""")

It works fine when I use Project and List with no problem but using
SpecialCD. I kept get error because of Null. Is there a way to
take either the value or null when using the Dlookup (strSpecialCD)


tbl_List

SpecialCD Project List
Living Room Carpet Group A
<null> Bedroom Group C
<null> Kitchen Goup D
Garage Car Group E

Your help would be much appreciated.
Thanks

 

[Brian]

You can dimension your variables as Variants to allow them to accept nulls

Alternatively, replace Null with an empty string using the NZ function:

strSpecialCD = NZ(DLookup("[SpecialCD]", "tbl_List", "[Report] like
""*" & DivMgr!Report.Value & "*"""),"")

This replaces the DLookup value with an empty string if the DLookup value is
null - before assigning it to the variable, so the variable can be a string
type.

 

[learning_codes]

You can dimension your variables as Variants to allow them to accept nulls

Alternatively, replace Null with an empty string using the NZ function:

    strSpecialCD = NZ(DLookup("[SpecialCD]", "tbl_List", "[Report] like
""*" & DivMgr!Report.Value & "*"""),"")

This replaces the DLookup value with an empty string if the DLookup valueis
null - before assigning it to the variable, so the variable can be a string
type.

Hi,

I create a simple lookup and it runs good.   I only get error "Null"
from the table.

    Dim strProject As String
    Dim strSpecialCD As String

    strDivMgr = DLookup("[Report]", "tblDivname", "[Report] Like ""*"
& DivMgr!Report.Value & "*""")

    strProject = DLookup("[Project]", "tbl_List", "[Report] like ""*"
& DivMgr!Report.Value & "*""")
    strSpecialCD = DLookup("[SpecialCD]", "tbl_List", "[Report] like
""*" & DivMgr!Report.Value & "*""")

It works fine when I use Project and List with no problem but using
SpecialCD.  I kept get error  because of Null.   Is there a way to
take either the value or null when using the Dlookup (strSpecialCD)

SpecialCD       Project        List
Living Room     Carpet         Group A
<null>              Bedroom     Group C
<null>              Kitchen       Goup  D
Garage            Car              Group E

Your help would be much appreciated.
Thanks- Hide quoted text -

- Show quoted text -

Thank you very much. It works great but I'm having a trouble with
single quoate. I tried to use like CH(34) but kept giving error
from "Where (tblExport.Report)=" & strDivName" All others are ok
exept ie: Bob's DVD

strDivName = """" & DivMgr!Report.Value & """"

DoCmd.RunSQL "SELECT tblExport.Project, tblExport.Report,
tblExport.Query, tblExport.Worksheet INTO tbl_Reports FROM tblExport
WHERE (tblExport.Report)=" & strDivName

Your help much appreciated.
Thanks