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
I thought I had solved my initial problem of removing the apostrophe character for each zip code field. Most of the fields have data that reads '12345-1234'. I want to remove each (') character and the "-1234" so I end up with a zipcode of 12345.
I created 2 queries for this process. I first run a query with the following statement "Left([ZIP],Len([ZIP])-6)". That gets rid of the "-1234' ".
The I run the second query with this statement "Right([ZIP],Len([ZIP])-6)".
These 2 queries work perfectly if the original zipcode is " '12345-1234' ", but if it is " '12345' ", the entire zipcode is deleted.
I have attached 3 (.jpg)'s to show you what I am talking about.
I have a bunch of server names that have names like aaa.bbb, xxx.yyy. All i need from these server names is the name before the first "." so in the first example all I would need is aaa. I've been using this formula in excel MID(A1,1,FIND(".",A1) - 1). This works great.. Is there any way to do this in access?
Thank you for taking the time to look at this Question.
I am using a form in which i only need the last sections.
example:- G/001 - Ceiling - Tiles - Perforated. this is what i get at the second, but i would like to drop the "G/001 - " and just have "Ceiling - Tiles - Perforated.", has anybody got any ideas on how to do this please.
I would like to remove the comma, but only one that is at the end of my string(there is a space after the comma, should be also removed).How should I do that in vba?
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.
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.
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 ...
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!
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.
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.
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
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?
I'm not good with access at all, i'm basically after removing all workgroup protection from a database file. I have full access to the file, admin passwords etc, i'm just having serious difficulty trying to find a tutorial or any information regarding how it can be removed!
I use the template service call management . In the work order section there is a field called Entered By . I would like to remove it as I do not need it and if you do not enter it the work order will not complete . Is there a way to remove it or make it so you do not have to enter anything there >> I have tryed to remove it but somehow it is connected to some thing else and I get error
I posted an earlier question about an Access query export to Excel putting leading apostophe (') before all data. To remove them I thought I would use Find/Replace but the Apostophe is not recognised!
Is there a way (programmatically) to remove the first character of each data entry in each cell in a column?
