Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Expression

    There is a table t01PriceList and query q01Pricelist. Fields include "Date","StockItem02" and "LatestPrice02". There is another query q01StockItem, with fields that include "LatestPrice01" and "StockItem01".
    a. The t01Pricelist table stores any price change on any stockitem when those items are bought.
    b. Therefore there will be multiple price changes against a specific stockItem.


    c. I want an expression to return in query q01StockItem in field "LatestPrice01" the latest Price from query q01PriceList on a specific Stockitem. Let us say the number under "StockItem" is Stk123.

    It is possible for the price to go up or down, hence I have a challenge with DMAX. But as said I need to return the Latest Price entry and the amount in LatestPrice02 to LatestPrice01, where StockItem is the same.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    databases have no sense of order - certainly not latest/earliest - without some basis of order - in this case most likely a date field to identify when a price changes

    if you have a date field, then the latest price would be the one with the latest (i.e. max) date

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I tried DLookUp ("[LatestPrice02]", "t01PriceList", "Dmax([Date]) "). for I mentioned that there is a field named "Date". Sorry that I don't know yet.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Create an UDF which returns the latest price of item at certain date. And then use this function with today's date.

    I myself use the function
    Code:
    Public Function ValidValue(parTable As String, IdField As String, IdCond As String, IdIsString As Boolean, RetField As String, DateField As String, parDate As Date)
        Dim dbs As Database
        Dim rs As Recordset
        Dim varQstr As String
        ' The function returns the value of RetField from table parTable
        ' where IdField equals IdCond and DateField is nearest past or equal to parDate.
        ' The parameter IdIsString must be True, when IdField has Text format
        
        On Error GoTo Err_ValidValue
        Set dbs = CurrentDb
        varQstr = "SELECT [" & RetField & "] FROM " & parTable & _
                " WHERE [" & DateField & "] <= " & Format(parDate, "\#mm\/dd\/yyyy\#") & _
                " And [" & IdField & "] = " & IIf(IdIsString, "'" & IdCond & "'", IdCond) & _
                " ORDER BY [" & DateField & "] DESC"
        Set rs = dbs.OpenRecordset(varQstr)
        rs.AbsolutePosition = 0
        ValidValue = rs.Fields(0).Value
        
    Err_ValidValue:
            rs.Close
            dbs.Close
            Set rs = Nothing
            Set dbs = Nothing
    End Function

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    for I mentioned that there is a field named "Date".
    date is a reserved word and should not be used for field names.

    you use dlookup/dmax in forms or perhaps in vba code. Your code is wrong, but the right answer depends where you are using this

  6. #6
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Yes Date is a reserved word. I used DteAdjusted but just to make my thread easy to read I said Date. Whether I use it in VBA of the form or as an expression in table as long as the result is good. I changed my mind so if you still may help me with the expression, it will be great.

    Purchase Invoice has a query q01InvPurchase and subquery q01InvPurchaseSub. In the subquery there are fields "UnitPrice","StockNumber" and "DocDate". In query q01StockItem with fields "StockNumber" and "DocDate" I want to create this field "LatestPrice" with an expression as suggested. There may be a major number of invoices but the expression will of course find the invoice at the latest date and the Price, if the StockNumber in q01InvPurchaseSub match StockNumber in q01StockItem.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if using the dlookup it would look something like this

    DLookUp ("[LatestPrice02]", "t01PriceList", "DteAdjusted=Dmax('DteAdjusted','t01PriceList')").

  8. #8
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I changed to the real names below. Some of my plans changed a bit but the question is the same. The expression you suggested works, but it gives the Price against the latest date in the whole table. There are multiple Inventory Items and I assume therefore the expression needs more of a "where" condition. The part in black are the real names of my objects and it works partly. The part in red is me trying a "where" condition. CmbEnt_ID04 is the field in query q02InvPurchaseSub and IntItmID the field in query q02InventoryItem that should be the same. The expression I put in q02InventoryItem as a calculated field of course.

    LatestPrice: DLookUp("[UnitPrice05]","q02InvPurchaseSub","DocumentDate01=Dmax('Docume ntDate01','q02InvPurchaseSub',"[CmbEnt_ID04*]=" & [IntItmID])")

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Remove the* in the Dmax criteria

    There's also a space in the dmax formula. Typo or glitch due to lack of code tags?
    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

  10. #10
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    LatestPrice: DLookUp("[UnitPrice05]","q02InvPurchaseSub","DocumentDate01=Dmax('Docume ntDate01','q02InvPurchaseSub','[CmbEnt_ID04]=' & [IntItmID])")

    Error. Front End cannot find the name IntItmID in the expression. I put this exact expression in a field in q01InventoryItem and IntItmID is in the same query. CmbEntID keeps the same data, but it also returns the same error.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you shouldn't really use domain functions in a query. They are very slow, which will be noticeable on larger datasets

    Assuming your query does not already include the q02InvPurchaseSub query, you should use a subquery


    LatestPriceSELECT TOP 1 UNITPrice05 FROM q02InvPurchaseSub WHERE CmbEnt_ID04=IntItmID ORDER BY DocumentDate01 DESC)

    bit concerned that your field naming convention appears to indicate a denormalised data structure, e.g. UNITPrice05 implies there is are 01 to 04 UNITPrice fields perhaps more, same for DocumentDate01

  12. #12
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you, I will try the subquery.

    I have more than 500 fields in the whole application and thought it good that no field will have exactly the same name than another. The field name most used has a suffix of over 25. You know that many fields opt to have the exact same name even though they are in different tables or queries. Yes UnitPrice01 then 02 then 05 works for me and I am of the opinion it is better to do it. Teach me if you feel different, it will be an important lesson to me.

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agree with ajax about using subqueries rather than domain functions especially if you have many records.

    Fields should only be related in more than one table if used as PK or FK fields either side of a join
    Personally I use the same field name in each table but others prefer to put a PK or FK suffix on such fields.

    Any other fields should be used in one table only and their values obtained using queries.
    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

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    My comment was about normalisation and just thought your naming convention implies your data was denormalised. So long as you have a naming convention that is consistent and works for you that is OK. Just consider if at some point in the future someone has to pick up your work, that they understand the convention.

    have more than 500 fields in the whole application and thought it good that no field will have exactly the same name than another.
    I agree, and also agree with ridders comments. A 'unitprice' should only appear in one table.

    If you have a unit price in another table, then it should be a different type of unit price which can be reflected in a more meaningful name. For example you might have a unitprice in a product pricing table and again in an order line table. So you might use prodUnitPrice and ordUnitPrice. They are both unit prices and the ordUnitPrice probably got populated from the prodUnitPrice table, but they have different reasons for existing.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to test the DLookup for comparison, need to concatenate the [IntItemID] parameter outside quote marks so the value is pulled from current record.

    LatestPrice: DLookUp("[UnitPrice05]", "q02InvPurchaseSub", "DocumentDate01=Dmax('DocumentDate01','q02InvPurch aseSub','[CmbEnt_ID04]='" & [IntItmID] & "')")

    If IntItemID is a number type field, remove its apostrophe delimiters:

    LatestPrice: DLookUp("[UnitPrice05]","q02InvPurchaseSub","DocumentDate01=Dmax('Docume ntDate01','q02InvPurchaseSub','[CmbEnt_ID04]=" & [IntItmID] & ")")

    However, CmbEnt_ID04 looks like the name of a combobox. This should be a field in table/query instead. Why reference query instead of source table?
    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.

Page 1 of 2 12 LastLast
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