How To Split Letter&number Field Into 2 Column And Skip Brackets?

Dec 2, 2005

HI!
I have big problem I have telephone number field like this
Tel(XX)XXXXX - X are numbers

I have to split it into two columns and skip brackets like this

Column1 - Tel
Column2 - XXXXXXX

Is it possible?

I have no idea how to manage it...I'd be very thankful for any
examples ? Or Help....I'm beginner

Thanks a lot
in Advance

View Replies


ADVERTISEMENT

How Do I Eliminate Brackets In MERGED Column

Nov 4, 2004

I recently MERGED two columns in a database file using the following code:

SELECT "(" & RESULT & ") " & QUALIFIER AS MERGED FROM SOIL_STL3_MERGE AS SOIL_STL3_MERGE;

The RESULT field was a number which I had to change to a TEXT field since my QUALIFIER was a TEXT field. Anyway the result was the field called MERGED which depicted the data as follows:

(100) U
(500) U
......
.......
I use the Find and Replace twice to get rid of the first ( and run it again to get rid of ) resulting in a field that looks like this:

100 U
500 U

My question is can I solve this through an UPDATE query statement through SQL or some other way with a function.

I would rather automate the removal of the ( ) or change my original code to NOT put brackets around my result.

Any and all help is most appreciated.

I thk you all in advance.

the raven man

View 4 Replies View Related

Question On Printing A Negative Number In Brackets: -5 -> (5)

Sep 7, 2004

I want to print negative numbers in brackets without the negative sign:
-5 beccomes (5)
I am doing this on a control that I am summing on. Currently this is what I am doing in the "Control Source" field.
IIF(Sum([field]) < 0, "(" & -Sum([field]) & ")", Sum([field]))

This works, however, I would assume it is running the sum 3 times (I do not know access internals, I may be wrong and would like to be corrected).

Is there a way to do this using the "Format" field??

Thank You
newbie and learning

View 5 Replies View Related

Skip Row Number Based On A Value

Dec 5, 2011

I have a report that is set up to count students in a class. Looks like this:

# Student Cert #
1 John Smith 12340
2 Joe Jones 12341
3 VOID 12342
4 James Adams 12343
5 Tom Arnold 12344

I want it to do this:

# Student Cert #
1 John Smith 12340
2 Joe Jones 12341
VOID 12342
3 James Adams 12343
4 Tom Arnold 12344

The row number is just a text box (=1) with the running sum turned on.

On this report I have to report all the certificates used for this class, in order. So I can't put the voided ones at the bottom.

I tried an IIF statement in the text box =iif([student]="VOID", "", 1)

This skips the VOIDs but starts over at 1 (no matter if it is set to "over group" or "over all"):

# Student Cert #
1 John Smith 12340
2 Joe Jones 12341
VOID 12342
1 James Adams 12343
2 Tom Arnold 12344

View 2 Replies View Related

Find The Lowest Number And Place The Number And Column Name Into In Field

Dec 15, 2005

I have a access table with 32 columns and 42,000 rows of numbers. I need to find the MIN number in the row and if the MIN number has duplicates then I need them all placed into another column by column name.
Example:
Starting file
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, ETC
05512,3,2,4,2

ENDING table needed
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, NEWCOLUMNname
05512,3,2,4,2,2 ORIGIN2 ORIGIN4

Where the new column name contains the MIN number in the row and all of the associated duplicates column names.

View 1 Replies View Related

How To Cut Second And Third Letter In One Column Records

Nov 2, 2005

Hi!
I have one column with text field
I would like to know how to cut for. example 2nd and 3 letter in records collected in this column?
Thanks in advance for any examples

View 1 Replies View Related

Split 1 Column Data Into 2 Separate Column

Oct 5, 2005

Hi,

I need a query that could split data into 2 different field.
The table pic is attached for your ready reference. I tird with Left & Right funtions with no results.

Any one can help me please.

Table name is Agcad and field on which I need to split is Board_Size. The problem is Board_Size data is sometime in 3 digit (first half part) and sometime second half part is in 4 digit. It is a text data type field.

Thanks in advance...
Ashfaque

View 1 Replies View Related

Adding Letter To Number

Mar 25, 2005

Hi,

I have a database in which info is imported from a flatfile.

In the database I have a query to select relevant info to appear on a report for invoicing.

Now the imported info adds a number in a column for all rows.

example: it adds 9600 to all invoices, based on the info that is imported.

Now I want the import and export invoices to be separate, and the numbers to display 9600A and 9600B.

Is there something I can add to the query in order to get this result?

Thx,

Lion85heart

Reading this, I think it might be a bit unclear, so pls. ask any questions that you might have.

View 7 Replies View Related

Letter + Auto Number From Surname

Oct 16, 2004

Hi i have to design a member database in access,
As the membership number i need to use the first letter of the surname,
then have an auto number.
e.g Smith, if the last member was S47 then Smith would need to be S48,
This will be used on a form for a video hire shop, so, the customer details
will be entered, is it possible for this auto number to work just by putting the name in
the surname field?

