Invalid Use of Null

[hkimpact]

Good morning,

Let me start with this .... :banghead:

I am trying to do a simple query that pulls data from a date range using the BETWEEN operator. Simple right? Apparently not as simple as I thought.

So I have 4 fields I am pulling info from. RSID, PCounty, Type, and RDate. For the date field on my query it looks like this:

ReferralDate: CDate([RDate])

The Criteria looks something like this. *Note: I will be using a form for the user to select the dates, I am just using these 2 dates as a test for my query.

Between CDate("01/01/2001") And CDate("01/01/2013")

After I run my query it pulls the exact data I want. But the problem is that when I go to sort it, I get an error that says "Invalid Use of Null".

Any ideas guys? I will post my SQL too if that helps. Thanks in advance for any leads to a solution.

SELECT tblReferalSource.RSID, tblParticipantInfo.PCounty, tblReferalSource.Type, CDate([RDate]) AS ReferralDate
FROM tblParticipantInfo INNER JOIN tblReferalSource ON tblParticipantInfo.ParticipantID = tblReferalSource.ParticipantID
WHERE (((CDate([RDate])) Between CDate("01/01/2001") And CDate("01/01/2013")));

 

[hkimpact]

One thing I do notice while playing around with this is that this has to do with the CDate function. If I don't convert anything using CDate, then I get all the same data apparently.

 

[hkimpact]

Ok Well this was pointless of me to post this on here because I have figure it out myself. Here was what I did to resolve the problem in case anyone out there runs into this problem.

In my table with the date I had it set as a TEXT type. This was a mistake, as we know it's a date/time field. After this was changed, everything worked flawlessly.