Help with formatting a Dlookup with 3 criteria including a long, string & date.

[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

 

[Pat Hartman]

OR, use the unambiguous yyyy/mm/dd if you need to format a date in a string as you do in this case.

I find that when I have a dated price list, it is simpler to use

Where SaleDate Between StartDate and EndDate

Than to try to get the "last, less than".

 

[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

 

[Pat Hartman]

Mark is absolutely correct. I almost never create SQL strings in VBA, instead I use saved querydefs so I don't run into this kind of problem. Not that I would anyway because I'm in the US so my default date format is mm/dd/yyyy. The problem comes down to SQL itself. It assumes that a date in string format is in mm/dd/yyyy unless the date is unambiguous as in yyyy/mm/dd or mmm/dd/yyyy or dd/mmm/yyyy. The problem with the two versions with the string month is language so the values would be different depending on your local language and that would also cause SQL a problem (unless VBA fixes it for you - someone with a non-english install could check and report) since I don't think it is multi-lingual.

So, the bottom line is - if you are creating an SQL string in VBA and you are embedding a date value, it MUST be enclosed in pound signs AND it MUST be in mm/dd/yyyy format or some non-ambiguous format.

However, if as in Mark's example, you are passing a parameter, Access will pass the parameter object as the correct data type assuming the dates are datetime data types. So, if you are using an unbound form control to pass a date, the control must have its format property set to short date or some other date format.

And finally, the magic is that dates are NOT stored as strings at all. They are stored as double precision numbers with the integer part being the number of days since Dec 30, 1899 (for Jet/ACE) or Jan 1, 1900 (for SQL Server. Other products use different origin dates. It doesn't matter. The ODBC driver knows the rule for its target. The decimal part of the field is the fraction of time since midnight so .5 is noon. .25 is 6 AM and .75 is 6 PM.

Formatting dates is for human consumption. Do it on your forms and reports but not when the value is being used internally. For example, if you format a date as dd/mm/yyyy and sort on it, don't be surprised if all the day 01's end up first regardless of their month. If that was your intent, swell. But, usually it is not.

 

[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.

 

[Pat Hartman]

Domain functions are fine as long as you don't use them in queries or VBA loops. Each domain function runs a separate query so if you have 10,000 records in your query, each domain function will run 10,000 separate queries.