Define query cirteria through form (1 Viewer)

Jens16

New member
Local time
Today, 21:47
Joined
Sep 17, 2021
Messages
1
Hi everyone,

I am new to access and encountering the first real problem:

I have an access database where colleagues can enter their expenditures for various projects from January to December. I created a query with 13 columns, one for the project number and one for the expenditures of each month.

I now want to provide a simple form from which one can filter the query. In particular it should be possible to filter only those projects where no expenditures for a given months have been recoorded (i.e. the relevant fields are empty)

I have created a form with a combo box from which one can select the months from January to December and a subform in which the data from the query is displayed. It is supposed to work like this: When the user selects "January" from the combo box, the subform should display only the projects for which no expenditures in January have been recorded.

I thought to realize this by including filter criteria in each of the monthly columns of the query according to the following example:

IF[Forms]![Formname]![Comboboxname]="January";"Is null";"")

Somehow it does not work. What am I doing wrong here?

Thanks!
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:47
Joined
Sep 21, 2011
Messages
9,025
You are constructing a database with an Excel mentality.
You should have a record for each month and user ?

Databases structures are narrow and deep, not wide and shallow like Excel.

Your table should have a field for the month, and that would contain either 1-12 or Jan to Dec etc.
 

mike60smart

Registered User.
Local time
Today, 20:47
Joined
Aug 6, 2017
Messages
956
Hi everyone,

I am new to access and encountering the first real problem:

I have an access database where colleagues can enter their expenditures for various projects from January to December. I created a query with 13 columns, one for the project number and one for the expenditures of each month.

I now want to provide a simple form from which one can filter the query. In particular it should be possible to filter only those projects where no expenditures for a given months have been recoorded (i.e. the relevant fields are empty)

I have created a form with a combo box from which one can select the months from January to December and a subform in which the data from the query is displayed. It is supposed to work like this: When the user selects "January" from the combo box, the subform should display only the projects for which no expenditures in January have been recorded.

I thought to realize this by including filter criteria in each of the monthly columns of the query according to the following example:

IF[Forms]![Formname]![Comboboxname]="January";"Is null";"")

Somehow it does not work. What am I doing wrong here?

Thanks!
Hi
Can you upload a zipped version of your database?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:47
Joined
Jul 9, 2003
Messages
13,416
I am new to access and encountering the first real problem:

You certainly have a problem, but it's not what you think! As indicated by gasman you are thinking in terms of spreadsheets and not MS Access. My blog here:-


will give you some insight into the problem you are making for yourself, and offer suggestions on how to correct the situation before it gets too far out of hand.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:47
Joined
Feb 19, 2002
Messages
33,168
Your table should have a field for the month, and that would contain either 1-12 or Jan to Dec etc.
The table needs year as well. The best solution is to assume the first of the month and use a standard date time field rather than separate fields for month and year or a mushed field with year and month.
 

Users who are viewing this thread

Top Bottom