Query that shows price valid on specific date

[PKnappers]

Hi,
I am trying to make a query that shows the price for a product, based on two parameters.
Parameter one is a product code.
Parameter two is a date. This date falls between two dates.

I have one list (table) where is product code and invoice date.
The second list (table) I have, contains product code, and price valid from date, and price valid to date columns. This price valid to date is often not filled, and the price I still valid as we speak. If the date is filled there is often a new entry with an updated price. But sometimes, even if there is a new entry in the table, the date 'valid to date' is sometimes also not filled.

I would like Access to show me the valid price for the specific product. What criteria should I give in the macro, in order that Access shows what I want?

For illustration purposes, a small overview of my table:

Product code, Price, valid from, valid to
AAAA, 12000, 01.01.2012, 31.12.2012
BBBB, 16600, 01.01.2012, 12.06.2013
AAAA, 13500, 01.01.2013, 28.08.2013
AAAA, 11500, 29.08.2013,
BBBB, 17600, 13.06.2013,


Product, invoice date, price according to price list
AAAA, 02.05.2012, ????
AAAA, 01.08.2012, ????
BBBB, 10.06.2013, ????
AAAA, 31.10.2013, ????
AAAA, 16.11.2013, ????


I look forward to your ideas.
If you happen to know how this search can be performed in Excel, I am of course also happy to read that. (But my index, or Vlookup functions, give only the first possible result in the table. As I do not know how to give in the date parameter.)

Thank you so much in advance.

 

[Cronk]

I would open a record set based on the product and sorted in date ascending order. I'd then search that recordset for the transaction date being greater or equal to the [valid from] field. ( I do hope you have not named your table field with that name).

 

[plog]

You posted this in the query section, said you are trying to make a query to do this, but in the middle you reference passing criteria using a macro. So what exactly are you trying to do? What do you need help with exactly?

A further confusion is the completeness of your data. I understand how to handle blank [valid to] dates, but what about other conditions--Does it have gaps for prices? Are there overlaps for prices? How should those situations be handled?

 

[PKnappers]

Thank you Cronk and plog! I appreciate you are thinking with me.
Instead of macro, I should have written 'query', my typo.

I am building a system that follows up if the invoices are issued with the correct price for the sold product. Is the price applied on the invoices in accordance to what we have agreed.
The next issue is more or less the same, but with a different price list.

I would like to have help with a query in Access of function in Excel, that gives me the price that belongs to that very product and falls within the date range. I work with two criteria: invoice date, and product code.

In Excel I tried Index and Match. This works fine if the product code is unique, and both the invoice information and the price list work with the same identical product code. This is not the case, my product code is not unique, the price updates, and the product code remains the same. The valid from and to dates change.

 

[plog]

I don't think you answered a single one of my questions. But, your issue (or what I guessed was your issue) was interesting so I worked on something.

Attached is a database with 2 similar tables as yours (you didn't provide table names and some of your fields are poorly named) and a query that determines the price for Invoiced items based on a date range.

 

[Frothingslosh]

You know, it occurs to me that you don't even necessarily need to store a 'To Date'. If there's a new price, there will always be a new 'From Date'.

Following that logic, you should, in theory, be able to simply search for the greatest 'From Date' that is equal to or less than the item's purchase date.

This avoids having to deal with Nulls at all.

Edit: This assumes, of course, that there is only ever one set price for an object at any given time. If there are overlaps or multiples, then please disregard.

 

[PKnappers]

Hi Plog, thank you for your files and the useful comment.

I read the code in the SQL view. and I updated it according to my specific needs... Now I copied it to my actual database, and updated the field names.

When I write the following code in the SQL view, the query runs fine:
SELECT Intrastat.[Doc no], Intrastat.[Vin code], Intrastat.[Doc date], Intrastat.[Invoice end item], Intrastat.[Intrastat value DKK], Standardpriser.Indkøbspris, IIf(Intrastat.[Intrastat value DKK]>Standardpriser.Indkøbspris,"True","False") AS Risk
FROM Standardpriser INNER JOIN Intrastat ON (Standardpriser.Variant = Intrastat.[Invoice end item]) ;

But when I add the statement for selecting just those records where the date match is made, I get an error message.
SELECT Intrastat.[Doc no], Intrastat.[Vin code], Intrastat.[Doc date], Intrastat.[Invoice end item], Intrastat.[Intrastat value DKK], Standardpriser.Indkøbspris, IIf(Intrastat.[Intrastat value DKK]>Standardpriser.Indkøbspris,"True","False") AS Risk
FROM Standardpriser INNER JOIN Intrastat ON (Standardpriser.Variant = Intrastat.[Invoice end item]) AND (Intrastat.[Doc date]>=Standardpriser.Reg# dato fra) ;

Can one comment what is wrong in this last past f the query?

 

[plog]

Standardpriser.Reg# dato fra

Poor field name choices strike again. You shouldn't have special characters (#, &, etc.) in field names. Nor should you have spaces. Because when you do, it means you have to do extra work to make your code and queries work.

That extra work is putting brackets around your poorly named fields. So if "Reg# dato fra" is a field name, you need to enclose it in brackets ([Reg# dato fra]).

 

[jdraw]

I'd just like to highlight the importance of what plog is suggesting. Too many posters cause themselves unnecessary grief by using naming conventions that allow embedded spaces and special characters. This tend to result in Access syntax errors, again mostly because of missing [ and] enclosing "brackets" ( a requirement of Access).
Stick to alphanumeric and "_"(underscore character); no embedded spaces -- avoid syntax errors with names.
See this link by Rainlover re :names/naming.

Good luck.

 

[PKnappers]

Ok, I understand the importance of good field names. I take the option of putting brackets around the poor field names. My raw data is not in Access, I linked a few Excel tables in order to make my database running.

Still, I have the problem, that the query in SQL is not working. The current issue I can into is the 'JOIN expression not supported'. Below a copy of my code.

SELECT Intrastat.[Doc no], Intrastat.[Vin code], Intrastat.[Doc date], Intrastat.[Invoice end item], Intrastat.[Intrastat value DKK], CIFs.[CIF DKK], IIf([Intrastat].[Intrastat value DKK]>[CIFs].[CIF DKK],"Yes","No") AS [Intrastat>CIF], Standardpriser.Indkøbspris, IIf(Intrastat.[Intrastat value DKK]>Standardpriser.Indkøbspris,"True","False") AS Risk
FROM CIFs INNER JOIN (Standardpriser INNER JOIN Intrastat ON Standardpriser.Variant = Intrastat.[Invoice end item]) ON CIFs.EIC = Intrastat.[Invoice end item] AND (Intrastat.[Doc date]>=Standardpriser.[Reg# dato fra]);


As the error comes up only when adding the last statement ' AND (Intrastat. ... dato fra]) ' I assume that my mistake is somewhere overthere...

Can one of you see why Access comes up with the JOIN error message?

 

[Frothingslosh]

What kind of data is [Reg# dato fra]?

 

[plog]

INNER JOIN (Standardpriser INNER JOIN Intrastat ON Standardpriser.Variant = Intrastat.[Invoice end item]) ON CIFs.EIC = Intrastat.[Invoice end item] AND (Intrastat.[Doc date]>=Standardpriser.[Reg# dato fra]);

That join looks screwy. For simplicity's sake you have 3 joining criteira, here's the tables and order in which they appear:

1. Standard -> Intrastat
2. CIFs -> Intrastat
3. Intrastat -> Standard

That 3rd one seems to be in the wrong place, it should be closer to the 1st one--somewhere inside the first set of parenthesis of your JOIN.