Show Results From Month Specified (1 Viewer)

MCCDOM · May 10, 2017

Hi There, I am trying to create a query that shows the records that fall within the month of a specified date. For example if I selected the date 10th May 2017 in my date picker text box txtMonth how can I get the query to return any matching record with a date in May please.
I have tried the following in the criteria box but unfortunately doesn't work:

Code:

Month([Forms]![frmReportLookup]![txtMonth])

Any help would be very appreciated.

Many thanks,

Dom

bob fitz · May 10, 2017

I suspect that you are using the expression as the criteria in a date column but it would need to be applied to a column that is only returning a month rather than a complete date.
Can you show us the full SQL of the query that you are trying this in.

MCCDOM · May 10, 2017

Hi Bob Fitz thanks for your reply. Here is the SQL for the query

Code:

SELECT tblBreakdown.BreakdownID, tblBreakdown.MailboxID, tblBreakdown.OfficeID, tblBreakdown.StaffID, tblOffices.DeptNominalCode, tblStaff.StaffName, tblBreakdown.DateAdded, tblMailbox.Cost, tblMailbox.[Mailbox Type], tblBreakdown.DateDeleted
FROM tblStaff INNER JOIN (tblOffices INNER JOIN (tblMailbox INNER JOIN tblBreakdown ON tblMailbox.MailboxID = tblBreakdown.MailboxID) ON tblOffices.OfficeID = tblBreakdown.OfficeID) ON tblStaff.StaffID = tblBreakdown.StaffID
WHERE (((tblBreakdown.BreakdownID) Is Not Null) AND ((tblBreakdown.DateAdded)<=[Forms]![frmReportLookup]![txtMonth]) AND ((tblBreakdown.DateDeleted) Is Null)) OR (((tblBreakdown.DateDeleted)=Month([Forms]![frmReportLookup]![txtMonth])));

bob fitz · May 10, 2017

Maybe:

Code:

SELECT tblBreakdown.BreakdownID, tblBreakdown.MailboxID, tblBreakdown.OfficeID, tblBreakdown.StaffID, tblOffices.DeptNominalCode, tblStaff.StaffName, [B]Month(DateAdded) AS AddedDate[/B], tblMailbox.Cost, tblMailbox.[Mailbox Type], [B]Month(DateDeleted) AS DeletedDate[/B]
FROM tblStaff INNER JOIN (tblOffices INNER JOIN (tblMailbox INNER JOIN tblBreakdown ON tblMailbox.MailboxID = tblBreakdown.MailboxID) ON tblOffices.OfficeID = tblBreakdown.OfficeID) ON tblStaff.StaffID = tblBreakdown.StaffID
WHERE (((tblBreakdown.BreakdownID) Is Not Null) AND (([B]Month(DateAdded)[/B])<=[Forms]![frmReportLookup]![txtMonth]) AND ((tblBreakdown.DateDeleted) Is Null)) OR ((([B]Month(DateDeleted)[/B])=Month([Forms]![frmReportLookup]![txtMonth])));

MCCDOM · May 10, 2017

Thanks Bob Fitz that's helped a lot.

MCCDOM · Jul 22, 2019

Hi All,

I'd like to refine this criteria even more. At the moment it won't show a record who's DateDeleted is more than or equal to the specified End Date. What I'd like to know is how do I get a record to show that has a DateDeleted value more than or equal to the end date but was deleted within the same month of the end date specified please?

Would appreciate any help I can get, thanks.

Pat Hartman · Jul 22, 2019

FYI, you MUST include year as well as month or you'll find your query returning records from the selected month but for multiple years unless of course, that is your intent. So, I assume that the form will format the selection as yyyy/mm so the criteria can work correctly.

See if this is closer to what you want. If it isn't, please restate the condition completely so we don't have to try to piece together multiple posts. I removed the extraneous parentheses (Access tends to go crazy with parens).

Code:

WHERE (tblBreakdown.BreakdownID Is Not Null AND Format(DateAdded, "yyyy/mm") = [Forms]![frmReportLookup]![txtMonthYear]) 
AND (tblBreakdown.DateDeleted Is Null OR Format(DateDeleted, "yyyy/mm") = [Forms]![frmReportLookup]![txtMonthYear]);

MCCDOM · Jul 23, 2019

Hi Pat, thank you for your suggestion but unfortunately it hasn't worked. Whenever I run a search now it comes back with the message 'No mailboxes were added upto the 16/06/2019'.

Currently what the search does is it returns all active mailboxes and deleted ones if the search date (txtMonthEnd) is before the deleted date (tblBreakdown.DateDeleted).
However what I want it to do, is to show any mailboxes that have a deleted date before the search date but only the ones that are within the same month and year as the search date please?

How might I go about this?

Thanks.

Pat Hartman · Jul 24, 2019

I don't see that text anywhere in the query you posted.
Can you please post your query.

Galaxiom · Jul 25, 2019

Pat Hartman said:

Code:

WHERE (tblBreakdown.BreakdownID Is Not Null AND Format(DateAdded, "yyyy/mm") = [Forms]![frmReportLookup]![txtMonthYear]) 
AND (tblBreakdown.DateDeleted Is Null OR Format(DateDeleted, "yyyy/mm") = [Forms]![frmReportLookup]![txtMonthYear]);

That is a horrendously inefficient way to select records. The Format functions must be applied to every record because the index on the date field cannot be used.

This can easily be 100 times slower than a properly designed SARGABLE query. The poor performance probably won't be evident until there are a large number of records but it will progressively worsen as the database grows.

