Search For A String Within A String
Oct 27, 2006
I have a column called CPU_S within a table called workstation that contains sample text like P111 933
I want to use the update command to search the CPU_S column for entries that contain this in there string then add P3 to a column called CPU_N
So far I have the code below but I don't know how to search a column entry for a specific string within a string. Can this be done and how?
UPDATE workstations SET CPU_N = "P4"
WHERE CPU_S = ;
View Replies
ADVERTISEMENT
Jun 7, 2013
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
View 13 Replies
View Related
Mar 25, 2006
I'm trying to create a query that searches the string in a field, for a substring that the user specifies when the query is run. This is the SQL code:
SELECT tblStatement.Date, tblStatement.[Payment Type], tblStatement.Details, tblStatement.[Paid Out], tblStatement.[Paid In]
FROM tblStatement
WHERE (((tblStatement.Details) Like "**"))
ORDER BY tblStatement.Date;
between those two asterixes on the WHERE line, I'd like a string that the user has been prompted for. I've tried this:
WHERE (((tblStatement.Details) Like "*[Enter a String]*"))
But that fails to work, I guess because its in quotations. If I simply had Like [Enter a String], then I get the prompt, but I'll need to enter a string which exactly matches what I am looking for. In other words, I need a query which will search say, "abcdefg" for the user defined input: "abc", which will return that result. I just dont know how to get this to work :s
View 1 Replies
View Related
Mar 12, 2007
Hi all,
I was looking for some help. I am trying to setup a table with a field for web address. People are entering www.website.com etc however I need them to make sure it starts with http:// Is their any way I can put validation on the field to make sure that this is entered? Or maybe I could use an input mask?
Any suggestions would be gratefully recieved.
Andy.
View 3 Replies
View Related
Sep 11, 2006
I have been hunting around to find exactly what I need but cant seem to find it...
what I need is a way to do a string search within a particular field (surname) and then filter by that string?????
Any ideas or point me to the right thread will be much appreciated.
View 1 Replies
View Related
Apr 11, 2006
I have a table containing names. Unfortunately, the first and middle name are in the same field, and not all have middle names. I need to separate them out in a report. Is it possible to search the field and break at the first space?
Any help would be greatly appreciated!
Thanks
View 1 Replies
View Related
Apr 25, 2007
Hi, I have a command button that opens a pdf file. However i will be distributing the database as a runtime package and need to account for people having different versions of adobe reader so need to search for the filepath string where Acord32.exe is found. This is the code I have but I am stuck! Ay help aprpeciated!
Private Sub Command4_Click()
Dim stAppName As String
Dim stPathName As String
Dim fs As Object
Set fs = Application.FileSearch
With fs
.LookIn = "C:Program Files"
.SearchSubFolders = True
.FileName = "AcroRd32.exe"
.Execute
????? stAppName = .FoundFiles
Set fs = Nothing
End With
'specify path name for version of adobe acrord32.exe
'stAppName = "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe "
stPathName = GetIniSetting("C:WINDOWSSSI_DL3_PROGRS.ini", "DIR", "REPORT_FILELOCATION") & "HOOF.pdf"
Shell stAppName & stPathName, vbMaximizedFocus
End Sub
View 1 Replies
View Related
Sep 11, 2012
I have the following in Column A
url.com?valueA
url.com?valueA&valueB
url.com?valueC
I need a function that
If string "ends with" valueA or ValueC, then replace it with ValueD
View 2 Replies
View Related
Feb 2, 2012
I need to find a way to search through all of the queries in my Access 2007 database to change the names of more than a few variables. There are close to 900 queries I need to search through many of which may use the variables as criteria, so if I miss even 1 instance I might screw myself completely.
I found this post and it tells me exactly what I need to do. Only problem is the post was written in 2002, and I've followed the below instructions only to have it not work.
As a contractor working predominantly on databases I haven't developed, it can be a daunting task trying to find references to tables, fields or functions within a list of several hundred queries.
The following code is attached to a command button on a simple form listing all queries in the database. The record source for the form is
SELECT DateCreate, Name FROM MSysobjects WHERE Type=5 ORDER BY DateCreate DESC
There is a text box where the user enters the text to search for. The code builds a table containing the SQL string of every query, then searches for the required text, and displays those that match.
Your form also needs a procedure the set the record source back to the default.
There are off-the-shelf applications such as Speed Ferret which perform this sort of function, however some employers are too cheap to purchase them!
Code:
sub cmdFilter_Click()
Dim db As Database
Dim rs As Recordset
Dim rsFilter As Recordset
Dim tdf As TableDef
Dim strSQL As String
Dim strQdf As String
On Error GoTo ErrorHandler
[Code] ....
View 11 Replies
View Related
Mar 25, 2014
I need to loop through text files and replace the new lines following the from, to, copy and subject fields with tabs and place the associated text on the same line as the from, to, copy and subject fields.
I start out with this. See below.
From:
ABC COrp@abc.com
To:
XYZ Inc.@xyz.com
Copy:
Me@abc.com; Myself@abc.com; Irene@xyz.com
Subject:
NOthing much
I need it to be like this.
From:ABC COrp@abc.com
To:XYZ Inc.@xyz.com
Copy:Me@abc.com; Myself@abc.com; Irene@xyz.com
Subject:NOthing much
How can I do this in access?
View 14 Replies
View Related
May 20, 2014
I am trying to write some code to search for similar strings. I am creating a database with records that all contain street addresses. These addresses may have more than one record attached to it, and we would like for folders to be created containing the records with similar street addresses. Problem is, all the existing records are from an excel spreadsheet that did not contain any data validation, so there are several instances of:
123 Street
123 st
123 street job 1
123 st job 2
etc....
So I am trying to write code to prevent this from happening in the future, by searching the database for a similar street address and asking the user if this is the address they are trying to enter. I have been trying to do this with the DLookUp function, as such:
Private Sub ProjectName_AfterUpdate()
Dim stLink, pName As String
pName = Me.ProjectName
stLink = DLookup("[ProjectName]", "tblMaster", "[ProjectName] LIKE '" & pName & "%'")
If IsNull(stLink) Or stLink = "" Then
[Code] ....
I have worded the stLink line different ways, and have used (*) instead of (%) but nothing is working. The CODE is working, as in no errors, but it is not finding a similar project that I know is present.
View 14 Replies
View Related
Jul 18, 2013
Access 2007
I can't figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.
Ultimately what I'm trying to do is - when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the "." with enough zeros for 10 digits.
Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.
Function Search()
Dim lssql As String
Dim lsSn As Recordset
Dim db As Database
Dim lsMessage As String
Dim sMsg As String
Dim vRetVal As Variant
Set db = CurrentDb()
[Code] .....
View 2 Replies
View Related
Jan 16, 2014
I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this:
A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.
I want to add VBA code that inserts a CR/LF in the memo field before all but the first occurance of a string that looks like a date, the first occurance doesn't need it. I will do it right after I import the CSV file into the table, so it happens only once, and it always prints and displays the CR/LF.
The memo field is called "NBS Update" and the table is called "CCRR Remedy Data"Here is what I have, but don't actually know what to put in to find the date and add a CR/LF:
Code:
Dim db As DAO.Database
Code:
Dim db As DAO.Recordset
Dim srtSQL As String
[code]...
View 14 Replies
View Related
Mar 11, 2014
I have an unbound form (named frmReportSearch) with unbound text & combo boxes providing the criteria for a query (named qSeqStreets). The form / query utilize 4 optional fields as search criteria plus date from / to. The results are returned via a report (named rptSeqStreets). The whole operation worked perfectly, however I realized I needed to change one of the criterion to a multivalued field. The change in the table (named Tasks) worked perfectly. I used three checkboxes (named chkA, chkB and chkC) to allow the user to select any combination of the 3 choices, including none (to be treated as no filter on [fldShifts]).
The three options in the field (named fldShifts) are "A" "B" and "C". I am able to manually run the query from design view by typing in the criteria "A" Or "B"... "A" Or "B" Or "C"... and any combination of the three options in the criteria box and running the query. I am using the following code under the OK button's OnClick. The Code below has other items related to all the options .... I didn't want to give partial code so you may understand better:
Code:
Private Sub btnOK_Click()
Dim strShift As String
Dim strA As String
Dim strB As String
Dim strC As String
[Code] .....
My problem is that the query criteria needs to be entered into the criteria box with quotes and separated by "Or" depending on if multiple checkboxes are selected.
I can get the results to show correctly in the textbox, however I imagine the query is adding an extra set of ""s to the string so rather than "A" Or "B" .. it is getting ""A" Or "B"". My query Sql and even design mode are pretty complex, so I wouldn't know how to use the sql in VBA without blowing some fuses.
View 1 Replies
View Related
Dec 2, 2013
I'm trying to search a for string within a subform to find information stored on the mainform to which the particular subform belongs.
The problem is that the subform is generated from a query which uses a number from the main form to generate.
So the subform record is only generated when the correct mainform record associated with it is loaded.
Now to solve my problem I've made a new query that brings up ALL the results that could be generated by the main form and from that I can search to find my search term I'm after and read off the ID number to tie it back to the mainform.
But all of this is done manually, I want a way to do all this using VBA in a way that the user can't edit any records as they are doing it.
View 3 Replies
View Related
Jan 26, 2005
Hi can anyone help me with this query?
'SQLTable "Resolutions Per Year", "SELECT ResName, COUNT(ResName) AS Total FROM Stats LEFT JOIN Resolutions ON (Stats.UserID = Resolutions.UserID AND Stats.ResID=Resolutions.ResID) WHERE Stats.UserId = '"&login&"' AND Date LIKE '%sYear%' GROUP BY Stats.UserID, ResName ORDER BY COUNT(ResName) DESC"
sYear is a variable
example sYear = 2003,2004,2005....
This does not work.
Is there anyway to assign the search string to be a variable?
View 2 Replies
View Related
Sep 29, 2006
Friends,
I'm using a search string to filter a continuous form. In an attempt to duplicate "Google", I'd like to use a single search field for multiple criteria.
Is there a way to use the space bar as an "OR" event in my search string?
example: my fat head
would look like: "My" OR "Fat" OR "Head"
I presently use code which strings together multiple search fields with an AND. Here's what I've been using:
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtLocation) Then
strWhere = strWhere & "([Location] Like ""*" & Me.txtLocation & "*"") AND "
End IF
If lngLen <= 0 Then
strWhere = Left$(strWhere, lngLen)
Forms!frm_SearchSortEvents.filter = strWhere
Forms!frm_SearchSortEvents.FilterOn = True
End If
End Sub
Thanks for you input!
View 2 Replies
View Related
Jun 8, 2005
It worked in acces 2000, and now am using acces 2003. Anybody any clue why the string strVoorstel would not get the value? It just returns nothing.
Here's the code:
Dim strVoorstel As String
Dim lngTel As Long
Dim strQer As String
Dim strDa As String
Dim lngNum As Long
...........
View 6 Replies
View Related
Jul 26, 2006
Hi Folks,
i am using the sql below but i am having a little syntax error:
i am trying to refer to a variable called strRAGCol
strSQL = "SELECT tblSite.SiteID, tblSite.SiteRAG, tblSite.Active " _
& " FROM tblSite " _
& " WHERE (((tblSite.Active)=Yes)AND((tblSite.SiteRAG)= strRAGCol));"
Any help much appreciated.
Mark
View 4 Replies
View Related
Sep 29, 2005
The following SQL query is returning no records when I know for a fact there are some there! Can anyone please hlp me?!
Dim strSQL As String
strSQL = "SELECT * FROM tblHirer WHERE HirerSurname= 'Forms!frmFinanceProposal!Child845!Text430'"
rsFindDuplicates.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rsFindDuplicates.RecordCount
Also, I was wondering how I would put as 2nd filter on the recordset to equal a particular date?
Cheers
View 2 Replies
View Related
Dec 16, 2004
I had a query and the sql is:
SELECT Institutions.*, Institutions.merge, Institutions.Inst_type
FROM Institutions
WHERE (((Institutions.merge)=True) AND ((Institutions.Inst_type)=[forms]![merge_inst_type]![Inst_type]));
and I had a command button which process
MergeAllWord ("select * from merge_query_true")
but I am getting an error like this "make sure the sql is correct , sql was
select * from merge_query_true
select * from merge_query_true ---- is this code ok?
If I had cut the part "AND ((Institutions.Inst_type)=[forms]![merge_inst_type]![Inst_type])); from the sql it works fine....
View 4 Replies
View Related
May 30, 2007
WHat am I doing wrong...I cannot get the syntax correct...
Form the Query Builder:
SELECT tbl_GroupMembers.GroupNumber, tbl_GroupMembers.Email
FROM tbl_GroupMembers
WHERE (((tbl_GroupMembers.GroupNumber)="10"));
NEED IT TO BE IN THIS SQL STATEMENT:
Set rs = dbs.OpenRecordset("Select * From tbl_Groups", dbOpenDynaset)
View 3 Replies
View Related
Aug 19, 2005
I have a fax number field iwhich has a mask. +(000)-00-000-000 in a table of many records.
The problem is that every record has a number in the first part of country code +(001)which must have come there by mistakes, few records have complete and true fax numbers.
I need a query that can Update this Fax_Number field where the length of the string is less than 7 digits.
Some thing like UPDATE Contacts SET Contacts.Fax_Number = ""
WHERE ((Len("Fax_Number")>"6"));
How can i do this. Thanks.
View 4 Replies
View Related
Sep 15, 2005
I have a string:
Cars > Car Import > Car Import: Car Import
The first section is the Category, the second is the Product, the third is the SubProduct and the fourth is the keyword.
What is the best way to split each of these words into its own variable? I think I can do the first and end one but not the Car Import and Car Import one.
Thanks,
Dave
View 4 Replies
View Related
Sep 18, 2005
I have a 67 binary code string produced from a query which concatenates these 1's and 0's.
What I need to do is have access decide what a particular string value/range is and return the process name. e.g
11000000000000000000000000000000000001000010000000 00000000000000001. The process name would be COMP RESOLVED MSA
10100000000000000000000000000000000001000010000000 00000000000000001.The process name would be COMP UNRESOLVED MSA
00000000000000000111010000000110000000000000000000 00000000000000001
The process name would be MSA NEW
There could be about 60 different Process Names
View 1 Replies
View Related
Dec 2, 2005
I'm working on a software that was developed by someone who left the company. The problem is that the software works for all locations execpt one that is in austria, after debuging I found out that there is a statement in the code that uses boolean true or false, the only way it worked is when I put an if statement with false or true as string, ex: if doc = "true" then
buttom line has anyone faced this problem, I searched microsoft website and they said that some vb engines don't convert boolean to string. Please Please if someone has a clue try to help. Thanks.
View 4 Replies
View Related
