Access Query multi field help?

[EPOS]

I have a database... 1 table...40 fields..

I currently have a query set up as a basic a LIKE search for each field and I have to do 40 each time..

Is there a way of being able to do 1 query and have it search all 40 fields?

So my field headings are
Part No
Description
barcode..
Album name
Singer/Group
and so on...

I since some of the albums are compliations I require to search all the fields...

Any ideas?

 

[cuttsy]

I'm not sure what you mean. Do you have 40 seperate queries and you want a 'one size fits all solution'?

How have you managed to have a table with 40 fields.
You need to normalise your database.

 

[EPOS]

Ive got the song names and artists in there as well... since of the compliation CDs I need to list and search them all..

I am a mobile DJ and with the size of the Database as it is now I cant rewrite it.

 

[EPOS]

sorry for double posting.. but yes.. i need a query to search all of the 40 fields cause atm i open a query and manually have to type the word I am searchign for into 40 lins of the query

 

[cuttsy]

Here in lies the nightmare of databases that aren't normalised.

You have to write huge pain in the ass queries. I don't know of any funky SQL magic you can weave to save you any time.

You could make a form with a textbox and a button that will write out the query everytime you click the button.

Have you used VBA much?

 

[EPOS]

not really but I can follow alot of things easily

 

[cuttsy]

This is very messy and a bit naff but I don't know what else you can do with a table like this.
Also I am doing this blind so it may have a fair few syntax errors.

Make a form with:

1 textbox call it txtCriteria
1 button: call it cmdSearch

Then right click the button and click on build event and then click on code builder.

The VBA window appears and you should see

Private Sub cmdSearch_Click()

End Sub

Inside this Sub you need to write the code that creates and runs your query.

Private Sub cmdSearch_Click()

    Dim sSearchMusic As String
    Dim sCriteria as string
    
    sCriteria = txtCriteria

    sSearchMusic = "SELECT * FROM YourTable"
    
    
    On Error Resume Next
    Call CurrentDb.QueryDefs.Delete("qryMusicSearch")
    Call CurrentDb.CreateQueryDef("qryMusicSearch", sSearchMusic)
    
    DoCmd.OpenQuery "qryMusicSearch"

    
End Sub

This code should create and run your query. However the query that it runs in the above example isn't the one you want.

You need to open the query that you have already made, right click on the title bar of the query window and select SQL View.

This will show you the SQL command that you need to copy and paste in the above code.

sSearchMusic = "Your query here"

The only difference is that in the SQL string you have copied there will be a load of text after the WHERE clause and it will say almost the same thing over and over again. This wont be exact but you get the idea

WHERE .... Singer1 Is Like *yoursearchtext* OR Singer2 Is Like *yoursearchtext* OR Singer3 Is Like *yoursearchtext* .... etc etc.

you need to go through all this text and remove yoursearchtext and replace it with " & sCriteria & "

WHERE .... Singer1 Is Like *" & sCriteria & "* OR Singer2 Is Like *" & sCriteria & "* OR Singer3 Is Like *" & sCriteria & "* .... etc etc.

When you have changed the text then it should work. You click the button and the query opens.