Between Function with Date

  • Thread starter Thread starter Aurora
  • Start date Start date
A

Aurora

I am using Access 2000

I have a report based on query that uses a calculated
field to figure a future date[NextDate] - based on a date
field [CalDate] added with a number of days [days]. I
want my report to only pull the records with a future date
in the next month. In the query [NextDate] field I used
the "Between/and" function to allow the user to input the
dates required. But this did not work - it only gave me a
blank record. But if I use > date and < date, it pulls up
the records I want in the query. But I have to make this
report simple for people who do not know Access. Does
anyone have a suggestion as to how to accomplish what I
want???????????????/

Aurora
 
Aurora

Please post the SQL statement of the suspect query (using "Between...").
For comparison, you could also post the SQL statement of the version that
works.

Which version of Access?

Jeff Boyce
<Access MVP>
 
Jeff -- Thank you for trying to help me. I really
appreciate it. I am using Access 2000.

I hope I pulled the information you wanted. While I can
run Access I am not proficient at SQL --


This is the SQL Statement for the Cal field [NextCalDate]
"Gage Infor" is one DB used for equip information
and "CalibrationData" is the 2nd Db used to record the
calibration for each machine.(one to many relationship)
"Freq" is the number of days to add to "[lastcaldate] to
calculate the [nextcaldate].


SELECT [Gage Infor].SerialID, Max(CalibrationData.CalDate)
AS MaxOfCalDate, CalibrationData.OldSerialID, [Gage
Infor].LastCalDate, [Gage Infor].Freq, [Gage
Infor].FreqNote, ([Gage Infor].[Freq]+[Gage Infor].
[LastCalDate]) AS NextCalDate, [Gage Infor].Type, [Gage
Infor].Desc, [Gage Infor].[Clock No], [Gage Infor].Dept
FROM [Gage Infor] INNER JOIN CalibrationData ON [Gage
Infor].SerialID = CalibrationData.Serial
GROUP BY [Gage Infor].SerialID,
CalibrationData.OldSerialID, [Gage Infor].LastCalDate,
[Gage Infor].Freq, [Gage Infor].FreqNote, ([Gage Infor].
[Freq]+[Gage Infor].[LastCalDate]), [Gage Infor].Type,
[Gage Infor].Desc, [Gage Infor].[Clock No], [Gage
Infor].Dept;

I used this "between" formula to stipulate the dates I
want.

Between [Enter Beg Date] And [Enter End Date]

Does this help you.
 
Aurora

I didn't see a WHERE clause in the SQL statement. That's "where" the
"Between ... And ..." would show up.

Can you run the query successfully using "hard coded" dates?
 
Jeff:
I am not sure of what you are talking about? Would you
like to e-mail me directly at (e-mail address removed) and
explain what you are looking for? If this would not be
proper I understand. I will keep trying to work around my
problem.

Aurora
 
Aurora

I may not be understanding.

For a query to "find" a range of dates, you need to tell it what range as a
Criterion. In the design mode, the Between ... And statement would need to
go in the Criterion row, under the field that holds the date you want to
search on.

In a SQL statement, that is shown as a "WHERE..." clause.
 
This is what I did originally. I used the Between/And
statement in the criteria line of the [nextcaldate]field,
which is a calculated field. But only got blank records.
Then I tried ">date and <date" in the criteria of the same
field and got the response I wanted. But doing it this
way would not be easy for people who do not know Access at
all.

Do you have any other suggestions??

Aurora
 
What happens if you enter specific values in the "Between " statement? For
example,

Between 1/1/2002 and 1/1/2003

(use dates you know will generate results).

((and I'm still curious why your SQL had no WHERE clause?!)
 
Jeff:

I sent you the SQL statement for the following calculated
field in my query. This field determins the next date
calibration is due for each piece of equipment. [Freq] =
# of day inbetween calibrations & [lastcaldate] is the
date the last calibration was done.

NextCalDate: ([Gage Infor].[Freq]+[Gage Infor].
[LastCalDate])

In the criteria of this field, I put between #01/01/04#
and #02/28/04#. The result was nothing - no records.

If I put >#01/01/04# and <#03/01/04# in the criterial of
this same field - it pulls up the correct 5 records.

But I want this query to ask the user to put in the dates
because this will change each month.

Aurora
 
Aurora

<skritch, skritch, ...> (sound of puzzled head-scratching)

I'm not sure why the "Between ..." statement isn't working for you.

You may have already done this, but if not, give it a go -- when a query (or
report or form) starts misbehaving, I will, after exhausting normal fixes,
try starting over from scratch.

If you build a totally new query, ...?
 
Try forcing this to be a date by using either CDate or DateValue

NextCalDate: DateValue([Gage Infor].[Freq]+[Gage Infor].[LastCalDate])


Jeff:

I sent you the SQL statement for the following calculated
field in my query. This field determins the next date
calibration is due for each piece of equipment. [Freq] =
# of day inbetween calibrations & [lastcaldate] is the
date the last calibration was done.

NextCalDate: ([Gage Infor].[Freq]+[Gage Infor].
[LastCalDate])

In the criteria of this field, I put between #01/01/04#
and #02/28/04#. The result was nothing - no records.

If I put >#01/01/04# and <#03/01/04# in the criterial of
this same field - it pulls up the correct 5 records.

But I want this query to ask the user to put in the dates
because this will change each month.

Aurora
-----Original Message-----
What happens if you enter specific values in the "Between " statement? For
example,

Between 1/1/2002 and 1/1/2003

(use dates you know will generate results).

((and I'm still curious why your SQL had no WHERE clause?!)

--
Good luck

Jeff Boyce
<Access MVP>

.
Click to expand...
Click to expand...
 
Back
Top