Null as Character in MS ACCESS 2003 Queries

[shanmugamgsn]

Hi All,



Table T1:

Column a	Column b
1	Sony
	Nokia
3	Samsung
	HTC

I have following query

Select * from Table t1
where t1.a Like ("*" & LTRIM(RTRIM([forms]![Search1]![cboInput1])) & "*")

By default when input is empty i need all values.

Here when my prompt value is empty i'm getting only "Nokia and HTC"
But i wish to get all 4 values. Is it possible?

I know in Oracle when i give

Select * from Table t1
where t1.a Like '%%'

This gives all values in Oracle.
But why not in MS ACCESS?

[JoeM]

I actually only get "Sony and Samsung" to return using your code.

Nulls often give Access fits. Try this variation:
Select * from Table t1
where NZ([a],"") Like ("*" & LTRIM(RTRIM([forms]![Search1]![cboInput1])) & "*")

[shanmugamgsn]

Ok will try and update soon..

I actually only get "Sony and Samsung" to return using your code.

Nulls often give Access fits. Try this variation:
Select * from Table t1
where NZ([a],"") Like ("*" & LTRIM(RTRIM([forms]![Search1]![cboInput1])) & "*")

[shanmugamgsn]

Thanks man that worked out