I have a date/time field that I need to execute a date range search. The
field always has a date, but may not have a time. When I execute a between
date criteria I only get a few item when there are 100's in the range. Any
sugestions?

thanks
Bank

A dateTime field always has a time. The time is midnight if no time is input.

When it fails what is the date range you are looking for? If it is a one-day
range the problem may be that the field does actually have a time in it and your
format settings are "hiding" the time from you.

When you get just a few items, are they in the date range you have specified?

Bank wrote:
>
> I have a date/time field that I need to execute a date range search. The
> field always has a date, but may not have a time. When I execute a between
> date criteria I only get a few item when there are 100's in the range. Any
> sugestions?
>
> thanks
> Bank

John,
I am doing a between request using a start and end date. The test is for
one year. I receive items only for the year range. Some items retreived
have the date and time, some only show the date. I get 15 items when there
are hundreds with a date, no time, entered within the date range.

Thanks,
Scott

"John Spencer" wrote:

> A dateTime field always has a time. The time is midnight if no time is input.
>
> When it fails what is the date range you are looking for? If it is a one-day
> range the problem may be that the field does actually have a time in it and your
> format settings are "hiding" the time from you.
>
> When you get just a few items, are they in the date range you have specified?
>
> Bank wrote:
> >
> > I have a date/time field that I need to execute a date range search. The
> > field always has a date, but may not have a time. When I execute a between
> > date criteria I only get a few item when there are 100's in the range. Any
> > sugestions?
> >
> > thanks
> > Bank
>

Can you post the actual SQL?

Also, check and make sure that datetime field is actually a date time field and
not a text field containing a string that looks like a date and a time.

One thing you might try

Field: RealDate: IIF([YourDateField] is Not Null,DateValue([YourDateField]),Null)
Criteria: Between #2005/01/01# and #2005/12/31#

That does two things - the calculated REALDate forces the value in the field to
be a date.
The criteria uses a date format that must be interpreted as Jan 1 2005 to Dec 31 2005.

If this works, then you can try entering dates in your standard method. If that
works, then you should be reasonably sure that the date you are searching for is
being treated as a string.



Bank wrote:
>
> John,
> I am doing a between request using a start and end date. The test is for
> one year. I receive items only for the year range. Some items retreived
> have the date and time, some only show the date. I get 15 items when there
> are hundreds with a date, no time, entered within the date range.
>
> Thanks,
> Scott
>
> "John Spencer" wrote:
>
> > A dateTime field always has a time. The time is midnight if no time is input.
> >
> > When it fails what is the date range you are looking for? If it is a one-day
> > range the problem may be that the field does actually have a time in it and your
> > format settings are "hiding" the time from you.
> >
> > When you get just a few items, are they in the date range you have specified?
> >
> > Bank wrote:
> > >
> > > I have a date/time field that I need to execute a date range search. The
> > > field always has a date, but may not have a time. When I execute a between
> > > date criteria I only get a few item when there are 100's in the range. Any
> > > sugestions?
> > >
> > > thanks
> > > Bank
> >

Hello,
I have a database that maintainthe equipments. I want to be able to run a
report to show me all the equipments that are due within 30 days for
maintenance
Hope some bodyy can help me with that.
Right now on my query I have <30, which doesn't do anything:-(

Thanks alot
--
camilia


"Bank" wrote:

> I have a date/time field that I need to execute a date range search. The
> field always has a date, but may not have a time. When I execute a between
> date criteria I only get a few item when there are 100's in the range. Any
> sugestions?
>
> thanks
> Bank

Do you have a datetime field that shows when maintenance is due?
Assumptions:
Datetime field that specifies date due for maintenance.
You want those items that have maintenance scheduled in the next (future) 30
days.

Field: DateDue
Criteria: Between Date() and Date()+30

If you want those that are overdue (for the last 30 days)
Criteria: Between Date()-30 and Date()

If you want those that are overdue at 30 or more days
Criteria: <Date()-30


"camilia" <(E-Mail Removed)> wrote in message
news:F6984FCB-EEB7-4CDA-BE82-(E-Mail Removed)...
> Hello,
> I have a database that maintainthe equipments. I want to be able to run a
> report to show me all the equipments that are due within 30 days for
> maintenance
> Hope some bodyy can help me with that.
> Right now on my query I have <30, which doesn't do anything:-(
>
> Thanks alot
> --
> camilia
>
>
> "Bank" wrote:
>
>> I have a date/time field that I need to execute a date range search. The
>> field always has a date, but may not have a time. When I execute a
>> between
>> date criteria I only get a few item when there are 100's in the range.
>> Any
>> sugestions?
>>
>> thanks
>> Bank

