Make A Field That Was Numeric / Alphanumeric

Feb 5, 2015

I have a database I have worked on for the sister company of the place I am employed.I have a field I am trying to make alphanumeric that has been numeric. (PO Number on the main form). I had make it alphanumeric a couple of months ago, but it disabled the Edit Customer Information button on the bottom right of the form to where I cant enter shipping addresses and things like that. It should be able to enter multiple shipping addresses.

I need making the PO Number field button alphanumeric and making sure it doesnt mess-up the Edit Customer Information button. I have attached both versions of my database. The GM at the sister company would also like me to create a Spin button where you can take an old record and keep all of the previous information on it, except it gives it a new Work Order# and you can change the date to something newer.

View Replies


ADVERTISEMENT

!! HELP -- URGENT !! - Need To Know How To Make Numeric Calculations In Access!!!

Mar 25, 2006

Erm Hi everyone I'm new.

Sorry about the rather striking title, but I have an assignment with the deadline tomorrow and I need to find out how to perform numeric calculations in access with different fields (i.e. adding numbers, minusing etc.)

Well, I had to make a project at school for my GNVQ ICT, I chose the advanced sales database, since its a project we have to add in our own features and find out about them ourselves. Well I thought I'd create one for my company inside school, I wanted to create a database that would print invoices and monitor all the resources we have. The project has to be in on monday!

However all was going well until I came upon a slight problem, I'm not sure how to do calculations in Microsoft Access!

Well basically this is what I'm aiming at, its nothing too complicated really.

I have a table called RESOURCES which keeps track of all of the stock the company has (e.g. how much paper, how many mugs, tshirts etc.). I want the database to be able to automatically take 1 away from the selected resource when a form is used to file a new order.

E.g.

I have a form asking me the details of the design, In the resources table I have set the quantity of mugs as 100, i.e. I have 100 mugs in stock and available.

I use the form and tell the database that I am making a design which will use 1 mug.

The database then goes to the resource table and takes 1 away from the available stock of mugs making the total mugs available 99.

I then the database to be able to do this automatically, when a checkbox is ticked, when the form is submitted I want it to see which text boxes are ticked and take the correct consequent resources from the RESOURCE table.

I have Access 2003, I have attached the file incase anyone needs it, note that the Forms have NOT been made only the resource, customers and orders tables have been created.

If someone could tell me how to do this I'd be extremely greatful, this is very URGENT I have to hand in the project on Monday!!!

I know I'm asking alot but I'd be really really greatful if someone could help me out!!!

View 4 Replies View Related

Queries :: Create Numeric Query Fields In MAKE Table

May 25, 2014

Have a Make table query that needs to create (add) several new fields where each field must be numeric design.

Have tried:

Score1: Not Null - does not seem to work (results in a Binary field)
Score1: 0 - which does give me the numeric field designation but every field in table contains a 0.

Would like to show Blank field (makes data input easier at a later time) but still have the Numeric designation.

View 6 Replies View Related

Sorting An Alphanumeric Text Field

Sep 30, 2005

I have a text field in a table that contains an alphanumeric code. i.e.

DEL998
DEL999
DEL1000
DEL1001
SUN998
SUN999
SUN1000
SUN1001
SUN1002
etc.

I want to run a query to find the highest number for a particular alpha code. In the example for DEL I would want the query to return DEL1001.

I have created a select query that asks for the alpha code, selects all codes starting with that code, sorts them in decending order and only displays the first record.

The problem is that because the field is a text field the numeric is not sorted like a number. So in the DEL case the query returns DEL999.

Any ideas on how I can get a result of DEL1001??

Thanks for your help

View 1 Replies View Related

Importing Alphanumeric Data Into Text Field

Mar 31, 2008

I have searched this forum but didn't find the answer, I hope I'm not the only one with this problem.
I have two text fields in a table that are updated through an excel file import. In the excel file, both fields contain data that is mostly numeric, but there are always about a quarter that contain letters as well. When I go to import the excel file, it sets to null any value in the fields that contains letters. If I sort the excel file in descending order for the field, it will import, but I have two fields that do this, so this brings more problems. Does anyone know why you can't just import anything in any order into a text field?

View 14 Replies View Related

Sort Alphanumeric Field - Code Problem

Dec 1, 2006

hi
i found this code here and it works IF the number comes first and is preceded by a letter
123AA

but it does not work if the letters come first
AA123

here's the code

