Dates not being evaluated right

  • Thread starter Thread starter RD
  • Start date Start date
R

RD

Hi again,

Gaaa! So, I'm comparing two date fields (SQL) and for some reason Access seems
to think 7/1/1999 comes after 6/30/2000. I've run into this occasionally before
and just found other ways to do what I wanted but this time I absolutely have to
use these two date fields. There is no other data that will identify the
records I need to pull. Anyone know why Access does this and how to make it
behave like a good little database?

Thanks for any help or insights,
RD
 
Sounds as though you've got your dates in text fields, not date/time fields.
Either that, or you're using the Format function on the date/time fields
(which converts them to text fields...)
 
Sounds as though you've got your dates in text fields, not date/time fields.
Either that, or you're using the Format function on the date/time fields
(which converts them to text fields...)
Click to expand...

Aaaugh! It's a linked table. I never thought to check. Ok, I've typecast them
to real date type. Now I'm having a hard time checking for Null.

Thanks for the tip,
RD
 
RD said:
Aaaugh! It's a linked table. I never thought to check. Ok, I've
typecast them
to real date type. Now I'm having a hard time checking for Null.
Click to expand...

What's your exact scenario? The solution may vary, depending on what you're
trying to do.
 
If you're not encapsulating the values in pound signs #, then you're
actually evaluating the values of a mathmatical expression

If 7/1/1999 < 6/30/2000 = True Then
becomes
If 0.0035017508754377188594297148574287 < 0.0001 = True Then

Which is false as .0035 is not less than .0001

So the If...Then should be If #7/1/1999# < #6/30/2000#

Depending on the fields and values involved you may need to test if the
value is a date IsDate() or build a date using DateSerial() or CVDate()

David H
 
IsNull() = True
Aaaugh! It's a linked table. I never thought to check. Ok, I've typecast them
to real date type. Now I'm having a hard time checking for Null.

Thanks for the tip,
RD
Click to expand...
 
What's your exact scenario? The solution may vary, depending on what you're
trying to do.
Click to expand...

The old "exact scenario" ploy, eh?

I'm creating an ad hoc report showing a list of people receiving a particular
type of aid that also receive one or both of two other types of aid. Two of the
three types of aid are associated with clients in the table tc_case. However
the third aid type is associated with the client in table tc_person. Table
tc_case has a neat field called "status" so finding "Active" cases is a snap.
There is no such field in tc_person. I have to go by approved_date and
discontinued_date. We get these tables from the state as text file extracts.
There is no changing the tables or the way the state does business so I just
have to deal with what I have.

So, now that I've used CDate() to change the dates from text to date I'm getting
an "invalid use of null" error when I try to compare them. There are many nulls
in both fields.

What I've done is to go back where I cast the fields and applied the Nz()
function to return a zero if null and then formatted that as a Short Date. That
shows up as 12/30/1899. It looks funny but it suits the purpose and the
business client will never see it. I can finally lay this one to rest.

Now I'm off to a requirements meeting for a custom Access application. I get to
build this one from the git-go and have almost complete control over the design.

Big thanks to you and David for your help and suggestions.

Regards,
RD
 
Back
Top