Searching table for data between 2 dates

[bloobuoy75]

Hi,

I'm trying to create a query / queries that return data from a table between 2 dates. Quite simple I thought, but the difficulty I'm having is that I'd like to be able to input the initial date myself. I want to return a number of results from the one input.

for example the criteria I'm using is

<=[Enter W/E date] And >[Enter W/E date]-7

and

<=[Enter W/E date]-8 And >[Enter W/E date]-14

and

<=[Enter W/E date]-15 And >[Enter W/E date]-21

and

<=[Enter W/E date]-22 And >[Enter W/E date]-28

etc.

I get an error of 'This expressison is typed incorrectly, or it is too complex to be evaluated.'

[TheSmileyCoder]

Do you get the error when you try to open the query, or only after typing a date?

It may be that you need to ensure that access knows you want to type a date.

Also your criteria does not seem to make sense to me. With AND between your criteria you will get no results returned.



If you want serious help you need to post the entire query AS WRITTEN, i.e. copy&paste it, don't just type it. Its very hard to work on incomplete information.

[zmbd]

bloobuoy75:
What TheSmileyCoder is asking for is the entire SQL used for the query. Before you post the SQL please read: Before Posting (VBA or SQL) Code

[NeoPa]

You have the syntax for your WHERE clause wrong BlooBuoy.

It's hard to explain as you haven't made clear where these are entered, but :

1. Ranges are better served by using :
   

   [X] Between Y And Z

2. When specifying two criteria ANDed together you need to specify both side of the comparison on each side. EG :
   

   ([X] >= Y) AND ([X] <= Z)

Remember Literal DateTimes and Their Delimiters (#) must be specified correctly.

PS. We will probably still need to see your SQL posted as specified in the link Z provided for you.

[TheSmileyCoder]

NeoPa managed to see the issue at hand, that you need to specify they field for each comparison. Its not enough to say:

YourDateField>X and <7

It must be

YourDateField>X and YourDateField<X

Or in your case:

((YourDateField<=[Enter W/E date]-22) And (YourDateField>[Enter W/E date]-28))

Of course alot of time could have been saved had you provided the full set of details from the start.

[leecorp]

Declare @startDate datetime
Declare @EndDate datetime

set @startDate ='2011-01-11 15:54:40.360'
set @EndDate ='2011-01-11 15:54:40.360'

SELECT  Id,Name
FROM  Test
WHERE  AccessDate BETWEEN @startDate AND @EndDate

I hope this may solve your Problem

Good Day

[NeoPa]

Leecorp:
I hope this may solve your Problem

Unlikely, I'm afraid Lee, as this is an Access question, and the syntax of the SQL posted appears to be T-SQL. It's certainly not compatible with Access or Jet.

[zmbd]

A Lot has been posted here; however, all based on our best guess....

Having re-read bloobuoy75 OP.... it sounds like OP is trying to use ONE query to return multiple recordsets: This simply will not work using the OP's implied method. There are some outer joins involved here to achieve the combined recordsets - and nothing OP has posted suggests that this is the case nor that OP has any knowledge of such a join.

bloobuoy75:
Post your sql as without it your question becomes one of supposition and not one of substance and I for one do not have the time to play 20 questions.

[NeoPa]

I'm afraid many members register simply to post one question, which they then proceed to fire and forget. Others fire multiple questions, but with that same attitude. You can't always rely on them ever to respond even in their own question threads.

One can never be sure in advance of course, but be wary of holding your breath with new members.

[zmbd]

That's OK... the "Z" is naturally blue ;-)