How to get current month data in access query (1 Viewer)

[hrdpgajjar]

Hi all,
I've a table where a column name "Callgivendate". I want to create a query which gives data of current month only.

I've tried "Format(Date(),"yyyymm") but it gives me "Data type mismatch" error.

How can i get current month data ?


Thanks

 

[Galaxiom]

Don't be tempted to do what many inexperienced developers do and process all the dates in the table. It is very inefficient

Create a condition:

WHERE Callgivendate BETWEEN DateSerial(DatePart("yyyy", DATE()), DatePart("mm", DATE()), 1) AND DateSerial(DatePart("yyyy", DATE()), DatePart("mm", DATE()) + 1, - 1)

 

[MarkK]

Format() the value to find, AND Format() your data ...

WHERE Format([Callgivendate], "myy") = Format(Date(), "myy")

 

[ebs17]

Format([Callgivendate], "myy")

The calculation on the table field inevitably prevents possible index use.
Therefore, @Galaxiom's suggestion is preferable, I would just rather use functions like Year, Month, Day than DatePart. But that is less important.
The second aspect is that the comparison expressions on the right-hand side only need to be calculated once because they are constant. The question then is whether you have to perform an (additional) calculation every time for the table field on the left-hand side, which in turn also means work.
Performance Important things can be taken into account straight away if they do not cause additional costs at the time - even if you are not dependent on this better performance at the moment.

 

[MarkK]

Agreed, this ...

WHERE [DateField] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)));

... is much faster.
I tested this on a table I have here with 330,000 rows, and my post #3 approach took 0.5254 seconds, and the WHERE clause above took 0.0098.
Thanks,

 

[Gasman]

Agreed, this ...

WHERE [DateField] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)));

... is much faster.
I tested this on a table I have here with 330,000 rows, and my post #3 approach took 0.5254 seconds, and the WHERE clause above took 0.0098.
Thanks,

Only if you had an index on that field though surely?
Most newbies have never heard of an index?

 

[ebs17]

The use of an existing index obviously has a much more significant effect. One user once reported a factor of more than 2500 from his own measurements, @MarkK's figure is 53.6 in this example.

But also: The format function creates a string from a date value. Calculations and type conversions are also work in themselves, which adds up per record.

 

[ebs17]

Most newbies have never heard of an index?

That's why the advantages of having and using indexes cannot be repeated often enough.
That's why it's better to always show and explain the solution using indexes in suggestions rather than an obvious "intuitive" simple solution.
Therefore, many textbooks and tutorials should be rewritten.

Incidentally, @Galaxiom's suggestion can easily be varied to other time periods (several months, quarters, several calendar weeks), even beyond the turn of the year. So you gain a high level of variability through parameterization.

 

[Pat Hartman]

I want to create a query which gives data of current month only.

Use form fields to specify the month and year. That gives you the option to see the FULL June values on July 1st or sometime in the future. You can set the default to the current month and year so the user doesn't actually have to type them. OR, you can use a start and end date. Again, you can prefill the two dates with June 1, 2024 and June 30, 2024

 

[hrdpgajjar]

Don't be tempted to do what many inexperienced developers do and process all the dates in the table. It is very inefficient

Create a condition:

WHERE Callgivendate BETWEEN DateSerial(DatePart("yyyy", DATE()), DatePart("mm", DATE()), 1) AND DateSerial(DatePart("yyyy", DATE()), DatePart("mm", DATE()) + 1, - 1)

It worked, thanks

 

[Pat Hartman]

It worked, thanks

Not really. When did your June data end? Did you work on Sunday so you could run the query at the end of the day?