Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Using VBA to change a data type

Using VBA to change a data type

ResolvedVersion 2003

Thu 7 May 2009, 16:05 Delegate Charlotte said...

Charlotte has attended:
Access VBA course

Using VBA to change a data type

We receive large data files with field names 'memo' when they should be text or number - is there a way of changing this using VBA - currently we try changing it manually and get a memory error.

Mon 11 May 2009, 09:43 Trainer Stephen said...

RE: Using VBA to change a data type

Hi Charlotte

Thank you for your question

You can convert memo filed data into string and integer values by using cstring and cint vb functions. The following code provides some examples

  Set dbData = CurrentDb

    Set rstEmployee = dbData.OpenRecordSet("Employees")

        

    rstEmployee.MoveFirst

    

    strAddress = rstEmployee.Fields("Address") 'This is a memo field

    

    MsgBox CInt(strAddress)

    

    

    MsgBox CInt(strAddress) * 30



    MsgBox CStr(strAddress)

Hope this is useful

Regards

Stephen

Mon 18 May 2009: Automatically marked as resolved.

Training courses

98.7 % recommend us

More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Access tip:

Create a custom Close button on a Form

By default, Forms that you create in Access have a full set of control buttons, that is, a title bar with Minimize, Maximize and Close buttons in the top right-hand corner and a Control Menu in the top left-hand corner.

Sometimes Access needs to follow certain procedures or events as users close a Form (such as automatically opening another Form perhaps). So it is often a good habit to create a custom Close button on a Form for everyone to use, rather than the standard closing cross.

To create a custom Close button:

1) In Form Design View, draw a Command Button onto the Form.

2) Use the Command Button Wizard to select Form Operations under Categories, and Close Form under Actions.

3) Choose Finish.

Access then creates the button and code to close the form for you. You can add any extra code that needs to run as the Form closes to the same Close procedure.

However, the creation of a custom Close button is pointless if the closing X in the top right-hand corner of the Form remains active. To make the closing X inactive, open the Form Properties box then on the Format tab set the Close Button setting to No. This will grey out the closing X leaving it visible, but inactive.

View all Access hints and tips

Using VBA to change a data type

Using VBA to change a data type

RE: Using VBA to change a data type

Access articles

Access tip:

Create a custom Close button on a Form