How to filter dates in the Query

[winxmun]

Hi there...

I am trying to run a query with the following data example:

Record Date1 Date2 Date3
Sally 28 Oct 29 Oct
Lisa 28 Oct
Andrew 27 Oct 28 Oct
Andrea 02 Oct 15 Oct 28 Oct
Alvin 15 Sep 28 Oct 28 Nov

Today is 28 Oct, hence the result from the query should be 3 records only, ie Lisa, Andrew & Andrea.

Today is 29 Oct, the result from the query should be Sally only.

Anyone can help? :help
TQ...

[geekgirlau]

First of all, your structure is incorrect - your data is not normalised. As soon as you have a structure that has "Date1", "Date2" etc. you know there's a problem. Oh, and it's not a good idea to name a field "Date" - never use a name that is the same as a built-in Access function or property, such as "Date", "Type" etc.


Person Date
Sally 28-Oct
Sally 29-Oct
Lisa 28-Oct
Andrew 27-Oct
Andrew 28-Oct
Andrea 2-Oct
Andrea 15-Oct
Andrea 28-Oct
Alvin 15-Sep
Alvin 28-Oct
Alvin 28-Nov

Ok, so assuming you've tidied up the structure, your query is just this:

SELECT * FROM MyTable WHERE [Date]=Date()

[winxmun]

My Record structure is a record with few different dates & to filter out today's date using the query.

Any solution?

[geekgirlau]

In my last message I've given you the solution:

Fix your data structure
The SQL statement for your queryIf you want to work with the current structure (and I wouldn't recommend it) your query will be:

SELECT * FROM MyTable WHERE [Date1]=Date() OR [Date2]=Date() OR [Date3]=Date()

I would highly recommend that you do a search on the term "database normalisation" - there is a very good reason as to why you don't structure a table so that it has "MyField1", "MyField2" etc.