Change field to alphanumeric (text) from number type.

[LonnieCAR]

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’m not that experienced with Access, so you’ll have to walk me through the steps. I am using MS Access 2010 btw. Thanks!

[orange]

Can you explain your business -- how it works with customers and a general " a day in our business" - to help us understand the situation?
What is special about this customer? You have your own PO and you also record a PO from the Customer? I would think a PO Number may be text because you don't do any arithmetic on PO numbers (similarly with Phone numbers). I would not recommend spaces within field or object names.

[LonnieCAR]

I work in the packaging industry. There are 3 companies housed together in the same area, a packaging company, label company and a company that makes shipping components. I work with all three companies. When the packaging company needs labels or shipping materials they tell us their PO# from their customer and we use this to process an order on our end. Recently, we have been getting orders from companies outside of our own and they have been using numbers that have text and number, so the system does not work with them. The gentleman that created the database has passed away and we are trying to make it work while we explore other options. Please advise. Thanks!

[LonnieCAR]

An example of a regular one is 202665. One that I need to work is 125a45

[orange]

I don't know how familiar you are with Access or database generally.
Is this a database you could make a copy and post it here? We don't need the whole database and definitely nothing confidential/private.

Is is possible that you could:
-Make a copy of your database for testing
-Make a copy of the table where PO# are stored
-In that copy, change the definition/datatype from Number to Text
-test the copied program and see if it works.

You may find there are a number of queries or vba code where there are datatype mismatches because of the change to number.

Always make a backup, always work on a copy...

[LonnieCAR]

Please view the attachment. Thanks. It's the 'PO Number' field on the main form.

[orange]

I do not use macros.
I would recommend you do not have embedded spaces in table field or object names because they require extra syntax which often leads to errors. But I wouldn't change this if you are not familiar with Access or this database.
I ran a query or two and received invalid use of Null error?
Which is the main form --you should refer to forms/tables etc by their real saved Names-- it will cause less confusion.
If the Customer PO number is NOT a Primary Key nor Foreign Key, I don't think changing the field's datatype from Number to Text will harm anything. But I have no familiarity with the details of your operation.
You could check your Queries to see if PO Number is used in where clauses. If so, you may have to use quotes (") around string values
eg Where [Customer PO number] = "12345" rather than where [customer PO Number] = 12345

Good luck.

[ssanfu]

I looked at your dB and agree with Orange. There are many things that needed correcting.

The main thing I changes is the structure. There are still things wrong but it is better. For example, you have a field in the Order Details table for the Product name AND a FK to the product table. But there are no products in the product table...??

One thing that I think is not good (and needs to be fixed) is that you have an autonumber as the Work order number. An autonumber should not be used for any meaningful information. You coold end up with a WO# that is -156895741.....
See:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm
In doing so, I fixed the shipping address problem. There were 10 shipping address field in the customer table and a table for customer addresses. Now just the table.



I also took out the special characters and spaces in object names.

A added a sub to calculate the subtotal. As it was, if you had more than 1 product, it didn't appear to give the correct dollar amount.

I added a sub report to the 3 reports.
I didn't remove any macros - I dislike macros ... but I didn't remove them.

There was a lot of unused code that I commented out.

The dB is still in A2000/2003 format. Should convert it to A2007/2010/2013 format.

Did a couple of other other things that slip my mind.....

Anyway, see what you think.

[LonnieCAR]

ssanfu and Orange - Thank you. Between Orange's advice and ssanfu altering my attachment it solved the problem really nicely. I did a couple of formatting changes and I think we are set to go! I would love to ask you two for help again, because I barely understand Access (I'm more of an Excel guy). Thanks!

[ssanfu]

Glad it worked out for you..

Good luck..