First thing I would do is create a hard coded query to check you can retrieve the data you expect.

Not sure what you man by a hard coded query, the code works if i take out the < but the Eventdate needs to be the same as the EffectiveDate. I want to put an EffectiveStartDate and EffectiveEndDate in my PriceT and when the EventDate falls between those 2 dates then it gives me the price that's set for those dates.

Hardcoded as in not taken from Form values.
You can replace 0 and #01/12/2024# with data you know exists.

SELECT PackagePrice
FROM PricingT
WHERE ProductID = 0
AND EffectiveDate <= #01/12/2024#

This is just Step 1 of a multi step process we'll work through together.

Also, whenever I see date formatting like that, I always look for problems there. Remember, Format() will turn your date into a STRING.

Step 1 - simple hard coded query
Step 2 - Aggregate to get the  MAX
Step 3 - Between or <= / >=
Step 4 - replace one value with a variable
Step 5 - repeat with other variables
Step 6 - move to DMAX hardcoded
Step 7 - replace one value with a variable
Step 8 - repeat with other variables

Great advice Alex

Great, will try tomorrow and let you know

Finally got this to work using what i first posted. not sur how or why but sems to be working now. thank you all for your help