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 Replies


ADVERTISEMENT

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

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

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 1 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

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

Adding Zero's Before Number

Aug 30, 2005

I have a field where a user has to input numbers. This number at most will have 8 digits. Something like 00000001 or 00001234. I want access to show those zero's before the number but I can't do it. Any suggestions.

Thanks,
Yusuf

View 7 Replies View Related

Adding 0 Infront Of A Number

May 31, 2007

Situation:

I am running a query to pull the left 4 numbers from an X coordinate the first 3 numbers from a Y and I need to make the final Serial Number at least 5 digits but the numbers in the table range from 3 to 5 digits.

The Question:

How do i add 0's in front of a number to make it at least 5 digits?

I have the following so far to pull the data with the exception of the 5 digits for the serialnum:

Equipment_Loc: (Left$([POLEDATA.Map_X],4) & "-" & Left$([POLEDATA.Map_Y],3) & "-" & [POLEDATA.SerialNum])

View 1 Replies View Related

Adding Another Number Digit Into My Fax Database.

Sep 3, 2007

Hi all, I am happy with my fax database until my goverment decided to add another 6 infront of all the phone and fax number.

How can I change all the numbers together?

Example from : 1234567 to 61234567

Thanks.

View 14 Replies View Related

Auto Number And Adding Boxes Please Help

Jan 6, 2006

I have been messing with this for a while And i am losing my head- I am not very good with access,
Never was taught it either, but I can duplicate almost anything just by looking at it and trial and error. but this auto number and adding the tech and labor and parts feild to total up in to the total column. I will try to attach the file so you can take a look at what I have and see where I am screwing up at. or what ever I am missing. or is there a way to duplicate last years (which works fine) but I dont want any of the data. just all the propert fields.

Email address tbeard@collinsequip.com

View 2 Replies View Related

Adding Sequence Number To A Query

Apr 3, 2007

I have a query that captures date values from a table based on a between function that works fine. What I would like to do is associate a sequence number fro each date value returned and either perform a make table or leave it as a select query. If I create a table I know that I aam able to add an autonumber field but the query is executed frequently and performing the addition of the autonumber field is not feesible. Any thoughts how I can generate the sequence number each time the query is executed?

Thanks

View 3 Replies View Related

General :: Adding 00 To Number Field

Feb 12, 2014

I have a text field with numbers like this: 2014556682 or 2014236781, etc.

My problem is that I need to insert 00 between the 2014 and the last 6 numbers.

View 5 Replies View Related

Modules & VBA :: Adding A Number To A Field

Nov 11, 2013

I have a table with 100,000 plus unique rows and I want to add a column (call it 'ID') and place a value in each row starting with 000001 and adding one on to each row. What is the correct code to do this. I've tried with various combinations of For...next but can't get anything to work.

View 3 Replies View Related

Forms :: Sort On Number Adding 0

Jul 3, 2015

Access 2013.

Table field is set to: Number/Byte, 0 decimals.

The field is used to sort records in a particular order.

In the form it is set to Standard, no Decimals and no Default Value.

When I create a new record and type in: 1, it makes it 10, 2 becomes 20 etc.

If I delete the value and type it in again, it does not add the 0.

It only does it in the form, not when I enter it in the table directly.

View 1 Replies View Related

Adding A Auto Increment Text/Number

Jan 4, 2005

I have a daabase that I am importing via excel. I want the entries to be numbered
ex: MT0001
MT0002
etc....

I would like it to promt the user for the last number(or next number in sequence) entered, then fill in the blank records with the next increment number.
The prefix will also change to so eventually the user would enter into the prompt RD0001. then autofill the 140 imported records with RD0002, rd0003... etc....

I can't really make seperate fields because the MT0001 number will become a barcode and putting them seperate causes many issues.

can this be done? Any help would be great I am still a beginner but slowly learning!
Thanks!

View 13 Replies View Related

Adding Auto-number Column To Query?

May 24, 2013

I'm just looking for a way to add an additional column to a select query that will just be autonumbered down all the way to the bottom of the data. How can I accomplish this?

View 8 Replies View Related

Adding A Second Auto Number Field To Current Table

Jun 22, 2007

Is this possible, and how can I do it?

Adding a second auto number field to current table and auto numbering current entries?

I need to combine some records from the same order number that currently have detailed names. I'm trying to simplify them, but I can't because it creates duplicate records.

View 2 Replies View Related

General :: Fill In Missing Number When Adding A Record

Jul 5, 2013

