DB functions - between dates 1

[lespaul]

I need to perform database calculations locating rows that one of the columns is between two dates, ie the field contains 5/16/02 and my criteria is between 5/1/02 and 5/31/02. What is the proper format for searching by date?

Thanks Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[DaleWatson123321]

Hi Leslie,

I've used Excel's database functions, along with criteria for ALL months. These criteria reference a SEPARATE cell for the YEAR. Therefore, for each new year, you only have to enter the new year in the ONE cell.

I also needed to change the Date field from "Text" format to "Date" format.

I hope this meets with your requirements. Please advise as to how the file "fits".

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ETID]

SELECT my_table.*, my_table.[my_DATE]
FROM my_table
WHERE (((my_table.[my_DATE])>#5/1/2002# And (my_table.[my_DATE])>#5/31/2002#));



....substitute your table name where "my_table" appears
and your date field where "my_date" appears

 

[ETID]

ooops..........

SELECT my_table.*, my_table.[my_DATE]
FROM my_table
WHERE (((my_table.[my_DATE])>=#5/1/2002# And (my_table.[my_DATE])<=#5/31/2002#));


try this...I missed an operator on the first one.

 

[lespaul]

ETID, Thanks, but the information I'm using isn't from a relational database, it is exported from Lotus Notes into a spreadsheet, then using the DB functions of Excel (DCOUNTA, DMAX, DMIN, DAVERAGE, etc.) By setting criteria (like the WHERE clause of SQL) in a spreadsheet, you can then use these functions to find the min/max value, average, etc. based on which cells match the criteria. Then I can manipulate the raw data into a report format that is meaningful.

Thanks Dale! I am always amazed at how powerful and versatile these functions are, I just wish the Excel help had more help on how to set up the criteria for different types of situations (like dates!). Thanks again for your help!

Au revoir, until the next time someone comes up with a different report request!

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[ETID]

....Oh, Duh!