Using Dates Obtained from a Form

  • Thread starter Thread starter John C.
  • Start date Start date
J

John C.

I have a form which asks the user for start and stop dates
of a query.

I'm using the design view to obtain the needed records.

The criteria for [Date] field is:
Between [Forms]![frmFindTechEntries]![txtDataStart] And
[Forms]![frmFindTechEntries]![txtDateEnd]

It returns no records when I run the query from the form.
If I run the query directly from the query list, and let
it ask me for the start/stop dates, it does retreive the
appropriate records.

Should there be "#" signs somewhere in the criteria?
 
One, it's not a good idea to use Date as the name of a field in a table (or
a control on a form, for that matter). Date is a VBA function, and you can
greatly confuse ACCESS over which is meant when you use Date. Enclosing Date
in [ ] will help avoid confusion, but you must always remember to do it.

Two, you don't need # delimiters in the criterion expression that you have
posted, so long as the controls on the form are (1) formatted as a date/time
format, and/or (2) the local settings for your date format is mm/dd/yy (or
mm/dd/yyyy). If your local setting is something else, then I'd change the
criterion expression to this:
Between CDate(Format[Forms]![frmFindTechEntries]![txtDataStart],
"mm/dd/yy")) And CDate(Format([Forms]![frmFindTechEntries]![txtDateEnd],
"mm/dd/yy"))


Three, is the "Date" field in the table storing date and time, or just date?
If it's also storing time, then it's highly unlikely that a date alone
(which stores a time of midnight with it) will find an exact match for many
of your records.

Post back with more info and let's see how we can get you to the results
that you desire.
 
Thanx Ken,

You point out a concern with the field name "Date". I
agree and will be modifying the name.

The form has text boxes that are formatted for mm/dd/yy.

The txtboxes are only receiving/storing dates, no time
portion.
 
Any chance that either of the textboxes have Null values (no value entered
by the user)? Is the form open when the query runs?
 
The default values are set to current date when the form
opens.

YES, the form is open when the query runs and it is
visible.
 
Long shot, but check spelling of controls on the form and the name of the
form to be sure they match what you've put in the query's criterion
expression.

If that isn't it, zip up a small example of the database with sample data
and form that are showing the problem, and email to me (remove this is not
real from email address) and I'll take a quick look.
 
I placed Msgbox Isdate(txtDateStart) in the code.
It shows true, the txtboxes are evaluating to a date.

I changed the Criteria from "Between..."
to ">=[form][control] And <=[form][control]"

That did not help either.

I ran the query directly, it returns the appropriate
records.

I verified the spelling of the Form and Control names.

Could it be the syntax I'm using for the criteria?...

Between [Forms]![frmFindTechEntries]![txtDateStart] And
[Forms]![frmFindTechEntries]![txtDateEnd]
 
See my reply email address. Remove this is not real from it and then you'll
have my email address.
 
PMFBI
Did you try the "CDate" suggestion in Ken's
first reply or just ignore it because your local
setting was not "something else."
It sounds to me like you just need to declare
the Parameter type (or use "CDate" suggestion).
Apologies again for butting in.
 
Back
Top