Problem with Search query (1 Viewer)

[Ramshan]

Hi everyone,

I am running a problem with my search query. The query I have used is,

SELECT Table1.PropertyName, Table1.City, Table1.Address,Table1.County,Table1.Material
FROM Table1
WHERE (Table1.PropertyName Like Nz(Forms!QueryForm1!PropertyName,"") & "*" And Table1.City Like Nz(Forms!QueryForm1!City,"") & "*" And Table1.Address Like Nz(Forms!QueryForm1!Address,"") & "*");

The Table1 has 5 fields and I have used only 3 for search purpose. The query works fine if the 3 fields which I have used for query contains some value in the table. If any of the value is missing in the table, it is not working. How to resolve this. Please help!, Appreciated.

Thanks

 

[Alansidman]

Try it without the NZ function. With the Like and wildcard, I don't think that you need to have the NZ as part of the criteria.

Alan

 

[Ramshan]

Hi Alansidman,

Even after removing Nz function it is not working. I think the Null values in the query form and table is not matching. I dont know how to resolve it. I have attached the sample access file here. Can anyone resolve it?

The datatable Table 1 contains all values filled for first record, one values missing for second record and 2 values missing fir 3rd record,

In the queryform, try searching for "city" = "oklahoma". It won't work because of the Null value issue.

Thanks!

 

[recyan]

Just see if below gives some guidelines :

SELECT 
    Table1.PropertyName, 
    Table1.City, 
    Table1.Address, 
    Table1.County, 
    Table1.Material
FROM 
    Table1
WHERE 
IIf(Len(Trim(Forms!QueryForm1!PropertyName))>0,((Table1.PropertyName) Like (Forms!QueryForm1!PropertyName & "*")),IIf(IsNull(Table1.PropertyName),"",(Table1.PropertyName))) 
And 
IIf(Len(Trim(Forms!QueryForm1!City))>0,((Table1.City) Like (Forms!QueryForm1!City & "*")),IIf(IsNull(Table1.City),"",(Table1.City))) 
And 
IIf(Len(Trim(Forms!QueryForm1!Address))>0,((Table1.Address) Like (Forms!QueryForm1!Address & "*")),IIf(IsNull(Table1.Address),"",(Table1.Address)));

Thanks

 

[vbaInet]

See attached.

 

[recyan]

Update :
Pls refer Brian's post (POST 12) in :

http://www.access-programmers.co.uk/forums/showthread.php?t=245511

Thanks