I have a weight field that I've been populating with the weight and the unit of measure. I want to seperate those into 2 different fields. I've created a new field called UnitofMeasure and now I need to go back and remove all the instances of 'oz' and 'lbs' from the first field.
Is this possible with SQL?
I'm creating a string with an IIf statement and placing a comma between values. How do I delete the last comma at the end of the resultant string?
View 2 Replies View RelatedI have a field called "SC" with a value 13-251. I need to remove the "-" ending up with 13251. I tried using, as a starting point, Mid(String, Start, Length) with no success. I found this in another topic, and thought this might be close to what I was looking for. I sure did not know how to use it. When I selected the run command I got the following response:
Query must have at least one destination.
I tried a few different things like adding an extra field and different pointers but I doubt if I'm even standing in the batters box.
I have a field called PropertyID which stores a unique 13 digit number, the 10th digit is always a 3. I want to remove the 3 to leave a 12 digit number.
I've tried running an Update query using Replace([PropertyID],"3","",10,1) but no joy, what am I doing wrong?
I have a manual date format that looks something like:
02/16/2015 09:06:15 AM PST
I would like to truncate the text so that just the date is showing. For all that I have found, I can remove a string from the beginning? any tips on removing a string from the end?
I'm using Access 2010.
I'm trying to append some text to a field used for comments for a Team Member profile. I don't really want to create a comments table for multiple comments regarding changes to the profile.
Some comments already exist and I want to add some more information programmatically when the profile is updated using a batch update at the end of a reporting period.
I thought it'd be easy in a query,
New Comment:[EXISTING COMMENT] & "; Additional Comment" But I was getting HTML Tags when I tried to concatenate the strings. "<div><font color=black> EXISTING COMMENT HERE </font></div>" then a linefeed and <div> </div>; Additional Comment HERE.
I found a function to kill all the html tags but the visual linefeed and the " " tag won't go away.
I'd like to concatenate two pure text strings to get one pure text string to put back into the comments field which will just be text wrapped with no forced linefeeds.
Hello, Tried a search but could'nt come up with an answer.
I have a number field with an amount.
The format will always change as far as the LEN is concerned.
I need to remove the decimal.
There will always be 2 numbers to the right of the decimal but the left will always change.
Example:
21543.11 Need 2154311
11.20 Need 1120
Thanks in advance! :)
How to get the last character of the string in query?
I have a table name PlateNo and I want to get the last character for registration purposes.
Ex. ZMD-123 - I want to get the number three(3) and if its possible every time the user types the last digit in criteria it will show all plate number ending in the entered number.
I have a program that has been working for a year or so, it imports records form a text file created by a web site. Someone has entered a hex("1A", "A8" in a name field. It is shutting my program down. I tried a alphanumeric check to replace it and a length check to try and skip the record but the program thinks it is at the end of the file and quits reading records. Is there a way to get rid of characters like this in vba?
"5407 MUYF3I23 GIS..LE "
Here is what I have tried
Code:
If Len(strread) <> 591 Then
MsgBox "Invalid Record Read " & strread
GoTo Skip_Loop
End If
If AlphaNumeric(CStr(Trim(Mid(strread, 32, 11)))) Then
!HT_FName = CStr(Trim(Mid(strread, 32, 11)))
[Code] .....
I've got a string variable with a value that could be typically
"ABCD|123|R"
The string needs to be split into its three parts, the pipe symbol being the separator. Then the middle numeric string must be converted back to a long.
I know i could do a loop which identifies each character in turn, but my question is:
Is there a VBA function that can pick out the position in the string of the "|" characters, so i dont need a loop ?
I have a string where alphabetical and numerical characters are mixed up. I need a string function where I can find the index of the first non-numerical character. I tried with the mid function but could not get it to work.
View 13 Replies View RelatedI have two table
1. dbo.period (OpeningDate, ClosingDate)
2. dbo.data (blah blah, doc_date)
I want to create a view as follows
Select doc_date from dbo.data
where doc_date> 'select OpeningDate from dbo.period'
both doc_date and opening date have the same format
but the error will still appear as follows:
"Conversion failed when converting date and / or time from character string."
Hi folks,
I have a table with the words "Church of (whatever)" in one of the records. I am attempting to do an update query to remove the "Church of" part but leave the remaining part of the string. I am using a combination of Format and left but am not having any look. Any suggestion are much appreciated.
Thanks
Colin.
I have this query:
INSERT INTO 1_2_06 ( Issue_ID, Assigned_To, ListType )
SELECT Issue_ID, Assigned_To, 'QPT'
FROM QPTActions
WHERE QPTActions.Entry_Date<=DateValue('1/2/2006') And QPTActions.Close_Date>DateValue('1/2/2006');
The Assigned_To values are a Lastname, Firstname format as they come out of the first database. But I need the comma stripped before they get entered into the INSERT database. Is it possible to do this on the fly?
I have a form where I want a textbox [txtMaxOrdLimit] to be visible only if another text box on the same form [PaNumber] contains the letter D in the string. This is the code I have on the forms On Current property but I'm missing something because textbox [txtMaxOrdLimit] doesn't show on the form at all.
If Me.PaNumber = "*D" Then
Me.txtMaxOrdLimit.Visible = True
Else
Me.txtMaxOrdLimit.Visible = False
End If
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 just want to get like if i have criteria like ;
Excellent
Good
Fair
Poor
Then what i need that in table if i select excellent then in query it should display 5, same if select good then 4, then fair 3 and so on..
Im linking to sql across ms access and I have all records uppercase.I need to set StrConv with first character uppercase and every other lowercase which is not problem.
View 5 Replies View RelatedI have 4 fields I'm trying to combine, but I need to add spaces between field 1 and the rest of them. The total character count needs to be 22 including the spaces.
Example:
Field 1: THE
Field 2: 1234
Field 3: BOAT
Field 4: 0001
End Result: THE 1234BOAT0001
Need to add 7 spaces to equal 22 characters.
Fields 1, 2, and 3 can vary in number of characters.
I have a field that contains 12 numbers. I need to replace the middle four numbers with a character so that the entire number is not readable. How do I do that?
View 14 Replies View Relatedi have just taken over
I have just taken over some database which have a load of tables and queries that I suspect are no longer requirer. Is there anyway to see when a table or query was last USED in a database??
Any help is appreciated
I have table where First Name, Last Name and Initial are all in one field. What is the syntax to remove initial? Been playing with Right and Len with no success, I'm sure this has been done before.
View 14 Replies View Relatedexplain me the Query Expression to remove the first 7 characters in a column, to rum a Query.Eg - I have entry like - 4/8/11-Pipe...I need to return the result like "Pipe".How to build the query.
View 5 Replies View RelatedHow to create an expression that removes the ".tif" extension of a file path.The data looks like this in it raw form:
J:201303080056273_ELECTRICALRETAILER_00100562 73_ELECTRICALRETAILER_001_3.tif
In my query grid in the filed row I have the following:
ImageRef: Mid([strImagePath],111,38)
which produces the following:
00056273_ELECTRICALRETAILER_001_3.tif
00056273_ELECTRICALRETAILER_001_31.tif
and so on...
What I need to do is remove the ".tif" part of the data. because of the way the path is output with regards to the tif image number, I'm having difficulty in targeting only that data that comes before the ".tif" extension.Is there a method I can use that will remove the right 4 characters and in conjunction with my expression above produce the following:
00056273_ELECTRICALRETAILER_001_3
00056273_ELECTRICALRETAILER_001_31
I've managed to work out how to get the data without the file extension ".tif" with the following expression:
ImageRef1: Left([strImagePath],Len([strImagePath])-4)
which gives me the following:
domgennt.dggroup.comglobalResourceApplication sUNIeFlowIMAGES201303080056273_ELECTRICALRET AILER_0010056273_ELECTRICALRETAILER_001_3
and
domgennt.dggroup.comglobalResourceApplication sUNIeFlowIMAGES201303080056273_ELECTRICALRET AILER_0010056273_ELECTRICALRETAILER_001_31
How can I combine the following expression with the above expression to get what I need:
Combine - Mid([strImagePath],111,38) with Left([strImagePath],Len([strImagePath])-4)
to get this result:
00056273_ELECTRICALRETAILER_001_3
00056273_ELECTRICALRETAILER_001_31
In access I queried my results and now I have to do this last step. I basically have an ID column and a tax fee column. I am trying to remove the duplicated ID numbers but if they are duplicated I need it to add up the tax fee column so I can have one ID with all the tax fees added up together instead of several different ones.
I tried doing the equivalent to a pivot table (works perfectly in excel) but when I tried doing it in access, it did not work.
I have a table called Stock Levels which contains 3 fields. (ID, ProductID, StockLevel) ID is the Pkey, ProductID contains duplicates and StockLevel which contains different stock levels
and I am trying to remove the duplicates and retain the the data so I am left with the correct stock number
what I have done is the following, but I am still getting duplicate values in productid and stocklevels
SELECT DISTINCTROW id, productid, stocklevel into mynewtable from stocklevels
I have attached a screenshot of the table