Results 1 to 7 of 7
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    Is it possible to add a dlookup function in the where clause of a query?

    Hi friends, is it possible to add a dlookup function in the where clause of a query? If yes, then how to do it?



    I tried the following, but the line is highlighted in red indicating a compilation error starting from "Cash_Out_ID_FK" in the Dlookup.

    Code:
    CurrentDb.Execute "UPDATE Cash_Out SET Cash_Out_Amount = " & Me!SupplyCharges.Value & " WHERE Cash_Out_ID_PK = DLookup("Cash_Out_ID_FK", "Supplies_Cash_Out", "SupplyID_FK=" & Me.supplyID_PK & "") "
    Thank you

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is supplyID_PK a autonumber type?

    Use apostrophes for the inner embedded quote marks for the DLookup arguments. Or double up quote marks for each of the apostrophes.

    " WHERE Cash_Out_ID_PK = DLookup('Cash_Out_ID_FK', 'Supplies_Cash_Out', 'SupplyID_FK=" & Me.supplyID_PK & "')"


    Why are you saving what appears to be aggregate calc?
    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.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    dim IDlookup as long
    IDlookup = nz(
    DLookup("Cash_Out_ID_FK", "Supplies_Cash_Out", "SupplyID_FK=" & Me.supplyID_PK),0)
    debug.print "IDlookup is " & IDlookup CurrentDb.Execute "UPDATE Cash_Out SET Cash_Out_Amount = " & Me!SupplyCharges.Value & " WHERE Cash_Out_ID_PK = " & IDlookup
    Try breaking it up like above.
    Also, Me.SupplyCharges must be a control on the form.
    IDlookup, SupplyID_FK and Cash_out_ID_PK must all be numeric.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It looks like the pk and fk values are in both tables, so why can't there just be a join on the two and delete from one table?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Thank you June 7, the quote marks made it work the way i was expecting.
    Is supplyID_PK a autonumber type?
    Yes supplyID_PK is autonumber
    Why are you saving what appears to be aggregate calc?

    Its not an aggregate calc that i am trying to achieve. Just want to insert/update the SupplyCharges value in the Cash_Out. Note that SupplyCharges isn't a calculated field, but entered by the user.

    Thank you for your time.

  6. #6
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Thank you Davegri.
    I haven't tried your approach yet, but i believe it will work since your are getting the value of Cash_Out_ID_FK before passing it to the WHERE clause of the query.
    However i still prefer to keep it simple as possible.

    Thank you

  7. #7
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Hi Micron,

    The structure of my tables is as follows:

    SuppliesTable --> Table that holds supplies data (SupplyCharges column is here)

    CashoutTable --> Table that records all cash expenses (including product purchase (supply) and charges related to purchase (Transport etc..). Note that all other expenses of the company are recorded in there. Meaning only one table for all cash out.)

    SupplyCashOutTable ---> Contains 3 fields only [ID, SupplyID, CashOutID]. To identify one or many cash-out records as being Supply related.

    So the logic i am pursuing is the following:

    When inserting a new payment for a supply:
    - A new record is created into CashoutTable with the amount of the payment and date as well.
    - A new record is created into SupplyCashOutTable, with CashOutID and SupplyID. Just to say that, this particular cash out is for the that supply.

    Tthe problem i had was with the supplyCharges. I didn't really know how to handle it, since it is also a cashout operation. Note that supply charges are threated separately from purchase.

    - So i finally decided to record it into SupplyCashOutTable. And the insert/update should happen when the user update the SupplyCharges field present on the form.

    Anyway, i have the code working now thanks to what June 7 proposed.

    Thank you for your time.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-29-2018, 11:07 AM
  2. Custom Function: how to add Where Clause
    By PeakH in forum Modules
    Replies: 3
    Last Post: 09-19-2017, 10:13 AM
  3. Agregation function - where clause
    By Khalil Handal in forum Access
    Replies: 3
    Last Post: 05-18-2017, 07:22 AM
  4. Replies: 15
    Last Post: 07-09-2015, 01:39 AM
  5. Replies: 4
    Last Post: 06-30-2014, 02:53 PM

Tags for this Thread

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