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?
View Repliesis 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
Dear All,
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?
View 2 Replies View RelatedHi 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 a criteria in a parameter query that asks the user to enter a Subsystem number (such as 4596-666-001).
The source data for my database has Systems (e.g, 4596-666) and each system has several Subsystems associated with it (e.g, 4596-666-001, 4596-666-002, etc). The System and Subsystem numbers are stored in the same column of the source data spreadsheet, and hence the same field in my database.
The source data is structured in such a way that Systems and Subsystems have different information. I would like to set up a query that shows the System information as well as the Subsystem information.
For example, if the user enters 4596-666-001 into the parameter dialog box the query returns all the information for 4596-666-001 and 4596-666 (but NOT any other Subsystem).
As a work around I have set up an OR situation where the user first enters the Subsystem number and then another parameter query dialog opens and they enter the System number. This works but is not as nice as simply entering the Subsystem number and having all information returned.
Is there a way to set up my query so the when 4596-666-001 is entered an OR is generated using the first 8 characters of the Subsystem number?
I'm trying to find a function in access that could do the following:
ZAB103-3012
ZAF405-HD-0001
Turn those cells into:
AB103
AF405-HD
Pulling everything to the right of the Z in the last step is the easy part but I can't figure out what function would be able to find the last "-" in the cell and pull everything to the left of it.
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 have a list of consumables;
Syringe 50ml
Syringe 20ml
Syringe 5ml
Syringe Cap
White Needle
I want to remove only the number and the ml part from the list, so I would end up with;
Syringe
Syringe
Syringe
Syringe Cap
White Needle
If I use
PHP Code:
Replace([DrugNameVial],"50ml","")Â
I get the desired result for the 50ml syringe size.
I have tried every possible combination of "**ml", "##ml", "Like [0-9]ml all with no success.
How this can be resolved without having to individual enter each syringe size "5ml", "20ml" etc
I can't even just take the text from the right till the first space as this would lead to problems with other consumables in the list.
I know there are many posts on this but still cannot find what I want ....
I have a string ....
... <surname>bloggs</surname> <fornames>Jane</fornames> etc.etc.
... <surname>williams</surname> <forenames>Jo</fornames> etc.etc.
In a query I know how to:
Find the Start and End Postions as follows:
StartSurnamePos: InStr([string],"<surname>") returns 19
EndSurnamePos: InStr([string],"</surname>") returns 34
I also know that by adding 8 to StartSurnamePos I can get Bloggs but how do I stop it there.
Using left, Right or Mid how do I pull out just
bloggs Jane
Williams Jo in seperate fields?
Thanks in advanced.
I have a text box and currently this is my control source
="Testing " & [test]/3
test = 1000 so my text box reports:
Testing 333.333333333333
Is there a way to make it into a form like $333.33...Also is there a way to make [test]/3 come out in a money text form? like "Three hundred thirty three dollars and thirty three cents.
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
Thanks
S
Hi everyone,
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
Many thanks,
MS-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'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!
View 2 Replies View RelatedI am looking to create a make table query to eliminate some portions of numbers from a field.
i have 23899999999
I need to parse 899999999 into a new field.
Is there a formular for that. Do I use a LEN formula.
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
THXS for Help
Steve
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 there I am extracting user names from e-mails in an access database.
I built the following command to do it
Left([E-Mails by Source - Test]![E-Mail],InStrRev([E-Mails by Source - Test]![E-Mail],"@"))
where [E-Mail] is the e-mail address.
It works great. But I still get the @ and the end of the name.
Any idea how I can get rid of it (other than using find and replace)?
Thanks for your help.
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?
thanks in advance
nel
Can anyone please help me, I currently have a series of queries that are called by a macro, these create a number of tables. But when each query runs it provide a prompt stating what it is doing, my question is can these be stopped so that the queries run without a user being present.
Thanks
I have a table that has mutliple records for the same person. I know how to create a query to remove duplicates but i don't know how to create a table to remove duplicates but keep the most recent record. Is this possible?
ie
client status date
123 A 1/1/07
123 C 2/5/06
123 A 9/3/07
results
client status date
123 A 9/3/07
When I ve gone in and deleted a record from my table (new table just testing it) ie. enter number 1 deleted it and than gone in and entered (on the form) another enter the (auto number) goes to 2.
I want to know how to i get it to go back to the previous number.
thanks
I have two tables: “tmptable” and “CC_CODES”
Both tables contain the same data; however the “tmptable” contains updated data. How can I compare both tables and remove all the duplicate in the tmptable, leaving only the updated data.
I will be comparing the fields “IsMandatory” from both tables
I have a field that contains serial numbers. The serial numbers are entered in difference formats. Some will have dashes, spacing and periods separating the numbers (example: 06-65432 or 06 65432 or 06.65432). I am trying to create a search field for this and the different formats makes it difficult. Is there a way to remove all separators so that all parts of the serial number are together?
View 2 Replies View Related