Thanks for your respond
The MaintDueDate is set up as a text in my table
I tried Between Date() and Date()+30 and it shows me one equiment that was
due in Feb and couple equipments in March.
I need to run a report every month to get all the equipments that are due in
that month.
I have been working on this for a long time and I am not getting anywhere:-(
--
camilia


"John Spencer" wrote:

> Do you have a datetime field that shows when maintenance is due?
> Assumptions:
> Datetime field that specifies date due for maintenance.
> You want those items that have maintenance scheduled in the next (future) 30
> days.
>
> Field: DateDue
> Criteria: Between Date() and Date()+30
>
> If you want those that are overdue (for the last 30 days)
> Criteria: Between Date()-30 and Date()
>
> If you want those that are overdue at 30 or more days
> Criteria: <Date()-30
>
>
> "camilia" <(E-Mail Removed)> wrote in message
> news:F6984FCB-EEB7-4CDA-BE82-(E-Mail Removed)...
> > Hello,
> > I have a database that maintainthe equipments. I want to be able to run a
> > report to show me all the equipments that are due within 30 days for
> > maintenance
> > Hope some bodyy can help me with that.
> > Right now on my query I have <30, which doesn't do anything:-(
> >
> > Thanks alot
> > --
> > camilia
> >
> >
> > "Bank" wrote:
> >
> >> I have a date/time field that I need to execute a date range search. The
> >> field always has a date, but may not have a time. When I execute a
> >> between
> >> date criteria I only get a few item when there are 100's in the range.
> >> Any
> >> sugestions?
> >>
> >> thanks
> >> Bank
>
>
>

So if you run the report in February of 2006 you want to show all equipment
with a MaintDueDate in February and if you run the report in July 2007 then
you want all equipment with a MaintDueDate in July of 2007.

If that is correct

Field: MaintDueDate
Criteria: Between DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())=1,0)

That will get every MaintDueDate record in the current month of the current
year.

