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
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
My users do a fair bit of copy/paste from any number of sources.
How do I force the paste action to strip out any hidden characters? Now I realize that a return and a space are hidden characters, so how do I strip out everything but the ones that I want?
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
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
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!
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 RelatedIs 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 RelatedI 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!
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.
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 RelatedIs 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
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-------------------------------------------------------------------------------
If possible, I'd like to some help with a query that strips the address number from a Parcel Address and leaves only the street name left.
Before:
1234 Somewhere St.
321 Crossing Meadows Dr.
1234A N. 9th St.
After:
Somewhere St.
Crossing Meadows Dr.
N. 9th St.
Thanks,
SKK
How can i strip out punctuation marks from a field that i am calling in a writeline command? There is a comma in the name (vendor name) and it throws off my import into my accounting program.
View 4 Replies View RelatedMS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
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.
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 = ;
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
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???
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
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.
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
I am trying to write a query to extract the first 3 characters from a character string but have no idea where to start since we usually use Select "field" etc etc
Example, for a Code ACC01, the query result should give ACC
Would appeciated any guidance. Thank!
TK
I have several queries looking up products from a table. I use a simple form to access the query and in the criteria of the field I use [Forms]![EnterCroft]![ProductName] to filter by name. (EnterCroft is the name of the form).
It works fine but doesn't like the asterisk (*) for all products.
I also want to use wildcard before and after key words in the product names, eg *cup* for anything with CUP in it's name.
How can I do this?
Thank you very much.
Hello all. Have a bit of a strange one here (and fairly frustarting). I have a table which includes a field (type memo) which holds text comments.
I have a query which includes this field. For some reason, when the comments field is empty (identified thorugh opening the table), when it is returned through the query is shows two characters, namely '@v'.
Can anyone shed any light on this for me? Thanks for reading :)