X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Serial Date Condition, Access 2016    
post Nov 13 2019, 10:07 AM

Posts: 204
Joined: 8-February 07

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?
Go to the top of the page
post Nov 13 2019, 10:39 AM

UA Admin
Posts: 37,457
Joined: 20-June 02
From: Newcastle, WA

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:

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

This post has been edited by GroverParkGeorge: Nov 13 2019, 10:46 AM
Reason for edit: added potential example

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Nov 13 2019, 04:43 PM

Posts: 204
Joined: 8-February 07

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!
Go to the top of the page
post Nov 19 2019, 09:18 AM

Posts: 274
Joined: 27-September 01

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.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    5th July 2020 - 09:27 PM