Query in Microsoft Access?

Question

Hi Everyone,

I'm trying to create a query in MS Access searching a table that has a date column; The criteria are taken from two text fields in a form. One is called Month whilst the other is Year.
Now what I want to do is to set the query, so that when one of the field is empty, the query will still do a search based on the other field. Instead of doing an 'AND' search as it is right now, which resulted in no result at all time.

E.g:
If Month is blank and Year is 2008, the query will search for all entry that were created in 2008.
If Month is 12 and Year is blank, the query will search for all entry that were created in December.
If Month is 12 and Year is 2008, the query will search for all entry that were created in December, 2008

i.e: Is there a way to create an if statement in the query that will check for a null value in a text field, and if it is true, just do a specific search criteria.

Any help will be much appreciated =D. Thanks!

-Xan

Answer 1

WHERE (year IS NULL OR year = YearExpression) AND (month IS NULL OR month = MonthExpression)



You can use the DatePart function to create the expressions you need to parse the datefield.



Access Datepart Function: http://www.webcheatsheet.com/SQL/access_functions/datepart.php

Answer 2

once you question a database you ask the database to show the guidance matching the question. Basiclally you assert "whats up database, are you able to tell me all of the folk with blue eyes and crimson claws" and the database responds with "Yo, helpful " this is particularly extra formal than that, yet you get the belief.

Answer 3

date <> null (date!=null) and you should turn on null for your field in that property