I have a need to strip of letters from a string but i needs to look for / as the length would change, below is an example of the data I am working with
record 1 example ) REP/1349/999/426066/XX/9
record 2 example ) REP/UDKBS01N/1/448174/XX/
what i need to extract is
1) = 1349
2) = UDKBS01N
I need to get the information between the first / and the second / is there a function in access to get this.
I am trying to extract the first instance of 3 numbers from a string using an update query to update another field in the same table(Master Equipment) titled "Code". the field is called "Equipment". the strings vary in length as well as the location of the 3 numbers needed, but I have examples of all possible locations the 3 number series could be located below. As you will notice, some fields do not contain 3 numbers together, for these I don't want to return a value.
07-FLP58351
07-MFDP58201
1PBE97601
1PT91105-2
2WPF/FF438582
A-WR-1
AAV58101-01
AC47201-01
In Access 2007, in a table “Source” -> "UserName" column, I'm trying to truncate and extract value in the string:
a. Value within the parenthesis preceded by "".
b. Value outside the parenthesis preceded by “”
“Source” table contains the column ‘UserName’
“Final” table contains the columns ‘UserName’, “Expected_Output_1” and “Expected_Output_2”.
I tried using the following expressions to extract the output results, but unfortunately there’re not displaying the results in correct format.
For extracting results of “Expected_Output_1” I used: Mid([UserName],InStrRev([UserName],"")+1)
For extracting results of “Expected_Output_2” I used:
Mid(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),InStrRev(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),"")+1)
Attaching the database for your reference.
Expected results should match the snapshot attached or table “Final” in the attached DB
Example:
UserName = C103E000001 then the “Expected_Output_1” = E000001, “Expected_Output_2” = nothing
UserName = NAHoM\_INTRA_MTS (NAHoMDNAGAIntranetAdmin) then the “Expected_Output_1” = _INTRA_MTS, “Expected_Output_2” = DNAGAIntranetAdmin
Hi all, I'm not sure if this is possible ...
I have a table which has contact details in it... two fields are FirstName and Surname. I want to create a new field called initials which takes the first letter from each of the former two fields. Is it possible to do this using an update query?
Or any other way maybe?
I do have a module to extract a number from a string :
Public Function ExtractedValue(strSent) As Double
If IsNull(strSent) Then
Exit Function
End If
Dim strTemp As String
Dim intLoop As Integer
[code]....
My problem is that the string I do have is like:
19.0 EA
195 PAC
42 M
150 L
the problem is that when the number is like 19.0 EA the value I get is 190, in reality it should be 19. The rest is fine as there are no dot's.
I have a column containing records of the timestamp of an event. I need to extract the date out of each of these records and put them in a separate table. The date and time of each record is contained within a bracket.
E.g.
ES~1~1412179200(Oct 02 00:00:00 2014)~1~ITM_W64MQMONITORLOG00~
0~0~ES~1~1412179203(Oct 02 00:00:03 2014)~1~ITM_Log_Entries~
0~0~ES~1~1412179204(Oct 02 00:00:04 2014)~1~ITM_Log_Entries~
As you can see, the number of characters of each record are different, so I am unable to do the Left$ count thingy.
Is there any other way to do it? I only need the date and time contained in the brackets.
"Att Mr/Mrs Vezi Your DEFAULTED account has been handed over to XXX. Payment DUE IMMEDIATELY Ref 1315519509. Tel 0009377500"
I need to extract the word "Ref " plus the number that follows it from the above string. The problem is that the word "Ref " is not always in the same place and the number following it is not always the same length. (ie it could look like "Ref 123456 " or "Ref 1234567 ") The only commonality between the records is that the number will always follow the word "Ref ".
I have tried to find this on the forum but it seems all the answers are to do with manipulating the strings based on what the postcode is whereas I just want to extract the postcode as a separate string as simply as possible.
Basically, I have a text field called Add5 which has the last line of the address including the postcode.
Example: "Northampton NN1 7PQ"
I am trying to end up with two strings like this
Add5 = "Northampton"
Postcode = "NN1 7PQ"
I only need to do this in one place, I don't think it needs a module.
I know there are many posts on this but still cannot find what I want ....
I have a string ....
... <surname>bloggs</surname> <fornames>Jane</fornames> etc.etc.
... <surname>williams</surname> <forenames>Jo</fornames> etc.etc.
In a query I know how to:
Find the Start and End Postions as follows:
StartSurnamePos: InStr([string],"<surname>") returns 19
EndSurnamePos: InStr([string],"</surname>") returns 34
I also know that by adding 8 to StartSurnamePos I can get Bloggs but how do I stop it there.
Using left, Right or Mid how do I pull out just
bloggs Jane
Williams Jo in seperate fields?
Thanks in advanced.
The code has fixed path information on a lot of places in different SQLs (DoCmd.RunSqL command). I want to replace fixed path info with variable path info. Variable path info is stored in the table.
I managed to achieve that in the following manner:
Code:
Dim db As Database
Dim dbName as String
Set db = CurrentDb
Set rs = db.OpenRecordset ("TableName", dbOpendynaset)
rs.FindFirst ("ID = " & 2)
[Code] ....
where I would use as variable Function name instead of dbName.
How to make module that will enable to use Function name as variable path information for SQL queries?
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.
I need to create a query that will pull duplicate names out of my db.
I would like it to pull all names that have:
duplicate LastName and duplicates of the first 3 letters of the FirstName.
For example, if I had the names:
Bland, Abe
Brown, Abe
Brown, Bill
Buster, Jon
Buster, Jonathon
I would like my query to return only Buster, Jon and Buster, Jonathon.
I have a column containing an id that consists of the first two letters of a weekday followed by an incrementing number. For example, for Monday, I have "MoA1" "MoA2" "MoA3" ... "MoA11".
The problem is that when I sort my list, it is ordering it: "MoA1" "MoA10" "MoA11" "MoA2" "MoA3" etc. Currently, my order by property is set to
MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME))
I would like to run a query which takes a code which contains a mixture of number and letter and returns all the number before the first letter. I was using the left function (=Left(([Codes],2)) but sometimes there is one number sometime two. The desired results are shown below;
7pol2try36 = 7
12cet9fre55 = 12
10yea3gtr77 = 10
Is this possible?
I have a query that holds data based on a field. If the field [Device In] is "TimeStation-1" in TblTime for example it holds "AV" in the field [House]. Trouble is some fields are blank and when this is so I want it to pull the last two letters from the [Notes] field. I have attached the database. The query is [QryDeductionsandSleep Ins].
View 4 Replies View RelatedOur Access database is getting a bit sluggish when loading/running reports. My mate said to me that the Access database that our 100+ users at work use to run reports should be "stripped back" to speed things up and to stop unnecessary actions running when they aren't needed. He pointed me in the direction of File>Options. Users don't edit data from this database, just to run preset reports. They have no editing capabilities either.
View 3 Replies View RelatedI hope someone can help with this one. After many years of using Access for ad-hoc data conversion this has beaten me.
I need to produce an ascii text file with fixed column widths, separated by commas, strange I know but the customer is always right. As it is fixed width I have inserted the commas by using a separate column for each one.
Numeric columns need to be left padded with zeros. I have constructed a query to do all the column selection and reformatting into a new table which I then export using a fixed length export file spec. Everything works fine except for 3 columns which are calculated by subtracting one column from another. I can get the data to look fine in the output table, the datatype is text, but when I export the table the leading zeros are stripped.
This is my expression: String(9-Len(FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0)),"0") & FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0).
The result in the table is exactly what I want: 000200.00 but when I export it I get a left adjusted 200.00.
I've tried using format with a "000000.00" mask which gives the same results.
I've tried removing the preceding comma column and including the comma as a prefix using the format mask ",000000.00" and also by concatenation. This looks fine in the table column ,000200.00 but I get an error when I export the table which blanks the column. Error attached.
I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.
I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)
This query produces the error given in the title of this thread, whilst the following works:
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)
This related query also works and shows a load of -1s and 0s correctly
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix,
IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND
But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.
I'm trying to set up a query where it returns me only the items that are repeated.
ie...I have a table that has clients that are one time clients and other that are repeated. Each client has a client number. I would like to get only the clients whose client number appears more than once. Is that possible?
I have a (simplified) table with
ID | Date | Time | machine | value
304 1-1-2014 06:00 115 0.54%
305 1-1-2014 06:00 111 0.56%
306 1-1-2014 07:00 111 0.52%
307 1-1-2014 07:00 115 0.53%
308 2-1-2014 07:00 111 0.56%
309 2-1-2014 07:00 115 0.58%
310 2-1-2014 06:00 111 0.54%
311 2-1-2014 08:00 115 0.53%
I try to find the ID corresponding to the maximum date+time grouped by Machine.
In this case I would like to find 311 (maximum date/time for machine 115) and 308 (maximum date/time for machine 111)
The way to extract the maximum date + time is not difficult
SELECT Max([Date]+[time]) AS datevalue, tbl_TexControl.Machine
FROM tbl_TexControl
GROUP BY tbl_TexControl.Machine;
But how do I get the corresponding ID? Not with Max([ID]) because then ID 310 is selected which is not the maximum of the time.
Model Full Name: "Jones, John P." (this was the one not in MS' examples)
Last Name Extract = Jones
lastN = IIf(InStr(1,[Model Full Name],",")=0,"",right([Model Full Name],len([Model Full Name] - (InStr(1,[Model Full Name],",")+2)))First Name Extract = John
firstN = IIf((InStr(1,[Model Full Name],",")+1)=InStrRev([Model Full Name]," "),Right([Model Full Name],Len([Model Full Name])-(InStr(1,[Model Full Name],",")+1)),Mid([Model Full Name],(InStr(1,[Model Full Name],",")+2),(InStrRev([Model Full Name]," ")-1)-(InStr(1,[Model Full Name],","))))
Full Name, Forename, Surname, Salutation. This data has been extracted from another database where the Surname field was not required but it is now. So i need to find a way to pull data from the salutation field into the surname field where salutation has data but surname is null. The next one when surname and salutation are null but full name has data i need to pull that across.
View 11 Replies View RelatedI have a query that I run to extract some values for calculations. Its run on an event based on a dropdown selection. At the moment, I run via:
Code:
DoCmd.OpenQuery "CallMidprice"
and then close it. this seems to take more time than is necessary when I only want to refresh the results.Open query without close, switches to the table but doesn't update until I press F5.When I try requery I get an error. "Runtime error '2109': There is no field named 'CallMidprice' in the current record.
Requery without 'Callprice' seems to requery my form.
i would like to make a query in ma access can extract text between first and second character "/" and when there are not "/" in field it returns null.now data in my table are like below
Quote:
No option1 1 100 2 145/Mechanical/0800 3 120/electrical/1620 4 131/mechanical/0200/dw-001
Now I like to make a query can extract text between first and second character "/" like below: No option1 discipline
Quote:
1 100 null 2 145/Mechanical/0800 Mechanical 3 120/electrical/1620 electrical 4 131/mechanical/0200/dw-001 mechanical
I have a list of dates on which tasks were performed.
I want to be able to see if there is a pattern, i.e. lots done on a Monday
Can I extract the Day from the date, and return a value Monday, Tuesday, Wednesday etc??
I have a field that contains the following data:
FirstName:Pedro LastName:Campos Restaurant:BI Strand Location:North Month:March
I need to extract the data into different fields like this:
Field1: Pedro
Field2: Campos
Field3: BI Strand
Field4: North
How to extract the information I want into different fields