I only know basic Access so please help.

Thanks a lot

Nick

View 2 Replies View Related

How To Update A Query To A Letter Instead Of Number?

Jul 5, 2005

I have a query that I try to update to Alphabetical instead of numerical. I am using MS Access. on the row say "Update to" I Enter Alphabetical letter but it doesn't work. I wonder if any way to do so. Please HELP!
Thanks

View 3 Replies View Related

Skip Field In Export Specification

Nov 3, 2005

I run a query that I export out to a tab delimited file. This text file is used for another application. The problem I am having is if I select "skip field" in my text "export specification" it still exports out that piece of data, even though I have selected "Skip Field". The data type on this field is a number and field size is integer.

I thought I read in another access newsgroup that this is a known bug? Is that true? Is there a fix? I am anxious to fix this because, I have 90 query's that I export to text and I would hate to have to go to each query and select "Not Show" that field.

Help?

View 4 Replies View Related

Validation Rule - Must Enter Number Then Letter

Mar 30, 2005

Hello,

I was hoping someone could help me with a validation rule for a table.

What I require is for you only be able to enter specific data, for example the first character must be a number and be 7, 8 or 9, and the second character entered must be a letter, only letters available are A, B, C, D, E, N and S. For example these will be accepted 9C 8D 7B 7S 7D 8E, other will not (3S, 7G, B8)

Can anyone tell me what it is i enter into the validation rule box or direct me to any online help with this.

Thx

View 1 Replies View Related

General :: How To Generate Alphanumeric Number With A Floating Letter

Aug 7, 2012

I am trying to use access to connect to SQLServer with an insert query. I need to generate alphanumeric number for each record. The letter will move to the right as the alphanumeric number increase.

For example: A0001 , A0002.... -> A9999
0A000, 0A001 ... => 0A999
1A000, 1A001 ... => 1A999
2A000, 2A001 ...2A999
9A000,9A001 ...=>9A999
00A00, 00A01 ... => 99A99
000A0, 000A1 ... > 999A9
0000A....=> 9999A
Last number will be 9999A

View 1 Replies View Related

Split Access Column In Two...

Feb 23, 2005

I have a column in my table which contains DATE information in the format: 23/02/2005

I would like to split this date into two additional columns:

A) the MONTH as a number eg. 02
B) the YEAR also as a number eg. 2005

I know this is possible in Excel but can it be done in Access 97? :confused:

Cheers for your help!

View 1 Replies View Related

Modules & VBA :: Convert Number Column Into String Column

Feb 28, 2014

I want to convert a number column into a text column.

Number Column is called Customer.

Below I can't match to the other table containing the text column Customer No.

How can I convert columns in vba?

Code:
strSQL = "SELECT CALCULATED_Forecast_TNS.*, USEFUL.TRP_in_EUR " & _
" FROM USEFUL INNER JOIN CALCULATED_Forecast_TNS ON (CALCULATED_Forecast_TNS.[Reference No] = USEFUL.[Reference No]) AND (USEFUL.[Customer] = CALCULATED_Forecast_TNS.[Customer NO])"

Set qdf = CurrentDb.CreateQueryDef("REPLACABLES", strSQL)

DoCmd.RunSQL "UPDATE REPLACABLES SET CALCULATED_Forecast_TNS.TRP_in_EUR = USEFUL.TRP_in_EUR"

View 1 Replies View Related

Tables :: Can Split A Column Values Into 2 Columns

Jul 3, 2014

I have a column that's called "Date" which stores values for begining to end date as 'Text' like: 070314-073114 So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114?

This database is still in Microsoft Access, and eventually after cleaning it up, we will move it to SQL Server.

View 14 Replies View Related

Split Data In A Column In Excel Via VBA Code In Access

Jun 6, 2012

I have an excel file I need to read and update a column in an access table. But the data I need is in a combined format like AA-000, BB-001 etc.

I need to separate the values AA, BB as one column and 000,001 as another column. How do I write code to accomplish this in a module in VBA.

So the tasks I need to do is:

read in the Excel file
split the data in a column in to two columns
update the already existing access table with the data in the two new columns.

View 2 Replies View Related

Storing The First Letter Of First Name, First Letter Of Last Name And Data Of Birth

Feb 19, 2006

Hi,

I'm designing a database in which the primary key is a combination of the first letter of first name, the first letter of last name and the date of birth.

How can I do that?

Regards,
CS

View 8 Replies View Related

How To Split Up One Column With Multiple Rows Related To One Person Into A Query

Jul 1, 2013

I have an Access Database and I want to split up one column into multiple columns. The one column has multiple rows that relate to one person, so for instance I have the following:I am taking information from both tables and I want to create a query but I need to separate out the mother, father, and guardian with the names attached

So from the Child table :

