Help with formatting a Dlookup with 3 criteria including a long, string & date. (2 Viewers)

[HillTJ]

All,

I have a query 'qry_Max_Price_Part_Supplier' that returns the latest cost of a product that could be supplied by various suppliers(works). The query contains the following fields 'MaxOfDate_of_Price', ' Cost_Price', "ProductID' (number) & 'JimCode' (string).

I wish to return the latest Cost_Price at that particular date (Not equal to the date), for that product from that supplier.

I thought I had it clear in my Head.

Function GetMaxCost(ProductID As Long, Supplier As String, Date_Quoted As Date)
GetMaxCost = DLookup("Cost_Price", "Qry_Max_Price_Part_Supplier", "ProductID=" & ProductID And "JimCode='" & Supplier & "'" & MaxOfDate_of_Price <= Date_Quoted)
End Function

As always, appreciate it.

 

[Ranman256]

Your delimiters and AND's are off:

GetMaxCost = DLookup("Cost_Price", "Qry_Max_Price_Part_Supplier", "[ProductID]=" & ProductID & " And [JimCode]='" & Supplier & "' and [MaxOfDate_of_Price] <= #" & Date_Quoted & "#")

 

[CJ_London]

When building a string you can help yourself by using debug.print to see what the code looks like - syntax errors aside (in which case build your string a bit at a time) - before using it in dlookup i.e.

debug.print "ProductID=" & ProductID And "JimCode='" & Supplier & "'" & MaxOfDate_of_Price <= Date_Quoted

assuming you started with

debug.print "ProductID=" & ProductID

your first syntax error would have occurred when you added the next bit

debug.print "ProductID=" & ProductID And "JimCode='" & Supplier & "'"



Note that Date_Quoted needs to be in the format mm/dd/yyyy (which it may be if that is your country style (USA for example) i.e.

"......[MaxOfDate_of_Price] <= #" & format(Date_Quoted,"mm/dd/yyyy") & "#"

Not sure of the benefit of MaxOfDate_of_Price <= Date_Quoted since by implication there can only be one record per product and supplier unless there are other fields grouped in the query - in which case dlookup will find the first record that meets the criteria - which may not be the record you want.

 

[HillTJ]

Gents, thanks. My intention is to return the cost that is nearest, less than or equal to, to the quote date. What i mean is say i raise a quote on 9/9/21. I need the latest cost from that particular supplier, which could have been a month earlier. If the cost price changes on 10/9/21, then i still reflect the cost of the earlier record until i raise a quote on say 11/9/21, in which i'll collect a later cost. If i made sense. Ps - Aus date format if you're confused. I have a query that returns the max price @ that date, so need to be earluer than or equal to that date to return the appropriate cost.

Good stuff. Thanks.

 

[CJ_London]

I believe the australian date format is dd/mm/yyyy - so you will need to use the format function. Otherwise a date of 11/9/2021 will be interpreted as 9th November in your dlookup, not 11th September.

Just want to make sure you are getting what you expect.

 

[HillTJ]

CJ_London, thanks. All good.

 

[Gasman]

Here is something I found on the net and I used to use, as always kept forgetting the exact format.
Then use that to format your dates

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

 

[MajP]

In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#

There seems to be some confusion. As @Gasman pointed out ALWAYS use mm/dd/yyyy regardless of your regional settings.

Microsoft Access tips: International Dates in Access

How to ensure dates are interpreted correctly in a Microsoft Access database, even when the user's regional settings are different from the US format.

allenbrowne.com

 

[MarkK]

Or, don't worry about formats and delimiters and use a parameterized QueryDef...

Function GetMaxCost(ProductID As Long, Supplier As String, Date_Quoted As Date)
    Const SQL As String = _
        "SELECT CostPrice " & _
        "FROM qMaxPricePartSupplier " & _
        "WHERE ProductID = p0 " & _
            "And JimCode = p1 " & _
            "And MaxOfDate_of_Price <= p2"
            
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = ProductID
        .Parameters(1) = Supplier
        .Parameters(2) = Date_Quoted
        With .OpenRecordset
            If Not .EOF Then GetMaxCost = .Fields(0).Value
            .Close
        End With
        .Close
    End With
End Function

 

[HillTJ]

Gents,

I've done this

In Declarations area i've added : Public Const strcJetDate = "\#mm\/dd\/yyyy\#"

Function GetMaxCost(ProductID As Long, Supplier As String, Date_Quoted As Date)


    'GetMaxCost = DMax("Cost_Price", "Qry_Max_Price_Part_Supplier", "[ProductID]=" & ProductID & " And [JimCode]='" & Supplier & "' and [Date_of_Price] <= #" & Format(Date_Quoted, "mm/dd/yyyy") & "#")
    GetMaxCost = DMax("Cost_Price", "Qry_Max_Price_Part_Supplier", "[ProductID]=" & ProductID & " And [JimCode]='" & Supplier & "' and [Date_of_Price] <= #" & Format(Date_Quoted, strcJetDate) & "#")
End Function

Does this seem reasonable? The Paramatized QryDef method is a bit above my pay grade, I'll try this when I have a moment.

 

[Gasman]

@MajP has a function somewhere on here, that returns a correctly formatted string for the type of variable.
You might try a search on here for that, or he might repost it, when he sees this?

You do not need the # with the constant, it does it for you.?, else you may as well do it manually each time?
Put ALL the criteria into a string variable, Debug.Print that variable to see what it is, then use in the DLookUp instead.

 

[HillTJ]

Gasman,

Yep, I tried it, changed to;

GetMaxCost = DMax("Cost_Price", "Qry_Max_Price_Part_Supplier", "[ProductID]=" & ProductID & " And [JimCode]='" & Supplier & "' and [Date_of_Price] <= " & Format(Date_Quoted, strcJetDate))

Will do the Debug.Print bit too just to check.

Thanks for the prompt response. I continue....

 

[MajP]

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

 

[MajP]

@Gasman,
I actually do not use that function, but the more expansive CSQL. Handles all datatypes and delimiters

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[HillTJ]

MajP, I saw Allen Brownes code & will probably use it. I was keen to get the Dmax to work but have now seen the light! Will do some homework with your link too. Cheers & thanks for the 'signigicant' response to my question.

 

[Gasman]

@Gasman,
I actually do not use that function, but the more expansive CSQL. Handles all datatypes and delimiters

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

That is the one I was thinking of.

 

[HillTJ]

Is there any real disadvantage of the Dmax method?. Clearly the syntax for dates has to be critically correct, but any reason to avoid this technique? I'll definitely investigate the other methods recommended & am always willing to learn.

 

[MarkK]

Domain function are not transaction aware. If you do DBEngine.BeginTrans, add a few records, and then run a DCount(), the records you added in the pending transaction will not be counted, not until you run DBEngine.CommitTrans.

 

[HillTJ]

Pat, i am doing exactly that. I call the dmax from a query!. The query is the basis of the 'many' subform for customer quotes. I pass the QuoteID from the main form to the query to return only the records with the QuoteID of the main form. It is unlikely that this subform would contain more than a dozen lines.

But, the Query referred to in the 'dmax' could contain several thousand records! And will grow as more products are added. If i called @MarkK's routine in post 10 instead each time, is that better?.

I'm very keen to adopt 'best practice' & keen to learn as i'm very likely to encounter similar situations in the future. When i started out, i was doing most of the calcs on the form! (Pretty much frowned appon for good reason). The database worked this way for several years without a glitch, but now it is up for a rewrite to add additional functionality.

Ps. I had a bucket load of embedded images too! So i have learn't something along the way!

Appreciate your time.

 

[HillTJ]

Pat, makes sense. Will give it a go. Thanks