Where statement for Date FieldS

[Adrianna]

Okay....I'm usually pretty clear headed about figuring things out, but this has got me all tied up this morning:-o

SELECT tblLeave.*
FROM tblLeave
WHERE ((([Enter Date Slection:]= tblLeave.Depart)OR(([Enter Date Slection:]>tblLeave.Depart))) OR (([Enter Date Slection:]=tblLeave.Return)or[Enter Date Slection:]<tblLeave.Return));

I'm trying to prompt the User to Enter the date that they would like to check. When the date is entered, I want to query for all of the records:

WHERE the UserDate ([Enter Date Selection] ) is equal to or after the Departure Date ([Depart]) or equal to or before the Return Date ([Return]).

Please help!

Thanks

 

[Adrianna]

Pat,
Thanks for your help. I guess I should have explained that I get this error:

The expression is types incorrectly, or it is too complex to be evaluated.....

I'm pretty sure that it is types correctly....because both forms of the expression give me the same error (your short expression and my long one). I'm prompted to enter the date and then the error message pops up after I try to execute it.



If there is anthing else that you can think of....I'd greatly appreciate it.

 

[pdx_man]

You can't have left hand evaluation. (Well, you can, but very specific code that I can't remember off the top of my head right now)

WHERE tblLeave.Depart >= [Enter Date Slection:] AND tblLeave.Return <= [Enter Date Slection:]

Don't worry, you will only be prompted once.

 

[Adrianna]

not sure what's wrong.....

I appreciate the help you both have provided, but I'm still getting the same error....the expression is to complex to evaluate

Well, I guess I'm going to have to find another way around it. I'll try it on another system first. If you all can think of anything else...please let me know!

Thanks

 

[raskew]

Copy/paste the following into a new query. Provided the table and field names are correct, it should provide what you're after.

SELECT tblLeave.*
FROM tblLeave
WHERE ((([Date Selected]) Between [Depart] And [Return]));

 

[pdx_man]

I'm guessing your issue is not on this particulate. Please post entire SQL.