Matching Prices Between Table & Query

[bels71]

I'm trying to align two sets of data and have it populate when there is a mis-alignment between two values. I have a table that has all current orders that have been placed specific to each customer. I'm trying to match it to a query I have that combines different tables into one for a final "Price History". The issue I'm having is lining up the correct value, per the date in my 'Price History' query. The price history changes daily, but at 6:00PM each day. I have both table and query set-up in the same format, however when the order is lifted on say August 5th at 10:00AM, it's pulling in the pricing history that's effective only beginning at August 5th at 6:00PM. Any thoughts how I can fix this to line it up?

[orange]

I recommend you show us your table structure (relationships window) and/or tell us more about your tables and design.

[bels71]

The fields I have matching from right to left are:

Pricing Sold-To (Number format) -> Customer Number
Pricing Item Number (Number format) -> Pricing Item Number (Number format)
Pricing Terminal (Number format) -> Terminal Number (Number format)

In the variance field my formula is: Variance: Round([Final US History]![Price]-[Converted Daily Orders]![Unit Price],4)

Under the 'Requested Date' field, I added the following criteria but it's not working to solve my problem:

>=[Final US History]![Date] And <=[Final US History]![End Date]

[bels71]

Anyone any idea?

[orange]

See this link re AgreedToPrice and the affect it has on Price History.

Can you post a copy of your database? Just some representative data.

[bels71]

I can't really post a copy of my database as it contains customer information. Here is a kickout of the two data field that I'm trying to match. The date of the history it ends up matching to the order is for the first history it shows starting on August 2nd, rather than the history that began the previous evening

[orange]

The date of the history it ends up matching to the order is for the first history it shows starting on August 2nd, rather than the history that began the previous evening

Unclear to me. You know your environment, but readers do not. More details/example with description.

Which are the records you want to match?
Are your Dates always "off" by 1 day?

You have CustomerName, CustomerNumber, Terminal, PricingItemNumber, and UnitPrice...

[bels71]

I have the two tables lining up for customer number, terminal, and item number. However, I'm trying to have records kick out that DO NOT match for UnitPrice. The issue is, if an order is picked up early on the 2nd, it should be trying to match to the price that was effective on the 1st at 6:00PM, however it's trying to match it to the price in the history table that shows for August 2nd (which isn't effective until 6:00PM of that day). It's matching the date, and not matching between the 'Start' and 'End' Date.

[Bulzie]

For the Date field, sounds like you have to use the previous day if it is before 6pm correct? So in query for that date field, something like this:
vDate = IIF([RequestTime] < #6:00 PM#, [RequestDate] - 1, [RequestDate])

Also on conditions, try: Between #[Final US History]![Date]# And #[Final US History]![End Date]#

Also test some things out by taking out variables and putting in real values. But real date values #8/1/2016# etc. for the Beg and End dates and see if it works, if it does then something wrong with syntax on dates. Try to take criteria out and see if your table and query match produce the correct recordset before criteria, etc.

[bels71]

That worked, thanks for your help!