Dlookup - blank record

  • Thread starter Thread starter learning_codes
  • Start date Start date
L

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
 
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.
 
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.



[email protected] said:
Hi,
Click to expand...
I create a simple lookup and it runs good.   I only get error "Null"
from the table.
Click to expand...
    Dim strProject As String
    Dim strSpecialCD As String
Click to expand...
    strDivMgr = DLookup("[Report]", "tblDivname", "[Report] Like ""*"
& DivMgr!Report.Value & "*""")
Click to expand...
    strProject = DLookup("[Project]", "tbl_List", "[Report] like ""*"
& DivMgr!Report.Value & "*""")
    strSpecialCD = DLookup("[SpecialCD]", "tbl_List", "[Report] like
""*" & DivMgr!Report.Value & "*""")
Click to expand...
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
Click to expand...
Your help would be much appreciated.
Thanks- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
Back
Top