filtering a query

  • Thread starter Thread starter sue dorsey
  • Start date Start date
S

sue dorsey

I have several tables that I run a query on to get a list
of historical events over 20 years. There is a first date
and late date field for each event. How can I filter to
get events that occur in one or two months?

Event first date last date
one 3/2/2002 3/12/2002
two 3/7/2001 3/7/2001
three 4/1/2003 4/3/2003
four 6/2/2001 6/19/2001
five 9/12/1999 10/21/1999
six 3/19/1998 4/1/1998

I want to filter or query from above query to events
starting in march only?

One step further, can I filter/query to events that
started and ended in March and April?

thanks for any suggestions
 
For events starting in March

SELECT *
FROM tblEvents
WHERE Month([first date]) = 3

For events started and ended in March or April

SELECT *
FROM tblEvents
WHERE Month([first date]) In (3, 4)
AND Month([last date]) In (3, 4)

Note that this event (if any) will be selected

seven 04/30/2002 03/01/2003

HTH
Van T. Dinh
MVP (Access)
 
I tried suggestion below, but could not get it to work. I
keep receiving message "data type mismatch in Criteria
expression"???

-----Original Message-----
For events starting in March

SELECT *
FROM tblEvents
WHERE Month([first date]) = 3

For events started and ended in March or April

SELECT *
FROM tblEvents
WHERE Month([first date]) In (3, 4)
AND Month([last date]) In (3, 4)

Note that this event (if any) will be selected

seven 04/30/2002 03/01/2003

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
I have several tables that I run a query on to get a list
of historical events over 20 years. There is a first date
and late date field for each event. How can I filter to
get events that occur in one or two months?

Event first date last date
one 3/2/2002 3/12/2002
two 3/7/2001 3/7/2001
three 4/1/2003 4/3/2003
four 6/2/2001 6/19/2001
five 9/12/1999 10/21/1999
six 3/19/1998 4/1/1998

I want to filter or query from above query to events
starting in march only?

One step further, can I filter/query to events that
started and ended in March and April?

thanks for any suggestions

.
Click to expand...
.
Click to expand...
 
Is [first date] Filed is of Date/Time type in your Table. If it is not of
Date/Time type, you need to convert it to Date/Time before using Month().

Post relevant details of your Table, some sample data and your SQL
String(s).
 
[first date] is Date/Time type with dd/mmm/yy
[last date] is Date/Time type with mm/dd/yy
-----Original Message-----
Is [first date] Filed is of Date/Time type in your Table. If it is not of
Date/Time type, you need to convert it to Date/Time before using Month().

Post relevant details of your Table, some sample data and your SQL
String(s).

--
HTH
Van T. Dinh
MVP (Access)



I tried suggestion below, but could not get it to work. I
keep receiving message "data type mismatch in Criteria
expression"???
Click to expand...



.
Click to expand...
 
That sounds right so I don't know what the problem is.

Post relevant details of the Table and the *actual* SQL String you used.

--
HTH
Van T. Dinh
MVP (Access)



[first date] is Date/Time type with dd/mmm/yy
[last date] is Date/Time type with mm/dd/yy
-----Original Message-----
Is [first date] Filed is of Date/Time type in your Table. If it is not of
Date/Time type, you need to convert it to Date/Time before using Month().

Post relevant details of your Table, some sample data and your SQL
String(s).

--
HTH
Van T. Dinh
MVP (Access)



I tried suggestion below, but could not get it to work. I
keep receiving message "data type mismatch in Criteria
expression"???
Click to expand...



.
Click to expand...
Click to expand...
 
Back
Top