"camilia" <(E-Mail Removed)> wrote in message
news:A3EFC1CB-7B53-4E7A-B7CC-(E-Mail Removed)...
> Thanks for your respond
> The MaintDueDate is set up as a text in my table
> I tried Between Date() and Date()+30 and it shows me one equiment that
> was
> due in Feb and couple equipments in March.
> I need to run a report every month to get all the equipments that are due
> in
> that month.
> I have been working on this for a long time and I am not getting
> anywhere:-(
> --
> camilia
>
>
> "John Spencer" wrote:
>
>> Do you have a datetime field that shows when maintenance is due?
>> Assumptions:
>> Datetime field that specifies date due for maintenance.
>> You want those items that have maintenance scheduled in the next (future)
>> 30
>> days.
>>
>> Field: DateDue
>> Criteria: Between Date() and Date()+30
>>
>> If you want those that are overdue (for the last 30 days)
>> Criteria: Between Date()-30 and Date()
>>
>> If you want those that are overdue at 30 or more days
>> Criteria: <Date()-30
>>
>>
>> "camilia" <(E-Mail Removed)> wrote in message
>> news:F6984FCB-EEB7-4CDA-BE82-(E-Mail Removed)...
>> > Hello,
>> > I have a database that maintainthe equipments. I want to be able to run
>> > a
>> > report to show me all the equipments that are due within 30 days for
>> > maintenance
>> > Hope some bodyy can help me with that.
>> > Right now on my query I have <30, which doesn't do anything:-(
>> >
>> > Thanks alot
>> > --
>> > camilia
>> >
>> >
>> > "Bank" wrote:
>> >
>> >> I have a date/time field that I need to execute a date range search.
>> >> The
>> >> field always has a date, but may not have a time. When I execute a
>> >> between
>> >> date criteria I only get a few item when there are 100's in the range.
>> >> Any
>> >> sugestions?
>> >>
>> >> thanks
>> >> Bank
>>
>>
>>

Hi John,
Yes, when I run the report lets say on March 1st, I want to see all the
equipments that are due in March and same thing in April
I tried what you suggested to do, but it gave me all the records in December.
Am I missing something???
Thanks alot for your help
--
camilia


"John Spencer" wrote:

> So if you run the report in February of 2006 you want to show all equipment
> with a MaintDueDate in February and if you run the report in July 2007 then
> you want all equipment with a MaintDueDate in July of 2007.
>
> If that is correct
>
> Field: MaintDueDate
> Criteria: Between DateSerial(Year(Date()),Month(Date()),1) And
> DateSerial(Year(Date()),Month(Date())=1,0)
>
> That will get every MaintDueDate record in the current month of the current
> year.
>
> "camilia" <(E-Mail Removed)> wrote in message
> news:A3EFC1CB-7B53-4E7A-B7CC-(E-Mail Removed)...
> > Thanks for your respond
> > The MaintDueDate is set up as a text in my table
> > I tried Between Date() and Date()+30 and it shows me one equiment that
> > was
> > due in Feb and couple equipments in March.
> > I need to run a report every month to get all the equipments that are due
> > in
> > that month.
> > I have been working on this for a long time and I am not getting
> > anywhere:-(
> > --
> > camilia
> >
> >
> > "John Spencer" wrote:
> >
> >> Do you have a datetime field that shows when maintenance is due?
> >> Assumptions:
> >> Datetime field that specifies date due for maintenance.
> >> You want those items that have maintenance scheduled in the next (future)
> >> 30
> >> days.
> >>
> >> Field: DateDue
> >> Criteria: Between Date() and Date()+30
> >>
> >> If you want those that are overdue (for the last 30 days)
> >> Criteria: Between Date()-30 and Date()
> >>
> >> If you want those that are overdue at 30 or more days
> >> Criteria: <Date()-30
> >>
> >>
> >> "camilia" <(E-Mail Removed)> wrote in message
> >> news:F6984FCB-EEB7-4CDA-BE82-(E-Mail Removed)...
> >> > Hello,
> >> > I have a database that maintainthe equipments. I want to be able to run
> >> > a
> >> > report to show me all the equipments that are due within 30 days for
> >> > maintenance
> >> > Hope some bodyy can help me with that.
> >> > Right now on my query I have <30, which doesn't do anything:-(
> >> >
> >> > Thanks alot
> >> > --
> >> > camilia
> >> >
> >> >
> >> > "Bank" wrote:
> >> >
> >> >> I have a date/time field that I need to execute a date range search.
> >> >> The
> >> >> field always has a date, but may not have a time. When I execute a
> >> >> between
> >> >> date criteria I only get a few item when there are 100's in the range.
> >> >> Any
> >> >> sugestions?
> >> >>
> >> >> thanks
> >> >> Bank
> >>
> >>
> >>
>
>
>

Well, in a way you are missing something. I had a typing error. I had an =
sign instead of a + sign. That error caused the computer to search for
everything between Feb 1 2006 and November 30, 2005.

Field: MaintDueDate
Criteria: Between DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)



"camilia" <(E-Mail Removed)> wrote in message
news:0E43DFC1-1AC2-41D1-940F-(E-Mail Removed)...
> Hi John,
> Yes, when I run the report lets say on March 1st, I want to see all the
> equipments that are due in March and same thing in April
> I tried what you suggested to do, but it gave me all the records in
> December.
> Am I missing something???
> Thanks alot for your help
> --
> camilia
>
>
> "John Spencer" wrote:
>
>> So if you run the report in February of 2006 you want to show all
>> equipment
>> with a MaintDueDate in February and if you run the report in July 2007
>> then
>> you want all equipment with a MaintDueDate in July of 2007.
>>
>> If that is correct
>>
>> Field: MaintDueDate
>> Criteria: Between DateSerial(Year(Date()),Month(Date()),1) And
>> DateSerial(Year(Date()),Month(Date())=1,0)
>>
>> That will get every MaintDueDate record in the current month of the
>> current
>> year.
>>
>> "camilia" <(E-Mail Removed)> wrote in message
>> news:A3EFC1CB-7B53-4E7A-B7CC-(E-Mail Removed)...
>> > Thanks for your respond
>> > The MaintDueDate is set up as a text in my table
>> > I tried Between Date() and Date()+30 and it shows me one equiment that
>> > was
>> > due in Feb and couple equipments in March.
>> > I need to run a report every month to get all the equipments that are
>> > due
>> > in
>> > that month.
>> > I have been working on this for a long time and I am not getting
>> > anywhere:-(
>> > --
>> > camilia
>> >
>> >
>> > "John Spencer" wrote:
>> >
>> >> Do you have a datetime field that shows when maintenance is due?
>> >> Assumptions:
>> >> Datetime field that specifies date due for maintenance.
>> >> You want those items that have maintenance scheduled in the next
>> >> (future)
>> >> 30
>> >> days.
>> >>
>> >> Field: DateDue
>> >> Criteria: Between Date() and Date()+30
>> >>
>> >> If you want those that are overdue (for the last 30 days)
>> >> Criteria: Between Date()-30 and Date()
>> >>
>> >> If you want those that are overdue at 30 or more days
>> >> Criteria: <Date()-30
>> >>
>> >>
>> >> "camilia" <(E-Mail Removed)> wrote in message
>> >> news:F6984FCB-EEB7-4CDA-BE82-(E-Mail Removed)...
>> >> > Hello,
>> >> > I have a database that maintainthe equipments. I want to be able to
>> >> > run
>> >> > a
>> >> > report to show me all the equipments that are due within 30 days for
>> >> > maintenance
>> >> > Hope some bodyy can help me with that.
>> >> > Right now on my query I have <30, which doesn't do anything:-(
>> >> >
>> >> > Thanks alot
>> >> > --
>> >> > camilia
>> >> >
>> >> >
>> >> > "Bank" wrote:
>> >> >
>> >> >> I have a date/time field that I need to execute a date range
>> >> >> search.
>> >> >> The
>> >> >> field always has a date, but may not have a time. When I execute
>> >> >> a
>> >> >> between
>> >> >> date criteria I only get a few item when there are 100's in the
>> >> >> range.
>> >> >> Any
>> >> >> sugestions?
>> >> >>
>> >> >> thanks
>> >> >> Bank
>> >>
>> >>
>> >>
>>
>>
>>