How Do I Trim Comma Space At The End Of A String?

I have a list box with an onClick that grabs the selection(s) made and pops them into a textbox on another form seperated by a ", ".

I don't want the last selection made to end with the ", "

txtselection = strList
Forms!FormName![txtselection].Requery
Trim ([txtselection]Right(", "))

How do I use the Trim and Instr function to remove a ", " at the end of the string?

View Replies


ADVERTISEMENT

How Do I Trim A Blank Space From The End Of A Text String?

I have the following code, which works great, except when the strSourceText is written to the new file, it has an extra space or carriage return on the end leaving a blank line in my final Text File. What code do I need to correct this and where do I put it?

SAMPLE CODE:
' cycle through the array again, but this time, open the file
' read it and put it's contents in the source file
For ctr = 0 To 3
' open the file for reading (thus the 1) and do not create it if it doesn't exist
Set strSourceFile = objFile.OpenTextFile(arrFileNames(ctr), 1, False)

' read the contents of the file into the strSourceText variable
strSourceText = strSourceFile.readall


' close the source file and release the variable
strSourceFile.Close
Set strSourceFile = Nothing

' write the data into the file.
strNewFile.writeline (strSourceText)

Next

' close the destination file
strNewFile.Close

View Replies View Related

Trim Comma Delimited Value Stored In A Text Field

With microsofts article, I have made to store multi options value of a list box in a text box with comma. However, since these are IDs being stored, I want these values to run a query and get results also.

But I am confused since have never used comma like in query

View Replies View Related

Remove Comma From String In Query

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?

View Replies View Related

Out Of String Space

What does the
What does the "Out of string space" message mean?

View Replies View Related

Search String For Space

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 Replies View Related

Comma Delimited For A Field With A Comma - Version: 2003 (11.0)

I need to export a file that has a name field with last, first formatting into a comma delimited formated file. When it is exported it separates the last, first into two fields. I then go into Excel and add a column J, I apply the formula =H1&", " &I1, then I copy and paste special value, delete H and I and save. Then the program that it is uploaded into, accepts it. Is there a way to avoid the Excel massage?

View Replies View Related

Left Trim / Right Trim

Hello,

Does anyone know of a way that I can get rid of characters off a product code so all I am left with is just the characters greater than zero?

Example...

AA0000000652618... I only want to be left with 652618.

Would it be best to use a left trim or to use a right trim function? Unfortunately there is no standard for the product numbers... meaning that some numbers are 15 characters in length (as is above) but others maybe shorter or longer...

Also where I would add in the Right or Left Trim piece of code?
Regards,

Peter Vav

View Replies View Related

Space Or No Space, That Is The Question - Version: 2003 (11.0)

if you have the option of advising your users on the best practice for inputing a last name like De Artega, would you have them eliminate the space ? I am building a table of 1,029 teacher names and was Just curious on how a veteran would handle this. To me it seems no space would be best thus reducing the possibilty of the user typing in extra spaces and also no space would make it consistent with other last names.

Rick

View Replies View Related

Trim Numbers

Tried to search for this, maybe I am searching the wrong term.

Anyways.. I have a table linked from a large mainframe, and what I am trying to do is trim any numbers that are 1 million and over, and still keep it a number.

Currently I have this:

VEHICLE_KMS2: IIf([VEHICLE_KMS]>999999,Left([VEHICLE_KMS],6),[VEHICLE_KMS])

What I need is to have it remain as a number, seems that the left function turns it into a string value.

Thanks

View Replies View Related

Trim Query

Using the built in function within access 2000 how can I do the following

I have the following types of number in a table

84125
80245

I want to be able to trim the leading 8 of the first number but on secon number I need to trim the 80 off the number.

I can quite easily trim off the 8 off both numbers t am left with a leading 0 on the second number. This has to also be trimmed.

The number will always be 5 digits long

Is there anyway of doing this by using the built in function Len or is there another way.

View Replies View Related

Mid Or Trim Function?

a maybe simple question:

With the mid function one can make sure that a part of a text is token to display on a report, output or whatever.

But how to do the following:
"John Isaac"
"William Bill"
"Michael Robert"

In the report the following has to be visible:
"John"
"William"
"Michael"

The use of mid is i think possible, and starting with the first letter as well, but how to determine when the space is coming?

Please advise!

View Replies View Related

Trim Function

Hi there

I saw my question in an online tutorial once but can't find it.

I have about a thousand records and I'm just concerned about one field.
I have to strip away part of the text.

Heres an example:

AFG-006/2/E
AFG-006/E
AFG-008/1
AFG-008/1/E

I have to strip away all of the left part from the ascii character (/)

so all the records would look like this:

AFG-006
AFG-006
AFG-008
AFG-008

