Change a make table fields data type through code 1

[MICKI0220]

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

 

[Andrzejek]

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

Where and how is it created now?

Have fun.

---- Andy

 

[MICKI0220]

It is created by a query.

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));

 

[dhookom]

You could try convert the field within the query like:

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

 

[MICKI0220]

Thank you. Thats what I needed to know.

 

[MICKI0220]

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

 

[dhookom]

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

 

[MICKI0220]

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.

 

[dhookom]

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

 

[MICKI0220]

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.

 

[MICKI0220]

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

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.

 

[dhookom]

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

 

[PHV]

What about replacing this:
Cint(dbo_tblApHistHeader.Notes) as TheNotes
with this ?
Val(dbo_tblApHistHeader.Notes) as TheNotes

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886