Public Function GetString(WholeString As String) As String
Dim i As Integer
Dim Temp As String
Temp = CStr(WholeString)
For i = 1 To Len(WholeString)
If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then
GetString = Mid(Temp, i)
Exit Function
End If
Next i
GetString = Temp
End Function

Public Function GetNumber(WholeString As String) As Double
Dim Temp As String
Dim i As Integer
Temp = CStr(WholeString)
For i = 1 To Len(Temp)
If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then
GetNumber = Mid(Temp, 1, i - 1)
Exit Function
End If
Next i
GetNumber = Temp
End Function


the probelm is with this line but i'm not sure what it is

GetNumber = Mid(Temp, 1, i - 1)

(i also get runtime error 13) but my data is in the same format as the example i downloaded.

any ideas anyone?

thanks in advance and thanks to the person who created the code

View 1 Replies View Related

Forms :: Automatic Alphanumeric Increment Field

Mar 5, 2015

I've got most of what he wants sorted but this last task I am completely flummoxed. All of his projects are allocated an ID (named Project Reference), starting from P010010 and increasing by 1 each time. I've made a form that allows a new project to be recorded by entering all the details and hitting the 'record' button, but he wants the Project Reference field to be automatically filled in each time (understandably), increasing by 1 from the last record.

So if the last record was P010311, then when the form opens the Project Reference should automatically be P010312.

I've looked into this and found many guides talking about DMax and DIM and strCriteria and whatnot, but no matter how many of them I follow and try to adapt to my own database I can't get it to work at all.

The table the ID comes from is called General, and the field is Project Reference. The ID should automatically be filled into a text box called txtRef whenever the form opens and a button to add a new record is pressed, being 1 higher than the previous ID.

View 1 Replies View Related

Tables :: Validation Rule To Change Field To Alphanumeric Only?

Dec 6, 2013

What would I need to enter into the validation rule to change the field to alphanumeric only?

View 3 Replies View Related

Change Field To Alphanumeric (text) From Number Type?

Nov 13, 2014

I have an existing form where users type in information and it generates a couple of reports. In one of the fields, Customer PO Number, the user enters a number from a customer. Up until yesterday all of the customers we have been dealing with have used numbers only for their PO numbers. However, we have a new customer that requires alphanumeric PO's. Is there a simple way to change this field from a number to alphanumeric without having to redo each form, report and/or query. I am using MS Access 2010.

View 9 Replies View Related

Forms :: Alphanumeric Data In Field - Multiple Auto Number

Dec 27, 2013

I have a table having the following fields:

StaffNo TextField PrimaryKey
Name
....
...

The data in StaffNo will be alpha numeric, like AKA-111, AKA-112, LMN-100, LMN-102

Here AKA and LMN describes the Sites where employee is working.

On Add New Employee, When user enter AKA- in StaffNo, on exit the next number on that site should be generated. i.e AKA-113.

View 8 Replies View Related

Forms :: Enter Couple Of Alphanumeric Groups Into A Field On Input Form

Apr 3, 2015

I would like to enter a couple of alphanumeric groups into a field on an input form. After I enter an alphanumeric group, I hit the enter and the data will add into the field and refresh to empty box ready for next entry. If I continue to enter another group and hit enter, the next group will be added to original field with a comma and a space in between. build the VBA in after update event to accomplish the task.

View 14 Replies View Related

Tables :: Numeric Field In A Table - Setting Field Property?

Nov 16, 2013

I have a numeric field (long integer) in a table. I've set the format to fixed. No matter what I set the decimal places to, auto, 0 or 2, when I enter a value of 0.71, it displays as 1.00

In my form, I've also set the field property to fixed, but it displays the value as 1.

View 2 Replies View Related

Making Join Between Numeric Field And Text Field

Dec 23, 2006

I would like to make a join between two field in 2 different tables:

1.) Table with number field for Purchase Order number
2.) Table with text field for customers PO ref (where normally above PO number is entered)

When I link the both I get an error/warning message that the "types in the expression don't match"

Can anyone help me to overcome this problem ??

Thanks

View 3 Replies View Related

Forms :: Sort Numeric In Alpha-numeric Text

Mar 11, 2014

I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.

The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.

Code for automatic number generation is:

Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _

[Code]....

My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?

View 2 Replies View Related

Numeric Field Entry

May 18, 2005

Trying to do the following:
Having built a table of customers, I need to have an automatically generated ID number for each that IS IN ORDER - which rules out Autonumber and it's gapping tendencies. So, I've developed a new CustID field and set it to "Number", and now just need to figure out how to get it to pull the previous entry and add "1" to it. So it would look like this:

