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...)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"RD" <(E-Mail Removed)> wrote in message
news:1120600646.fbdd20e8b38211dac95c822caada14f8@teranews...
> 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
>

On Tue, 5 Jul 2005 18:39:53 -0400, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

>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...)

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" <(E-Mail Removed)> wrote in message
news:1120604667.0e3cea4496f8726a20a0e97e1a8b86d2@teranews...
> On Tue, 5 Jul 2005 18:39:53 -0400, "Douglas J. Steele"
>>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...)
>
> 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.

What's your exact scenario? The solution may vary, depending on what you're
trying to do.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

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

RD wrote:
> 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
>

That to.

Douglas J. Steele wrote:
> 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...)
>

IsNull() = True

RD wrote:
> On Tue, 5 Jul 2005 18:39:53 -0400, "Douglas J. Steele"
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>
>
>>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...)
>
>
> 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
>

On Tue, 5 Jul 2005 20:58:36 -0400, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

>"RD" <(E-Mail Removed)> wrote in message
>news:1120604667.0e3cea4496f8726a20a0e97e1a8b86d2@teranews...
>> On Tue, 5 Jul 2005 18:39:53 -0400, "Douglas J. Steele"
>>>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...)
>>
>> 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.
>
>What's your exact scenario? The solution may vary, depending on what you're
>trying to do.

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