Trim First 4 Characters And Link To Another Table

Jan 2, 2008

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

Trim Field To 35 Characters

Jan 26, 2008

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

Left Trim / Right Trim

Sep 29, 2006

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

Select Only First Two Characters From Table

Dec 5, 2005

I have a table which contains a nine digit number. How do I write a query to limit the restults to just the first two digits of that nine digit number?

Thanks

View 2 Replies View Related

Can A Field In A Table Be Wrapped At 50 Characters?

Oct 22, 2004

I have to import a field from Excell that contains more than 50 characters in the field.

I need the line to stop at the last complete word prior to 50 characters and start the next record in that field automaticly and continue to fill and wrap at the last complete word prior to 50 characters.

Would it be better to do this in another Office 2K program?

I have several thousand lines to type if this can't be automated. What a drag.

TIA

M :confused:

View 2 Replies View Related

Field In Table Not Allowing Certain Characters

Jan 18, 2008

Hi Folks,

I have a table called tblSchemeCodes with the following field names:

Field Name Data Type Field Size Required Indexed

strCompany Text 1 Yes No
strSchemeCode Text 3 Yes Yes(No Duplicates)
strSchemeName Text 100 Yes No
strOrigNo Text 9 Yes No
strCategory Text 9 Yes No
dtmDateAdded Date/Time No No

Here is an example of some of the scheme codes I have:

HG
HGA
HGB
HGD
HGF
HGG
HGJ
HGP etc etc

however when I try to add HGE, it won't allow it, even though it doesn't exist within the table, it keeps changing it from HGE to HE, and HE already exists, so a can't add messgage pops up, which is should, because duplicates are not permiited.

I've tried turning off all of the required aspects and changed the "Yes (no Duplicates)" to "No", and the problem persists!!

Has anyone any idea why access would behave in this way and how I might resolve it.

Any assistance would be appreciated

John

View 14 Replies View Related

How To Search An Access Table For Unwanted <cr> Characters

Apr 3, 2006

How to search an Access table for unwanted <cr> characters

Occasionally a stray carriage return <cr> Ascii 013 character finds its way into an Access table. These destroy the database when the table is processed by an outside utility for data cleansing.

To prevent this from happening, we have been told to clean the table before submitting it, i.e. remove all of the following:

carriage return, comma, double quotes, equals, greater than, smaller than

Is there any utility available which will remove all these characters when being run only once? (i.e. not find/replace which has to be started separately for each of these characters)

How do I search for a <cr>, even with find/replace?

Thanks.

Adrian

View 2 Replies View Related

Tables :: Subscript / Superscript Characters In A Table

Nov 1, 2014

if it is possible to store text in a table that includes subscript / superscript characters. As an example; need to indicate the units of measurement for some data and therefore need to be able to pull data from the table such as the following: kg/m2, m/s2 etc. In both these case i need the 2 character to be superscript.

I'm trying to make this an automated process so pulling it directly from the database.o a method of storing the data as a text string would be ideal.Otherwise i imagine a rather difficult VBA function will be involved

View 1 Replies View Related

General :: Querying A Table With Wildcard Characters

Jan 18, 2013

I have a data base,one of the field contain Data like "ZZZ-DEFS#UUH1234567".

There should always be 19 characters in this field including #.I want to design a query which can sort out entries less than and more than 19 characters, so that wrong entries can be corrected .

View 3 Replies View Related

General :: Special Characters In Table Values

May 1, 2013

My database has several tables (and queries) that have fields that contain people's names. Some names, like O'Neil, contain apostrophes. Other fields contain couple names, like Tom & Laura Jones. Both the ' and the & prevent queries, forms, and reports from working correctly.

View 3 Replies View Related

Using Boolean 'and' To Update A Table Based On Values In A Link Table

Feb 24, 2008

The attached Access XP file demonstrates my problem. I've included a form to make testing easier.

Each record in the Projects table has one or more linked entries in the Keywordlink table, showing keywords that apply to that record. Each record in Projects has a Yes/No 'Utility' field.

A third table, Keywords, supplies the keywords that the user can apply to records in Projects, using the subform on the main form. The Keywords table also includes a True/False 'Utility' field. I have set this to True for for the first three keywords.

I need a query, a series of queries or some VB code that updates Projects_Utility for all records to True if and only if the record's linked entries in Keywordlink include all of the keywords for which Keywords_Utility is True (a boolean 'and', as opposed to an 'or'). Otherwise, Projects_Utility must be set to False.

In the attached file, with the current settings in the Keyword table, the 'GetSelectedProjects' query should then produce single-row listings for ClientA and ClientF.

I'd appreciate any help you can give me on this. I'm not a programmer, but I can manage a bit of VB code if I have to.

View 5 Replies View Related

Queries :: Make Table Query - Remove Characters

Jun 26, 2013

I have a current file with GBP 12.00. I wish to remove the GBP in a make table query when ran to print off.

View 2 Replies View Related

Tables :: Two Fields In A Table / Link To Same Lookup Table

Mar 14, 2013

I have a table "Product" and in this table I have two fields "StoragePlaceID1" and "StoragePlaceID2". Both these fields link to the look-up table "StoragePlace"

*Attached Image "Product_StoragePlace" from the Access Relationship Window"*

When I want to add a new Product from my inter face i get the error you can see in the Attached Image "Save_Error".I think the problem is that the Relationship is defined as One-to-Many,there a way to define the relationship as Zero-to-Many.

View 5 Replies View Related

Trim Numbers

Jul 6, 2005

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

Trim Query

May 2, 2007

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

Mid Or Trim Function?

Dec 10, 2007

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

Trim Function

Jan 8, 2008

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

How To Trim A Numeric Value

Aug 28, 2015

I have tons of values for example...

043401792930179293000000000000
486934620469284720452458733452
548065110164648465132013541355

I only want to keep the values in red. 2nd, 3rd and forth columns of each value. Is there some sort of Trim I can do?

View 7 Replies View Related

Table Link

Jun 5, 2007

Is it possible i create one master table and link few table together??
In the mdb that i attached there have 3 table - tblMaster, tblConfiguration1 and tblConfiguration2.
Using the relationship to link tblMaster's field Configuration to other 2 table Configuration field but there only showing 1 table data.
Is it allow in tblMaster able to show this 2 table together??

View 5 Replies View Related

Can't Link New Table

Apr 23, 2007

hi

I have a new table that I added to the back end of my Access 2003 database. I compacted & repaired both the front and back and I can't see the new table in the front end still. Do you know why this is?? I can't find anything on this.
thanks-

View 2 Replies View Related

Trim To 2 Decimal Places

Sep 19, 2005

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

Error With Trim Function

Nov 1, 2005

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

Trim Function In Query

Sep 5, 2006

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

Trim Within Makeup Query

Oct 31, 2006

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

Trim Like An Excel Mofo

Dec 12, 2006

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

Query Expression Trim

Jan 30, 2007

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