Searching between Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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


KARL DEWEY said:
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 said:
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?
Click to expand...
Click to expand...
 
Your form has to be open, is it?

Christine said:
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


KARL DEWEY said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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.

KARL DEWEY said:
Your form has to be open, is it?

Christine said:
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


KARL DEWEY said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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.

Christine said:
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.

KARL DEWEY said:
Your form has to be open, is it?

Christine said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top