CUSTID NAME
1 Bob
2 Fran
3 Phil
4 Tom
5 (ready for next entry)

Any way to use a function I don't know about, or program some type of solution? Thank you

View 1 Replies View Related

Numeric Field Overflow

Jul 12, 2006

Guys, When I generate an excel sheet through vb.net code, I get a Numeric Field OVerflow when the values generated contain a date!

my database is ms access...

The problem is that each excel sheet generated has different columns, depending on what columns the user selects to view. for example: if the user chooses name, age and date, the date would appear on the 3rd column, if the user chooses name, nationality, age, family name, date, then the date would appear on the 5th column, etc.....

Does anyone know the way around this??
thanx!

View 1 Replies View Related

Wildcard On A Numeric Field

Jun 28, 2005

I have a query that prompts with a parameter box for the field Contributor_ID. Contributor_ID is a numeric field & Key and I use it to allow the user to print a report based on which Contributor_ID value the user enters. This works fine. However, I would like the user to have the option to enter * and then have the report include ALL the contributor IDs. I have done this before on a text field without a problem, however is this not possible on a numeric field as it is not working? I'm trying not to have another version for the ALL option.

If this is not possible, are there any alternatives?

View 2 Replies View Related

NUMERIC FIELD OVERFLOW!!! <Gah!!>

Sep 13, 2005

Hi,
I'm running an Append Query from an Excel <linked> file to a table in access. From what I can tell, the first filed is overflowing with a linked filed numeric of 'Double'. You can't change linked table fields. However when I first link the Excel file, the Append works... :D only when I reboot the PC and start the next day's work, do I get this message... :eek:

1. Should I include in my MACRO an import to a table and then append...? :mad:
2. Is there a trick in the append query I can use to prevent the error...? :rolleyes:

Thanks
MD

View 1 Replies View Related

Incrementing Numeric Field Value

Nov 26, 2006

I have this bit of code in a routine.
CustNum is a Numeric field with a number
I need to increase by one
With the code below It comes always as 1


Set Db = CurrentDb
Set rs = Db.OpenRecordset("Miracle_Cloth_Main", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Name = NewData
rs!Cust_ID = NewData
rs!CustNum = [CustNum] + 1
rs.Update
rs.Move 0, rs.LastModified ' Go to new record
'Me.Bookmark = rs.Bookmark ' Go to new record
'rs.Close


Could someone help with the correct code.

View 2 Replies View Related

Numeric Field Overflow

Dec 29, 2004

im able to ru n a delete query then im able to run an append query, when i run it a second time it gives me the error numeric field overflow, any ideas guys
Mark

View 6 Replies View Related

Numeric Field In Table

Apr 7, 2013

How to create field in table that just get number?

View 13 Replies View Related

Numeric Field Overflow Error

Dec 28, 2005

Hello everybody...

Does this Error look formiliar???

"Numeric Field Overflow 3349"

The case is:

I have a .txt file wicht I link to the database.
If I run a query based on that .txt file I get the Error.
...BUT sometimes if i close the database and compact it it works..

??????????????

Thank you for your help in advance...

View 10 Replies View Related

Enter Text Into A Numeric Field

May 14, 2005

I use Access as my grading program at school. Right now, if I want to excuse a student for an assignment, I delete that record.

What I would like to do is put an "X" or some other string into a field that is numeric and Access would ignore that score when computing the average.

A longshot, but I thought I would ask.

Thanks.

View 3 Replies View Related

Transferspreadsheet - Numeric Field Overflow

Jun 10, 2005

I have been using a transferspreadsheet cmd for a while now in Access 2003, but when I recently split the database so that the table the import is going to is now linked rather than residing in the same mdb file, I get a numeric field overflow error. I manually made all the values in the import 0, converted it to text, but got the same error.

When I imported the table back into the file, rather than having it linked, the transfer worked fine.

Can you not run the transferspreadsheet command on a linked table or am I missing something?

View 3 Replies View Related

Forms :: Numeric Field Control

May 5, 2013

how to control the numeric field range..e.g. number 00.01< and > 99.99...I am looking for some simply code under On Change() event...how I can restrict the field should only take input number no character or special characters.

View 5 Replies View Related

Numeric Field Overflow -&gt;with Linked Txt File

Jul 7, 2005

When I'm trying to execute a query using agregate functions.
This error message occurs "Numeric Field Overflow".

The table is linked to text file.


Any idea?

View 3 Replies View Related







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