Comparing Dates in a Query

[ChampionDuy]

I have a form that is based on one table that selects diferent fields from a table. The form is opened by the user after the user types in quarter dates, a quarters beginning date and ending date. The form should only display records if that record has a date in that range. I have tried to solve this two ways and I cant get it to work right. The first way I tried it was I just created a form that held the fields that I needed and then on the Open event of that form I compared the text boxes that contain the certain dates to a global variable that I declared as the dates the user enters. This worked but it only compares the first record in the table. It does not search and compare the whole table. The Second way I tried it was with a Query that passed global variables into it. This worked when I ran the query but for some reason it does not when I save it as a form. For some reason it gives me like 300 records when there should be only 15. For some reason I dont think it is looking at the dates. Here is the query.

SELECT [Type], [crime], [command], [CCN], [Locate], [Perunit], [Approve], [Auth], [Expdate], [Status], [results], [Eval1], [Eval2], [Eval3], [Eval4]
FROM casefile
WHERE (ExpDate>=StartDateFun() And ExpDate>=EndDateFun()) Or (Auth>=StartDateFun() And Auth<=EndDateFun());


The startdatefun and enddatefun are functions that I use to try to pass the global variable to the query for date comparison. Any help would be greatly appreciated. Thanks

 

[The_Doc_Man]

Well, I'm not sure completely but if you are interested in calendar quarters, try looking into the DatePart function, which includes the calendar quarter ('q') as one of its options. I would bet that if you gave a couple of dates on the form and converted them to year and quarter, then build a query based on the table, but with year and quarter instead of actual dates, you would get what you wanted quickly enough.

And yes, it is perfectly fine to compute different functions of the same field twice in the same query.

 

[llkhoutx]

Your date field may have a time component, depending how it is created. Two dates with different time components don't match.