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

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 04:19
Joined
Apr 1, 2019
Messages
713
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.
Code:
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

Well-known member
Local time
Today, 11:19
Joined
Apr 9, 2015
Messages
4,339
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

Super Moderator
Staff member
Local time
Today, 15:19
Joined
Feb 19, 2013
Messages
16,555
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

To train a dog, first know more than the dog..
Local time
Today, 04:19
Joined
Apr 1, 2019
Messages
713
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

Super Moderator
Staff member
Local time
Today, 15:19
Joined
Feb 19, 2013
Messages
16,555
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

To train a dog, first know more than the dog..
Local time
Today, 04:19
Joined
Apr 1, 2019
Messages
713
CJ_London, thanks. All good.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:19
Joined
Sep 21, 2011
Messages
14,056
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
Code:
Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:19
Joined
May 21, 2018
Messages
8,463
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:19
Joined
Feb 19, 2002
Messages
42,986
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

bit cruncher
Local time
Today, 08:19
Joined
Mar 17, 2004
Messages
8,178
Or, don't worry about formats and delimiters and use a parameterized QueryDef...
Code:
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

Super Moderator
Staff member
Local time
Today, 11:19
Joined
Feb 19, 2002
Messages
42,986
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

To train a dog, first know more than the dog..
Local time
Today, 04:19
Joined
Apr 1, 2019
Messages
713
Gents,

I've done this

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


Code:
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

Enthusiastic Amateur
Local time
Today, 15:19
Joined
Sep 21, 2011
Messages
14,056
@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

To train a dog, first know more than the dog..
Local time
Today, 04:19
Joined
Apr 1, 2019
Messages
713
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

You've got your good things, and you've got mine.
Local time
Today, 11:19
Joined
May 21, 2018
Messages
8,463
Code:
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

You've got your good things, and you've got mine.
Local time
Today, 11:19
Joined
May 21, 2018
Messages
8,463
@Gasman,
I actually do not use that function, but the more expansive CSQL. Handles all datatypes and delimiters
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 04:19
Joined
Apr 1, 2019
Messages
713
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

Enthusiastic Amateur
Local time
Today, 15:19
Joined
Sep 21, 2011
Messages
14,056
@Gasman,
I actually do not use that function, but the more expansive CSQL. Handles all datatypes and delimiters
That is the one I was thinking of. (y)
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 04:19
Joined
Apr 1, 2019
Messages
713
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

Super Moderator
Staff member
Local time
Today, 11:19
Joined
Feb 19, 2002
Messages
42,986
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.
 

Users who are viewing this thread

Top Bottom