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