Search By Last/first Name Concatenated
Aug 3, 2007
Hi all,
In the header of my form I have two comboboxes that search the main form to bring up a specific record in the detail section. The first, Combo2, uses the primary key of the table on which the chart is based for the search, and works well. The second, Combo6, is based off of a query that displays patient names concatenated as "LastName, FirstName." I can't search by last name alone because some last names are present for multiple people. Is there any way to take a selection in this combobox and use it as the filter for both the first and last name fields on the main form? Here's my current code for the search:
Code:Private Sub cmdSearchPatientInfo_Click() Dim strWhere As String Dim lngLen As Long Const conJetDate = "#mm/dd/yyyy#" If Not IsNull(Me.Combo2) Then strWhere = strWhere & "([Medical Record Number] = """ & Me.Combo2 & """) AND " End If '====This works fine If Not IsNull(Me.Combo6) Then strWhere = strWhere & "([Last Name] Like ""*" & Me.Combo6 & "*"") AND " End If '====This does not lngLen = Len(strWhere) - 5 If lngLen <= 0 Then 'Nah: there was nothing in the string. MsgBox "You have not entered any search criteria.", vbInformation, "Whoops!" DoCmd.Echo False DoCmd.Close acForm, "editPatientInfo" DoCmd.OpenForm "editPatientInfo" DoCmd.Echo True Else strWhere = Left$(strWhere, lngLen) Me.Filter = strWhere Me.FilterOn = True Me.Detail.Visible = True End IfEnd Sub
I have pretty limited familiarity with VBA, and I'm not really sure how to proceed. Any help would be VERY much appreciated. Thanks!
View Replies
ADVERTISEMENT
Feb 11, 2008
To make this easier to understand.
I have two tables
Table 1 Table 2
Member (is a number) Member(is a number)
Case (is a number)
A Member may have more than one Case. The Member number is unique while the Member may have many Cases.
I want to create another table from these two called Table 3
In this table I want (in one record, two fields) the Member and all that members cases.
Table 3
Member (one name)
Case(may have more than one number)
The result might look something like this
Member Case
1234 11111,33333,56565,6767
Can somone tell me how to do this?
View 2 Replies
View Related
Nov 5, 2004
I have built a query, a series of concatentions. What I need to do is add this query as a field on my form. So that the info that has been concatenated is stored in my table. Can this be done? I am able to show this query field on a report, but I need to pull the concatented info into the table. Hopefully I have explained this well enough.
Sincerely,
Tasha
View 5 Replies
View Related
Oct 23, 2007
Hi, I need a little help. I will give you the background on what i am doing to hopefully make this easier to resolve. P.S I am open to other approaches.
1) Items are requested for order indervidually
Table/Query looks like this
Code:ID#PartNumberStatusQty ReqOrder StatusNeed DateWork Order110-980-21Stock Required2Waiting26/10/2007WO07-12CR212-5-3Stock Required30Waiting26/10/2007WO07-1310-980-21Stock Required1Waiting28/10/2007WO07-24CR212-5-3Stock Required20Waiting28/10/2007WO07-25CR212-5-3Stock Required15Waiting28/10/2007WO07-2
2) The following Module concatenates the columns (my idea being the user can order all items in bulk.
Code:Public Function Conc(Fieldx, Identity, Value, Source) As Variant Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim SQL As String Dim vFld As Variant Set cnn = CurrentProject.Connection Set rs = New ADODB.Recordset vFld = Null SQL = "SELECT [" & Fieldx & "] as Fld" & _ " FROM [" & Source & "]" & _ " WHERE [" & Identity & "]='" & Value & "'" ' open recordset. rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly ' concatenate the field. Do While Not rs.EOF If Not IsNull(rs!Fld) Then vFld = vFld & ", " & rs!Fld End If rs.MoveNext Loop ' remove leading comma and space. vFld = Mid(vFld, 3) Set cnn = Nothing Set rs = Nothing ' return concatenated string. Conc = vFldEnd Function
3) A query calls on this
Code:SELECT Conc("ID#","PartNumber",[PartNumber],"Qry_70_BOM_Awaiting_Order") AS [BOM ID#], Qry_70_BOM_Awaiting_Order.PartNumber, Sum(Qry_70_BOM_Awaiting_Order.[Qty Req]) AS [Total Qty Required], Conc("Work Order","PartNumber",[PartNumber],"Qry_70_BOM_Awaiting_Order") AS WOrdersFROM Qry_70_BOM_Awaiting_OrderGROUP BY Qry_70_BOM_Awaiting_Order.PartNumber;
Producing this result.
Code:BOM ID# PartNumberTotal Qty RequiredWOrders1, 3 10-900-213 WO07-1, WO07-22, 4, 5 CR2162-5-365 WO07-1, WO07-2, WO07-2
OK, Now after that, (ALL ABOVE WORKS FINE).
I would like my users to purchase againts the new BOM ID# records as seen above. Once a order is placed against these i will, put something against each records to show there order status. (i'll take care of this part.)
Code:BOM ID# PartNumberTotal Qty RequiredWOrders Order Status1, 3 10-900-213 WO07-1, WO07-2 Waiting2, 4, 5 CR2162-5-365 WO07-1, WO07-2, WO07-2 Ordered
Finally, sorry about the lenght of this, I need to be able to use the above BOM ID# to reference back to the the ID# table at the start of the thread. In this example Id's records 2 - 4 and 5 will show ordered.
In other words how do i extract 2, 4, 5 back out of the above and into there original state, but including the order status?
Hope this makes sence
View 1 Replies
View Related
Dec 14, 2007
Hello all
I have a table containing the following fields:
Customer ID
Customer Name
Countries
The ID is numeric, the name is a string and the countries are the tricky bit since it contains a string of concatenated 2 digit ISO codes: "USCAGB".
What I would like to do is convert the concatenated representation to a normalized one. Take this example table:
ID Name Countries
2 John SEDK
3 Maria USCAGB
From the above table I would like to create a new table that links Customer IDs to single countries as
ID Name Countries
2 John SE
2 John DK
3 Maria US
3 Maria CA
3 Maria GB
How would I go about solving this?
View 4 Replies
View Related
Sep 30, 2014
I have imported a lot of data from excel into a table. There is in one of the columns some multi concatenate info I want to separate in it's own individual row.I am not sure if there is a way to do this in a query or if there was an MsAccess2007 example somewhere. I looked on the internet but couldn't find anything.
the columns are like this now
123456 7RFE01, 7RFE03, 7MFE06, 7MFH04
237890 8RFE80, 8MFE03, 9GFE06, 5QFH06, 3QFY06
I want the columns to be like this in a query or new table.
123456 7RFE01
123456 7RFE03
123456 7MFE06
123456 7MFH04
237890 8RFE80
237890 8MFE03
237890 9GFE06
237890 5QFH06
237890 3QFY06
View 5 Replies
View Related
Jan 11, 2012
I'm putting together a shipping database. We scan in a string of barcodes, separate out the Serial Numbers from the Barcode Data, concatenate them together as a string with asterisks as a delimiter and join them together with a date code and manufacturing line identifier. Throughout all of the data manipulations I am able to maintain the Serial Numbers in my table in the order which they were scanned in. The very last function is to concatenate the Serial Numbers and Lot information together into one long string which will then be output to a label creation software. And up to that point, the Serial Numbers are still in the correct order (based on them being linked to an autonumber field in the initial table) Upon the final concatenation, the order of the Serial Numbers is being changed, sometimes 17 out of 18 Serial Numbers will be correctly ordered with just one being randomly placed in the string out of order. Here is the code in SQL that I use for concatenating.
INSERT INTO tempMotorPalletLabel ( PalletLabel )
SELECT DISTINCT tempMotors_to_Warehouse.PalletNumber&"*"&tempMotor s_to_Warehouse.Count&"*"&tempMotors_to_Warehouse.P artNumber + '*' + Replace(Replace(ConcatRelated("SerialNumber","temp Motors_to_Warehouse","PalletNumber='"&PalletNumber &"'"),' ',''),',','*')
FROM tempMotors_to_Warehouse;
And this SQL Query transfers the concatenated string to a table that my Label Software queries for data to create the label with.
INSERT INTO AccessMotorLabelData ( PalletNumber, [Count], PartNumber, PalletLabel )
SELECT DISTINCT tempMotors_to_Warehouse.PalletNumber, tempMotors_to_Warehouse.[Count], tempMotors_to_Warehouse.PartNumber, [tempMotorPalletLabel].PalletLabel
FROM tempMotors_to_Warehouse INNER JOIN tempMotorPalletLabel ON tempMotors_to_Warehouse.PalletLabel=[tempMotorPalletLabel].PalletLabel;
View 14 Replies
View Related
Nov 3, 2014
I concatenated 2 fields CR_Numbers: = CR_Number & [Sub Number] to display 12.01: from 12 and 1. I am now wanting to lookup the CR_Numbers to edit or add information into other fields.
I want to use a pop up form to edit/add data without having the form add an entire row to the table. How do I reference the Table ID so that it will go to the equivalent row to add the data in?
Main Table = Change Request
Email Table = AORB_Email
Fields in Form!AORB_EMail: Change Request (CR_Numbers), Priority (Pri_Name), Hours (Hour)
I plan on using the Priority and Hours in the email to set some dates and expiration times as well as in the subject of the email. I have figured out the email setup, and if I manually fill in the Priority and Hours fields everything works as it should.
View 14 Replies
View Related
Jul 13, 2006
I have a Make Table query that includes a new field concatenated from a numeric field and a memo field, with some assorted text elements -
Issue Description:"("&[number]&") "&[Description]
When I run the Make Table, the new field "Issue Description" is created as a text field, NOT as a memo field.
Is there any way to insure that this field is created as a memo field.
Thanks.
Susan
View 1 Replies
View Related
Aug 8, 2013
I have a field in a table that is to be concatenated from two other fields, PolPrefix and PolNum. On a form I got it to show the full field 'PolicyNumber' by making the control source =[PolPrefix] & [PolNum].
But this doesn't update the field PolicyNumber in the respective table, and only shows it on the form. How do I get a concatenated field defined by the user into a table so that I can call that field other places in the database?
View 3 Replies
View Related
Jul 9, 2015
I am trying to use a criteria to filter out nulls in concatenated variable, which slows the query considerably.The part that makes it slow looks like this -
WHERE ... AND (([tbl1].[x] & [tbl1].[y]) <> "") AND ...
If I work them individually, it's not a problem, the query is fast, but I have to combine them first in order to produce the correct output.
View 13 Replies
View Related
Feb 25, 2013
I am attempting to create a printed label with a concatenated field. I want the field to abbreviate several shapes and colors but i will just start with the shape.
=IIf([Shape]="round","RD","TH")
This is what my basic statement says. I am attempting to nest other options inside of this.
=IIf([Shape]="round","RD",IIF([Shape]="oval", "OV", IIF([Shape]="triangle", "TR", "TH")))
Is this the correct syntax? I can not seem to get it to work.
View 7 Replies
View Related
Feb 12, 2014
I have a calculated field which consists of a few other fields concatenated, i.e. [field1] = [field2] & [field3] & [field4].
I need field 1 to be unique when its concatenated, but fields2-4 don't need to be unique. Is there a way to do this?
View 3 Replies
View Related
Mar 16, 2015
I have a class roster report in which the Employees' names are concatenated in a text box. When I select to sort the concatenated names alphabetically, it is sorting by the Unique ID, which is a letter and four numbers. The letter used to match the last name, but newer IDs are random. So most names are still in order, but some are not.
The Employee field Row Source is:
SELECT [Last_Name] & ", " & [First_Name] AS Employee, tbl_AM_Operators.User_ID FROM tbl_AM_Operators ORDER BY [Last_Name] & ", " & [First_Name];
What do I need to change to have the Employees' names alphabetized on the report?
View 8 Replies
View Related
May 15, 2013
I have a form in which I have a combobox. The code in the row source is:
Code:
SELECT [tblBooks].AuthorFirst & " " & [tblBooks].AuthorLast AS Expr1 FROM tblBooks GROUP BY tblBooks.AuthorLast, tblBooks.AuthorFirst HAVING (((tblBooks.AuthorFirst & " " & tblBooks.AuthorLast)<>"") And ((Count(*))>1)) ORDER BY tblBooks.AuthorLast, tblBooks.AuthorFirst;
This is an unbound box.
The rest of the form contains bound textboxes and other bound comboboxes.
This form is processed by the following VBA code:
Code:
Private Sub cmdAddBook_Click()
'Place the authors first and last name in an array
splitAuthor = Split(cboAuthor.Value, " ")
'Create a new record with the form information
DoCmd.GoToRecord , , acNewRec
[Code] ....
What happens is that it places all the records (Except those in the array) in a field and overwrites the values in that field.
I have used the same GoToRecord statement on other databases and it works just fine. I suspect that it is due to the unbound combobox.
What I need to find a way to place all the data in the corresponding fields of a new record including separating the author's first and last name when retrieved from the combobox. I just need to figure out how to get the data in the table somehow.
View 2 Replies
View Related
May 13, 2013
I am trying to sort with Docmd.Runcommand acCmdSortAscending
When it tries to sort a concatenated field i am getting the runtime 2046 Error
Code:
Private Sub cmbSort_AfterUpdate()
Select Case cmbSort
Case 1
Me.CompanyName.SetFocus
DoCmd.RunCommand acCmdSortAscending
Case 2
Me.DateCreated.SetFocus
DoCmd.RunCommand acCmdSortAscending
Case 3
End Select
View 3 Replies
View Related
Jun 18, 2014
why the code below is not functioning properly. When I type in an acronym in the textbox, it keeps saying there is an error "Run-time error '3345': Unkown or invalid field reference 'ABO'." I do have ABO in the field.
The dysfunctional code:
Code:
Private Sub btnFind_Click()
If (TxtFind & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Acronym] = " & TxtFind
[Code] .....
The red highlighted line is where the debugger leads me to. Something with identifying the field? I would like to enable the search procedure to search throughout the entire records rather than just a specific field. How may I write such a line or two?
View 5 Replies
View Related
Jun 24, 2015
I have a form that has a subform on it. The main form shows a category of furniture and has custom navigation buttons and a search text box for asset numbers and command button that runs the search. The subform shows the asset numbers associated with that furniture category, sometimes there is only one asset number, in other cases there could be 60. There is a scroll bar to scroll through the asset numbers when there are too many to see in the initial window.
The buttons all work as they should except when I search for an asset number that is part of a category that has too many asset numbers to show in the main window. When this happens the "previous" and "next" navigation buttons do not take you to the previous or next record. All of the other buttons on the form work though - you can go to the first, or the last record, and you can search for a new asset.This is the code for the search:
Code:
Private Sub cmdAssetSearch_Click()
Dim rs As Object
If IsNull(Me.TextAsset) Or Me.TextAsset = "" Then
MsgBox "Please type in an asset number to search for.", vbOKOnly
Me.TextAsset.SetFocus
[code]....
I've also attached a picture of what I mean when I say there are more asset numbers than what the window shows.
View 6 Replies
View Related
Jun 10, 2013
I currently have a database with a few search forms. I recently attempted to add a box on one of my search forms to search 3 cells of a record for a key word. What happens is when I go to search, say for P0442, it does not bring up all of the records that contain that keyword in the 3 cells I have outlined. If I step down the code in the OR boxes of the query, it seems to work better but then for some reason my other search criteria , (Tracking number etc) does not work. I will upload the database for reference. I am currently working on the Search(View Only) and that is where you will find the query to work on.
View 3 Replies
View Related
Sep 21, 2012
I'm having Table with some universities name and i want web link address for all universities. Take university from table1 in column1 and search on google page and return first link of the search page and save into column2...
View 1 Replies
View Related
Aug 5, 2014
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 Replies
View Related
Apr 21, 2015
1. I created a form with some search-fields which are related to a query. Then I added a Subform in which I put some more Search criteria (So that I can easily hide and unhide those additional searchfields). It sounds strange but is necessary ;-). Now I related those searchfields in the subform to the same query. When I run that query a window pops up that I should put in a value in all those searchfields which are in the subform. But I told Access that it should display all rows, if there is no value in those searchfields. Just as I did it with the Searchcriteria in the Main form. Do I have to do something special, when I have a query which is related to two Forms?
2. I want a searchfield to search in three different columns. Usually the value will just be found in one of those columns. As the Table I search is very long and has many searchfields and multiple of those will relate to more than one column, is there an easy way to do it in VBA? As I did it by using the "or" field when designing a query, but this seems very slow and unstable.
View 6 Replies
View Related
Jul 10, 2012
i need to create a system that can search data using keyword.so i want to create search button that i will create it at form. currently i have 1 table and in that table i have 10 fields which are project_name, doc_ref_no, doc_title, volume, book_no, author, doc_status, box_barcode, filling_location, doc_availability.
i have create 2 forms, one form for user to add new record and another one is to search record. the user can search by enter project name, doc title, volume and box barcode. for project name and volume, i use combo box and for doc title and box barcode i'm using text box.
i want to create search function that can detect from user keyword and even user did not fill in all the form, the system still can search the record.
View 5 Replies
View Related
Mar 4, 2014
I am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.
View 3 Replies
View Related
May 7, 2013
I have a search combo box to search for a field on my form. But it just goes to that particular record. The combo box is for client id and it has more than one record. How can I have it return just the records pertaining to the client id choosen in the combo box.
View 5 Replies
View Related
Mar 10, 2008
I have a database that uses four forms and each form has it's own table and each form has it's own search button to find a specific record within that table. I would like to use one search button that will look at all four tables and bring up the correct record when the request number is entered. Can I link all the tables to perform this or is code required? Can someone send me in the right direction for how to do this? Give me an example of code if it's required?
Thanks for any help I can get.
Laura Edmark
View 7 Replies
View Related