'Invalid use of Null' error message

[Alc]

The following query runs without issues.

SELECT Payment_Mth, CDate(Payment_mth) AS Expr1
FROM Table1
WHERE (((Payment_Mth) Is Not Null 
And (Payment_Mth)<>"Does not apply" 
And (Payment_Mth)<>""));

I get a set of data along the following lines
September 2007 01/09/2007
October 2007 01/10/2007
September 2007 01/09/2007
etc.
which is what I want.

However, when I try to filter to show only a particular date, I get an error

SELECT Payment_Mth, CDate(Payment_mth) AS Expr1
FROM Table1
WHERE (((Payment_Mth) Is Not Null 
And (Payment_Mth)<>"Does not apply" 
And (NBReferral.Payment_Mth)<>"") 
AND ((CDate(Payment_mth))=#01/10/2007#));

An 'Invalid use of Null' message is displayed.

What am I missing, here?

 

[JEA]

What format are your dates in, UK or US?

I don't know if it's the same with SQL, but VBA doesn't like UK format dates where the day value is less than 12. It tries to convert it to US format but can't decide which is the day value and which is the month.

I don't know much about SQL, so I may be completly wrong, but try using US date formats:

Format(DateVar, "mm/dd/yy")
and
#10/01/2007#

 

[Alc]

Thanks for the response. I've hit that problem in the past, so tried it both ways. No luck, unfortunately - I get exactly the same error.

 

[Brianwarnock]

I'm going to take a guess here

in the first example you are dealing with strings and the use of Null is fine, in the second all dates are checked and its the ((CDate(Payment_mth))=#01/10/2007#)); that is causing the problem when Payment_mth is null, run the filter as a second query when all the Nulls have been filtered out.

Brian

 

[Alc]

Sounded perfectly logical but, unfortunately, didn't help.
I created one query to remove all nulls and change the stored text values into dates, using CDate().
However, when I try to filter the results of that first query, to just show a particular month, I get the same error.

 

[Brianwarnock]

Access must feed the info througth linked queries or summat, however I think we both missed a trick , why not filter on payment_mth eg "October 2007"

Brian

 

[Alc]

I need to search for all records within a given range, I was just using that month as an example.
If I want to find say, January to July, then Feb, March, etc. are being excluded.

I think I've found a way round it.
1) Store the Payment_mth as a number, rather than a string (date format is out, as the phrase 'Does not apply' has to be one of the options for the user)
2) Assign an extremely high value number for 'Does not apply', to exclude it from searches
3) Filter on the number

This appears to work, so I'll go with this.
One of those times when the fix needs to be quick, rather than pretty.

Thanks, both, for the suggestions.

 

[neileg]

Firstly, you are missing a closing bracket.

Next, if you are querying for a specific date, you don't need to test for null or "Does not apply".

Finally, JEA is correct to raise the UK/US date issue. SQL is a US invention and using UK date formats can cause problems. I tend to use an unambiguous date format like 1-Oct-2007 to avoid this. Your situation is compounded by using CDate() on a text field. It would be better to keep your dates as Date/time instead of text and use some other method to record "Does not apply".

 

[GolferGuy]

Rather than just testing for Null, use an IIf test for null, if so, do not keep the record, otherwise test to see if the data will match your criteria. Something like:
IIf(IsNull(Payment_Mth),False, IIf(Payment_Mth<>"DoesNotApply" AND NBReferral.Payment_Mth<>"" AND CDate(Payment_Mth)=#01/07/2007#, True, False))
By putting all of that code into where the field goes, then you can put True in the criteria area and this record will only be accepted if all 4 tests are the way you want it to be.

This way you are only working with a field value that you now is not null.

 

[Rich]

Assign an extremely high value number for 'Does not apply', to exclude it from searches

 

[Alc]

Assign an extremely high value number for 'Does not apply', to exclude it from searches

'November 2007' would be 12008, as it's the first month of FY2008.
'December 2007' would be 22008, as it's the second month of FY2008.
and so on.

'Does not apply' gets set to 100000, thereby excluding it from any searches being carried out on specific date ranges.

 

[Rich]

But is the phrase "does not apply" included somewhere in the record?

 

[Alc]

Not in the stored record, no. It does, however, have to an option available to the user.

It's all working fine, though, using this method, so I'll stick with it.