Text Box in Form vs. User-Defined Criteria

[mjbruesch]

Having Problems...I have to write some queries based on a starting
date. Normally I'll set up a text box in the form, have the user enter
the date in that text box and pull the date from there in my query as
such "like Forms.formName.textboxName". This has worked great except
for one of my forms where it takes an inordinate amount of time for
the queries to run. In attempting to troubleshoot the problem I
changed how the date is entered to having the query prompt the user
for the date with the little pop-up box like such [Enter Date:]. One
such query took 4 minutes to run pulling the date from the Form but
took only 20 seconds to run with the date entered in the pop-up. All
queries on the same form have this problem but I don't have this
problem on any other forms I've done this with. I even tried deleting
the text box and recreating it thinking I maybe messed up one of the
settings but still have the same problem. I have the text box format
as Short Date (like the other forms). I even tried defining the
parameter in the query to explicitly tell it that it was a Date/Time
and still didn't help. I've changed all the queries to have the pop-up
instead but would like to use the text box so the user doesn't have to
keep entering the same date over and over for multiple queries.

Any ideas?

 

[kingston via AccessMonster.com]

What version of Access are you using? Try going to Tools -> Options... ->
General and make sure Track name AutoCorrect info is unchecked. Otherwise,
try to recreate the form by renaming it and then making a copy of it with the
original name. You can create a new Access file and import all of the
objects into it to see if the problem persists. Do you have any code
attached to the form that you can step through? If you do, try to /decompile
the file. You've probably tried this, but how about Tools -> Database
Utilities -> Compact and Repair Database...
hth

Having Problems...I have to write some queries based on a starting
date. Normally I'll set up a text box in the form, have the user enter
the date in that text box and pull the date from there in my query as
such "like Forms.formName.textboxName". This has worked great except
for one of my forms where it takes an inordinate amount of time for
the queries to run. In attempting to troubleshoot the problem I
changed how the date is entered to having the query prompt the user
for the date with the little pop-up box like such [Enter Date:]. One
such query took 4 minutes to run pulling the date from the Form but
took only 20 seconds to run with the date entered in the pop-up. All
queries on the same form have this problem but I don't have this
problem on any other forms I've done this with. I even tried deleting
the text box and recreating it thinking I maybe messed up one of the
settings but still have the same problem. I have the text box format
as Short Date (like the other forms). I even tried defining the
parameter in the query to explicitly tell it that it was a Date/Time
and still didn't help. I've changed all the queries to have the pop-up
instead but would like to use the text box so the user doesn't have to
keep entering the same date over and over for multiple queries.

Any ideas?

 

[John W. Vinson]

Any ideas?

Yes. Don't use LIKE on date/time fields!

LIKE is appropriate only when you want to use wildcards to search in a Text
field. A LIKE query without wildcards on a date field forces Access to convert
the date/time field to a text string, and search that text string (without the
benefit of any index on the field).

Just use the default = operator, and be sure there's an index on the date
field in your table. You should get sub-second response unless your table is
truly enormous.

John W. Vinson [MVP]