My search form displays blank table (1 Viewer)

qendrimgjata

New member
Local time
Today, 10:25
Joined
Feb 20, 2015
Messages
3
Dear access geniuses!
I have made a search form for my company, where all the data is put simply into the tables and i have made a search form to search with the selected fields, it was working at first, but now it just displays blank table when i search.
I am using ms access 2007.
Here is the sql of the query::
Code:
SELECT 
    ClientT.[Project Number], 
    ClientT.[Client Surname], 
    ClientT.[Client Name], 
    ClientT.[Client Tel], 
    ClientT.[Client Email], 
    ClientT.[Client Address],
    ClientT.Council, 
    ClientT.[Planning Ref], 
    ClientT.[House Name/Num], 
    ClientT.Street, 
    ClientT.Area, 
    ClientT.[Town/City], 
    ClientT.County, 
    ClientT.[Project Description], 
    ClientT.[Planning Officer],
    ClientT.[Planner Tel], 
    ClientT.[Planner Email], 
    ClientT.[Decision Target Date], 
    ClientT.[Date Approved], 
    ClientT.[Date Planning Expires], 
    ClientT.Comments, 
    ClientT.[Landlord Details], 
    ClientT.BCCompany,
    ClientT.[BC Ref], 
    ClientT.[BC Officer], 
    ClientT.[BC Tel], 
    ClientT.BCEmail, 
    ClientT.Status, 
    ClientT.[Date Approved1], 
    ClientT.ID
FROM 
    ClientT
WHERE 
    (
        ((ClientT.[Project Number]) Like "*" & [forms]![SearchF]![JobNum] & "*")
        AND 
        ((ClientT.[Client Name]) Like "*" & [forms]![SearchF]![FirstN] & "*")
        AND 
        ((ClientT.Council) Like "*" & [forms]![SearchF]![Council] & "*")
        AND 
        ((ClientT.[House Name/Num]) Like "*" & [forms]![SearchF]![HouseNum] & "*")
        AND 
        ((ClientT.Street) Like "*" & [forms]![SearchF]![Street] & "*")
        AND 
        ((ClientT.Area) Like "*" & [forms]![SearchF]![Area] & "*")
        AND 
        ((ClientT.[Town/City]) Like "*" & [forms]![SearchF]![TownCity] & "*")
        AND 
        ((ClientT.County) Like "*" & [forms]![SearchF]![County] & "*")
        AND 
        ((ClientT.[Planning Officer]) Like "*" & [forms]![SearchF]![Planningofficer] & "*")
        AND 
        ((([ClientT].[Client Surname]) Like "*" & [forms]![SearchF]![SecondN] & "*") Is Null)
        AND 
        ((ClientT.[Post Code]) Like "*" & [forms]![SearchF]![Postcode] & "*")
    )
ORDER BY     
    ClientT.[Project Number];
Is there a problem here?

please help.

thank you!
 
Last edited by a moderator:

qendrimgjata

New member
Local time
Today, 10:25
Joined
Feb 20, 2015
Messages
3
and when i click run query with the search form blank it should display everything should it?
 

namliam

The Mailman - AWF VIP
Local time
Today, 11:25
Joined
Aug 11, 2003
Messages
11,695
The problem is that it wont work well with NULL values, like "**" wont find any null value fields

Plus this line:
Code:
AND ((([ClientT].[Client Surname]) Like "*" & [forms]![SearchF]![SecondN] & "*") Is Null)
Looks pretty bad :(
 

pr2-eugin

Super Moderator
Local time
Today, 10:25
Joined
Nov 30, 2011
Messages
8,494
Deleted duplicate Thread. Please refrain from posting the same question multiple times.
 

qendrimgjata

New member
Local time
Today, 10:25
Joined
Feb 20, 2015
Messages
3
So what should i do? that was a mistake, that line was meant to be the same as the others, not is null
 

Users who are viewing this thread

Top Bottom