Trimming Off First 7 Characters In A String

Oct 21, 2005

My DB is on SQL Server with an Access adp front end.

SQL uses the Windows NT user name and password for security and also pulls in the user name into a field in every record so we know who has added and modified it.

For one table we are actually using the user name field on the form and in a report for reasons other than security.

The user name is filled in with first the name of the network group, then a slash, then the user name itself. So there are 7 characters I don't really care about.

Is there a way in VBA to remove those first seven characters and only show what comes afterwards? Thanks for the help!

View Replies

Trimming Characters

Feb 25, 2007

Good Morning to all

i would like to kindly ask if there is a pre defined function in the VBA SQL of access in which it will trim a certain field programmatically

for example

Old string = Manila-MSE Building - 30.26f
New String = Manila-MSE Building

Thanks for your help

View 4 Replies View Related

Strip Last 21 Characters From A String

Sep 21, 2005

I need to be able to strip the last 21 characters from the following strings using a command in a query. Any ideas? I know I need to Instr command but not sure how to do it.

Greg
Impulse Manufacturng

96803-386868-386869-163241
INSP-F96191B-384761-389552-162206
INSP-F96191A-384758-389551-162205
F96191A-384758-389551-162205
F96191B-384761-389552-162206
INSP-97407-389462-389463-164428

View 5 Replies View Related

Want To Know How To Chop The Last 3 Characters Of A String

Nov 24, 2005

I am new to VB and want to know how to chop the last three characters off a string. Am writing dynamic SQL and have to take the AND off the last WHERE clause if needed.

Any help greatly appreciated

View 5 Replies View Related

Removing Characters From A String

Jan 5, 2005

is there a way to do this in VBA in Access I looked at trim but it does not help much

Dim MyString, TrimString
MyString = " <-Trim-> " ' Initialize string.
TrimString = LTrim(MyString) ' TrimString = "<-Trim-> ".
TrimString = RTrim(MyString) ' TrimString = " <-Trim->".
TrimString = LTrim(RTrim(MyString)) ' TrimString = "<-Trim->".
' Using the Trim function alone achieves the same result.
TrimString = Trim(MyString) ' TrimString = "<-Trim->".

I have a select statment setting the data that will be in a combo box
I would like to trim this data...

for example the select statment returns something like
category>subcatgory>field>subfield

so I want to trim it down to be
field>subfield

View 3 Replies View Related

Modules & VBA :: Cut Off First 6 And Last 2 Characters Of String

Mar 24, 2014

I would like to cut off the first 6 and last 2 Charaters in an after update event but not sure how, I cannot use mid as the length of the string may change but never the first 6 or last 2, can some one show me how it's done ...

View 4 Replies View Related

Characters Before Searched String?

Dec 9, 2011

Is there a function, similar to mid, except that it will return all characters prior to the searched string instead of after?

View 4 Replies View Related

Parsing The Left And Right Characters Of A String

Aug 19, 2005

I have the following string: NE1220904
I need to remove the left two characters and the right four characters of a string in a query. I have found how to remove one side or the other, but not from both sides. Thanks in advance for the help!

View 6 Replies View Related

Left Three Characters From String Using Query

Sep 8, 2005

In MySQL I can use the LEFT(3, field_name) command, but I essentially want access to pull all records from a table where the the first three digits of a number field match those pre-selected by a form drop down.

This database is in Access 97. Is there a way to compare the first x number of digits using SQL only, or do I need to run each line through code first, and then check it (I really don't want to do that)?

example numbe rin field:

123456789

SQL checks to see if 123 matches value selected on a form.

View 1 Replies View Related

Queries :: Replace 2 Characters In String With 0s

Dec 8, 2014

I'm familiar with writing a replace query to replace characters or strings but what I'm trying to do this time is a bit unique to me. I have a string of numbers that will either be 8 or 9 digits in length. The first 1 or 2 digits will be the State code (1-50 hence the discrepancy in number of total digits), then a 2 digit agency code, then the last 5 digits are a producer code. What I need to do is change the 2 digits for the agency part both to 0. So basically characters 6 and 7 if you're counting from the right. To me it feels like I'm trying to do it backwards so I'm having a hard time writing it.

View 12 Replies View Related

Strip A Filename String Of Any Disallowed Characters

May 8, 2006

I am writing some search results to an excelsheet for reports using the time and date functions to build a filename. I downloaded a StripSpecialCharacters() module, but it doesn't take them all out (only takes ascii above 127).

Does anybody know of another function to do it. searched the archive to no avail.

Thanks in advance

View 5 Replies View Related

Modules & VBA :: Check If String Contains Numeric Characters?

Nov 7, 2013

Is there an easy way in VBA to loop through a recordset and determine if the text string in a text field includes numeric characters?

I'm trying to do something like this:

Code:
Do Until rst.EOF
rst.Edit
If Left(rst!FldText,10) contains any numeric characters Then
rst!FldType = "Mixed"
ElseIF Left(rst!FldText,10) contains "PO" Then
rst!FldType = "PO"
Else
rst!FldType = "Std"
End If
rst.Update

View 2 Replies View Related

Queries :: Possible To Query A Field For Special Characters In A String?

Oct 11, 2013

Is it possible to query a field for special characters in a string? I want to find any records that have characters other than A-Z, 0-9

View 9 Replies View Related

Trimming An Address

Mar 21, 2007

I have a table that has about 5000 street addresses (ex. 1234 your st.). I want to get the all the characters until the first space. So for (1234 Your St.) I want to get 1234 for W1234 St I want W1234. Is this possible?

View 1 Replies View Related

Trimming Query Return Value

Aug 25, 2005

HI, i have a field in a query called [cost_type], these typically contains the values "principle works" or "additional works" how can i set the query up so that it doesn't return the "works" part of the record? Could i just return the first 10 characters of the result?
thanks

View 1 Replies View Related

Trimming Data In Listbox

Oct 26, 2006

Another issue for my contacts database for work. I have a listbox on the edit contacts form that lists all contacts in the database.

The listbox rowsource is
SELECT [ContactID], [LastName] & ", " & [NamePrefix] & " " & [FirstName] & " " & [Business/Organization] FROM tblContacts ORDER BY [LastName] & ", " & [FirstName] & " " & [Business/Organization];

Some of the contacts that are businesses or organizations, do not actually have the first and last name filled in, just the business/organization name. So what happens in the list I get all of the entries that do not have FirstName/LastName at the top of the list, with the Business name following a few spaces. The way it looks is:

, Stop & Shop Supermarkets

But I would like to trim that beginning part if there is no FirstName/LastName so that Stop & Shop Supermarkets gets sorted with the S's, like this:

Stabile, Lisa
Stop & Shop Supermarkets
Stott, Joan

Is there something I need to put in the rowsource to accomplish this? I've searched these forums for an answer, and turned up no results.

Attached, I have a screenshot of the form with the listbox.

Thanks
ScrmingWhisprs

View 4 Replies View Related

Is Trimming Field Size Where Suitable Good Practice?

Jun 6, 2007

If I have a DB with several tables containing thousands of records, and most of those records only ever need say 8 characters, does anyone know if changing the field size from default 255 chars to 8 chars will actually benefits the the DB?

Potential benefits I'm thinking may occur are reduced filesize and maybe some speed?

View 3 Replies View Related

Modules & VBA :: Trimming White-space After Pulling Data From Excel Cell?

Apr 15, 2014

I've got a piece of VBA scripting which runs as an event linked to a button on my MS Access form.

I maintain a database of members of staff at my organisation. It's pretty outdated...

I'm basically wanting to pull in their updated data (extracted from on our payroll system) from a spreadsheet, into a form, when clicking a button on a particular person's record.

This is what I have so far.

Code:
start = Forms!frm_main2!txt_start_row.Value - 1
conv = DDEInitiate("EXCEL", "Staff List.xlsx")
cell_employee_number = "R" & start & "C1"
cell_surname = "R" & start & "C2"

[Code] ....

The function "CStr(DDERequest())" converts the cell number into the readable data, however I seem to have whitespace below the value.

What would I need to do to strip out this whitespace? Would I use strtrim? If so, I am unsure of the syntax... how would I incorporate strtrim into the above?

View 7 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

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

String Validation (string Must Start With Http://)

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

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

Corrupt Characters

Oct 3, 2006

My client is reporting corrupt characters cropping up in fields. These characters appear as Asian Characters. They only corrupt one record.

This happens very seldom. In fact, it hasn't happend for about 2 months and today it happend again. Their was no change to the database.

Is this a network problem? Network noise corrupting data?

Thanks

View 5 Replies View Related

Characters In Query

Feb 19, 2007

Hi
I want to ask something simple but because i am new i am dont know it!!! If you try to make queries in Access, untill how many characters you can put in each query???

View 14 Replies View Related

Help Removing Characters

Feb 21, 2007

Hello,

I am totally new at this database stuff. I have been searching the archives for a couple hours and confused on how to accomplish what I need to do. I saw references to using a module but I have not used that feature yet.

I am using Access 2003. I need to remove/delete " - " space dash space, "-" and "&" from a string in one field (DGName). I'm trying to do this with a make table query

DGName
P1000
P1000 - SMLS
P1000-CA (not a type error)
UD000 - C&B
V-NET

and I want it to come out like this

DGName
P1000
P1000SMLS
P1000CA
UD000CB
V-NET

Thanks
Sean

View 2 Replies View Related

How To Extract Only The First Four Characters

Mar 20, 2007

Hi, Everyone,
I have an excel column that has building location and building name in one column, example 1245 Accounting, how can I copy the 1245 into it's own column, please help, i can do it either in access or excel. Please.

View 3 Replies View Related

Only Want To Write The 1st 7 Characters

Jan 24, 2005

users are using a hand held scan gun to enter data into a field. if the barcode is 12 characters long, I only want the 1st 7 to be written to the table.

I was thinking a validation rule using TRIM or LEN but can't seem to get it to work.

Any ideas?
Thanks

View 7 Replies View Related