I'm sure there's a way to do this with Trim, maybe Ltrim but I'm not sure how this would be done. Does anyone have any ideas. I have over 1000 records which means it wouldn't be practicle to do it manually.

Thanks

View Replies View Related

Trim To 2 Decimal Places

I have this code:

MaxCPC = Bid2 / 7

But I want MaxCPC to be stored to 2 decimal places. If I click in the field, quite often I get something like 2.3256 but I only want 2.32.

How?

View Replies View Related

Error With Trim Function

I have used the Trim function in a query and am getting an error. At first I was concatanating several fields but have cut it back to the most basic form but still receive the error. The error says that the trim expression failed. Here is the code I used:

fullname: trim([last]) + ", " + trim([first]) + " " + trim([middle])

I then used
fullname: trim([last])

I got the same error. Just using the field last does not generate an error.

Any ideas? I created the DB in Access XP and am getting the error in 2003. I tried the changes in 2003 but still got the error.

thanks. :)

View Replies View Related

Trim Function In Query

Hi all,
I have a local table that has data imported from a spreadsheet
and we have discovered that one of the fields brings in trailing spaces. Since the Access DB has some querys to manaipulate the data I thought I would just create a qry that would trim the value of that field but I am getting error messages when I try to run the query.
Basically it is a update query with the field value written as:-

Code: Trim([Code])

I am leaving the update field in the qry blank and it complains about it not having a destination field which is understandable so how do I go about trimming the value of a field in a query. Is it even possible ?

Thanks in advance,
Mitch....

View Replies View Related

Trim Within Makeup Query

I have a makeup query, were i am tryin to trim one of the fields, how ever i keep getting this message,

Extra ) in query expresion 'TemplLines..[Trim([ARTNR])].'


However in the field i have typed this "ProductNumber: Trim([ARTNR])"
I cannot see were i am going wrong. I have included a print screen of my query any ideas.

View Replies View Related

Trim Like An Excel Mofo

Hi there.

I have a little problem.

I have a file that I split into little code files each with their own little Code and description. Now whoever the smart one was that is making these codes and descriptions has put double spaces between some descriptions and before and after some others.

Now in excel I don't have a problem removing them. I just use the good old Trim() function. But in access like most other programs the trim function just removes the leading and trailing spaces but leaves the double spaces inside the actual strings there.


Now my question is this. Is it possible to write a query or macro that would inspect each description and search for double spaces and either remove them if their on the end/beginning of a string or replace them with a single space if they are in the inside of a string.

Basically a search and replace function i guess is what im looking for here.

View Replies View Related

Query Expression Trim

Hi Guys

Another expression that's troubling me. I need an expression that can take this field.

Z:MusicDownloadDDale DaviesUntitled EPDale Davies - Untitled EP - 05 - No Perfect Child.mp3

Trim the front to the 4th ie. Dale DaviesUntitled EPDale Davies - Untitled EP - 05 - No Perfect Child.mp3

Then the end of it ie Dale Davies

So all I have left in a seperate field is the Name. Also I'd like to know what parameters to change to trim to the 2nd or 3rd .

Thanks alot guys

Adam Greer

View Replies View Related

Access Substring Or Trim

I use this SQL in another query platform, but how can I do it in an access query object?

SUBSTRING (AL1.TRAIN_ID FROM 1 FOR 3)


Which cuts my 7 length string down to 3 char.

View Replies View Related

Trim Field To 35 Characters

I have a field "DisplayURL" that contains values of various lengths. Some are only 12 or 13 characters long, but others are 50 characters long. I need to run an update query to "cut-off" all the characters past the 35 limit - so that all records in the field will be 35 characters or less.

I've searched this forum and online and there are similar posts but nothing quite like this, I thought it would be a trim function but I keep reading that it only removes spaces. Any ideas?

View Replies View Related

Trim Function In Access?

Dear all:

Is there a trim function that exists in access vb so I can apply to a text box to remove irregular spaces in a textbox field?

I am importing an excel file and a column has names with irregular spaces in it. Hopefully this trim function can solve that issue.

Many thanks,

Regards,

Dion

View Replies View Related

Trim The First 3 Characters - Version: 95 (7.0)

I want to Trim the first 3 characters and return the rest of the characters in the field to a new field

72W999QA99 into 999QA99

there are fields with less characters as well.

Thanks

View Replies View Related

Trim? - Version: 2000 (9.0)

Hi

Im trying to update fields to equal the text within that field minus 1 character to the right,

eg if the field contained "jump" I want to update to "jum"

I tried using Trim(Right([field])) but ended up with "p"

Any tips?

Thanks

View Replies View Related

Import Csv With Comma's In A Field

