Using VBA to change a data type

Resolved · Medium Priority · Version 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:

How To Find All Overdue Accounts?

To find overdue accounts create a filter that compares today's date with the Invoice Date in the table. To do this:

1. Open the Query in Design View
2. Select the field for the filter and in the criteria row enter:

<Date()

This filter returns records where the Invoice Date is before today's date.

This filter can be manipulated if, for instance, Invoices are due 15 days after the Invoice Date. For this the filter would be:

<Date()-15

This filter returns records where the Invoice Date is 15 days before today's date.

View all Access hints and tips