Results 1 to 8 of 8
  1. #1
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162

    Dlookup not working.

    Hi there!
    In my database I have an input form "PaySupplier_Bank" by which I put data to the table "PaySupplier_Bank" for payment to supplier through bank. While I fill up the form it helps to have a look of Supplier Balance and Consignment balance. That's why I have added two text boxes "ConsBal" and "SupBal". In these two text boxes I have used Dlookup function to fetch those balances according to "Cons_ID>" and "Supplier_ID>" fields that are chosen on the form. The "ConsBal" text box gets data from the query "ConsWiseBalance" and the "SupBal" text box gets data from the query "SuppWiseBalance". However, "ConsBal" is working but "SupBal" is not working. A little help is earnestly needed.
    Furthermore my assumption was as the intermediate query "SuppWiseBillTotal" was made from "Cons" table where the field type was Combo box, I tried changing the query's "Supplier_ID>" field's Display control to text box, but still would not work. I am attaching the database.
    Attached Files Attached Files

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    control source for SupBal: Remove the spaces.
    Code:
    =DLookUp(" [SuppWiseBalance]![Balance Bank] "," [SuppWiseBalance] "," [SuppWiseBalance]![Supp_ID]=" & [Supplier_ID>])

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Advise not to use spaces nor punctuation/special characters in naming convention.
    This will bite you ITA someday.
    Of what significance are the < > characters?

    Also should not begin object names with number or character.

    Correct spelling: category

    Rather unusual for a 0 ID value. Why are ID primary keys not an autonumber field?

    Query/table name prefix not required.
    =DLookUp("[Balance Bank]","[SuppWiseBalance]","[Supp_ID]=" & [Supplier_ID>])
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by davegri View Post
    control source for SupBal: Remove the spaces.
    Thanks, it worked!

  5. #5
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by June7 View Post
    Advise not to use spaces nor punctuation/special characters in naming convention.
    This will bite you ITA someday.
    Of what significance are the < > characters?

    Rather unusual for a 0 ID value. Why are ID primary keys not an autonumber field?
    Thanks June7 for your advice. But my explanations are as follows:
    1. The characters ">" & "<" helps me identify the source type of the field's value. I used < tag to mean that the data is coming from a value list and > tag to mean that it is a foreign key.
    2. I did not use the auto-number field in order to save some bytes. As LongInteger uses 4 byte but if I use Byte or Integer type this would help me keep the file size small. And I am planning to add some VBA procedure to the form so that on BeforeUpdate or some other event it automatically saves an incremented ID field value. I'm not sure if I should start from 0 or 1. That's why I started from 0.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Have you documented significance of these suffix characters so a successor can understand why they were used?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by June7 View Post
    Have you documented significance of these suffix characters so a successor can understand why they were used?
    This is going to be my first ever database. Couple of challenges I face seems new to me. Some are solved through trial and error. Some additional adjustments are yet to be made. I guess I have to tell my successors verbally about it's mechanism.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I wrote a DB Administrators reference manual to leave for my successor.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. WTF? DLookup not working...
    By dniezby in forum Programming
    Replies: 17
    Last Post: 03-26-2017, 04:05 PM
  2. DLookup not working
    By Lou_Reed in forum Access
    Replies: 29
    Last Post: 03-13-2017, 01:25 PM
  3. DLookup not working,HELP!!
    By riocobre in forum Access
    Replies: 2
    Last Post: 03-12-2017, 11:36 AM
  4. Dlookup not working
    By shaunacol in forum Forms
    Replies: 8
    Last Post: 06-12-2015, 09:28 AM
  5. DLookup isn't working
    By cec in forum Access
    Replies: 7
    Last Post: 12-28-2012, 12:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums