Between 2 dates

[Infinite]

Hello! I currently have:

Work: IIf([Date]>[RStartDate] And [Date]<[REndDate],[RCost]*[Quantity],[Price]*[Quantity])

What is supposed to happen, is I have a retail price per item, and a default price per item. We have change the retail price in the last couple of years mulitple times, so I created a tblItemRetail table that has a RItemKey (PK), RITem, RStartDate, REndEndDate, RCost and RShowOnline. I simply stuck the star date and end dates for the dates that we changed the item retail price. The problem im having, is that query is not working correctly. In the 2 attached pictures you can see what it should do. It should just give me the retail price for a 44 mag. But, it gives me 2 prices. The price that it was (there was a price change, so its working) but, it ALSO gives me the original price. What am I doing wrong here? Thanks!

 

[plog]

You are grouping by Work. Each distinct value in fields that are GROUP BY causes it to produce a row. Most likely you should use an Aggregate function under Work (MAX, MIN, COUNT, SUM, etc)

I honestly don't know if your query will ever produce what you want. Can you demonstrate what you want with sample data? I would need 2 sets:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results. Using A, show me what you expect your query to return.

 

[Infinite]

You are correct, but when I change it to sum, it gives me a odd number. $38.41. I have no idea what that is. That is also, the wrong number, but with the correct amount of records. One step closer.

 

[Infinite]

Ill just give you a quick example.

I have a 22 Pistol that sold for $5. But, the retail price is $6.
What I want is the wholesale, and the only way to get wholesale cost, is to first figure out the retail cost.

So, I would change that $5, to $6, as that is the retail price.

I should start with a 22 pistol, sold for $5, and if it falls between a Start date and end date, then it should change to the cost that is associated with the start and end date.

In this case, its $6.

In the end, that is what I want. If that isnt enough, ill just take a bunch of screenshots, that should help also. If needed.

 

[plog]

In the end, that is what I want. If that isnt enough, ill just take a bunch of screenshots, that should help also. If needed.

Or perhaps....maybe....I don't know...you give me the data I requested?

 

[Infinite]

I fixed it I think. All I had to do was change it from GROUP BY and/or SUM to LAST. That seems to have gotten me all the data I wanted, as I wanted it. I think