×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Change a make table fields data type through code

thread700-1691399

Change a make table fields data type through code

Change a make table fields data type through code

(OP)
I have a table that is created and dropped each week. It is used as an export to populate and update fields in another program. Unfortunately one table has the field as a memo and the table it is going in is a number data type. I manual change the datatype of the memo field to number before I export it. Because I have to manually do this, I have to run this report every week instead of allowing the user to just fill in the date fields and click a button. I would like to automate this process. Is there a way I can write code to change the datatype prior to an automatice export?


Thank you

Micki

RE: Change a make table fields data type through code

Quote:

I have a table that is created [...] each week

Where and how is it created now?

Have fun.

---- Andy

RE: Change a make table fields data type through code

(OP)
It is created by a query.

CODE

SELECT dbo_tblApHistHeader.InvoiceNum, dbo_tblApHistHeader.VendorId, dbo_tblApOpenInvoice.InvoiceDate, dbo_tblApHistHeader.Notes, dbo_tblApVendor.Name INTO DUMP
FROM (dbo_tblApHistHeader INNER JOIN dbo_tblApOpenInvoice ON (dbo_tblApHistHeader.InvoiceDate = dbo_tblApOpenInvoice.InvoiceDate) AND (dbo_tblApHistHeader.InvoiceNum = dbo_tblApOpenInvoice.InvoiceNum) AND (dbo_tblApHistHeader.VendorId = dbo_tblApOpenInvoice.VendorID)) INNER JOIN dbo_tblApVendor ON dbo_tblApHistHeader.VendorId = dbo_tblApVendor.VendorID
WHERE (((dbo_tblApOpenInvoice.InvoiceDate) Between [Forms]![frmDump]![txtBegin] And [Forms]![frmDump]![txtEnd]) AND ((dbo_tblApHistHeader.Notes) Not Like "0") AND ((dbo_tblApOpenInvoice.CheckNum) Is Null));

RE: Change a make table fields data type through code

You could try convert the field within the query like:

CODE --> SQL

SELECT dbo_tblApHistHeader.InvoiceNum, dbo_tblApHistHeader.VendorId,
 dbo_tblApOpenInvoice.InvoiceDate, Cint(dbo_tblApHistHeader.Notes) as TheNotes, dbo_tblApVendor.Name 
INTO DUMP
FROM (dbo_tblApHistHeader INNER JOIN dbo_tblApOpenInvoice
 ON (dbo_tblApHistHeader.InvoiceDate = dbo_tblApOpenInvoice.InvoiceDate)
 AND (dbo_tblApHistHeader.InvoiceNum = dbo_tblApOpenInvoice.InvoiceNum)
 AND (dbo_tblApHistHeader.VendorId = dbo_tblApOpenInvoice.VendorID))
 INNER JOIN dbo_tblApVendor ON dbo_tblApHistHeader.VendorId = dbo_tblApVendor.VendorID
WHERE (((dbo_tblApOpenInvoice.InvoiceDate) Between [Forms]![frmDump]![txtBegin] And [Forms]![frmDump]![txtEnd])
 AND ((dbo_tblApHistHeader.Notes) Not Like "0") AND ((dbo_tblApOpenInvoice.CheckNum) Is Null));

Duane
Hook'D on Access
MS Access MVP

RE: Change a make table fields data type through code

(OP)
Thank you. Thats what I needed to know.

RE: Change a make table fields data type through code

(OP)
I spoke too soon. I just noticed that it does change the data type but no data comes into that field. It should be a a bunch of numbers. I think what has to happen is that the table needs to be created like I do and then run some code to change the data type. Just a thought

Micki

RE: Change a make table fields data type through code

I don't understand "bunch of numbers". Are you storing more than one number in the memo field?
What have you tried in order to trouble-shoot?
Are you seeing numbers in the datasheet view of the query?

Duane
Hook'D on Access
MS Access MVP

RE: Change a make table fields data type through code

(OP)
The application we are getting this information is a memo field. When we implemented this program we needed a match field created to match up documents with data from two different sources. Unfortunately it is a canned program and the user defined fields are of the memo type and could not be altered. My users put numeric data in this memo field which is the document number in the document imaging application. The data is all numeric in this memo field...For example...100256 would be the document id...That same number is the document number in the other database. So when I want to populate fields with the information that is in the accounting system. I create a "dump"..meaning I dump the data into the other system by matching the id's ...However the dump will not work if the data types are not the same. Therefore after I run the query and make the table. I change the memo field to a numeric field, then export the table to a text file so I can import all the information into the document imaging system....I hope I have not confused you.

RE: Change a make table fields data type through code

Again "Are you seeing numbers in the datasheet view of the query?"
Is the [DUMP] table structure always the same? If so, why not delete all the records and then append?

Duane
Hook'D on Access
MS Access MVP

RE: Change a make table fields data type through code

(OP)
Yes I see numbers in the datasheet view of the table I am pulling it from as a memo field. I see it as numbers when I create the table when I use the original query. I do not see anything in the field (null), when I use the "int" conversion.

I do not append because it will not append a memo field into a number field. I get an error. "datatype mismatch" . In order to bring the text file into the new system it runs a canned workflow process. I have no control on how it works. I just know that if I export the table with the field as a memo field it errors out. I must change the datatype to a number field prior to exporting to text file and then it works fine.

RE: Change a make table fields data type through code

(OP)
I have tried code and I keep getting this error
"Syntax Error in ALTER TABLE statement"
My code is as follows"

CODE

DoCmd.RunSQL "ALTER TABLE Dump ALTER COLUMN Notes number(10)"

I have tried int, plain number etc. I keep getting the same results. If I manually change it from memo to number it works fine without any errors.

RE: Change a make table fields data type through code

I still think you can use the CInt() in the original query. It may be an issue with some values in the memo field like nulls or something.

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close