CODEWITHSUNDEEP
ms-accessvbams-access-2010
D. Orsi
D. Orsi

Reputation: 1

Converting number field to text with VBA

I have a series of Access queries. The code worked as intended, but I changed some filtering criteria (changed date ranges to reflect the new year).

I am now running into a problem with the following code:

'1) Make Leaks LMS Date from FMSPROD
DoCmd.OpenQuery "LeakLMSDateMkTbl"
DoEvents
    
'Change Leak number data type from number to text.
dbs.Execute "ALTER TABLE LeaksLMSDate " & "ALTER COLUMN LEAK_NO CHAR(20);"
dbs.Close
    
'2) Make Leaks All Orders Table
DoCmd.OpenQuery "LeaksMkTbl"
DoEvents

The first table combines several tables from one database using the LEAK_NO field as an identifier. In that database the data is stored as a number.

The second table brings in some fields from another database where the same information is stored as text. The conversion code in the middle was inserted to modify the field type to convert the LEAK_NO field from a number to text.

When I run the associated sub, the first table is created, but I get

Run-Time error 3615 Type mismatch

in expression targeting step 2.

When I look at the LeaksLMSDate table, the LEAK_NO field is still formatted as a number.

Upvotes: 0

Views: 689

Answers (1)

Skippy
Skippy

Reputation: 1590

You could try using an Append query instead of a Make Table query for query 1. To do this there are a couple of other things you would need to do. Firstly define the target table columns with the correct data types up front. Secondly, assuming you're going to be running this process multiple times, you will need to put in a Delete query before the Append query (to clear out the data from the previous run). You can then get rid of the ALTER TABLE command.

Upvotes: 0

Related Questions