Searching A Field For Specific Data
Jun 14, 2006
Hello everyone,
Ive designed a databse - no problem. Ive done all queries I need to - no problem. Ive designed forms for entering data and reports for showing data - no problem. THE ONE PROBLEM I have is with 1 field.
The problem is that the field is a memo box, used to add data on groups attended. For example: General, customer care, mystery shopping.
Can I run a query so that a different user can search the memo box for attendence to a certain group? eg, search this databse and field for customer care.
At the moment I can only seach on the first word in the field. I dont really want to have seperate fields as each employee can have attendence to different groups ie bo can have just general while bill has general, mystery shopping, customer care etc.
Any help would be great
View Replies
ADVERTISEMENT
Nov 22, 2014
I am trying to create a query with a parameter on it, but on a date box, but only searching for a specific month, without regards to the year. So what I want is for me to run the query, a pop up box asks "which month?", I then put in Jan for example, and the results will show all records with the month Jan regardless of the day or year.
Is this possible? If so - whats the code I would use?
View 2 Replies
View Related
Apr 24, 2014
i wanna to insert a new field in the table ... which fill automatically with the date in which i modified the data in this record ...and then i'll insert this field in a report
View 1 Replies
View Related
Jun 14, 2013
I have a table with more than a million records. I want to add a new column with date equals to e.g.3/14/2012 for all the records. I am using update query, but it is super slow (taking more than a hour)..is there any other way to do this?
View 6 Replies
View Related
Jul 14, 2007
so i have an interesting question and im hoping that someone can help on this one. i need to pull date from a specific table, no problem, that's written and working fine, next i need to be able to join the data from another table by a primary key, again no problem. third, i need to be able to select the date (using WHERE) for a specific date range. (i.e. i enter the date range of 01/7/2007 to 15/7/2007) and the query comes back only showing the data from that specific time, not the data from before or after. this is where my problem lies, all the entered data is being shown after entering my date range. i am going to include my SQL statement, just so you can actually see what im really talking about.
SELECT srealest.Name0, srealest.Dist1, SREpayments.Face2Pd, SREpayments.Penalty2Pd, SREpayments.[2paid], SREpayments.Face3Pd, SREpayments.Penalty3Pd, SREpayments.[3paid], SREpayments.Face4Pd, SREpayments.Penalty4Pd, SREpayments.[4paid], srealest.Map, srealest.Parcel, srealest.LeaseHold, srealest.TaxRebate1, srealest.TaxFace1, srealest.TaxPenalty1, srealest.TaxYear, srealest.BillNo, srealest.PdRebate1, srealest.PdFace1, srealest.PdPenalty1, srealest.DatePd
FROM SREpayments INNER JOIN srealest ON SREpayments.BillNo=srealest.BillNo
WHERE (((SREpayments.[2paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[3paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[4paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date])) Or (((srealest.DatePd) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]))
ORDER BY srealest.Name0;
any thoughts or ideas on how to accomplish this would be greatly appreciated!
View 2 Replies
View Related
Oct 3, 2005
Hello, I have just spent ages doing searches and reading everything I can on locking. But, I have yet to find an answer as to how I can lock a specific field in a specific record.
e.g. Staff enter customer details, then at the end of the day the admin (me) checks it over and presses a big old button that stops them from locking certain fields in the current record only - they must still have access to the unlocked fields of the current record, and it must not lock any other records.
I'm guessing there's some VB code in the form of fieldname.lock = true, but then it locks the field throughout the whole table!
Can anyone tell me how to do this please?
View 11 Replies
View Related
Jul 10, 2007
Hi,
I currently have an employee database - all info is currently in one table.
I am wanting to create a search box in my form which allows me to search for multiple bits of information. ie. I would like to be able to type in either a first name, surname, full name, or employee id number (for example), press enter, and for it to display the results (either by going to the first that it finds, and me then clicking find next, or by displaying all found in an index so that you double click the one that you want - i am not too fussed... the main issue is getting the search box to look in multiple fields).
I currently have the following code:
Private Sub cmdFind_Click()
DoCmd.ShowAllRecords
DoCmd.GoToControl ("Surname")
DoCmd.FindRecord Me!txtIDFind
'If FindRecord = True Then
'Exit Sub
'End If
DoCmd.GoToControl ("EIN")
DoCmd.FindRecord Me!txtIDFind
'If FindRecord = True Then
'Exit Sub
'End If
DoCmd.GoToControl ("forename")
DoCmd.FindRecord Me!txtIDFind
End Sub
(ignore the notes - they were trial and error for something else).
Currently, when i press find, this code will find the first of anything - ie i time in Frank and the first Frank will appear (unless someones surname begins with Frank). What i can't seem to do is get it to progress to the next record if i press a find next button.
I have also tried the following code, but it only works if i put in an EIN number:
Private Sub Findall_Click()
DoCmd.ShowAllRecords
DoCmd.GoToControl ("ein")
DoCmd.FindRecord Me!txtIDFind, acStart, acAll, True
Any thoughts? I think i'm probably looking at this in completely the wrong way.
Oh, also i am quite new to VB, so please forgive any silly questions that i am likely to ask!
Kind regards,
Fergus.
View 3 Replies
View Related
Aug 10, 2006
Hi,
Can you help me? I am very new to using MS Acess and have just built a very basic DB to store enquiries coming in to Head Office from potential franchisees.
Within this DB I have included a "Yes/No" box with a Title of Live Franchisee.
When an enquiry becomes a franchisee I tick the box as yes.
My question is how do I search for all the "Yes's" so that I can display all the live franchisees.
Best Regards
Keith :(
View 3 Replies
View Related
Oct 18, 2005
Database description
I have one to many relationships between tables related with PersonID. From the entry forms I enter data and there is another form to search data. The data search form displays some 5 fields from different tables: first name, last name, phone, email...
Problem description
When I enter new data in all the above named fields, then the search engine can find and display the new record. However, when I leave blank some of the fields, let's say, the phone field, then the search does not display the entire row, although the first and last name, for example, exist in the appropriate table.
I would appreciate, if anyone could help me to solve the problem.
Thanks
View 1 Replies
View Related
Apr 3, 2006
Hello,
I'm new to using Access, and come from a library background, and I'm trying to use Access to build a library database. So far it's going well, but when it comes to searching using a form and applying a filter I can only get it to search one field at a time, and really I want to search two or more depending on which field I'm using.
Is this possible? and how do I do it?
Any help very would be gratefully received.
Thanks
Mozz
View 5 Replies
View Related
May 25, 2005
:mad: I have a combo box which is being used for surname searching. It displays surnames in the first column and first names in the second column. I would like the user to be able to type or select a surname from the dropdown list and where there are multiple entries of that surname select the correct record. At present the first matching surname is the one that keeps being selected.
I am displaying the complete record in fields lower down in the same form.
I also have a firstname search combobox but the code will be almost identical.
Can someone help me with this problem. Thanks in advance.
View 2 Replies
View Related
Dec 22, 2011
I have around twenty shipment tables for different days and one main store check table.In the shipment tables i have bill numbers that correspond with shipping id's and in the store table i only have bill numbers.
I would like to check if the bill number in the store table appears in all 20 tables.after doing that i want the corresponding shipping ID to appear in the store table. Is this possible?
View 5 Replies
View Related
Aug 31, 2014
I am new to MS Access database and I am currently developing a DB with search function in a form for an inventory purpose, I have followed some VB codes from the web.
Here is my issue; when I try to search using the configured combo box in the form it gives me these sets of values, here is the screenie //oi57.tinypic.com/24fxwzk.jpg (just add the http: before the forward slashes as the site wont allow me to post images or links yet)
Row source for data: "-1";"Office 2010";0;"Office 2007";1;"Visio 2010";2;"Visio 2007";3;"Project 2010";4;"Project 2007"
and here is the VB code:
If Not IsNull(Me.compName2) Then
strWhere = strWhere & "([Computer Name] Like ""*" & Me.compName2 & "*"") AND "
End If
If Not IsNull(Me.cUser2) Then
strWhere = strWhere & "([Current User] Like ""*" & Me.cUser2 & "*"") AND "
[Code] ...
I just wanted to eliminate the numbers and replace them with the actual values as name. ex. "-1";"Office 2010" instead of (-1) doing the search, it should be the value "Office 2010"
View 6 Replies
View Related
Sep 17, 2007
Morning all,
Quick question... I figured out how to make all the searching items work... individually. The problem is is that I want to be able to search with two fields at a time. For example, say i have mo, date, job, pay and i want to seach for the MO and JOB and query the results accordingly. I can't get it to do that. Would I have to make a large If statement? I'm not entirely sure. Also now my problem is that it won't execute my first if statement or I should say it ignores it all togeather. I know it executes the code in the order it is written. If someone could shed some light I would be much apprecative. As always here is the code and attached is the program. What I'm working on is in the query table.
I also would like to be able for it to say if the fields MO and JOB do not comeup witha result for a messege to display there is no result. Or if the field is null not to do anything. I tried doing that in some of my code to no success perhaps I did somthing wrong.
Have a great day!
Dim strSQL As String
Dim sqlStringWHERE As String
Dim strhead As String
Dim strJOIN As String
' strSQL = "SELECT * from Panel WHERE MO like " & Chr$(39) & "*" & Me.qMO & "*" & Chr$(39)
strJOIN = " AND "
strhead = "SELECT * from Panel "
If Len(Me.qMO & vbNullString) Then
sqlStringWHERE = "WHERE [MO] like " & Chr$(39) & "*" & Me.qMO & "*" & Chr$(39)
Else
sqlStringWHERE = "WHERE [MO] = " & Chr$(39) & Me.qMO & Chr$(39)
sqlStringWHERE = sqlStringWHERE & strJOIN
End If
If Len(Me.qCode & vbNullString) Then
If Len(sqlStringWHERE) = 0 Then
sqlStringWHERE = "WHERE "
End If
sqlStringWHERE = "WHERE [CODE] like " & Chr$(39) & "*" & Me.qCode & "*" & Chr$(39)
Else
sqlStringWHERE = "WHERE [CODE] = " & Chr$(39) & Me.qCode & Chr$(39)
sqlStringWHERE = sqlStringWHERE & strJOIN
End If
strSQL = strhead & sqlStringWHERE
View 7 Replies
View Related
May 1, 2008
Hi there, I am currently working on our intranet at work and am creating an Access database so we can record all of the box files at our storage facility.
One of the fields is called 'contents', which is a Memo field, where the person who is inputting the data will describe what is in the box. They will then use the autonumber generated by the database to label that box.
That way we can locate the boxes much easier than at the moment.
I have created a results page with a search box at the top using ASP in Dreamweaver CS3 and it works fine if I replicate EXACTLY what is written in the contents memo field.
ie: if the contents field for record number one reads
'Archived box of insurance documents dating back to 1999'
I would need to type in that exact phrase to pull that info back.
What I want it to do is if I was to put in '1999' or 'insurance' then it would return the same result (and others with those specific words).
What do I need to do in my code?
Here is the current code
Code:<%Dim rs__MMColParamrs__MMColParam = "1"If (Request.QueryString("content") <> "") Then rs__MMColParam = Request.QueryString("content") End If%><%Dim rsDim rs_cmdDim rs_numRowsSet rs_cmd = Server.CreateObject ("ADODB.Command")rs_cmd.ActiveConnection = MM_intranet_STRINGrs_cmd.CommandText = "SELECT * FROM archive WHERE content = ?" rs_cmd.Prepared = truers_cmd.Parameters.Append rs_cmd.CreateParameter("param1", 200, 1, 255, rs__MMColParam) ' adVarCharSet rs = rs_cmd.Executers_numRows = 0%><%Dim Repeat1__numRowsRepeat1__numRows = -1Dim Repeat1__indexRepeat1__index = 0rs_numRows = rs_numRows + Repeat1__numRows%><%Dim MM_paramName %><%' *** Go To Record and Move To Record: create strings for maintaining URL and Form parametersDim MM_keepNoneDim MM_keepURLDim MM_keepFormDim MM_keepBothDim MM_removeListDim MM_itemDim MM_nextItem' create the list of parameters which should not be maintainedMM_removeList = "&index="If (MM_paramName <> "") Then MM_removeList = MM_removeList & "&" & MM_paramName & "="End IfMM_keepURL=""MM_keepForm=""MM_keepBoth=""MM_keepNone=""' add the URL parameters to the MM_keepURL stringFor Each MM_item In Request.QueryString MM_nextItem = "&" & MM_item & "=" If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item)) End IfNext' add the Form variables to the MM_keepForm stringFor Each MM_item In Request.Form MM_nextItem = "&" & MM_item & "=" If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item)) End IfNext' create the Form + URL string and remove the intial '&' from each of the stringsMM_keepBoth = MM_keepURL & MM_keepFormIf (MM_keepBoth <> "") Then MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)End IfIf (MM_keepURL <> "") Then MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)End IfIf (MM_keepForm <> "") Then MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)End If' a utility function used for adding additional parameters to these stringsFunction MM_joinChar(firstItem) If (firstItem <> "") Then MM_joinChar = "&" Else MM_joinChar = "" End IfEnd Function%>
and for the search form:
Code:<form id="content_search" name="content_search" style="margin: 0px; padding: 0px;" action="archive_results2.asp"> <label> <input name="content" type="text" id="content" value="" /> </label> <label> <input name="button" type="submit" class="directory_form_button" id="button" value="Search Content" /> </label> </form>
Thanks for any help that could come my way!!
Cheers
View 2 Replies
View Related
Mar 19, 2014
I have a database that I am creating for my work. I have a form that I am trying to get it to search the information from a table to pull the record on the form. I would like to search infomation such as employee id and wanting it to pull that information from the table.
View 7 Replies
View Related
Mar 20, 2013
I am trying to make a database containing the technicians of our companies.
I found a online template i am using, and this has a quick search field.
I have created a multivalued field that contains the technicians skills. (pulldown with checkboxes)
When i try to use the quick search field access returns a error message saying something like:
"The multivalued field '[Category]' can not be used in a WHERE- or HAVING-string."
The field i use for skills is an old category field.... Just renamed and changed a bit..
View 4 Replies
View Related
Jan 22, 2007
Hi. Please do not lecture me on database normalization, as this truly is not in my control.
I have 2 dozen tables, each with 13 fields. All of the field names exist in at least 12 of the tables. And all tables share a ssn field where values are common.(Confused yet? Sorry, if you are...)
If I design a query showing all 2 dozen tables and their fields...
Can I set up a query criteria where I enter the requested ssn and then the required field name and have the result show the values of all the fields with that name, among the ones of the 2 dozen tables where that field name exists? (for that specified ssn)
If I am only as clear as mud, please let me know, and I will try again.
Or, by asking if I can search for field values querying by field name, am I any clearer?
Russ
View 14 Replies
View Related
Dec 18, 2014
I am trying to save specific data from a data entry form to a specific depending on what the user selects from a dropdown/combo box.
So if the user selects "test company from the combo box, that data (from that form) will save to the Test company data table.
View 5 Replies
View Related
Jan 9, 2012
I am new to Microsoft Access. I have built two entry forms for product release information. I have a drop box under the label customer for each specific customer. I would like for this form to send the data entered in the form to the customer specific table from the customer names in the drop box. I have tried to research and execute this myself thus have been very unsuccessful.
View 14 Replies
View Related
Mar 31, 2005
Hello,
I am trying to get data from a specific record in a sub form. and save a result in the same record.
specifically:
I am using the following fields
"teacher type","Class", "Step", "Hours", "Pay"
I have two teacher types, Contract and hourly. a teacher can be either of these or both. I am taking the class and step info from the subform, looking up the data in a table to determine their pay and saving it to the "pay" field.
this works fine as long as I only have one record. if I add the second record the calculation changes bot of the records "Pay" field value to the same thing. what I want to do is have it only calculate the pay for the contract teachers and have it saved to the proper record and the same for the hourly teachers.
I have seperate routines for the calculation of each type.
Any help will be appreciated!!
Bill Hesson
bhesson@guhsd.net
Tech Specialist II
El Cajon Valley High School
View 12 Replies
View Related
Oct 26, 2007
Hi I could do with some advice on how to start the following and if best to use both excel and access to achieve it.
I want to enter a range of data when I receive a bill such as date, supplier, bill amount, description, quoted price, customer job and retail price. Once I enter this I want to be able to do the following,
1.Pick a customer job and date range and get a report for all the details of bills received in relation to that customer in that time so that I can send the customer a list of items they bought in that time period and what each item cost.
2.I want to be able to compare cost prices from different suppliers and print a list of all items which shows who the preferred supplier is for each item based on the cheapest.
3.The quoted price from suppliers should automatically appear when the description and supplier has been entered.
4.There needs to be some sort of warning if the quoted price doesn’t match the bill price
5.I need to be able to get a report which gives the details for any bill which was entered that was not bought from the preferred supplier.
Some advice on pointing me in the right direction to get started is what I need and would be greatly appreciated
Thanks
Adw
View 6 Replies
View Related
May 6, 2014
i have a table
ID - File Type - Source - Delivery - Return
1 - X - LB - ROY - LB via Roy
2 - Y - USA - Smith - USA via Smith
3 - Z - LB - -
my question is how can i make the last column (Return) to fill like the data above..what i mean if (Delivery) not null So the return will be filled like the above if the (Delivery) is null the return will be null too
-the user will fill only the columns (File Type - Source - Delivery)
-the column (Return) will be filled automatically if (Delivery) not null
View 4 Replies
View Related
Aug 31, 2011
i want to do a report that display the data according to a form. for example: in the form i write the name of the company and 2 dates and i click the button and it opens me a report that contains data about this specific company and between those two dates.i thought i could do this with creating queries and when you write in the form it changes the criterias (the criterions) of the queries and the report will be based on the queries.how form can change the criterion of a query. or is there better way to display specific data according to a form.
and i have related question: i did a form that has a sub form (i mean a table in my form) and textbox. i wanted that when you write in the text box, for instance, name of someone the subform display all the rows which contains this name. but i cant connect the textbox to the subform, it only displays the rows that which contains this name but it does it in it own order.
View 2 Replies
View Related
Jun 14, 2006
I have a query drawing information from two tables. The first table has information about a product, and the second table holds all the faults found per product. So any one product can have anything from 1-14 faults. I have created a report from the query, but the Fault item that I put on it only ever shows the first fault for that product. Is there some way of fixing this at report level, or on the query?
The tables are connected through the ID field, but the primary key for the faults table is another autonumber. Ex:
Primary Key___ID___Fault
___1__________ 1____fault 1
___2__________ 1____fault 2
That's two faults for the same product. Maybe I could create new fields in the query like Fault1: [Fault] with criteria set to primary key = 1? And one for each fault? But when I do it, it sets that criteria to the entire query, so the results end up blank because the "primary key" field doesn't exist on the product table.
I guess what I'm asking is can you set a criteria for just one field of your query to follow? Or can I resolve this issue in the report design?
Many thanks,
Tom
View 1 Replies
View Related
Mar 25, 2005
Good day,
I need a textbox to a five digit number. How would I write code to reflect this.
Thanks.
View 1 Replies
View Related
