A TRUE Keyword Search

[orange]

Did you look at the vba code in meep4.zip attached to post #50?
or the zip in post #56?

The code to create the Where clause is available in each.

Is there some part you don't understand, or is your requirement different?

[gatsby]

im sorry but im not really good in vba. im just a beginner to Access. im learning a bit.

below is the code in my dbs search :

Private Sub SearchFor_Change() 
'Create a string (text) variable
    Dim vSearchString As String
'Populate the string variable with the text entered in the Text Box SearchFor
    vSearchString = SearchFor.Text
'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
    SrchText.Value = vSearchString
'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
    Me.SearchResults.Requery

'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        Exit Sub
    End If
'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    'Me.SearchResults.SetFocus
'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
    DoCmd.Requery
'Returns the cursor to the the end of the text in Text Box SearchFor
    Me.SearchFor.SetFocus
    If Not IsNull(Len(Me.SearchFor)) Then
        Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If

End Sub

i dont know how to work this that it will search all keywords (though it can search all fields) even the user will write it in a mixed manner.

[orange]

I left a message in www.access-programmers.co.uk/forums/showthread.php?t=244092

How often do you do these searches? How big will the data base (to be searched) go and how quickly?

There are options, and they all involve vba to some degree. You should learn vba anyway. You can do some small exercises to mock up certain things - without building a full blown application.

When searches are coded along this line Where fld1 Like "*abs*" or fld2 like "*abs".... this is brute force and does not take advantage of any indexing, and will be slow as the data set size grows. So the approach to the search is tempered by your specific needs, the volumes etc.

For a different search where size and increasing data and multiple users with multiple searches see this post
https://www.accessforums.net/program...her-32497.html

[gatsby]

hey orange, thanks for your inputs here. though i didnt get what i want but still my database is getting better now. do you have higher version of Access? because i need help in other aspect of my database. im using 2010 by the way.

[orange]

I can handle 2003 or 2010.
What is you question/requirement?

[gatsby]

the problem is located on this link:

https://www.accessforums.net/access/...ect-33659.html

database attached....

i hope you could help me with this. thanks.

[gatsby]

any help on this one?

[orange]

any help on this one?

You seem to be dealing with more than 1 issue. Your original was a progressive type search based on characters in a string.
Now it seems the calculations are an issue. As rpeare said -- if you have multivalued fields that could be an issue.

You posted here under keyword search, so does that mean you still have an issue with search?
Please be as specific as you can.

[gatsby]

the calculation has the issue... im sorry if it is confusing...

[orange]

I don't follow whatever the calculations are attempting to do. Your Year combo shows 2009 to 2013. When I look at the combo I'm seeing a blank row down to 2012 --indicates to me we are looking at different things for some reason.

If I select 2010 in the first combo first row, then Escalation for all rows is 110% and Contingency for all rows is 115%.
If I go back to the combo and select the blank row for Year, the escalation and contingency stay at the original 110% and 115% - so it seems they are not being reset/requeried.

If I select 2010 for the first combo first row, Escalation for all rows is 110% and Contingency for all rows is 115%(as above).
If I go to the first combo second row and select 2011, Escalation in all rows goes to 105%, and contingency goes to 120% for all rows --including the first row (procurement estimate) which had the 2010 year??

I don't know what the calculations are trying to do/are supposed to do.

[gatsby]

yes, thats my problem. it doesnt work the i want it to work.

if the user will click Year 2011 and if the amount is 7,331 (Design Engineering Amount), the amount should change to 8,064.1 since it has a 110% increase... if the user will choose EST for Rate Basis also, the amount 8,064.1 (after an increase of 110% in Year 2011) will change to 9,676.92 because there is an increase of 120% after choosing EST for Rate Basis. and this should all happens in this row only. Another calculation for the next facility which is Project Mngt O/H (Owner PMT) which has a calculation of [Unit Weight] * 1700 * [Escalation] * [Contingency].... and so on....

by the way, Formula for Design Engineering is [Unit Weight] * 850 * [Escalation] * [Contingency]

[orange]

I don't think you have worked out the logic you want to occur. And you are the only one who knows what that is.
You should be able to draw a "flow chart" or "series of IF statements" to identify the algorithm/logic involved. And you should be able to do that to a large degree on a plain sheet of paper.
Do you really want/need a drop down on each row that seems to override whatever you selected previously?

I suggest you identify WHAT should happen when you select the Date from the drop down.
Should some other condition be tested? Should some controls be reset? etc.

In cases like this I often start with a very simple situation and work out the logic to "solve" the issue. Then expand it to 2 situations or a loop and then determine where resets or refresh etc has to occur. I will often use (what we used to call stub processing) a simple msgbox to highlight what should be done when this condition arises.
eg. If user selects a Year from the combo, then MsgBox "Year selected, must calculate escalation based on values for Year selected".

Since the keyword search I wrote in this thread originally, did not deal with the onChange event, but dealt with keywords or partial strings; and since you have chosen to use the OnChange search (originally provided by John BB), it may be time to create a brand new thread specific to your Form and calculations. It seems, to me anyway, that the key point of the TRUE keyword search is now lost in this new issue.

[gatsby]

thanks for your reply.

problem here orange is that im new to Access though I have tried creating some simple/small databases already but still i consider myself a newbie.
as of now, i dont have a problem with the TRUE keyword search. actually, i have this problem about calculation in this thread:

https://www.accessforums.net/access/...ect-33659.html

but no one is replying so i was hoping (i guess) someone in my other thread will reply.

okay, i will continue this in my other thread (Calculations Per Row Effect) and will attach an Excel file since i did it first in Excel before doing an Access but i could not move on on this issue. in Excel its fine and i got the problem but in Access i dont know how to do it.

the thread is here again:

https://www.accessforums.net/access/calculations-per-row-effect-33659.html