Advice when working with dates on form Between StartDate AND EndDate (3 Viewers)

[tucker61]

In my Data tables i now have added in a StartDate and EndDate - As per image below.
My combo box on my charge page - now looks at the Start and End Date - to bring back the most upto date unit cost -
This is fed by the CboxCost_Type_Desc.

What i am seeing is that when the end date has passed - the box appears to be blank and does not show the description.

Whats the best way round this ?
The charge date on the below was the 07/05/2024

SELECT tblQCCostTypes.Cost_Type_ID, tblQCCostTypes.Cost_Type_Desc, tblQCCostTypes.Unit_Cost, tblQCCostTypes.Area
FROM tblQCCostTypes
WHERE ((([Forms]![frmCharges]![tbChargeDate]) Between [Tblqccosttypes].[StartDate] And [Tblqccosttypes].[EndDate]))
GROUP BY tblQCCostTypes.Cost_Type_ID, tblQCCostTypes.Cost_Type_Desc, tblQCCostTypes.Unit_Cost, tblQCCostTypes.Area
HAVING (((tblQCCostTypes.Area)="PNC"))
ORDER BY tblQCCostTypes.Cost_Type_Desc;

 

[gemma-the-husky]

If there's no end date recorded, the value will be null, and it looks like those records are not excluded. Would that make sense?

So you may need to add "and [end date] not null" to your query.

 

[Pat Hartman]

1. Your form doesn't show us the date
2. Your query doesn't show us the area

Therefore, we can't validate the results.

Also, (((tblQCCostTypes.Area)="PNC")) should be part of the Where clause, NOT a Having.

 

[plog]

This is just too confusing to debug. What's the purpose/context of that query? No matter its purpose, I'm pretty sure its incorrect.

Is it to populate the drop down? If so, why are so many other fields in the SELECT?

Is it to generate the other fields on the page to get the latest price? If so, shouldn't there be a subquery to determine the "latest" result?

And just overall--it's an aggreate query (uses GROUP BY), but you use no aggregate functions. It's just a poor man's query to eliminate duplicates (which probably would already be achieved since you have the ID field of the table in the SELECT). I just don't understand what the aggregation without aggregate functions achieves.

I just think we need to take a step back and figure out what it is you ultimately want to achieve.

 

[Uncle Gizmo]

Seeing as your query is based on a single table you could "Take out the Tables" like this:-

You can remove most of the references to the table name when your SQL Statement is based on a single table. This trick depends on NOT just selecting the table name, but also the DOT “.” following it. You need to select the dot (.) to prevent your action removing the actual reference to the table in the FROM Clause.

I demo how to do it in this video clip:-

It's the seventh tip down on my "Nifty Tips" page here:-

Nifty Tips - Nifty Access

Nifty Video Tips Using an excellent new feature of Elementor Page Builder I am now able to show you time indexed clips from my MS Access Videos. This way you can quickly and easily see a video demonstration of just a few minutes, a Demo that gives you a leg up in your MS Access

www.niftyaccess.com

 

[tucker61]

This is just too confusing to debug. What's the purpose/context of that query? No matter its purpose, I'm pretty sure its incorrect.

Is it to populate the drop down? If so, why are so many other fields in the SELECT?

Is it to generate the other fields on the page to get the latest price? If so, shouldn't there be a subquery to determine the "latest" result?

And just overall--it's an aggreate query (uses GROUP BY), but you use no aggregate functions. It's just a poor man's query to eliminate duplicates (which probably would already be achieved since you have the ID field of the table in the SELECT). I just don't understand what the aggregation without aggregate functions achieves.

I just think we need to take a step back and figure out what it is you ultimately want to achieve.

OK all - thanks for the advice so far -
The code is to populate the Combobox - limited by the date the job was raised.
I have different prices for older dates.

I have removed the fields that are not required.

so my new code is below.

This is now working.

    StrSql = "SELECT tblQCCostTypes.Cost_Type_ID, tblQCCostTypes.Cost_Type_Desc, tblQCCostTypes.StartDate, tblQCCostTypes.EndDate " & vbCrLf & _
"FROM tblQCCostTypes " & vbCrLf & _
"GROUP BY tblQCCostTypes.Cost_Type_ID, tblQCCostTypes.Cost_Type_Desc, tblQCCostTypes.StartDate, tblQCCostTypes.EndDate " & vbCrLf & _
"HAVING ((([Forms]![frmPlannedRtmCharges]![tbChargeDate])>=[tblQCCostTypes].[StartDate] And ([Forms]![frmPlannedRtmCharges]![tbChargeDate])<[tblQCCostTypes].[EndDate])) " & vbCrLf & _
"ORDER BY tblQCCostTypes.Cost_Type_Desc;"
cboxCostType.RowSource = StrSql

 

[Gasman]

Tip:
I prefer to put the space at the start of the next line. That way I do not have to scroll all the way over to the right to see if one exists.
I would have thought it you were going to use vbCRLF at the end of each line, then it would not even be required?

    StrSql = "SELECT tblQCCostTypes.Cost_Type_ID, tblQCCostTypes.Cost_Type_Desc, tblQCCostTypes.StartDate, tblQCCostTypes.EndDate " & vbCrLf & _
" FROM tblQCCostTypes " & vbCrLf & _
" GROUP BY tblQCCostTypes.Cost_Type_ID, tblQCCostTypes.Cost_Type_Desc, tblQCCostTypes.StartDate, tblQCCostTypes.EndDate " & vbCrLf & _
" HAVING ((([Forms]![frmPlannedRtmCharges]![tbChargeDate])>=[tblQCCostTypes].[StartDate] And ([Forms]![frmPlannedRtmCharges]![tbChargeDate])<[tblQCCostTypes].[EndDate])) " & vbCrLf & _
" ORDER BY tblQCCostTypes.Cost_Type_Desc;"

 

[CJ_London]

Are those dates in the first post US or UK format?

and for 9/5/2024 and 10/5/2024 you have 2 valid prices if UK format or a big gap with no price from 6th September to 4th October if US format