I am working on a database where the records are numbered sequentially. On a regularly basis, records will be deleted leaving a gap in the record number sequence. I would like to have my db recognize that a number(s) is number in the sequence and use the missing number (filling in the gaps) when a new record(s) is added until all spaces are filling and then to continue from the largest number sequentially.

View 2 Replies View Related

Number Field Adding Bogus Data Beyond Decimal Point

Jan 6, 2008

I have a field for Item Price.
It would be a currency field, but I want it to be able to work with up to 6 decimal places. currency only seems to handle 4.
So, in the table, i changed it to a Single.
In the form, I left the control on Currency.

If I type in $1.43, instead of storing $1.43000000 it stores $1.43003243953

Why is it adding all these bogus sub decimal numbers? Any clue?

Thank you,
Evan

View 5 Replies View Related

Queries :: Adding New Records To Table - Random Number No Duplicates

Jul 31, 2013

I have a form which allows the user to add new records to a table. After the user had entered all the information into the form, they click a command button to add the record. In addition to adding the new record, my command button runs an query which is supposed to generate a random number between 1 & 1,000,000,000 and update the record ID field with that number.

Here is the formula I have been using in the "update To" now of my query: Int((1000000000-1+1)*Rnd()+1)

My problem is that I keep getting duplicates. You would think that the chances of getting a duplicate number would be pretty small with this large of a range, but I get a duplicate almost every time.

I have tried indexing (No duplicates) the field in the table, but that did not work. When my query generated a duplicate number, the record was just not added to the table.

I also tried a two step approach:
1-Make a table of all in use record ID numbers from my table (tblIdNo)
2-Update new record with a random number that is not in tblIdNo

This was a no-go too

How to build an update query that will update each new record added to the table with a random number between 1 & 1,000,000,000 without any duplicates? This seems like it should be so simple, and I am starting to get really frustrated.

I would prefer to accomplish this through a query/queries (if possible) rather than with 100 lines of code. This database is not for me, it's for another group, and the individuals in this group are totally freaked out by code.

View 3 Replies View Related

Reports :: Adding Specific Number Of Blank Lines Between Records

Jun 20, 2013

I have a WO Table and a related WO Procedures Table. I would like to add a specific # of blank lines/records between each record but that # will vary. So the report will show the WO Procedure Desc then a specified # of blank lines below it and then the next WO Procedures Desc then a specified # of blank lines below it etc.

I thought to add a field to the Proc tbl called #ofLines and whatever number was entered would be the # of blank lines to appear below the record on the report.

View 3 Replies View Related

Modules & VBA :: Adding Additional Sequential Number To Avoid Duplicate Value

Jun 10, 2013

I am not sure if it is possible to implement this but I would like your input. We get request from client via a web based form which contains a unique ID number called RDEFNumber. When we process the request internally, we use this number to identify the request throughout the process. The request from client contains processing of multiple files which are transmitted to us in different times of the day or days.

Every time we receive a file, an email is sent to the IT team with all the information about the file. We use an internal database to send the email to the IT team and use the RDEFNumber as the identifier in the subject line. What I am trying to do is to add an additional number at the end of the RDEFNumber automatically to differentiate the subject line.

For example, we take "1791" as a RDEFNumber. For the first time we enter 1791 to the database RDEFNumber field, it should check the table "tblRDEF" (record source) for duplicate value. If no Duplicate exists, it should keep the number as same. When we get the next file for processing with the same 1791 number and enter this number in the RDEFNumber field, it should automatically change it to "1791-1" since "1791" already exists in the database. When we get the next file, the number should change to "1791-2" and then "1791-3" and on.

View 13 Replies View Related

Forms :: Adding Duplicate Record To A Number Of Employees At Same Time

May 8, 2014

Currently if an employee attends a mandatory training session the details are entered individually into in a single table which contains all the employees' attendances to training, this is achieved via a bound form.

Works Fine.

My question is, if the training is carried out by a number of employees can this attendance be recorded on one form and assigned to each of the employees who attended?

Using Access 2003

View 3 Replies View Related

Modules & VBA :: Updating Or Adding Records To Form - Find MAX Of Number Portion

Jun 28, 2013

I want whenever I'm updating or adding records to my form, the ID automatically take the value of the previous ID and increment it by 1.

The field type is text (mixed with number) - PM0000000.

I've done some research, what I understand is that I need to:

-do a lookup and
-find the MAX of the number portion.

Name of form - Payment
Name of table - Payment
Name of field - payment_id

I tried these, but to no avail...

Private Sub payment_id_Click()
payment_id = DLookup(("[payment_id]", "Payment", "[payment_id]=Forms![Payment]![payment_id]-1")payment_id + 1)
End Sub

[Code] .....

View 8 Replies View Related







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