Help with search PLEASE! Due tomorrow 2

[DarkOne72]

I have a main table with several fields in it and then I have a form to peform 11 different ways to search the data in the table. Some of them are drop downs and some are fields you type what you need too. Below are the fields I have a query built for and the meathod I used:
-------------------------------------------
Like "*" & [Forms]![csearch]![Manufacturer] & "*"
Like "*" & [Forms]![csearch]![Model#] & "*"
Like "*" & [Forms]![csearch]![Serial#] & "*"
Like "*" & [Forms]![csearch]![Condition] & "*"
Like "*" & [Forms]![csearch]![Description] & "*"
Like "*" & [Forms]![csearch]![Ram] & "*"
Between nz([Forms]![CSearch]![DATEMIN],#1/1/2004#) And nz([Forms]![CSearch]![DATEMAX],#1/1/2129#)
Like "*" & [Forms]![csearch]![Jack#] & "*"
Like "*" & [Forms]![csearch]![Location] & "*"
Like "*" & [Forms]![csearch]![Processor] & "*"
Like "*" & [Forms]![csearch]![Status] & "*"
-------------------------------------------------
If I run the search it gives me the return like there aren't any records found which isn't the case I have 810 records. The other fields that are in the table which aren't searchable are also in the query as just SHOW.
Also, if I take outall but 1 search field with formula above it works; other than that it doesn't.
Please help with the correct way for a search string.

Thank you

 

[wmikeh]

Could you copy and paste the sql from your query onto this forum so that I could take a look at it? I don't know if I am following you but I think you are saying that you are using a query with criteria 'like *(field)*' in all of the searchable fields... is that right?

 

[DarkOne72]

Here is the sql statement, it looks long because the only way I ever got the LIKE command to work right is if I add it 2 times under each one eg below:

Field: Manufacturer
Table: Main
Sort:
Show: (check box)
Criteria: Like "*" & ....etc
or: Like "*" & ....etc

---------------------------------
Heres the sql:
---------------------------------
SELECT Main.Manufacturer, Main.[Model#], Main.[Serial#], Main.Condition, Main.Description, Main.Ram, Main.Date, Main.[Jack#], Main.Location, Main.Processor, Main.Status, Main.Size, Main.Reason, Main.[CIFA_P/N], Main.[G/L_Acct#], Main.Total_Orig_Cost, Main.[Computer Name], Main.Department, Main.Comments, Main.VIN
FROM Main
WHERE (((Main.Manufacturer) Like "*" & [Forms]![csearch]![Manufacturer] & "*") AND ((Main.[Model#]) Like "*" & [Forms]![csearch]![Model#] & "*") AND ((Main.[Serial#]) Like "*" & [Forms]![csearch]![Serial#] & "*") AND ((Main.Condition) Like "*" & [Forms]![csearch]![Condition] & "*") AND ((Main.Description) Like "*" & [Forms]![csearch]![Description] & "*") AND ((Main.Ram) Like "*" & [Forms]![csearch]![Ram] & "*") AND ((Main.Date) Between nz([Forms]![CSearch]![DATEMIN],#1/1/2004#) And nz([Forms]![CSearch]![DATEMAX],#1/1/2129#)) AND ((Main.[Jack#]) Like "*" & [Forms]![csearch]![Jack#] & "*") AND ((Main.Location) Like "*" & [Forms]![csearch]![Location] & "*") AND ((Main.Processor) Like "*" & [Forms]![csearch]![Processsor] & "*") AND ((Main.Status) Like "*" & [Forms]![csearch]![Status] & "*")) OR (((Main.Manufacturer) Like "*" & [Forms]![csearch]![Manufacturer] & "*") AND ((Main.[Model#]) Like "*" & [Forms]![csearch]![Model#] & "*") AND ((Main.[Serial#]) Like "*" & [Forms]![csearch]![Serial#] & "*") AND ((Main.Condition) Like "*" & [Forms]![csearch]![Condition] & "*") AND ((Main.Description) Like "*" & [Forms]![csearch]![Description] & "*") AND ((Main.Ram) Like "*" & [Forms]![csearch]![Ram] & "*") AND ((Main.Date) Between nz([Forms]![CSearch]![DATEMIN],#1/1/2004#) And nz([Forms]![CSearch]![DATEMAX],#1/1/2129#)) AND ((Main.[Jack#]) Like "*" & [Forms]![csearch]![Jack#] & "*") AND ((Main.Location) Like "*" & [Forms]![csearch]![Location] & "*") AND ((Main.Processor) Like "*" & [Forms]![csearch]![Processsor] & "*") AND ((Main.Status) Like "*" & [Forms]![csearch]![Status] & "*"))
ORDER BY Main.Manufacturer;

 

[denosaur]

Hi!
If any one of the search criteria that the user enters on the form can be true to be returned in your query, then you should try replacing the current WHERE clause in your SQL statement to the following:
...
WHERE (((Main.Manufacturer) Like "*" & [Forms]![csearch]![Manufacturer] & "*") OR ((Main.[Model#]) Like "*" & [Forms]![csearch]![Model#] & "*") OR ((Main.[Serial#]) Like "*" & [Forms]![csearch]![Serial#] & "*") OR ((Main.Condition) Like "*" & [Forms]![csearch]![Condition] & "*") OR ((Main.Description) Like "*" & [Forms]![csearch]![Description] & "*") OR ((Main.Ram) Like "*" & [Forms]![csearch]![Ram] & "*") OR ((Main.Date) Between nz([Forms]![CSearch]![DATEMIN],#1/1/2004#) OR nz([Forms]![CSearch]![DATEMAX],#1/1/2129#)) OR ((Main.[Jack#]) Like "*" & [Forms]![csearch]![Jack#] & "*") OR ((Main.Location) Like "*" & [Forms]![csearch]![Location] & "*") OR ((Main.Processor) Like "*" & [Forms]![csearch]![Processsor] & "*") OR ((Main.Status) Like "*" & [Forms]![csearch]![Status] & "*"))...

FYI - I eliminated everything in your original WHERE clause to the right of the OR, inclusive of the OR and then replaced the ANDs with ORs.

I hope that helps.

 

[DarkOne72]

I tried replacing the sql statement as you suggested with the above and it gives me an error of "Characters found after end of sql statement" I removed the 3 dots incidently.

However I have tried replacing the AND with OR's and it did not work. Anything else I can try? If this helps a lot of data was from and excel sheet which I copied into the table, if I key in the information it will not find the search; however it does find the records I originally pasted in there. (yes all fields were the same format)
The original post and sql statement I posted earlier is whats working for the above information.

Thanks for the help!

 

[SJMcAbney]

Due tomorrow? Why leave it so late?

Stewart J. McAbney | Talk History

 

[DarkOne72]

Well all was working until I copied the data into database now it won't search properly. I am getting an extension on it though, hehe. So hopefully with the expertise of people in the group I can get this resolved.

 

[denosaur]

Okay...
Try this SQL statement:

SELECT Main.Manufacturer, Main.[Model#], Main.[Serial#], Main.Condition, Main.Description, Main.Ram, Main.Date, Main.[Jack#], Main.Location, Main.Processor, Main.Status, Main.Size, Main.Reason, Main.[CIFA_P/N], Main.[G/L_Acct#], Main.Total_Orig_Cost, Main.[Computer Name], Main.Department, Main.Comments, Main.VIN
FROM Main
WHERE (((Main.Manufacturer) Like "*" & [Forms]![csearch]![Manufacturer] & "*") OR ((Main.[Model#]) Like "*" & [Forms]![csearch]![Model#] & "*") OR ((Main.[Serial#]) Like "*" & [Forms]![csearch]![Serial#] & "*") OR ((Main.Condition) Like "*" & [Forms]![csearch]![Condition] & "*") OR ((Main.Description) Like "*" & [Forms]![csearch]![Description] & "*") OR ((Main.Ram) Like "*" & [Forms]![csearch]![Ram] & "*") OR ((Main.Date) Between nz([Forms]![CSearch]![DATEMIN],#1/1/2004#) And nz([Forms]![CSearch]![DATEMAX],#1/1/2129#)) OR ((Main.[Jack#]) Like "*" & [Forms]![csearch]![Jack#] & "*") OR ((Main.Location) Like "*" & [Forms]![csearch]![Location] & "*") OR ((Main.Processor) Like "*" & [Forms]![csearch]![Processsor] & "*") OR ((Main.Status) Like "*" & [Forms]![csearch]![Status] & "*"))
ORDER BY Main.Manufacturer;

Hope that works out.

 

[DarkOne72]

I tried it and it still returned the same results as in prior posts. it gives the information but everything instead of something like just manufacturers.

thank you though....still working on it

 

[PHV]

And this ?
SELECT Manufacturer, [Model#], [Serial#], Condition, Description, Ram, [Date], [Jack#], Location, Processor, Status, Size, Reason, [CIFA_P/N], [G/L_Acct#], Total_Orig_Cost, [Computer Name], Department, Comments, VIN
FROM Main
WHERE (Manufacturer Like "*" & [Forms]![csearch]![Manufacturer] & "*" OR [Forms]![csearch]![Manufacturer] Is Null)
AND ([Model#] Like "*" & [Forms]![csearch]![Model#] & "*" OR [Forms]![csearch]![Model#] Is Null)
AND ([Serial#] Like "*" & [Forms]![csearch]![Serial#] & "*" OR [Forms]![csearch]![Serial#] Is Null)
AND (...
... Is Null)
AND (Status Like "*" & [Forms]![csearch]![Status] & "*" OR [Forms]![csearch]![Status] Is Null)
ORDER BY Manufacturer;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DarkOne72]

So far so good, however the only one that doesn't work is the Jack # it returns ALL records. I have inlcuded the sql to see if I did something wrong:

SELECT Manufacturer, [Model#], [Serial#], Condition, Description, Ram, [Date], [Jack#], Location, Processor, Status, Size, Reason, [CIFA_P/N], [G/L_Acct#], Total_Orig_Cost, [Computer Name], Department, Comments, VIN
FROM Main
WHERE (([Manufacturer]) Like "*" & Forms!csearch!Manufacturer & "*" Or Forms!csearch!Manufacturer Is Null) And (([Model#]) Like "*" & Forms!csearch![Model#] & "*" Or Forms!csearch![Model#] Is Null) And (([Description]) Like "*" & Forms!csearch!Description & "*" Or Forms!csearch!Description Is Null) And (([Condition]) Like "*" & Forms!csearch!Condition & "*" Or Forms!csearch!Condition Is Null) And (([Location]) Like "*" & Forms!csearch!Location & "*" Or Forms!csearch!Location Is Null) And (([Processor]) Like "*" & Forms!csearch!Processor & "*" Or Forms!csearch!Processor Is Null) And (([Ram]) Like "*" & Forms!csearch!Ram & "*" Or Forms!csearch!Ram Is Null) And (([Jack#]) Like "*" & Forms!csearch![Jack#] & "*" Or Forms!csearch![Jack#] Is Null) And (([Serial#]) Like "*" & Forms!csearch![Serial#] & "*" Or Forms!csearch![Serial#] Is Null) And (([Status]) Like "*" & Forms!csearch!Status & "*" Or Forms!csearch!Status Is Null) And (([Date]) Between nz(Forms!csearch!DATEMIN,#1/1/2004#) And nz(Forms!csearch!DATEMAX,#1/1/2129#))
ORDER BY Manufacturer;

 

[PHV]

What is the data type of Jack # in the Main table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DarkOne72]

All are text except Ram which is number and Date is Date. Also just verified that model # and Serial # are returning all records as well. sorry

thanks for you help by the way

 

[DarkOne72]

good news! the sql you gave earlier worked fine; I had another qry for jack# and it was set wrong. It is all working great! thanks to everyone who help!

Hopefully one day I can repay the favor!

DarkOne