Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30

    Question Unanswered: Blank DLookup...

    Hi There,

    I am currently building a db in Access 2000 and am having some problems getting data to show using a DLookup.

    Firstly I will explain what I am trying to do. I have a table that stores Agents details which looks like this:

    AgentID (PK), AgentName, StreetAddress, Suburb, Postcode

    Now, I have a text box in one of my reports that displays the name of an agent (it's also stored in my Bookings table as "OrderReceivedFrom" which is a string). By using a Dlookup which displays fine. The name of this text box is called "Dealer".

    So what I'm trying to do is show the StreetAddress of an Agent in another text box by using a DLookup. The controlsource of this text box currently looks like this:

    =DLookup("StreetAddress", "Agents", "[AgentName] = '" & [Dealer] & "'")

    The problem I'm having is when the report is run nothing displays in the address text box, not even an error like it's Null. I tried declaring a String variable as the above DLookup and it's giving me an "Invalid use of null" error but I don't understand why.

    Any help is muchly appreciated on this one as it's driving me nuts!

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I wouldn't use =DLookup("StreetAddress", "Agents", "[AgentName] = '" & [Dealer] & "'").

    I would use =DLookup("StreetAddress", "Agents", "[AgentName] = '" & Me.Dealer.Value & "'").

    The reason you're getting invalid use of null is because Dlookup returns a null if no value can be found using your criteria, and you're assigning this null to a textbox which is probably expecting a string.

    You can circumvent this by using Nz()

    Code:
    Nz(value, [valueIfNull])
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30
    Thanks for that HomerBoo, will give it a try when I get home from work and let you know how it all goes.

    Cheers.

  4. #4
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30
    Sorted! :-)

    Realised that it was the client changing the agents name manually from "Hire Service" to "Hire Services" which threw it out.....just so happened that this was the customer I was testing it out on!! always the way...

    Anywayz, thanks very much for your help on this one.

    Regards,
    Chillylounge.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •