Database query, some trouble getting values from nullable fields

Question

I'm working with a huge Access (.mdb) database, for my bad luck I can't change any table design, just able to create queries, well...

Having this table (part of):

Table1

Id       Autonumber
Name     Text(50) Not Null
Prod     Text(8) Null

where Prod field is a date in format "ddMMyyyy", and not required.

With values like this:

Id   Name   Prod
------------------------
1    KX_W   06061988
2    AXR    (null)
3    ELR    03021957

Then I'm trying this query, to get records according to a date:

PARAMETERS [@basedate] Date;

SELECT
    Table1.Id,
    Table1.Name
FROM
    Table1
WHERE
    ((Table1.Prod) Is Not Null) AND
    (GetDate(Table1.Prod) >= [@basedate])
ORDER BY
    Table1.Id;

*GetDate() is a VBA module function that returns a Date value (dd/MM/yyyy) from a string argument.

When executing the query, I'm getting this message: "This expression is typed incorrectly or it is too complex to be evaluated..."

The table is about 50K-record sized, but I'm pretty sure that's not the reason, there are other queries around this database, 10x more complex and run very quick!

I've tried with a piece of this table having no null values on Prod field and works fine, but when I try with the entire table, the message pop up; what is that I'm missing?

Do you run other queries using this GetDate() function, and if so, do they function properly? — Mark C.
@OverMind sure there is a lot of queries using it, but this is the only one sending null values to it, even with ((Table1.Prod) Is Not Null) condition. — Shin
Would it be easier to format the @basedate value as ddMMyyyy and compare it to table1.prod & "" (converting nulls to blank) and skip the call to getdate() entirely? — Beth

Mark C. Mark C. · Accepted Answer · 2014-06-26T18:08:06

As a last resort, you can try to use a nested subquery.

SELECT SUB.ID, SUB.Name
FROM
(SELECT t.ID, t.Name, t.Prod WHERE t.Prod Is Not Null) AS SUB
WHERE GetDate(SUB.Prod) >= [@basedate]
ORDER BY SUB.ID

Database query, some trouble getting values from nullable fields

1 Answers