Dlookup with multiple criteria (1 Viewer)

A1ex037

Registered User.
Local time
Today, 08:44
Joined
Feb 10, 2018
Messages
50
I have been searching for answer to the issue that has been troubling me lately. This is my first access database project, so I expect a lot of things to go sideways, especially since I lack skills. Anyway, I have a table called PriceHistory and it holds ProductID, Price, StartDate (date is in short format, presented as dd.mm.yyyy). Price for the actual product is changed over time, and I need to find it within the specified date range.

On a form that has to use it, I have an "after update" event procedure attached to drop-down that selects the ItemName and it should restore the correct price based on the date displayed on the form. I have tried numerous variants, and without luck. I have also looked examples listed on general/gen0018.htm (I am still not allowed to post links)

So far, I have something like this, and I cannot figure out what am I doing wrong.

Private Sub ItemName_AfterUpdate()

Dim curCurrentPrice As Currency

UsedID = Me.ItemName.Column(1)

curCurrentPrice = DLookup("[Price]", "PriceHistory", "[ProductID] = " & UsedID & " AND "[StartDate] <= #" & [Forms]![Sales]![SaleDate] & "#")

Me.PriceH = curCurrentPrice

End Sub


If I try to use it without

AND "[StartDate] <= #" & [Forms]![Sales]![SaleDate] & "#"

just to see what will happen and it works just fine (normally, it does not do the job since the date criteria is not included). So I assume that it is something related to dates. Most likely the format of the dates (and I have paid attention to it, I'm not in the US)

Any help is appreciated.
 

boerbende

Ben
Local time
Today, 08:44
Joined
Feb 10, 2013
Messages
339
use
"[StartDate] <= #" & format([Forms]![Sales]![SaleDate],"yyyy\/mm\/dd") & "#")
 

boerbende

Ben
Local time
Today, 08:44
Joined
Feb 10, 2013
Messages
339
It also looks like you have a double quote too many
& " AND "[StartDate] <= #" & [Forms]![Sales]![SaleDate] & "#")
 

A1ex037

Registered User.
Local time
Today, 08:44
Joined
Feb 10, 2018
Messages
50
Thanks a lot! I've tried with format option today, and it produced an error. Looks like I still have a lot to learn the correct syntax.

Based on your suggestion, DLookup now looks like:

curCurrentPrice = DLookup("[Price]", "PriceHistory", "[ProductID] = " & UsedID & " AND [StartDate] <= #" & Format([Forms]![Sales]![SaleDate], "yyyy\/mm\/dd") & "#")


Now it does not produce an error, but it looks like it does not takes date into account. I have tried it on a form immediately. These are the values from PriceHistory:

ID | ProductID | StartDate | Price
388 130 20.2.2017. 2.700 RSD
395 130 12.12.2017. 1.400 RSD

When "after update" is executed (the date on the form is 14.12.2017, it returns the value of 2700RSD, instead of 1400RSD). The SaleDate format on the form is ShortDate. Maybe it is because the both dates in PriceHistory are older than SaleDate, and the first value is taken into account?
 

MarkK

bit cruncher
Local time
Today, 00:44
Joined
Mar 17, 2004
Messages
8,178
I'm not sure if DLookup() is a sharp enough tool here, since the criteria you are using might return many records, and if it does, you also need to sort those by date and return the most recent.

Maybe you need to run a quick query like...
Code:
    Const SQL_SELECT As String = _
        "SELECT TOP 1 Price " & _
        "FROM PriceHistory " & _
        "WHERE ProductID = p0 " & _
            "AND StartDate <= p1 " & _
        "ORDER BY StartDate DESC;"
    
    With CurrentDb.CreateQueryDef("", SQL_SELECT)
        .Parameters("p0") = Me.ItemName.Column(1)
        .Parameters("p1") = Forms!Sales.SaleDate
        Me.PriceH = .OpenRecordset!Price
        .Close
    End With
See what happens there? See how the SQL sorts the result set by StartDate DESC and only picks the first row?
hth
Mark
 

A1ex037

Registered User.
Local time
Today, 08:44
Joined
Feb 10, 2018
Messages
50
Thank you for your reply. There should not be many changes per item (currently there are 2-5, I really don't expect more than 10 changes). I have tried your suggestion, and I'm getting

Run-time error 3464 : Data type mismatch in criteria expression
It fails in: Me.PriceH = .OpenRecordset!Price

I do understand what you suggested (even thou I will need more practice to construct something like this myself).
 

MarkK

bit cruncher
Local time
Today, 00:44
Joined
Mar 17, 2004
Messages
8,178
See if this solves the problem...
Code:
Me.PriceH = .OpenRecordset.Fields(0).Value
Mark
 

MarkK

bit cruncher
Local time
Today, 00:44
Joined
Mar 17, 2004
Messages
8,178
What about these other values?
Code:
.Parameters("p0") = Me.ItemName.Column(1)
.Parameters("p1") = Forms!Sales.SaleDate
What are the datatypes in the table vs the values you are assigning here? I would expect ProductID to be a long integer, so you will get this error if the value at Me.ItemName.Column(1) is not numeric. I would guess StartDate is a date type, so is the value at Form!Sales.SaleDate a valid date?
hth
Mark
 

A1ex037

Registered User.
Local time
Today, 08:44
Joined
Feb 10, 2018
Messages
50
ProductID from PriceHistory is Number (Long Integer), and Me.ItemName.Column(1) refers to an ID of the Item itself (that one is an autonumber). StartDate is a Date/Time type (short date), and a Form!Sales.SaleDate is also a short date (that one is a calendar, but format is short date).
 

MarkK

bit cruncher
Local time
Today, 00:44
Joined
Mar 17, 2004
Messages
8,178
Well, the error you are getting, data type mismatch in criteria expression, is that the datatype of one of the parameters you are supplying to criteria expression of the query is not the same type as the field you are comparing it to.

A few test lines of you code you could run before you create the QueryDef...
Code:
If Not CurrentDb.TableDefs("PriceHistory").Fields("StartDate").Type = 8 Then MsgBox "PriceHistory.StartDate is not a date"
If Not IsDate(Forms!Sales.SaleDate.Value) Then MsgBox "The value in the Sales form is not a date"
If Not IsNumeric(Me.ItemName.Column(1)) Then MsgBox "ItemName.Column(1) is not numeric."
See how those will test the types of the values?
Mark
 

A1ex037

Registered User.
Local time
Today, 08:44
Joined
Feb 10, 2018
Messages
50
I am not getting any output, except the standard data type mismatch. Just to make it clear, lines were inserted just above
With CurrentDb.CreateQueryDef("", SQL_SELECT)
 

MarkK

bit cruncher
Local time
Today, 00:44
Joined
Mar 17, 2004
Messages
8,178
I don't know what else to do to troubleshoot this. Looks like we've tested all the types in advance and it should work, so when it doesn't work, I don't know what else to suggest. If you post the Db, I'll take a look.
Sorry,
Mark
 

A1ex037

Registered User.
Local time
Today, 08:44
Joined
Feb 10, 2018
Messages
50
No problem. I still don't have enought posts, but thanks for the sticky about uploading by boblarson.

I have stripped everything, except necessary things. It is a work in progress (as I said, I'm learning a lot about it, and I know that there are things that I could do better).

If you hit form Sales, after selecting category / subcategory / item01, error appears.

I had some success with boerbende's suggestion, and it is inserting the designated price into priceH field nicely, except that it fails on date.

Thanks for help.
 

Attachments

  • Example.zip
    72.8 KB · Views: 173

MarkK

bit cruncher
Local time
Today, 00:44
Joined
Mar 17, 2004
Messages
8,178
It works on my machine. What if you try this, so the date format is not a factor...
Code:
Private Sub ItemName_AfterUpdate()
    Const SQL_SELECT As String = _
        "SELECT TOP 1 Price " & _
        "FROM PriceHistory " & _
        "WHERE ProductID = p0 " & _
            "AND StartDate <= p1 " & _
        "ORDER BY StartDate DESC;"
[COLOR="DarkRed"]    Dim d1 As Date
    
    d1 = Forms!Sales.SaleDate
[/COLOR]    With CurrentDb.CreateQueryDef("", SQL_SELECT)
        .Parameters("p0") = Me.ItemName.Column(1)
        .Parameters("p1") = [COLOR="darkred"]d1[/COLOR]
        Me.PriceH = .OpenRecordset.Fields(0).Value
        .Close
    End With
End Sub
Guessing...
Mark
 

A1ex037

Registered User.
Local time
Today, 08:44
Joined
Feb 10, 2018
Messages
50
Mark, you actually gave an answer right on top. As soon as I switched my regional settings to English-US, the code worked. So it had to be something with the way access deals with date (I noticed that all my tables are depending on regional settings). For the past few hours I have been reading about access / dates and formats, etc. trying to figure out how to "enforce" access to work with my regional settings. I ended up changing the parameters line to

.Parameters("p1") = Format([Forms]![Sales]![SaleDate], "dd.mm.yyyy")

and everything worked flawlessly!
Many thanks for your support! Great job!
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 07:44
Joined
Jan 14, 2017
Messages
18,186
I ended up changing the parameters line to

.Parameters("p1") = Format([Forms]![Sales]![SaleDate], "dd.mm.yyyy")

and everything worked flawlessly!

In case you aren't aware, formatting dates turns them into strings
So if you want to sort those values, the sort order may not be as intended

e.g. doing an ascending sort, 03/02/2018 will be shown before 31/01/2018 when you probably want the opposite ...

Either don't format your dates in the first place
Or use CDate(YourStringDateField) afterwards
 

A1ex037

Registered User.
Local time
Today, 08:44
Joined
Feb 10, 2018
Messages
50
Thanks for the tip. I wasn't aware of that. I will surely read about it and play with it today in order to get it right. I know I have a lot of learning to do, and I really appreciate any help!
 

Users who are viewing this thread

Top Bottom