Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Serial Date Condition

Posted by: melonheadmis Nov 13 2019, 10:07 AM

Currently I have a database that can report money collected in 2019. >DateSerial(Year(Date()),Month(Date()),0)
However, I would like to be able to choose a year. I've tried a parameter query which yielded nothing.
Can one of you experts be able to assist me?

Posted by: GroverParkGeorge Nov 13 2019, 10:39 AM

In order to offer more detail, we'd need to have more information about the table(s) involved and the field(s) involved, and the form on which you want to implement the filtering, but a general approach would be similar to that most often used.

On a form, create an unbound text box control to hold the filter, i.e the year. Add a command button to launch the process.

In a query that will return the records you want, add a criteria that refers to this control.

I'm not sure your example does what you state, though. It will return a specific DATE. That date is the last day of the prior month. So today, it returns October 31, 2019. How exactly does that work for you?


====================

A query that filters on a year would be something like this:

SQL
SELECT FieldOne, FieldTwo, YourDateField
FROM tblYourTableNameGoesHere WHERE Year(YourDateField) = Forms!fromFormNameGoesHere.txtYearFilter

Posted by: melonheadmis Nov 13 2019, 04:43 PM

I have a report that has many DLookUps on the bottom of the report. The report tells me what we brought in today - and the payment method, ie. cash, and for the month and one for the year.
I run a macro and it calculates the total. I wanted to recreate that for 2018 or any year instead of the current year. The report has no problem with the calculations for the current date, but I can't get the monthly or yearly macro to work. I thought there would be a way, ie parameter query but I can't get anything to work. Thanks for helping!

Posted by: mempie Nov 19 2019, 09:18 AM

Where are you choosing the year? GroverParkGeorge's syntax should work. What expression are you using? Also, your question suggests that you want a total for the whole year, but your expression would give you the total for just the current month, as GroverParkGeorge also said.