Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    June
    In post 8, the OP stated that
    CmbEnt_ID04 is the field in query q02InvPurchaseSub and IntItmID the field in query q02InventoryItem that should be the same
    If it's a number field then it should be:
    Code:
    LatestPrice: DLookUp("[UnitPrice05]","q02InvPurchaseSub","DocumentDate01=Dmax('Docume ntDate01','q02InvPurchaseSub','[CmbEnt_ID04]=' & [IntItmID])")
    I'm assuming that single quotes will work here as its inside an existing DLookup expression ... though I've not tested it

    However, as already stated using two domain functions in a query field is a bad idea.
    ajax provided a better solution in post 11
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  2. #17
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, I missed that about the field name. I am guessing it is a lookup field which I never build in tables.

    Post 10 said that structure erred as the IntItmID field is not found in q02InvPurchaseSub, but it is in the query the expression is in, therefore concatenate outside the quote marks to pull value from current record of the query.

    I agree domain aggregates will likely cause very slow performance (even when not nested). Just offered as educational purpose.
    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. #18
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Red face

    Click image for larger version. 

Name:	Expression.jpg 
Views:	13 
Size:	230.8 KB 
ID:	33156

    Yes I agree and will always persist until there is no expressions in my application, only using subqueries. I still need to learn. I copied the line as Ridders52 suggested and the attachment shows the error on the attachment.

    I have owned 3 sizable businesses over 30 years employing 1000 people but never had the ultimate system I had an obsession for. My son is 20, completed College in December. Tomorrow we start marketing, even though there are many things to be added before I will call it the ULTIMATE PROCESS APPARATUS, but that is our quest. Our application does many dynamic things which the standard International programs does not do. We improve by the day and have no other life than Access right now but we enjoy it. Thank you.

    We will soon learn to use Access as front end and maybe SQL as back end. Does that solve all capacity issues?

  4. #19
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Sorry I removed the space in Document, but it gave the same error.

  5. #20
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    think you are missing a & and ". I don't use domains in queries, but your string is not referencing the table. Just a guess but try

    DLookUp("[UnitPrice05]","q02InvPurchaseSub","DocumentDate01=Dmax('Do cume ntDate01','q02InvPurchaseSub','[CmbEnt_ID04]=" & [IntItmID] & ")")

    Edit: just noticed June has said the same thing


  6. #21
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Oops - sorry - my code in post 16 was incorrect. Sorry June (& Perfac)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #22
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SQL would not be the backend. Could be SQLServer or SQLServerExpress or MySQL but SQL is a query language used by multiple database platforms.

    Yes, SQLServer would likely solve capacity issues but it's expensive.
    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.

  8. #23
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    SQLServerExpress is free.
    Its also far more stable/secure than Access
    and can manage databases up to 10GB.
    If that isn't sufficient then other versions with even more capacity are available but as June has already said, those are expensive
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-16-2017, 03:33 AM
  2. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  3. Replies: 5
    Last Post: 09-25-2013, 09:35 AM
  4. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  5. Replies: 4
    Last Post: 10-26-2012, 12:49 AM

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