Extract month number from date field 2

[MikeT]

If I have a number, say...5, can issue an SQL statement on a date field to return results for where the date field is between 5/1 and 5/31?

SELECT * FROM myTable wHERE MONTH(myDateField)=5

Something like the above would be ideal.

 

[THoey]

Did you look at the DATEPART function? You should be able to do the following:

SELECT *
FROM MyTable
WHERE DATEPART("m", MyTable.DateField) = 5; Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[MikeT]

Yep, thats exactly what I needed. Thanks!

 

[WildHare]

Hey Mike - did you try your own suggestion?

Select * from MyTable where Month(MyDate) = 5

should work just fine = uses the MONTH function, which returns the month integer from the date field....

How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MikeT]

Wow - imagine that!

 

[Guest_imported]

I have been unsuccessfully trying all kinds of variations of the DatePart function to condense a short date to just the month AND a two digit year.

How can I make all entries from 5/01/02 through 5/31/02 show as 5/02?

I found this non-DatePart expression works but it gives me a 4 digit year and I'd prefer a 2 digit year: Expr1: Month([Date]) & "/" & Year([Date])

 

[DesertTrip]

Got it! I get to answer my own question after much testing and tweaking (hours later)

If you want to condense a short date to a 4 digit date in your query, try this expression:

Month([Date]) & "/" & Right(Year([Date]),2)

(I added a criteria of [Enter Mo/Yr] for the user, too)

 

[RichUK]

DateByMonth: Format$([TableNme].[DateField],"m yy&quot