Datediff

  • Thread starter Thread starter MarkH
  • Start date Start date
M

MarkH

I have a basic select query which calculates a datediff
for a named variable On monday this returned the correct
records against a user entered number <=[Enter Number of
Days]. Now, after the long weekend Access seems to have a
mind of its own...the datediff still calculates correctly
lets say the results were 7,8,69,79 and the user entered
70. The records returned are 7,69(instead of the expected
7,8,69)...it is treating the single digit datediff
results as if they were digits in the 'tens' place. Any
ideas other than kill the query and rewrite it? Thanks.
 
MarkH said:
I have a basic select query which calculates a datediff
for a named variable On monday this returned the correct
records against a user entered number <=[Enter Number of
Days]. Now, after the long weekend Access seems to have a
mind of its own...the datediff still calculates correctly
lets say the results were 7,8,69,79 and the user entered
70. The records returned are 7,69(instead of the expected
7,8,69)...it is treating the single digit datediff
results as if they were digits in the 'tens' place. Any
ideas other than kill the query and rewrite it? Thanks.
Click to expand...

Sounds to me as if your numbers are being treated as text rather than
numbers. In that case "8" is greater than "69" and "79". What is the
expression you are using to generate that number? A simple DateDiff should
return an integer, but if you have more than that in the expression it
might be converting the value to text on you. Wrapping the whole thing in
a CLng() function would force it back if you can't resolve it otherwise.
 
Hi thanks for the really quick answer. My expression is
Days To Go: DateDiff("d",Date(),[90 Day Statutory Permit
Date]) with criteria <=[Enter the number of days]. I will
try Clng() but thought i'd go ahead and let you look at
this in case anything jumps out at you. Thanks again.
-----Original Message-----
MarkH said:
I have a basic select query which calculates a datediff
for a named variable On monday this returned the correct
records against a user entered number <=[Enter Number of
Days]. Now, after the long weekend Access seems to have a
mind of its own...the datediff still calculates correctly
lets say the results were 7,8,69,79 and the user entered
70. The records returned are 7,69(instead of the expected
7,8,69)...it is treating the single digit datediff
results as if they were digits in the 'tens' place. Any
ideas other than kill the query and rewrite it? Thanks.
Click to expand...

Sounds to me as if your numbers are being treated as text rather than
numbers. In that case "8" is greater than "69" and "79". What is the
expression you are using to generate that number? A simple DateDiff should
return an integer, but if you have more than that in the expression it
might be converting the value to text on you. Wrapping the whole thing in
a CLng() function would force it back if you can't resolve it otherwise.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


.
Click to expand...
 
And yet another twist...when the criteria is entered by
the user as a single digit, it is evaluated as if it were
ten times higher, e.g. 9 returns the datediff values up
to and including 90...but 10 acts normally...
-----Original Message-----
MarkH said:
I have a basic select query which calculates a datediff
for a named variable On monday this returned the correct
records against a user entered number <=[Enter Number of
Days]. Now, after the long weekend Access seems to have a
mind of its own...the datediff still calculates correctly
lets say the results were 7,8,69,79 and the user entered
70. The records returned are 7,69(instead of the expected
7,8,69)...it is treating the single digit datediff
results as if they were digits in the 'tens' place. Any
ideas other than kill the query and rewrite it? Thanks.
Click to expand...

Sounds to me as if your numbers are being treated as text rather than
numbers. In that case "8" is greater than "69" and "79". What is the
expression you are using to generate that number? A simple DateDiff should
return an integer, but if you have more than that in the expression it
might be converting the value to text on you. Wrapping the whole thing in
a CLng() function would force it back if you can't resolve it otherwise.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


.
Click to expand...
 
FYI for the newsgroup, I had to wrap the Criteria in CLng
() i.e. <=CLng([Enter the number of days])
-----Original Message-----
MarkH said:
I have a basic select query which calculates a datediff
for a named variable On monday this returned the correct
records against a user entered number <=[Enter Number of
Days]. Now, after the long weekend Access seems to have a
mind of its own...the datediff still calculates correctly
lets say the results were 7,8,69,79 and the user entered
70. The records returned are 7,69(instead of the expected
7,8,69)...it is treating the single digit datediff
results as if they were digits in the 'tens' place. Any
ideas other than kill the query and rewrite it? Thanks.
Click to expand...

Sounds to me as if your numbers are being treated as text rather than
numbers. In that case "8" is greater than "69" and "79". What is the
expression you are using to generate that number? A simple DateDiff should
return an integer, but if you have more than that in the expression it
might be converting the value to text on you. Wrapping the whole thing in
a CLng() function would force it back if you can't resolve it otherwise.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


.
Click to expand...
 
Back
Top