I have a csv file which seperates each field with a comma. Now i have some fields that contain comma's within them but they are enclose in quotation marks. How would i import it so that it doesnt seperate these fields?

View Replies View Related

Importing Problem Due To Comma

I use TransferText to import some data. However, I am getting errors where the data is greater than $1,000 because it has a comma in it! But the file is comma delimited.

The row of data that causes problems looks like this:

001000002002105000PMTo,528,79,$1.95,"$1,030.95"

They have put quotes around it.

How do you get around this problem?

Thanks,

Dave

View Replies View Related

SProc With Comma Value As Parameter

How do I send a comma value to a stored procedure?

In Denmark we use comma as decimal separator (whereas US uses dot) and I'm having issues with sending Single values to a stored procedure! If I use dot as a separator Access will simply remove the dot. E.g. 2.5 turns into 25 and 2,5 doesn't get inserted at all (or updated for that matter). If I just run the query from the database window all is fine but not through ADO. Is there anyway to tell Access that this is a Single value and not a delimiter?

Regards, Jon.

View Replies View Related

TRIM() Function In Query Help Needed

Hello, need help with the Trim() function. In a query I am using this SQL:

SELECT TRIM(tblAllMembers.First_Name), tblAllMembers.Middle_Name, TRIM(tblAllMembers.Last_Name), tblAllMembers.Address1, tblAllMembers.Address2, tblAllMembers.City, tblAllMembers.StateOrProvince, Trim(tblAllMembers.Postal_Code)
FROM tblAllMembers
WHERE (((tblAllMembers.City) Is Not Null) AND ((tblAllMembers.StateOrProvince) Is Not Null) AND ((tblAllMembers.Country)="USA"));

However when I run the query, the First_Name, Last_Name and Postal_Code doesn't trim leading and following blank spaces.

Help please

View Replies View Related

Trim First 4 Characters And Link To Another Table

I have the following code:

#: Mid([Emergency-Approver],InStr([Emergency-Approver],"EBF-"),5)

Here is my problem. I need the number that is at the end of EBF, sometimes there will be a dash some times a space, i know that I can use RIGHT to remove the first 4 characters, however I do not know where to place that in the above code.

My second issue is once I have the number only showing in the field I want to link that answer to a table that has a description of what that number represents.

The table name is Approval_Code. I want to have it do a VLookup type of function however that is not available in access. So I need to know how to get that result in a new field.

Any help would so be appreciated

Thanks
Rue

View Replies View Related

Trim Text Box Causing Error?

Hi all, this is my first post, I'll try and make it a good one :-)

System:
Windows 2000
Office 2000

I have a small network of PC's here which have access to our central database. I have a simple form created using the label wizard which is used to print address labels for mailing lists.
On most of the PC's here it works fine but on one or two (which have no obvious difference in thier setup) I get an "ODBC--Call Failed" error and it won't run the report????
I have puzzled over this for a while when I decided that I would remove the Trim function from the lines of the address fields on the label, these are put in automatically by the label wizard. This stopped the error message and the reports now run.
I don't know why this is happening but I am hoping someone will.

Thanks,

Matt.

View Replies View Related

GTRIM - Trim Leading/Trailing ANYTHING

Code Archive post





This is the function I have been looking for. Thank you. However, I am a novice at Modules and user defined functions. When I copied the module to mine (Access 97) and attempted to use the function in a query criteria the message "undefined function 'gtrim' in expression" is returned. I went back to the downloaded database, developed a trial table and in running a query in that table with the gtrim expression, I got the same error message. Can you help me?

Edited by: ghubbell on 02.19.04.

View Replies View Related

Trim Or Mid Function - Version: 2000 (9.0)

I'm having a problem trimming off characters from the beginning and end of a field. Id like to remove the first 3 and last 2 characters from text in a field called PARID. Im assuming I use the MID function in a module. However Im not having any luck. Any help would be greatly appreciated.

Thanks,
SKK

View Replies View Related

Help Wih TRIM And MID Functions - Version: 2000 (9.0)

I need some serious help. I'm trying to extract some data from a text field where the data is not entered consistently. The data can be entered as '909182 (Vol. 82)' or '909182 (Vol 82)' or sometimes even as '909182 Vol (82)'. What I'm trying to extract is the '82' out of the text field.

I keep getting an error on the following expression:

Expr5: IIf([PCN] Like 'Vol.*',(Trim(Mid([PCN],InStr(1,[PCN],"Vol. ")+1,InStr(InStr(1,[PCN]," ")+1,InStr(InStr(1,[PCN],")")-1))))),(Trim(Mid([PCN],InStr(1,[PCN],"Vol")+1,InStr(InStr(1,[PCN]," ")+1,InStr(InStr(1,[PCN],")")-1))))))

Can someone please let me know what I'm doing wrong?

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved