How do you change an Access field type from Yes/No to Short Text?

Question

I have created a temporary table which will be output to Excel that uses Yes/No fields which I want to turn into text answers Yes & No (as opposed to -1 and 0), so it is clear to the users of the spreadsheet. Right now I have:

Private Sub MySub()
 
Dim db As DAO.Database
Dim fld As DAO.Field
Dim prop As DAO.Property
 
   Set db = CurrentDb()
   Set fld = db.TableDefs("InterestsTable").Fields("Racing")
   Set prop = fld.CreateProperty("Format", dbText, "Short Text")
   fld.Properties.Append prop
   Set fld = db.TableDefs("InterestsTable").Fields("Solo")
   Set prop = fld.CreateProperty("Format", dbText, 3)
   fld.Properties.Append prop
 
End Sub

Neither of the options seems to work, what am I missing/doing wrong? I'm using Access 365 (I can't seem to find the build number).

Mike.

What does "Neither of the options seems to work" mean - error message, wrong result, nothing happens? Need to change field data type, not Format property. I tested DoCmd.TransferSpreadsheet and Excel sheet shows "True" and "False" - wouldn't that be sufficient? Why are you using a temp table? What method are you using for export? — June7, Sep 27 '22 at 01:52
Does this answer your question? [Change a field type programatically using VBA Access 2007](https://stackoverflow.com/questions/34030230/change-a-field-type-programatically-using-vba-access-2007) — June7, Sep 27 '22 at 01:57
Both the options in the code I tried kept the fields as -1 and 0 when I export, I'm using a temp table (which is filtered using SQL so I don't make the field type change to my permanent data). If there is a way to do it in SQL that would work for me. Ultimately I just want the cells in the Excel sheet to be blank except where it is true (that I can do) then I want 'Yes' (or some other obvious indicator) so they stand out. — LearningVBAtheHardWay, Sep 27 '22 at 05:45

score 0 · Accepted Answer · answered Sep 27 '22 at 06:11

0

Create a simple select query where you omit that field and replace it with this expression:

RacingYN: Format([Racing], "Yes/No")

Then export this query.

answered Sep 27 '22 at 06:11

Gustav

53,498
7
29
55

score 0 · Answer 2 · answered Sep 27 '22 at 06:34

There are many ways to skin this cat. You can mix and match using the menu and doing things in VBA at every step. You could flip it by importing from excel, recording a macro, viewing the resulting vba and converting the result.

For you approach create the table with the yes/no type then insert the data then export. Even easier hit the make table tab and use a calculated field (the calculated field returns strings)

------------------------------------------------------------------------------------------------------------
| StarTrekOfficerID  |         StarTrekOfficerFirstName         |           StarTrekOfficerRank            |
------------------------------------------------------------------------------------------------------------
|                  1 | James                                    |                                        1 |
------------------------------------------------------------------------------------------------------------
|                  2 | Spock                                    |                                        2 |
------------------------------------------------------------------------------------------------------------
|                  3 | Leonard                                  |                                        3 |
------------------------------------------------------------------------------------------------------------

SELECT StarTrekOfficers.StarTrekOfficerFirstName, IIf([StarTrekOfficers].[StarTrekOfficerRank]=1,'yes','no') AS isCaptain INTO temp
FROM StarTrekOfficers;

even easier there is no need to make the table. you can export a query:

SELECT StarTrekOfficers.StarTrekOfficerFirstName, IIf([StarTrekOfficers].[StarTrekOfficerRank]=1,'yes','no') AS isCaptain
FROM StarTrekOfficers;

yes/no is easy but for more complicated problems abstract the calculations to public functions and call the functions in the calculated field. if you put it all into one function then the return type of the calculated field would be the return type of that function.

Just right click whatever query or table you use and select export then excel. If you want to export using vba google docmd.Transferspreadsheet

How do you change an Access field type from Yes/No to Short Text?

2 Answers2