Modules & VBA :: Searching For Partial Primary Key?
Jan 8, 2014
I am trying to search for a partial number in the primary key field. I have based my search off Allen Browne's search form [URL]
I have posted my full code below. I am having trouble with the portion searching the Member_ID field.
My first version:
I would get a Run-time error '3464':
Data type mismatch in criteria expression.
Code:
If Not IsNull(Me.txtMember_ID) Then
strWhere = strWhere & "([Member_ID] = """ & Me.txtMember_ID & """) AND "
End If
My second Version: (I was searching for 54)
I would get a Run-time error '3075'
Syntax error (missing operator) in query expression '([Member_ID] = Like "*54*")'
Code:
If Not IsNull(Me.txtMember_ID) Then
strWhere = strWhere & "([Member_ID] = Like ""*" & Me.txtMember_ID & "*"") AND "
End If
Version 3:
If I use this the search works when the Member_ID matches exactly
Code:
If Not IsNull(Me.txtMember_ID) Then
strWhere = strWhere & "([Member_ID] = " & Me.txtMember_ID & ") AND "
End If
Code:
Private Sub Member_Search_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
I've successfully created, for the first time, a database with many attributes that is searchable by most of those attributes as well. I've finally gotten the swing of how tables, forms, queries, and macros link up and am understanding how MSA works.
I can't seem to find any information on here that tells me how to do wildcard searches. For my particular application, people enter items into the database in a nonstandard fashion, i.e. "oring," "o-ring," and "o ring." I need to enable the capability to search "ring" so my search functions are fully maximized.
Hi there, was wondering if there is way to link tables in Microsoft Access, if the primary key is not matching 100%, for example lets assume that we have two table, with fruit type as their content.
Table1 FruitType ORANGE APPLE PEAR
Table 2 FruitType ORAN PPLE EAR
as you can see the content in the second table is somewhat incomplete or corrupted "Orange" is missing an "E", "Apple" is missing an "A", and "Pear" is missing a "P".
I have a database as follows: Date1 Name Issue Action_Item1 Status1 Action_Item2 Status2 .....
with up to 5 max action items/status; where Status is a checkbox.I ONLY want the report to return records where there is text in an action_Item input AND the corresponding status is FALSE. The issue I'm having is that there may be ACtion_Item inputs withOUT text; so I can't simply query the Status checkbox for false.how to get started.
I have prepared a table in a database which includes a yes no box to one of the fields.
The idea of the yes/no field is that this is recording if the record is awarded. What i would like to achieve is that if the yes no box is checked "True" i would like to be able to copy some of the data from this table into a new table and that the form associated with this table opens up to enable the user to populate the remaining fields.
I believe i need to run this as an on click event procedure but cannot identify the code to enable the data to be copied accross to the other table.
I am provided a spreadsheet that contains multiple rows of similar data; each row/record represents a different stage in the process of financial transactions (requisition, purchase order, & voucher payment). Each financial transaction has these three records, with the amounts in one of three columns (pre-encumbrance, encumbrance, and expense), depending on the process.
What I am really after is the fuller, more detailed description that is apparently only available for the two records I don't want to import into the database (which is tracking only expenses and not the other two stages of the process). There is apparently no way to cross-reference these multiple rows due to the way the original database was designed (and we apparently have no control over this).
After importing the spreadsheet into Access, I would like to match the partial text string (truncated description) to the full description in another record, and update the record with the truncated description to the full description. To make mattes more complicated, I will also have to match values in the "pre-enc" or "enc" field with the "exp" field across these three records to make sure the correct descriptions are being matched because the truncated description will match multiple distinct records with the longer description.
I've got a bit of a problem with # mark when I want to search a number containing "#". The number is as follows: 33.#5hy4-1234When I insert a part of this number to a textbox eg. #5hy to search records containg it, it gives me no results (despite there are such in a database) I know that it is to do with this # mark. I have an sql query: (...) where sachnummer like '*" & variable & "*'
The database is a patient database used for tracking Inpatient data and Handover (or Handoffs for those in the US). It has been merrily ticking over since December and has accrued several thousand records. To get access to this data in new and interesting ways Im trying evolve my rudimentary search function which currently only works for diagnosis, name and consultant (in otherword text fields).
Thus I have an unbound form that has various input boxes on it. I have borrowed some code to power the form and have replaced with own fields:
Code:
Dim criteria As String
Looks for input text and adds it to the final criteria to power the search list at the end based on field in this case txtDiagnosis
If Not IsNull(Me.txtDiagnosis) Then
If criteria <> "" Then criteria criteria & " and " criteria = criteria & "txtDiagnosis like " & Chr(34) & "*" & Me.txtDiagnosis & "*" & Chr(34) End If
[code]...
ISSUE 1: I am struggling with the date search function. It doesn't work. Whether it is prudent to search for either a admitted date or a discharged date?
ISSUE 2: I have a check box for "complications" that I'd like to be included on in the search.
I have a text file that cannot be imported and turned into a table because it has no line feed characters and is therefore well over 255 columns. The fields are delimited by commas, however.
How can I search this file for all instances of "{id" and then grab the next 10 characters to the right and store them somewhere?
I have a form where a user enters data. One of the things the user enters is a "lot size". I need this lot size field to be checked against a query in the database to determine if the quantity is acceptable or not.
The order number in this query is in certain cases missing a leading zero, so I need to truncate this from the user entered form field.Finally I want the user to be notified if they are trying to exceed the lot size in the query.My code is as follows:
Code: Public Function RemoveFirstChar(RemFstChar As String) As String Dim TempString As String TempString = RemFstChar If Left(RemFstChar, 1) = "0" Then If Len(RemFstChar) > 1 Then TempString = Right(RemFstChar, Len(RemFstChar) - 1) End If End If
I'm trying to search for a variable (varCod) in a table(SerialNumberCustomer) but I want to only store the returned variable in an array if its shipDate is between 9/30/2001 and 10/1/2012
this is what my code looks like so far:
Set rst = CurrentDb.OpenRecordset( _ "Select * from SerialNumberCustomer WHERE SerialCardId = " & varCod & " AND (ShipDate BETWEEN #09/30/2001# AND #10/01/2012#) ")
The line of code works and it returns all of the values in SerialCardId where it's = to varCode but it doesn't go through with the shipDate requirement. It's instead returning all of the dates with the found value.
I have a form with a search box, Its UNBOUND called ID . Everytime I search for an ID number it shows no results, However I know there are entries in the table. This is the code I am using :
Code: Private Sub ID_AfterUpdate() If (ID & vbNullString) = vbNullString Then Exit Sub Dim rs As DAO.Recordset Set rs = Me.RecordsetClone rs.FindFirst "[Id] LIKE ' " & ID & " ' "
[Code] ....
I am assuming I need to add something to search for auto numbers but i don't know what!
I am using Access 2010 with Win-7.Shared, split database with several users.Database Purpose:PM Procedure for Medical Equipment..To be able for the user to copy the file name of 1 or more technical manuals and paste into a table used for a subform on the procedure parent form.Users can then open the related files stored in a common directory that are related to the procedure when later reviewing the procedure .
Problem:File path on the common drive for our group remains static Syspro_5VOL1BIOMEDCOMMONEquipment (Service Manual).The subfolders are not static.There is an unlimited number of sub folders by vendor, model, or make.The subfolders sometimes get renamed as vendors change ownership.The file itself usually does not get renamed.New subfolders get added as needed.Everyone in our group has access to this area of the server.
Question:Is there a way in VBA that when a user opens the procedure record form and dbl-clicks the name of the file in the subform, Access can start at the parent folder and keep searching through all the subfolders until it finds the file?I cannot depend on people in my group to be able to correctly update hyperlinks so I need a way people with no Access knowledge can even use even if its a little slow.
I currently have this code to spit out some data into a table, however I would like to tweak it to be more of an append query. I know I need to use "INSERT INTO" somehow based on searching, but I also want to ensure the table doesn't get duplicate records in it from the previous code. Manually adding a primary key isn't an option as the table may be deleted.
Code: Public Function Output_RFC_Tables_Test() Dim Conn As ADODB.Connection Dim RS As New ADODB.Recordset
I have an unbound textbox in a form which is filled with a text value eg "RWL" when the form opens. I now need another textbox that goes to the Company Data base finds the "RWL" text and finds its Primary key and puts it in the new textbox?
I got this function which is supposed to find a record based on the primary key, and then give three fields a value if they should be empty.
Public Function DoesXrefExist(faPersonID As Long, faRoleCode As String, faDorpID As Long, faDorpCode As String) If (DLookup("PersonID", "PEOPLE", "PersonID =" & faPersonID) > 0) Then End Function
Thats as far as i have got...for the next part.I get the feeling that i need to use the recordset object that exists somewhere.How do i access that recordset object? I have not been using any DAO or ADO, just straight VBA. I know i can create recordsets and stuff using DAO/ADO, but there must be a recordset that has been created already by the Dlookup command...
what is the best/cleanest way to return the next primary key value which is in the format yyyy-###.These are case files for the current year, ### being 3 digit number incremented by one for each new case.
I could use a function that finds the last one, checks the date, increments by one and returns it, but maybe I could use a public property or a class? I'm keen to use classes more for code maintenance.With a class then: would i create a PrimaryKey variable of the class type/object, so when I need a new key I would just type PrimaryKey.NewKey, for example?
I have a question concerning the automatically sequential numbering of a primary key as part of a composite primary key.
Tbl_treatment: ID=numeric field (also in tbl_pt and in tbl_tumor) Tumornr=numeric field (also in tbl_tumor) Treatmentnr=numeric field
[ID] and [tumornr] are fixed and i would like to automatically number [treatmentnr] per [ID] AND [tumornr] in a way that the output will look like this:
However, when i use the SQL-formula below my output looks like this. It seems as if the function is not properly taking the composite primary key of [ID] AND [tumornr] into account or not finding the true max value:
I have an Access 2010-form which inserts a record in a MS SQL 2008-database by using an ADODB-connection. I need to retrieve the primary key of the newly added record.
With code at the bottom I create a SQL-string which is stored in variable strSQL. If I execute the SQL-string directly in a MS SQL 2008 Query Window, the record is inserted and the MS SQL 2008-result pane shows a column "ID" with the primary key of the newly added record.
If I execute same SQL-string in MS Access 2010 the record is inserted. However, the code fails on Debug.Print rs![ID] with error "Item cannot be found in the collection corresponding to the requested name or ordinal". Same error appears if I use Debug.Print rs.Fields(0). I've enable the reference "Microsoft ActiveX Data Objects 2.8 Library". It looks as the recordset rs is closed as soon the command "Set rs = cn.Execute(strSQL)" finished.
Why I can't retrieve the Primary Key with VBA-code below?
Code: Private Sub cmdSave_Click() dim cn As ADODB.Connection dim rs As ADODB.Recordset set cn=New ADODB.Connection
I wan't access to calculate partial sums based on dates(formatted american style here), in particular, the amount ordered of a certain product until a certain date instead of the amount ordered at a certain date. So:
product date amount a 01-01-2005 1 b 02-01-2005 1 a 07-01-2005 2 a 10-01-2005 1 b 01-02-2005 4 b 01-03-2005 1
Should become:
product date amount a 01-01-2005 1 b 02-01-2005 1 a 07-01-2005 3(=1+2) a 10-01-2005 4(=1+2+1) b 01-02-2005 5(=1+4) b 01-03-2005 6(=1+4+1)
I have done it with the following query:
SELECT tespartialsum.produkt_nr, tespartialsum.transdate, tespartialsum.amount, Sum(tespartialsum_1.amount) AS SumOfamount FROM tespartialsum AS tespartialsum_1 INNER JOIN tespartialsum ON tespartialsum_1.produkt_nr = tespartialsum.produkt_nr WHERE (((tespartialsum_1.transdate)<=[tespartialsum].[transdate])) GROUP BY tespartialsum.produkt_nr, tespartialsum.transdate, tespartialsum.amount;
it works, but only on a restricted version of the table. I want to do it faster to be able to do it for the entire table(70000 products, 1000.000 lines).
