Query and dates

  • Thread starter Thread starter Megan
  • Start date Start date
M

Megan

Hello,

I have a query that returns the number of days between two
dates (date hired and date()). I want the query to only
return dates that fall between user specified time frames.

This is what I have:

Date Diff: DateDiff("d",[Hire Date],Date())

([First Date]) And <([Second Date])
Click to expand...

If I take out ([First Date]) and replace it with 5 and
replace ([Second date]) with 30, I get the correct
results. However, the critera as it is above returns only
a blank row.

Any help would be greatly appreciated!
 
If I understand you want, say the actual dates between
oh, January 1, 2003 and January 5, 2003 such as
January 2, 2003
Janaury 3, 2003
January 4, 2003???

If so, no way. Not without a lot of coding, the DateDIff
functions gives an integer value not the actual date. It
converts your dates into the serial numbers for that date
and make the calculation, not really using an actualy
date format value. The only way may be to take the first
date (January 2, 2003) convert to serial number,
consecutively add each number you obtin from the DateDiff
adn then convert back to a Julian date. Good luck!
-----Original Message-----
Hello,

I have a query that returns the number of days between two
dates (date hired and date()). I want the query to only
return dates that fall between user specified time frames.

This is what I have:

Date Diff: DateDiff("d",[Hire Date],Date())

([First Date]) And <([Second Date])
Click to expand...

If I take out ([First Date]) and replace it with 5 and
replace ([Second date]) with 30, I get the correct
results. However, the critera as it is above returns only
a blank row.

Any help would be greatly appreciated!
.
Click to expand...
 
If I understand you want, say the actual dates between
oh, January 1, 2003 and January 5, 2003 such as
January 2, 2003
Janaury 3, 2003
January 4, 2003???

If so, no way. Not without a lot of coding, the DateDIff
functions gives an integer value not the actual date. It
converts your dates into the serial numbers for that date
and make the calculation, not really using an actualy
date format value. The only way may be to take the first
date (January 2, 2003) convert to serial number,
consecutively add each number you obtin from the DateDiff
adn then convert back to a Julian date. Good luck!
-----Original Message-----
Hello,

I have a query that returns the number of days between two
dates (date hired and date()). I want the query to only
return dates that fall between user specified time frames.

This is what I have:

Date Diff: DateDiff("d",[Hire Date],Date())

([First Date]) And <([Second Date])
Click to expand...

If I take out ([First Date]) and replace it with 5 and
replace ([Second date]) with 30, I get the correct
results. However, the critera as it is above returns only
a blank row.

Any help would be greatly appreciated!
.
Click to expand...
 
Megan said:
Hello,

I have a query that returns the number of days between two
dates (date hired and date()). I want the query to only
return dates that fall between user specified time frames.

This is what I have:

Date Diff: DateDiff("d",[Hire Date],Date())

([First Date]) And <([Second Date])
Click to expand...
Click to expand...

Try:

(DateDiff("d",[Hire Date],Date()) > ([First Date])
And DateDiff("d",[Hire Date],Date()) < ([Second Date]))


Sincerely,

Chris O.


If I take out ([First Date]) and replace it with 5 and
replace ([Second date]) with 30, I get the correct
results. However, the critera as it is above returns only
a blank row.

Any help would be greatly appreciated!
Click to expand...
 
Hi,

I tried the suggestion Chris made...but it still does not
work. It seems as though the query is not recongizing the
user entered numbers because if I 'hard wire' the numbers
in, it works fine. If I try and input them through a
message box, it gives me pretty much whatever it wants.
For example if I have as the critera:
[Enter first day]
Click to expand...
and I input 10, it will still give me all of the records,
even those less than 10 days old. If I try >[first day]
and <[last day] I get a blank row (the same results with
between [firts] and [second]).

Is it possible that the user entered numbers are not
recongized by the query?
-----Original Message-----
Hello,

I have a query that returns the number of days between two
dates (date hired and date()). I want the query to only
return dates that fall between user specified time frames.

This is what I have:

Date Diff: DateDiff("d",[Hire Date],Date())

([First Date]) And <([Second Date])
Click to expand...

If I take out ([First Date]) and replace it with 5 and
replace ([Second date]) with 30, I get the correct
results. However, the critera as it is above returns only
a blank row.

Any help would be greatly appreciated!
.
Click to expand...
 
Hi,

I tried the suggestion Chris made...but it still does not
work. It seems as though the query is not recongizing the
Click to expand...


Oh, sorry.

It should be:

((HireDate >= FirstDate) And
(HireDate <= SecondDate))

This can be dropped into the conditional argument of an IIF expression, or
into an IF statement in VBA.


Here's a quick expample in VBA:

Public Sub TestDateBetween()

Dim HireDate As Date
Dim FirstDate As Date
Dim SecondDate As Date

HireDate = InputBox("Enter Hire Date")
FirstDate = InputBox("Enter First Date")
SecondDate = InputBox("Enter Second Date")

If ((HireDate >= FirstDate) And _
(HireDate <= SecondDate)) Then
Debug.Print "True"
Else
Debug.Print "False"
End If

End Sub




user entered numbers because if I 'hard wire' the numbers
in, it works fine. If I try and input them through a
message box, it gives me pretty much whatever it wants.
For example if I have as the critera:
[Enter first day]
Click to expand...
and I input 10, it will still give me all of the records,
even those less than 10 days old. If I try >[first day]
and <[last day] I get a blank row (the same results with
between [firts] and [second]).

Is it possible that the user entered numbers are not
recongized by the query?
-----Original Message-----
Hello,

I have a query that returns the number of days between two
dates (date hired and date()). I want the query to only
return dates that fall between user specified time frames.

This is what I have:

Date Diff: DateDiff("d",[Hire Date],Date())

([First Date]) And <([Second Date])
Click to expand...

If I take out ([First Date]) and replace it with 5 and
replace ([Second date]) with 30, I get the correct
results. However, the critera as it is above returns only
a blank row.

Any help would be greatly appreciated!
.
Click to expand...
Click to expand...
 
Back
Top