Joe Smith Address Phone

The query that I created is from two tables, the child table and the family table: ( I used the dashes just for it to be easier to read)

------Child Table/PrimaryKey --------Family Table---------------------

First Name--- Last Name--- First Name--- Last Name--- Relationship
Joe ----------------Smith ----------Jane --------Doe-------------Mother
Joe ----------------Smith ----------John---------Smith---------- Father

So in this scenario I need to get the name of the second parent in another column....meaning have all the parents appear on the same line for this child, and each child there after..

So I need it to look like this:

------Child Table/PrimaryKey --------Family Table---------------------

First Name------ Last Name------ First Name------ Last Name------ Relationship------ 2First Name------2Last Name------2Relationship
Joe----------------Smith------------ Jane-------------- Doe------------- Mother-----------John---------------- Smith------------ Father

View 1 Replies View Related

Split Form Column Headings (Names) In Access 2010

Feb 26, 2014

Is there a way of changing the column heading names on the split form portion of an access form. I tried changing them using a query to populate the form and changing the caption of each field in the query and that didn't work. I also tried adding a [Caption] at the design level of the table and that had no affect also. The split form seems to be displaying a portion of the actual field name or something like that.

View 3 Replies View Related

Split Receipt Number

Apr 28, 2006

I have a DB with a field named "rec #"
That receipt number is made up of a date,a letter, a 4 digit number and a 3 digit number.
That receipt number might look something like this
01012006P0002001

01012006 date
P letter
0002 4 digit number
001 3 digit number

What I want to do is to split this receipt number into the individual parts and create a field for each part.
Can someone help me ?

Thanks

View 6 Replies View Related

Query To Look At The 3rd Letter In A Field

Sep 1, 2005

Hi, anyone know if it possibe to create a field within a query that looks at a 3 letter number code in a field of a table named "employee master" to see if the last number is an "I" or not. So for example a number code in a field named "Department" is "KTI". I would like to display "indirect" in that field if it contains a "I" in the 3rd letter of the number. And display "direct" if the third letter is a "D" intstead, tried doing IF statements but not sure how to go about it, any help would be great.

View 5 Replies View Related

Add A Letter To The Beginning Of Field

Dec 4, 2005

When I run a query I want to preceed the result of one of the fields with the letter V.

ie. the field reads GH567Y and I want it to read VGH567Y

Thanks

View 1 Replies View Related

Letter By Letter Search Through A Table

Aug 21, 2005

ok here is what i want.. imagine a search box, just an empty text box that allows the user to type things into it...

there will be a simple table from the database behind it, here is the typical structure of a record

<Country>Poland
<Price1>60
<Price2>85
<Price3>95

in this table there will be say a 1000 of these records...
my user is going to have to quickly search through these records while on the phone to a customer, and quote one of the relevant prices associated with that country

what i would like is this most convenient system..
as the user types in the letter 'P' just below all the records with countries that begin with 'P' are displayed (with a scroll down arrow if needed)..as well as the 3 relevant prices with that record

if they then type an 'O' into the box (which will now hold 'Po') all the records with countries beginin with 'Po' will be displayed (eg Poland)

if they delete the 'o'. once again the displayed records below the box will return to just the countries begginin with 'p'

all that is needed is for the records to be displayed, thats it.. but the adding and deleting of the letters within the text search box will need to instantly manipulate this list..

i have posted on other forums, and have been told this can be acheived in access, is it hard?

View 9 Replies View Related

Modules & VBA :: Determining Number Of Users Of Split Database

Nov 1, 2013

We have a database that is split. Every user has their own front end (installed via a .bat file so that the users always have a local copy of the most recent version), linked to a shared back end on a server. This seems stable and functional for us so far.

However, because our users are geographically separated, I don't actually know how many folks are using the database. I know the folks who contact me, but everyone in our company could use it if they wanted to.

For requesting funding, guiding future development, etc., I need to get some metrics:

Total number of unique users
Avg uses per day

Total number of simultaneous users (daily, weekly, and monthly maxes and mins)

What is the best way to get this info? Write user info and a timestamp to a table each time a front end is launched? Are there tools for monitoring this sort of information?

View 3 Replies View Related

General :: Split Database - Inconsistent Number Of Records

Nov 8, 2012

I have split the database, with the back-end residing on the server. Only 1 other person is working in Access right now; she's verifying the data. Today she's working directly in the back-end, could this be the problem. If it is, I'm going to have to create a front-end for her quickly.

My problem is this...I'm working on queries and forms so that the scientists who will ultimately be using this application, won't be able to go in and inadvertently change something in the tables set-up. When I'm in the back-end main data table, it says I have 2723 records.

When I create a front-end query to query all the records in that table, it says I have 2160 records.

The input form that I created with most of the same fields as the query (created BEFORE I created the query...I'll have it pull from the query now instead of directly from the table) also says we only have 2160 records.

Using Access 2010

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved