Open Form Displaying Records that Meet the Criteria selected

[Bubu85238]

Hi,

I am trying to open a form in Access 2003, based on the criteria selected in
another form.

Here are my TABLES and fields:
SEARCH: date, time (all text fields)
TIMESLOTS: id, date, time (all text fields)

Here are the FORMS and fields:
SEARCH: date, time
SEARCH RESULTS: id, date, time

What I would like to do is open the SEARCH form and select a date, module,
and time; then press a button to open the SEARCH RESULTS form displaying only
the records that meet the criteria selected in the SEARCH form.

I have tried using a query and building the SEARCH RESULTS form from the
query, where the query included conditions on the date and time fields to
select the records that match the date and time fields from the SEARCH form.
Although I dont get an error when I use a button to open the form; I get no
results. If I open the form by opening it directly I get a pop ups asking me
to enter the date and the time and then I do get valid records in the SEARCH
RESULTS form.

What can I do to get the form to open with valid records using a button?

Thanks in advance for the help.

 

[Rick Brandt]

Hi,

I am trying to open a form in Access 2003, based on the criteria
selected in another form.

Here are my TABLES and fields:
SEARCH: date, time (all text fields)
TIMESLOTS: id, date, time (all text fields)

Here are the FORMS and fields:
SEARCH: date, time
SEARCH RESULTS: id, date, time

What I would like to do is open the SEARCH form and select a date,
module, and time; then press a button to open the SEARCH RESULTS form
displaying only the records that meet the criteria selected in the
SEARCH form.

I have tried using a query and building the SEARCH RESULTS form from
the query, where the query included conditions on the date and time
fields to select the records that match the date and time fields from
the SEARCH form. Although I dont get an error when I use a button to
open the form; I get no results. If I open the form by opening it
directly I get a pop ups asking me to enter the date and the time and
then I do get valid records in the SEARCH RESULTS form.

What can I do to get the form to open with valid records using a
button?

Thanks in advance for the help.

The words "date" and "time" are reserved words (both are the names of
functions) and as such should not be used as the names of any object you
create. Access can easily get confused between whether your query or code
is looking for YOUR date or the built in function named date.

Surrounding such field names with square brackets in queries and expressions
makes them okay to use, but a better practice is to always use names like
RecordDate, PurchaseDate, or similar so that you don't have to worry about
it.

Also to be considered is that DateTimes in Access are stored as Double
numeric types and there can be rounding that occurs to the value you "see"
compared to what is actually stored. This can make exact comparison
criteria a problem. You might have to use a greater-than and less-than
criteria on a very small range to get the comparisons to match up. This
issue only applies in your case to the time field and not the date. Note
that a single field containing both date and time is generally a better way
to store a date and time.

 

[Bubu85238]

Thanks for the information. I'm using a text field for both date and time,
the reason i did thi is because I am importing the date and time values into
the table from an Excel spreadsheet where I have manually typed them in, in
the format that I want to see them in my database. I will try putting the
name fields in brackets and see if that works with my query.

Thanks!

Jose