Convert the date in the textbox to a date range to use in the query. This way, functions are only applied to generate the Select criteria. Be sure to index DateDeleted.

Code:

WHERE DateDeleted 
BETWEEN DateSerial(Year([Forms]![frmReportLookup]![txtMonth]), Month([Forms]![frmReportLookup]![txtMonth]), 1) 
AND DateSerial(Year([Forms]![frmReportLookup]![txtMonth]), Month([Forms]![frmReportLookup]![txtMonth]) + 1, 0)

Pat Hartman · Jul 25, 2019

So three functions per field is better than one? Who knew?
Doesn't SARGABLE refer to the ability to use indexes? If you are not storing the value as yyyy/mm then I'm not sure what index could be used in any event. Both methods actually convert the stored value to a different value which is NOT indexed anywhere.

https://dba.stackexchange.com/questions/162263/what-does-the-word-sargable-really-mean

Galaxiom · Jul 26, 2019

Pat Hartman said:
So three functions per field is better than one?

In the expression I posted, definitely. There is a world of difference between a function on the left hand side of a WHERE clause where it must be applied to every record and applying any number of functions to the right hand side where they are processed just once per query execution.

Who knew?

Apparently not Pat Hartman.

It greatly surprises me that a developer with such an extent of experience could have overlooked such a fundamental database concept for so long.

Doesn't SARGABLE refer to the ability to use indexes?

Yes. I believe I intimated that principle when I posted:

Galaxiom said:
The Format functions must be applied to every record because the index on the date field cannot be used. (Emphasis added)

I included the term SARGABLE so anyone who didn't understand the importance could look it up.

If you are not storing the value as yyyy/mm then I'm not sure what index could be used in any event.

Date fields can be indexed like any other field. They work with the underlying numeric value and are very efficient.

Both methods actually convert the stored value to a different value which is NOT indexed anywhere.

Incorrect. My expression compares a date range against the index.

Everyone should take a closer look at the WHERE clause I posted. It is vitally important for developers to understand why it can be literally orders of magnitude more efficient than any of the others posted.

Don't make the common mistake of thinking the brevity of an expression is what matters.

Pat Hartman · Jul 26, 2019

It greatly surprises me that a developer with such an extent of experience could have overlooked such a fundamental database concept for so long.

Sometimes we just address the problem at hand which was that the OP was using only month but needed to consider year as well to get a correct result. Of course, you jumped in before the OP actually answered the question I asked which was - do you really want records for the specified month "this" year or "any" year. So, the final answer will actually depend on that. If the answer is "this" year then you are correct, the range check will be more efficient but somehow I doubt that the user will ever have enough data in the table for my suggestion to actually pose a problem. In any event, the more efficient solution is always preferred.

MCCDOM · Jul 26, 2019

Hi both, thank you for your suggestions.
To clarify I would like the search to return all results of active mailboxes that were created on or before the search date specified. This would include mailboxes created years ago. This search function is working however.
It's trying to get the search to include any mailboxes that have a deleted date where the deleted date is within the the same month and year of the search date. So for example if I search for all mailboxes created up to and on the 26/07/2019 I would like it to return all mailboxes including ones that have a deleted date within the month of July, 2019. Is this possible to do and if so how please?

Many thanks for your help so far.

Dom

Pat Hartman · Jul 26, 2019

Given the new requirement, I changed the name of your text field from txtMonth to txtSearchDate because it will need to be a full date.

Code:

SELECT tblBreakdown.BreakdownID, tblBreakdown.MailboxID, tblBreakdown.OfficeID, tblBreakdown.StaffID, tblOffices.DeptNominalCode, tblStaff.StaffName, tblBreakdown.DateAdded, tblMailbox.Cost, tblMailbox.[Mailbox Type], tblBreakdown.DateDeleted
FROM tblStaff INNER JOIN (tblOffices INNER JOIN (tblMailbox INNER JOIN tblBreakdown ON tblMailbox.MailboxID = tblBreakdown.MailboxID) ON tblOffices.OfficeID = tblBreakdown.OfficeID) ON tblStaff.StaffID = tblBreakdown.StaffID
WHERE tblBreakdown.DateAdded <= [Forms]![frmReportLookup]![txtSearchDate])
AND (DateDeleted Is Null OR DateDeleted BETWEEN DateSerial(Year([Forms]![frmReportLookup]![txtSearchDate]), Month([Forms]![frmReportLookup]![txtSearchDate]), 1)  AND DateSerial(Year([Forms]![frmReportLookup]![txtSearchDate]), Month([Forms]![frmReportLookup]![txtDate]) + 1, 0))

MCCDOM · Jul 30, 2019

Thank you Pat, I will give that a try and report back.

MCCDOM · Oct 31, 2019

Sorry for the late reply but thank you Pat, your last suggestion has worked perfectly!

Pat Hartman · Nov 3, 2019

You're welcome.

Show Results From Month Specified (1 Viewer)

MCCDOM

Registered User.

bob fitz

AWF VIP

MCCDOM

Registered User.

bob fitz

AWF VIP

MCCDOM

Registered User.

MCCDOM

Registered User.

Pat Hartman

Super Moderator

MCCDOM

Registered User.

Pat Hartman

Super Moderator

Galaxiom

Super Moderator

Pat Hartman

Super Moderator

Galaxiom

Super Moderator

Pat Hartman

Super Moderator

MCCDOM

Registered User.

Pat Hartman

Super Moderator

MCCDOM

Registered User.

MCCDOM

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread