Filetring Date Fields by Month Name

D

Daren Hawes

Hi,

I just have a question about filtering date fields by Month name.

For example

Data is

ID Date
---------------------
1 1/1/2005
2 2/2/2005

My problem query is

Select * from table where Date = 'January'

I have tried

Select * from table where Date = MonthName(Month(theDate)) = 'January'

but the above fails

Any ideas?

Thanks daren
 
A

Allen Browne

Try one of these:
Select Table1.* from Table1 where Month(Table1.[Date]) = 1;
Select Table1.* from Table1 where MonthName(Table1.[Date]) = 'January';

The first (numeric comparison) would be better. There are some problems with
MonthName() in Access 2000, and the string comparison would be slower.

Hopefully your field is not actually called Date, as that is a reserved word
in VBA and will give problems in code. I've tried to illustrate how to be
really clear for the query in case that is the name.
 
D

Daren Hawes

Hi Allen,

No date was just an example. I not get "Invalid Procedure Call" error when
trying

SELECT *
FROM Consultants
WHERE (((MonthName([Consultants].[DOB]))='January'));

Access added all the brackets.

Thanks Daren

Allen Browne said:
Try one of these:
Select Table1.* from Table1 where Month(Table1.[Date]) = 1;
Select Table1.* from Table1 where MonthName(Table1.[Date]) = 'January';

The first (numeric comparison) would be better. There are some problems
with MonthName() in Access 2000, and the string comparison would be
slower.

Hopefully your field is not actually called Date, as that is a reserved
word in VBA and will give problems in code. I've tried to illustrate how
to be really clear for the query in case that is the name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Daren Hawes said:
I just have a question about filtering date fields by Month name.

For example

Data is

ID Date
---------------------
1 1/1/2005
2 2/2/2005

My problem query is

Select * from table where Date = 'January'

I have tried

Select * from table where Date = MonthName(Month(theDate)) = 'January'

but the above fails

Any ideas?

Thanks daren
Click to expand...
Click to expand...
 
D

Daren Hawes

Here is the exact query

SELECT Consultants.ID, Consultants.[First Name], Consultants.[Last Name],
Consultants.DOB
FROM Consultants
WHERE MonthName(Consultants.DOB) = 'January';

Thanks Daren


Allen Browne said:
Try one of these:
Select Table1.* from Table1 where Month(Table1.[Date]) = 1;
Select Table1.* from Table1 where MonthName(Table1.[Date]) = 'January';

The first (numeric comparison) would be better. There are some problems
with MonthName() in Access 2000, and the string comparison would be
slower.

Hopefully your field is not actually called Date, as that is a reserved
word in VBA and will give problems in code. I've tried to illustrate how
to be really clear for the query in case that is the name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Daren Hawes said:
I just have a question about filtering date fields by Month name.

For example

Data is

ID Date
---------------------
1 1/1/2005
2 2/2/2005

My problem query is

Select * from table where Date = 'January'

I have tried

Select * from table where Date = MonthName(Month(theDate)) = 'January'

but the above fails

Any ideas?

Thanks daren
Click to expand...
Click to expand...
 
A

Allen Browne

Daren, you did have the syntax correct the first time, i.e. you need Month()
inside MonthDate().

If it is not working correctly for you, and you are using Access 2000, it is
probably the problem I referred to. Microsoft's description of the problem
is at:
http://support.microsoft.com/?id=225956

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Daren Hawes said:
Here is the exact query

SELECT Consultants.ID, Consultants.[First Name], Consultants.[Last Name],
Consultants.DOB
FROM Consultants
WHERE MonthName(Consultants.DOB) = 'January';

Thanks Daren


Allen Browne said:
Try one of these:
Select Table1.* from Table1 where Month(Table1.[Date]) = 1;
Select Table1.* from Table1 where MonthName(Table1.[Date]) =
'January';

The first (numeric comparison) would be better. There are some problems
with MonthName() in Access 2000, and the string comparison would be
slower.

Hopefully your field is not actually called Date, as that is a reserved
word in VBA and will give problems in code. I've tried to illustrate how
to be really clear for the query in case that is the name.

