Search Query Assistance Please

[DarkOne72]

Hello,

I have 2 table that i need to do a search on. the first table is Laptop_Data which has the following fields:
Service_Tage Model MEMORY PS 2007 SP3 PC_Name PDF_Name Issued_To Supervisor Last_Tech_Stamp Notes

and the 2nd is Laptop_Repair with the following fields:

Date_In Technicians_Name Supervisor Problem_Category Reason_For_Repair Action_Completed Date_Picked-Up IT_Tech Completed Last_User_Stamp

I am trying to create a search screen that will allow me to search by Service_tag, Model, Memory, Supervisor from the 1st table and 3 more from the 2nd table Problem_Category, Completed, Date_In.

The 2 tables are joined together by Service_Tag number as it is the primary key.
I have tried to build the query to do the search but some search options work and others don't and when I add more LIKE "*" statements another portion will work but then others fail. HELP!

I was using the Like command in the format of: Like "*" & [Forms]![csearch]![Service_Tag] & "*"

and had a search screen with unbound forms that would use an event procedure which would then use the query to reutrn the results. I can't seem to get SQL to do it either, any ideas?

Thanks in advance.

 

[HarleyQuinn]

Are you using OR statments between your LIKE's?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[DarkOne72]

I have tried that in the SQL but it didn't work.

 

[HarleyQuinn]

Could you post your SQL?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[DarkOne72]

Sure, thank you for your help!

SELECT DISTINCT Laptop_Data.Service_Tag, Laptop_Data.Model, Laptop_Data.Memory, Laptop_Data.Supervisor, Laptop_Data.Last_User_Stamp, Laptop_Data.PS, Laptop_Data.[2007], Laptop_Data.SP3, Laptop_Data.PC_Name, Laptop_Data.PDF_Link, Laptop_Data.Issued_To, Laptop_Data.Notes
FROM Laptop_Data
WHERE (((Laptop_Data.Service_Tag) Like "*" & [Forms]![csearch]![Service_Tag] & "*"))
OR (((Laptop_Data.Model) Like "*" & [Forms]![csearch]![Model] & "*"))
OR (((Laptop_Data.Memory) Like "*" & [Forms]![csearch]![Memory] & "*"))
OR (((Laptop_Data.Supervisor) Like "*" & [Forms]![csearch]![Supervisor] & "*"))
OR (((Laptop_Data.Last_User_Stamp) Like "*" & [Forms]![csearch]![Last_User_Stamp] & "*"));



I have tried it with OR and also AND still cant get it to work.

Rick

 

[DarkOne72]

Not sure if this helps but some columns have Null values, so maybe an AND Nzexpression might be needed in each.

I don't know just thinking outloud lol.
Rick

 

[dhookom]

Like * will not match a null. You can easily convert any null to a zero-length-string by adding "" to the field:

SELECT DISTINCT Laptop_Data.Service_Tag, Laptop_Data.Model, Laptop_Data.Memory, Laptop_Data.Supervisor, Laptop_Data.Last_User_Stamp, Laptop_Data.PS, Laptop_Data.[2007], Laptop_Data.SP3, Laptop_Data.PC_Name, Laptop_Data.PDF_Link, Laptop_Data.Issued_To, Laptop_Data.Notes
FROM Laptop_Data
WHERE (((Laptop_Data.Service_Tag[b][red] & ""[/red][/b]) Like "*" & [Forms]![csearch]![Service_Tag] & "*")) 
OR (((Laptop_Data.Model[b][red] & ""[/red][/b]) Like "*" & [Forms]![csearch]![Model] & "*")) 
OR (((Laptop_Data.Memory[b][red] & ""[/red][/b]) Like "*" & [Forms]![csearch]![Memory] & "*")) 
OR (((Laptop_Data.Supervisor[b][red] & ""[/red][/b]) Like "*" & [Forms]![csearch]![Supervisor] & "*")) 
OR (((Laptop_Data.Last_User_Stamp[b][red] & ""[/red][/b]) Like "*" & [Forms]![csearch]![Last_User_Stamp] & "*"));

Duane
Hook'D on Access
MS Access MVP

 

[DarkOne72]

I tried the SQL and it is doing the samething mine is. When I run it it just shows all the records instead of the records that contain the search criteria; whether it is all of part of the specfied search.

thanks again for the help.

 

[HarleyQuinn]

If any of the controls on the form are blank then you are effectively writing (assuming that Service_Tag is populated):

WHERE (((Laptop_Data.Service_Tag & "") Like "*" & [Forms]![csearch]![Service_Tag] & "*"))
OR (((Laptop_Data.Model & "") Like "**"))
OR (((Laptop_Data.Memory & "") Like "**"))
OR (((Laptop_Data.Supervisor & "") Like "**"))
OR (((Laptop_Data.Last_User_Stamp & "") Like "**"));

This will always return all records as Like ** will match anything.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[DarkOne72]

I replace the SQL from the WHERE down with your and it still doesn't return the correct results and you can only search by service tag, I need it to be able to search by the different columns. For instance if you want to search by model or memory or service tag you can.

Thanks

 

[dhookom]

Try take my SQL and replace the 4 ORs with ANDs.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

SELECT Service_Tag, Model, Memory, Supervisor, Last_User_Stamp, PS, [2007], SP3
, PC_Name, PDF_Link, Issued_To, Notes
FROM Laptop_Data
WHERE Service_Tag & '' Like '*' & [Forms]![csearch]![Service_Tag] & '*' 
AND Model & '' Like '*' & [Forms]![csearch]![Model] & '*'
AND Memory & '' Like '*' & [Forms]![csearch]![Memory] & '*'
AND Supervisor & '' Like '*' & [Forms]![csearch]![Supervisor] & '*'
AND Last_User_Stamp & '' Like '*' & [Forms]![csearch]![Last_User_Stamp] & '*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

I didn't mean to use my SQL, that was to show you why your SQL is returning all of the records.

Using AND's will solve this problem, I was just trying to show you why you were getting the results you were.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[DarkOne72]

PHV,

Yours did the trick I believe, what was so different from my original one when I tried the ANDS?
I want to thank all of you for your help as well.

PHV..btw do you now how to duplicate data from one field to another field that is in a subform that is on the same form (if that made sense, lol)
In other words I have a form with a subform in it, I need the value from the main form in one cell to be duplicated to another in the sub form.

Thanks again guys

 

[PHV]

what was so different from my original one when I tried the ANDS
Look at the red parts of Duane's post 31 Mar 09 7:51

value from the main form in one cell to be duplicated to another in the sub form
Can't those controls be linked ?

 

[DarkOne72]

It will let me link chil and master but only 3 and I have like 5 fields I want duplicated.

 

[PHV]

I have like 5 fields I want duplicated
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

You can't link master/child on "LIKE". A value in the main form must match exactly a value in the subform.

Duane
Hook'D on Access
MS Access MVP