Search between two date fields

[ross_burrows]

Hi,

I have a table with a from and to date field.

I want a query that will allow the user to select a date and it will show all the records that are within the from and to date range.

Is this possible?

 

[RuralGuy]

The answer is yes. What part are you having problems with?

 

[AC5FF]

I'm by no means an Access guru; I barely consider myself an Access Newbie!
But If I read your original post correctly, couldn't you do this:

Between

![from] and

![to]
?

 

[ross_burrows]

I'm by no means an Access guru; I barely consider myself an Access Newbie!
But If I read your original post correctly, couldn't you do this:

Between

![from] and

![to]
?

Thanks, I will give that a go.

 

[ross_burrows]

The answer is yes. What part are you having problems with?

I have a date started field and a date ended field. I want the user to entere a date and it will show all records that are within the date.

 

[RuralGuy]

So you need assistance writing the query? Have you used the query builder yet?

 

[ross_burrows]

So you need assistance writing the query? Have you used the query builder yet?

Yes please.

 

[Brianwarnock]

Interesting , normally the records have a date and the user selects a start and end date to apply to that field and are thus able to use the Between... And... Criteria function. In your case the record has 2 fields and the user selects 1 date

In the query grid for the Start field enter criteria <= Selecteddate
and in the To field >= Selecteddate on the same criteria row thus creating an AND condition.

Brian

 

[rhett7660]

Brian..

I tried what you mentioned and it didn't work. I inherted a database that has two fields: DateFrom and DateTo.

I put the

<= SelectedDate in the DateFrom
>= SelectedDate in the DateTo

Both are on the Criteria Line. Is there something else I should be doing?

I tried this:
<=[DateFrom]
>=[DateTo]

It shows all the dates but I can't pick the dates. Does this make sense?

 

[Brianwarnock]

Crickey this is a blast from the past, I only came on to check emails before leaving on holiday in 30 mins, The criteria is the wrong way round should be >=datefrom and <= dateto, sorry about that.

Brian

 

[rhett7660]

Brian..

Yeah I know.... This is the only one I could find that was related to what I needed to do. Thanks for the update and I will let you know how it works.

 

[rhett7660]

Brian...

I put in what said in the criteria and I am getting the following error:

"Data type Mismatch in criteria expression". I checked the fields are set to date just to make sure.

 

[rhett7660]

Brian..

With a little more tinkering I was able to get it to work.

Thanks a bunch!