Searching between Dates

[Christine]

In my database I have a search form with all the different categories and
drop down menus to choose from.

There is a place to fill in the Date from and Date to to search in regard to
the date the document that the record referes to was created.

In my main query I have
Field: Date
Table: Main [this is the only data table so its right!]
Criteria: >=[FORMS]![F1]![TEXT10] And <=[FORMS]![F1]![TEXT12]

But when I go to search for XYZ Client records between two dates.. nothing
comes up when I know there is a document in the main table I can find it
manually. It only comes up if I look up the date in the main table and then
type it in to both the date to and from field example I know there is a
document for XYZ Client created on 8/17/2004... it will only come up if I
search for XYZ Client 8/17/2004 to 8/17/2004.... if i move the start or end
date to expand the range of date my query doesn't find anything.... ANY
SUGGESTIONS?

 

[KARL DEWEY]

Is the datatype of the field in which you store the created date a datetime
field or a text field.
Better that using >=[FORMS]![F1]![TEXT10] And <=[FORMS]![F1]![TEXT12] would
be Between [FORMS]![F1]![TEXT10] And [FORMS]![F1]![TEXT12]

Also remember that if the date was loaded using Now() it will have a time
component and any time after midnight is a numberlarger than the date alone.

 

[Christine]

When I put the criteria as your suggestion when you submit the form it now
prompts me to enter in a date parameter value - which it shouldn't

there is no time it is just the date typed in, formated as mm/dd/yyyy

Is the datatype of the field in which you store the created date a datetime
field or a text field.
Better that using >=[FORMS]![F1]![TEXT10] And <=[FORMS]![F1]![TEXT12] would
be Between [FORMS]![F1]![TEXT10] And [FORMS]![F1]![TEXT12]

Also remember that if the date was loaded using Now() it will have a time
component and any time after midnight is a numberlarger than the date alone.

In my database I have a search form with all the different categories and
drop down menus to choose from.

There is a place to fill in the Date from and Date to to search in regard to
the date the document that the record referes to was created.

In my main query I have
Field: Date
Table: Main [this is the only data table so its right!]
Criteria: >=[FORMS]![F1]![TEXT10] And <=[FORMS]![F1]![TEXT12]

But when I go to search for XYZ Client records between two dates.. nothing
comes up when I know there is a document in the main table I can find it
manually. It only comes up if I look up the date in the main table and then
type it in to both the date to and from field example I know there is a
document for XYZ Client created on 8/17/2004... it will only come up if I
search for XYZ Client 8/17/2004 to 8/17/2004.... if i move the start or end
date to expand the range of date my query doesn't find anything.... ANY
SUGGESTIONS?

 

[KARL DEWEY]

Your form has to be open, is it?

When I put the criteria as your suggestion when you submit the form it now
prompts me to enter in a date parameter value - which it shouldn't

there is no time it is just the date typed in, formated as mm/dd/yyyy

Is the datatype of the field in which you store the created date a datetime
field or a text field.
Better that using >=[FORMS]![F1]![TEXT10] And <=[FORMS]![F1]![TEXT12] would
be Between [FORMS]![F1]![TEXT10] And [FORMS]![F1]![TEXT12]

Also remember that if the date was loaded using Now() it will have a time
component and any time after midnight is a numberlarger than the date alone.

In my database I have a search form with all the different categories and
drop down menus to choose from.

There is a place to fill in the Date from and Date to to search in regard to
the date the document that the record referes to was created.

In my main query I have
Field: Date
Table: Main [this is the only data table so its right!]
Criteria: >=[FORMS]![F1]![TEXT10] And <=[FORMS]![F1]![TEXT12]

But when I go to search for XYZ Client records between two dates.. nothing
comes up when I know there is a document in the main table I can find it
manually. It only comes up if I look up the date in the main table and then
type it in to both the date to and from field example I know there is a
document for XYZ Client created on 8/17/2004... it will only come up if I
search for XYZ Client 8/17/2004 to 8/17/2004.... if i move the start or end
date to expand the range of date my query doesn't find anything.... ANY
SUGGESTIONS?

 

[Christine]

I'm not quite sure what you mean by that statement - my form has to be open
for me to type in the dates and click the submit button.

Your form has to be open, is it?

When I put the criteria as your suggestion when you submit the form it now
prompts me to enter in a date parameter value - which it shouldn't

there is no time it is just the date typed in, formated as mm/dd/yyyy

Is the datatype of the field in which you store the created date a datetime
field or a text field.
Better that using >=[FORMS]![F1]![TEXT10] And <=[FORMS]![F1]![TEXT12] would
be Between [FORMS]![F1]![TEXT10] And [FORMS]![F1]![TEXT12]

Also remember that if the date was loaded using Now() it will have a time
component and any time after midnight is a numberlarger than the date alone.

:

In my database I have a search form with all the different categories and
drop down menus to choose from.

There is a place to fill in the Date from and Date to to search in regard to
the date the document that the record referes to was created.

In my main query I have
Field: Date
Table: Main [this is the only data table so its right!]
Criteria: >=[FORMS]![F1]![TEXT10] And <=[FORMS]![F1]![TEXT12]

But when I go to search for XYZ Client records between two dates.. nothing
comes up when I know there is a document in the main table I can find it
manually. It only comes up if I look up the date in the main table and then
type it in to both the date to and from field example I know there is a
document for XYZ Client created on 8/17/2004... it will only come up if I
search for XYZ Client 8/17/2004 to 8/17/2004.... if i move the start or end
date to expand the range of date my query doesn't find anything.... ANY
SUGGESTIONS?

 

[KARL DEWEY]

it now prompts me to enter in a date parameter value
Exactly what is does the prompt say?

Post your SQL statement by opening the query in design view and clicking on
menu VIEW - SQL View. Highlight, copy, and paste in a post.

I'm not quite sure what you mean by that statement - my form has to be open
for me to type in the dates and click the submit button.

Your form has to be open, is it?

When I put the criteria as your suggestion when you submit the form it now
prompts me to enter in a date parameter value - which it shouldn't

there is no time it is just the date typed in, formated as mm/dd/yyyy


:

Is the datatype of the field in which you store the created date a datetime
field or a text field.
Better that using >=[FORMS]![F1]![TEXT10] And <=[FORMS]![F1]![TEXT12] would
be Between [FORMS]![F1]![TEXT10] And [FORMS]![F1]![TEXT12]

Also remember that if the date was loaded using Now() it will have a time
component and any time after midnight is a numberlarger than the date alone.

:

In my database I have a search form with all the different categories and
drop down menus to choose from.

There is a place to fill in the Date from and Date to to search in regard to
the date the document that the record referes to was created.

In my main query I have
Field: Date
Table: Main [this is the only data table so its right!]
Criteria: >=[FORMS]![F1]![TEXT10] And <=[FORMS]![F1]![TEXT12]

But when I go to search for XYZ Client records between two dates.. nothing
comes up when I know there is a document in the main table I can find it
manually. It only comes up if I look up the date in the main table and then
type it in to both the date to and from field example I know there is a
document for XYZ Client created on 8/17/2004... it will only come up if I
search for XYZ Client 8/17/2004 to 8/17/2004.... if i move the start or end
date to expand the range of date my query doesn't find anything.... ANY
SUGGESTIONS?