Daren Hawes said:
I just have a question about filtering date fields by Month name.

For example

Data is

ID Date
---------------------
1 1/1/2005
2 2/2/2005

My problem query is

Select * from table where Date = 'January'

I have tried

Select * from table where Date = MonthName(Month(theDate)) = 'January'

but the above fails

Any ideas?

Thanks daren
Click to expand...
Click to expand...
Click to expand...
 
D

Daren Hawes

Thanks for your help Allen,

I now get Data Type Mismatch in critera Expression error

SELECT Consultants.ID, Consultants.[First Name], Consultants.[Last Name],
Consultants.DOB
FROM Consultants
WHERE MonthName(Month(Consultants.DOB)) = 'January';

Any Ideas?

Thanks Again

PS I am using Access 2003 mode..

Allen Browne said:
Daren, you did have the syntax correct the first time, i.e. you need
Month() inside MonthDate().

If it is not working correctly for you, and you are using Access 2000, it
is probably the problem I referred to. Microsoft's description of the
problem is at:
http://support.microsoft.com/?id=225956

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Daren Hawes said:
Here is the exact query

SELECT Consultants.ID, Consultants.[First Name], Consultants.[Last Name],
Consultants.DOB
FROM Consultants
WHERE MonthName(Consultants.DOB) = 'January';

Thanks Daren


Allen Browne said:
Try one of these:
Select Table1.* from Table1 where Month(Table1.[Date]) = 1;
Select Table1.* from Table1 where MonthName(Table1.[Date]) =
'January';

The first (numeric comparison) would be better. There are some problems
with MonthName() in Access 2000, and the string comparison would be
slower.

Hopefully your field is not actually called Date, as that is a reserved
word in VBA and will give problems in code. I've tried to illustrate how
to be really clear for the query in case that is the name.


I just have a question about filtering date fields by Month name.

For example

Data is

ID Date
---------------------
1 1/1/2005
2 2/2/2005

My problem query is

Select * from table where Date = 'January'

I have tried

Select * from table where Date = MonthName(Month(theDate)) = 'January'

but the above fails

Any ideas?

Thanks daren
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

The query should work, though it will generate #Error on fields where DOB is
null.

There could be other issues, such as bad references, or perhaps sandbox
mode.

Why not take the easy route? Lose the MonthName() and just use the month
number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Daren Hawes said:
Thanks for your help Allen,

I now get Data Type Mismatch in critera Expression error

SELECT Consultants.ID, Consultants.[First Name], Consultants.[Last Name],
Consultants.DOB
FROM Consultants
WHERE MonthName(Month(Consultants.DOB)) = 'January';

Any Ideas?

Thanks Again

PS I am using Access 2003 mode..

Allen Browne said:
Daren, you did have the syntax correct the first time, i.e. you need
Month() inside MonthDate().

If it is not working correctly for you, and you are using Access 2000, it
is probably the problem I referred to. Microsoft's description of the
problem is at:
http://support.microsoft.com/?id=225956

Daren Hawes said:
Here is the exact query

SELECT Consultants.ID, Consultants.[First Name], Consultants.[Last
Name], Consultants.DOB
FROM Consultants
WHERE MonthName(Consultants.DOB) = 'January';

Thanks Daren


Try one of these:
Select Table1.* from Table1 where Month(Table1.[Date]) = 1;
Select Table1.* from Table1 where MonthName(Table1.[Date]) =
'January';

The first (numeric comparison) would be better. There are some problems
with MonthName() in Access 2000, and the string comparison would be
slower.

Hopefully your field is not actually called Date, as that is a reserved
word in VBA and will give problems in code. I've tried to illustrate
how to be really clear for the query in case that is the name.


I just have a question about filtering date fields by Month name.

For example

Data is

ID Date
---------------------
1 1/1/2005
2 2/2/2005

My problem query is

Select * from table where Date = 'January'

I have tried

Select * from table where Date = MonthName(Month(theDate)) = 'January'

but the above fails

Any ideas?

Thanks daren
Click to expand...
Click to expand...
Click to expand...
 
Top