Changing field data types vba Results


is it possible to change the data type a field in a table using VBA code?

I know how to retrieve it but don't know how to set it...

I retrieve it by

	temp = CurrentDb.TableDefs.Item("CMAssign").Fields.Item("Chassis").Type

i got temp = 19.. so to set it up i tried using

	CurrentDb.TableDefs.Item("CMAssign").Fields.Item("Chassis").Type = 19

but doesnt work.. it says invalid operation.

any idea? Thanks in Advance for your help, I really Appreciate it

does anyone know how to change the data type of a pre-existing field in a table from text to long in VBA?

Good morning folks,

Is it possible to change the Data Type for a field in a table using VBA. For instance I want to change the Data Type from "Hyperlink" to "OLE Object" save the table and then change it back to "Hyperlink" and then save the table again.

The reason I need to do this is because on importing a fixed width text file the last field has information which provides a link to the location of the image pertaining to that particular record. Ideally this would be a hyperlink, but because it is a text file it doesn't arrive in the database as a true hyperlink.

So what I have to do is to have the field in the database set to hyperlink data type, which places an underscore for the entire information in that field, but doesn't behave like a hyperlink when you double click it. so what I have to do to get it to become a true hyperlink is change the data type from "Hyperlink" to "OLE Object" save the table and then change it back to "Hyperlink" save the table again and then on double clicking that field, it opens up the image pertaining to that record.

If I could find a way of doing this action via VBA during the import process I have created, it would save having to do this manually. however I'm lost on how to do it, so your help would be most appreciated.


Is there anyway in VBA that I can open a table and then change the data type of a field from Number to text and close the table again?

I have got the following for the opening & closing the table but it's the data type bit which I am missing:

Docmd.OpenTable "tbl 1c Final Product Sales By Qty", acViewDesign, acEdit
Docmd.Close actable "tbl 1c Final Product Sales By Qty", acSaveYes

Can this be done?


I want to change a field's data type from number to text using vb. The Type Property for fields is read-only. Is there an easy way to do this?


I have a make-table grouped query. One of the fields called [TotalDue] has Expression in the Totals: row. When the table is created the [TotalDue] field is TEXT. How can I change it to a NUMERICAL data type using VBA?

To give a little background I import a table every morning from an excel spreadsheet exported from an Oracle database that contains datetime fields, if the first row of one of these fields is null then it imports to Access as text (The table is deleted first, then readded).

With this I have to stop the automation at this point and then go to design view and change the data types, then run another macro to finish the process.

I have not found a way to automate the data types at all though through any of my books or online.

Thanks for any help;

I have a table named horizon that is imported from an excel file. I have automated the process using the "DoCmd.TransferSpreadsheet" function in a macro. There is a column that imports with a Data Type as currency. I have found no way to change that with the DoCmd.TransferSpreadsheet import. So at this point, I have attempted to instead change the column Data type to a number and then from there, change the field property of the column to be a "fixed" format. The code below changes the data type from Currency to Number without a problem, but it does nothing to change the field properties. After running it, I go to Design View and the Data Type has changed, but the Field Properties are still set to Currency.

Code: 'Change the column Data type from "Currency" to "Number DoCmd.RunSQL "ALTER TABLE Horizon ALTER COLUMN [Cost] Double;" 'Change the column field properties from Currency to Fixed With db.TableDefs("Horizon").Fields("Cost") .Properties.Append .CreateProperty("Format", dbText, "Fixed") .Properties.Append .CreateProperty("DecimalPlaces", dbDouble, 2) End With My end goal here is to import the excel spreadsheet and format the columns correctly, and then export it as a csv file - all through a vbs macro.

OK guys I have one of 2 things I am having issues figuring out. I have the following code that I would like to run in a query but having a problem changing the decleration from for to query:

Option Explicit
'************************************************* ************
' FUNCTION: PrevRecVal()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'************************************************* *************
Function PrevRecVal(shipmentlookup As Form, Trans As String, KeyValue, _
Zip As String)
Dim pZip As DAO.Recordset

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0

' Get the form recordset.
Set pZip = shipmentlookup.RecordsetClone

' Find the current record.
Select Case pZip.Fields(Trans).Type
' Find using numeric data type key value?
pZip.FindFirst "[" & Trans & "] = " & KeyValue
' Find using date data type key value?
pZip.FindFirst "[" & Trans & "] = #" & KeyValue & "#"
'Find using text data type key value?
pZip.FindFirst "[" & Trans & "] = '" & KeyValue & "'"
Case Else
msgBox "ERROR: Invalid key field data type!"

Exit Function
End Select

' Move to the previous record.

' Return the result.

PrevRecVal = pZip(Zip)

Exit Function
Resume Bye_PrevRecVal
End Function

Or I would like to sum the fmiles from this section of code. I have not found a "sum" function.

Public Declare Function RouteStopPairByParm Lib "batch32.dll" _
(ByVal pStop1 As String, ByVal pStop2 As String, _
ByVal pParams As String, ByVal pErrFile As String, _
ByVal pPath As String, _
ByRef pMiles As Double, _
ByRef pHours As Byte, ByRef pMins As Byte) As Byte

Public Function fmiles(pZip As String, Zip As String) As Double
Dim x As Byte
Dim miles As Double
Dim hours As Byte
Dim mins As Byte
Dim parms As String
Dim dpath As String
Dim errorfile As String

parms = "-xx -h14.0 -p"
dpath = "C:program FilesProphesyCommonTripsdb"
errorfile = "err.txt"

x = RouteStopPairByParm(pZip, Zip, parms, errorfile, dpath, miles, hours, mins)

If x = 1 Then
fmiles = miles

fmiles = 0

End If

End Function

As always any and all help is greatly appreciated!!


Hi All

I have a memo field in an Access 2003 database table which contains text I wish to use as body text in an email created using VBA.
Even though the memo field supports more than 255 characters it still only places 255 chartacters in Outlook.

I have tried changing the data type in VB to variant instead of string but still no change.

I would appreciate any assistance.

Cheers in advance.


At some point, you may find it necessary to use VBA to get your job within a database done. Also, there are cases where some databases model a complex set of business rules and requirements and you must use VBA generously.

The most important thing to understand is that there are no truly hard and fast rules regarding how to be a good programmer. However, since the time of vacuum tubes, several programmers have observed patterns and thus contributed to a set of guidelines that help other programmers, regardless of whether they are using C, COBOL, Java, Visual Basic, or any one of many programming languages available.

This article deals in generalities and a bit of programming theory. The overall objective is to provide a maturing programmer with a framework to work within when considering how they will implement solutions to the problem at hand. Thus, the article is intended to call your attention to *potential* best practices, but it is still *your* responsibility to decide whether you will apply these recommendations or not.

Have hammer, will see nails

The first thing to consider is a caveat: VBA is truly a wonderful way to extend your application's functionality, and it can be tempting to turn to it for problems for which there exists a better solution without need for VBA. There's a saying- if your only tool is a hammer, everything starts to look like a nail. Thus, just because it's possible in VBA, it doesn't mean you should do it in VBA. SQL has its niches, and so does Access's built-in functionality and bound forms/reports/controls. Therefore, you may want to make a point of asking yourself what is the right tool for the job before swinging away.

Topics CoveredNaming Conventions & Reserved Words Option Explicit and Variable Declaration Data Types do matter Scope of Action Another Meaning of Scope Compile early and compile often and test your code just as often How do I code this? Using Constants, Functions, and Properties Eight atomic procedures are better than one big sub Using class modules to maximize reusability and reduce work
Naming Conventions & Reserved Words

Our first step is to decide on a naming convention. It actually doesn't matter what you ultimately decide on. The real criteria here is that this is a convention you are able to stick with throughout the life of project. It has failed as soon as you stray away from the convention because you found something more convenient.

The most commonly used convention among Access developers is Leszynski-Reddick style. That should give you an idea of what is usually included in such conventions.

Furthermore, you should be aware of 'Reserved Words'; it may seem intuitive to name a field storing dates "Date", but how would Access know if you're talking about a field named "Date" or a function named "Date"? This kind of naming can create confusion and unpredictable behavior. Generally, if there's a function, object, or property that has a name, it's a reserved word and thus should be avoided (or at least prefixed/suffixed).

Here's a list of reserved words that will serve as a good starting point and give you a sense of what constitutes a reserved word.

Option Explicit and Variable Declaration

The single thing that will do the most to help you to code efficiently is to require variable declaration for everything.

This can be set by Tools -> Options. On the dialog, select Editor tab and check "Require Variable Declaration"

This will automatically insert a line at top of every module:

	Option Explicit

This protects you from accidentally creating extra variables that you did not intend. For example,

	Public Sub TryMe()

Dim st As String

sr = "foo"

End Sub

Without the option explicit, we would end up with two variables, st which is never set, while sr now has value of "foo" which should have been set for the variable st. With Option Explicit, this would raise an error and thus call your attention to the troublesome variable which you can then take steps to correct.

Another benefit of using Option Explicit is that it helps to reinforce the good habit of specifying a data type explicitly. The default data type for any variable is Variant when we do not specify a type. While variant has its uses, it is relatively expensive because VBA has to resolve the type every time you use a variant.

	Dim x 'This will be initialized implicitly as a variant. Not good.
Dim y As Integer 'Better now we have an explicit data type

Data Types do matter

As alluded to earlier, it is important to be very precise in what data type you use. Each data type has its costs in terms of memory consumption and time to process that data type.

Generally, it is best to use the smallest data type that fits your needs. For instance, an Integer data type will serve you well if you do not need a number less than -32768 or greater than 32767.


	Dim obj As Object 'May be iffy
Dim wrksht As Excel.Worksheet 'This is more explicit

Declaring a generic Object data type is also expensive because, like the Variant data type, VBA has to resolve the capability of data type Object before it can use it. For programmers who actually need late binding, this is a legitimate use.

The bottom line is that you need to know various data types that are available to you and be explicit when defining variables. This also works for disambiguating different libraries. Suppose we used both ADO and DAO (a problem area for those using Access 2000, 2002, and 2003 with MS flip-flopping between the libraries). There is nothing wrong with using them both as long we disambiguate:

	Dim drst As DAO.Recordset
Dim arst As ADODB.Recordset

There is one more point to remember when working with data types- it's easy to end up with several different types when you think you're using just one. This operation actually involves two data types:

	Dim dtMyDate As Date
dtMyDate = "2008-31-10"

Which has two data types, but VBA will automatically coerce the string to a date for you, but this may not be always what you want. Therefore, there are instances where you will have problem with data types. It will be useful for you to remember that not everything is what it seems to be. This is especially true when you are dealing with objects such as controls or recordsets where you may set various data type to its various properties; being careless can have unintended side effects.

Furthermore, if it happens that you have two different data types but need to perform operations with both, it's usually a good idea to be explicit in casting them.

	MyString = MyInt + MyDbl* 
'How does VBA know if you wanted a Double stored as a String or an Integer stored as a String?
MyString = Cstr(CDbl(MyInt) + MyDbl) 
'This explicitly tells VBA that you want your Integer cast to Double, added to another Double, then cast as a String.

Scope of Action

One simple concept becomes incredibly important all too soon when dealing with VBA. That concept is "scope of action" - which in programming terms, means - how far-reaching is this program's operation? How much do you want it to do? How much data will it have to "touch" to accomplish its goal?

At one end of the spectrum, we can talk about a bit of VBA code that is associated with a single control on a single form, with the intent of making the control's background pale green when a number is positive or pink when the number is negative. This is a very simple code requirement that probably has the smallest possible scope of action. In VBA terms, the hardest part here is knowing where to put the code, which will probably not exceed five or six lines including the IF statement and its two main branches - one for the positive case and one for the negative case.

At the other end of the spectrum, VBA can read a directory, open a file, control a loop that reads records, import data to a table via Recordset operations, and update a progress bar on a display while ALL of the above is going on. Or it can open an Excel Workbook to read various cells. It can open a Word document to perform searches and statistical operations. It can do all of these at once.

With this incredible range of action, VBA can be your friend for all sizes of problems. However, there are times when VBA is more of a hindrance than a help. For beginners, the temptation is prevalent to write some VBA code to convert some complex data from one format to another. However, an update query can often do this without any VBA code at all. OR you can write the VBA code as a function that could then be used by a query. That means that the data conversion would be in VBA code but record navigation would be done via SQL.

Therefore, one useful skill is to know whether some action is or is not within the range of actions available within VBA. It is recommended that any person wanting to solve a problem with VBA should first know other aspects of Access and SQL syntax. VBA can and will help you "re-invent wheels." This sometimes leads to a glut of wheels, which isn't necessarily a good thing.

Another Meaning of Scope

VBA actions can have implied scope. However, the VBA routines themselves also potentially have a specific scope in terms of "visibility." The visibility of a VBA function or subroutine depends on where it is defined and who can call it (activate it). Scope of visibility, in simplest terms, is "who/what can see it?"

Access supports two types of VBA (code) modules. These are the standard modules and the class modules. A standard module is accessible from an object shown in the Module tab of the database window. Code in a general module could be activated from any form or report you have defined.

Forms and reports have class modules. All private procedures in a class module is visible only from the object associated with that module, and public procedure are accessible only when the object is open. A closed form doesn't exist in the list of forms. (It is, however, in the "Documents" or "AllForms" collection. But that's a concept for another lesson.)

A common mistake is to define a function in a class module for form A and expect form B to be able to use that function. If you want to reuse a function in multiple forms, it must be stored in a general module. This, too, is a practical application of the concept of "scope."

One fine point: The scope of action for a piece of VBA code does not have to match the scope of visibility for that code. But some authorities suggest that the two should be related by your choice of module design.

Therefore, an issue to consider when you are thinking about using VBA is to decide HOW you want to use it - from many places or one – and how much you want to do with it - narrow scope of action or wide-open scope of action. "Ambitious" is a word that accurately describes a novice VBA programmer wishing to write his/her first VBA subroutine to have a wide scope of action from a class module.

So when you are contemplating VBA as a solution, ask yourself two questions. (1) Who/what will need to do this? (2) To what must this be done when run from each of the possible answers to #1? THEN and ONLY THEN are you ready to think about the steps required to implement a VBA solution to your problem.

Compile early and compile often and test your code just as often

The biggest favor you can do to yourself is to make a habit of compiling your code frequently. This aids the process of debugging because you confine yourself to only last few lines you added since the last successful compilation.

Likewise, you definitely want to get in habit of testing your code often. By focusing on writing one procedure at a time and testing it, you can make the process simpler. Of course, this is not always possible, so discretion is required in determining what constitutes smallest block of code that can be meaningfully tested.

This is especially true with "Object or With variable not set" or "Too few parameters; expected 1" and similar run time errors. Run time errors are those not caught by the compiler, which only looks at syntax errors. Thus, it may be not always clear what triggered the error in first place. You do not want to see that error after having written a hundred lines of code without having had compiled & tested the code.

How do I code this?

A common situation that crops up even for most experienced programmers is they have a process that they haven't quite coded for and don't know the appropriate methods, properties, or statements to execute the statement. When you consider that Windows APIs collectively can have 60,000 different procedures, it should become apparent that even the C programmer has to know how to get the data he needs to program, just as much as we need to programming in VBA.

Therefore, a good habit is to get quite familiar with help files. I even reference help files for several methods that I've already used just to make sure I am aware of all possible side effects of using those methods. At this point, I should note that for Access 97, help files is quite extensive, but not as in 2000 & 2003. In case of 2003, I actually find that using search from the Visual Basic Editor to get the appropriate information is more effective than using the search in Access UI.

Another useful tool is to use Object Browser (can be called by pressing F2 or Views -> Object Browser), which is very useful for a quick perusal of different libraries, objects contained inside each of libraries and what arguments they take and/or return.

Also, it bears repeating that for any problem you are having right now, someone had it before you and in all probability already solved it, so it is usually a matter of searching effectively on various websites. Sometimes they are contained in whitepapers, technical documentation, and/or kb/msdn articles provided by Microsoft. Sometimes they are just a post on a bulletin board. The greater length you are willing to go through to read the fine print and test it out, the better programmer you will be.

Finally, there are times where it's just beneficial to test for yourself instead of running on a wild goose chase or waiting for an MVP to swoop in and rescue you or even if you just plain don't understand. This also implies that your debugging skills should be honed so you can be fully informed when examining the result of the test case. Tips on debugging is beyond the scope of this paper but there are several resources detailing different tools used in debugging.

Using Constants, Functions, and Properties

A rule of thumb is that whenever you have a magic number, a formula or something that exemplifies your business rules, it belongs as a constant, function, and/or property of a module.

For magic numbers such as Pi of a circle, Euler's constant 'e', or anything that will never change ever, constants makes the most sense. (If you need a magic number to be stored persistently but not immutable, the lookup table may be more appropriate.)

	Const Pi As Double = 3.14159

With numbers that you use as a part of business rules... say, calculating tax rates, functions are more appropriate.

	Public Function TaxRate(SubTotal As Decimal) As Decimal

TaxRate = SubTotal + (SubTotal * CurrentTaxRate)

End Function

Note that the 'CurrentTaxRate' isn't defined in that function- that is because it is up to you to decide the best place to store such information. Perhaps querying a table holding history of all tax rates is what your business requires. Or the business may be content having one tax rate that is updated periodically. Regardless of how you decide to implement this, functions are big time-savers.

A prime example is when you need to use calculated controls. It's easy to say "Why write a function when I can just do it all in a calculated control with the control source set to "=SubTotal + (SubTotal * CurrentTaxRate)"?

Well, that is true. Now say that you have 100 of them. Now you've been told that you must charge a shipping & handling fee after the tax. Now you could go and change every one of 100 calculated controls, or you could just go to the function and change it once and all the 100 controls will work as expected. Your decision.

Ideally, your programs should be a bunch of black boxes that can be strung up with a given string so you do not have to go through the arduous process of figuring out which one line causes an error in a giant main function (and there are plenty of errors that will raise on one line but is actually caused by a bad line that could be several lines earlier).

Furthermore, by far the biggest advantage of using functions (and subroutines as well) in a standard module is that it's incredibly easy to debug. You do not even need to open a form to debug it. You do not need to run the application and test it as if you were an end user to debug it. You only need to run it either by clicking the play button or calling it from immediate window (the small window at the bottom of the Visual Basic Editors):

Result will be printed in the immediate window

Property is like Public variables (aka Globals) but is much more versatile and provides you with more control over how it can be manipulated.

A good example would be when you want to use a listbox and its rowsource needs to be changed for every record. Instead of referring to the querydef and recordset every time you fire the OnCurrent event of the form, you can just make it a property and actually concentrate on modifying the parameters. This is what it would look like in the form's module:

	Private rst As DAO.Recordset

Property Get qdf() As QueryDef

'MyQuery is a Parameter Query with the SQL:
'PARAMETERS lngPrimaryKey
'SELECT a FROM table WHERE b = [lngPrimaryKey];
Set qdf = CurrentDb.QueryDefs("MyQuery")

End Property

Private Sub Form_OnCurrent()

qdf.Parameters = Me.MyPrimaryKey 'Set the parameter
Me.MyListBox.Recordset = qdf.OpenRecordset

End Sub

Note that qdf is a read-only property. You can refer to it from everywhere within the form's module (actually, from anywhere as properties are public by default but can be set private if so desired), and if you need to change the name, there's only one place to do it. Everything else will just work™.

Eight atomic procedures are better than one big sub

A common mistake for beginning programmers is to beam with pride over their one-hundred line subroutine. Rarely, if ever, should any procedure should be that many lines. This usually results because you try to wrap a process into a single procedure, when each procedure should in theory represent single task.

It's much easier to write out a procedure that does only one thing but does it well, and then pipe several simple-minded procedures together to generate a complex output. In fact, that is how programmers have been doing it for decades. Suppose we had a process that required us to extract a set of data, format it into something, then export it out. It would be easier to write one sub:

	Public Sub Foo()

Extract it!

Format It!

Export It!

End Sub

But this is better for a production environment:

	Public Sub foo()




End Sub

Public Function Extract(MyData As Arguments)

Extract It!

End Function

Public Function Format(MyData As Arguments)

Format It!

End Function

Public Function Export(MyData As Arguments)

Export It!

End Function

Now, a case can be made that that the second approach is more work than first one because you now have to write four procedures instead of one. However, by writing four smaller procedures, you significantly simplify your debugging process because you only need to know that your function works as expected then forget it. Furthermore, down the road, you may discover that you needed a similar task for a different process... Well, golly, it's a function so it's ready to go! Increasing reusability of your code will make you more productive.

Also, it's easier to go back five years later and re-read the atomic functions and understand what they do, rather than reading the big sub and wondering what the heck you did there. So in this sense, the second one leads itself to better documentation without requiring you to write comments as would certainly be required in first example.

Using class modules to maximize reusability and reduce work

One common task that crops frequently is automating Excel and manipulating its spreadsheet. Thus, it's not uncommon to see code for initializing such automation such as this:

	Private Sub foo()

Dim objExcel As New Excel.Application
Dim objWorkBook As Excel.WorkBook
Dim objWorkSheet As Excel.WorkSheet

Set objWorkBook = Excel.OpenWorkbook("MyWorkBook")
Set objWorksheet = objWorkBook.Worksheets(0)

'Do something....

Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing

Whew, that was a lot of work managing several objects, especially if all we wanted to do was to export a bunch of data in a specific worksheet for same workbook. And then multiply that by every time we need to create a new procedure to deal with different aspects (e.g. maybe to read off a worksheet for example, or to add new worksheet or whatever).

Some may use public variables to help resolve this issue but that has its own set of problems. One good example is if you set a public variable to this worksheet but later refer to it thinking it was set for that worksheet, you have a problem.

A Class module is a perfect solution in this circumstance. You only need to declare one line:

	Dim MySpreadSheet As New MyExcelClass

And the class module will run through the steps to initialize the application for you, retrieve the workbook and worksheet for you from this single line. Depending on what methods and properties you give to a class module you can even make it as simple as:


In which the method AppendNewData (written by you) already knows the correct position to place the new data, so you only need to write the class module once, then just refer to the methods of that class module. Intellisense (that tiny scrolldown menu that pops up to help you complete code) also will work for your custom class modules.

If you find yourself needing classes; Here's a primer. Generally, your code should aim to be highly cohesive yet loosely coupled.


Whenever you turn to VBA to fill in the niches to meet your business requirements, it is possible for you to start out with a generic procedure that encapsulates the task at hand. When you have a rough draft, it become more apparent how you can refactor your code into black boxes that are 1) easy to use, 2) can be used for several different tasks, 3) are self-documenting or self-evident. Developing those habits will serve you well in long time.

Last thing. Whenever a solution seems out of reach, or the light bulb above your head turns on, no matter how brilliant and crystal clear it is, you want to first ask yourself: Who else has had this problem or task in front of them, and how did they solved it? Their solution is just a search away. By doing this, a solution can probably be found to your insurmountable problem, or if others solved similar tasks differently than you had planned to, then maybe your planned solution is not the best way to approach your task. Paraphrasing AWF's geoergedwilkinson: "People are very clever in their ability to twist VBA or Access to do easy things in the hardest way possible". A critical eye is what saved me from running down a long alley only to hit a wall or re-inventing unnecessarily complicated wheel when there already exist a nice & simple wheel.


I have an access database where the data source is an Excel S/sheet.

One of the fields that i import is a text field that shows when a file has been reopened. Unfortunately the data in it is not just a date but reads " file was reopened on 1/6/2012" ( or whatever the data was)

I have run the query succesfully to split this out into a new field that shows only the date. However because of the way the field is imported it is always text and therefore after running the queryto split the data it is still text, which is of course no good for future queries etc.

The source data is updated daily and therefore even if I change the data type manually for now I will need to move on at some point and I dont really want users to have to go in and change the field on adaily basis.
All of the import is automated so my question is..............Is there a way in VBA to change the data type of this field from Text to Date.

The table is called tblReOpenDates and the field is dtmReopened.

Thanks for your help as always.

Hi there

I'm using Access 2007 to import data from Excel using VBA. I have this code:

Private Sub cmd1Importa_Click()
Dim myRec As DAO.Recordset
Dim strQry As String
Dim dbExcel As DAO.Database
Dim rsExcel As DAO.Recordset

Set myRec = CurrentDb.OpenRecordset("Tabela1")

Set dbExcel = OpenDatabase("C:BasesTestesTestesTabelaParaImpo rtar.xlsx", False, True, "Excel 12.0; IMEX=1;")
Set rsExcel = dbExcel.OpenRecordset("Plan1$")

Do While Not rsExcel.EOF
myRec.Fields("Coisa1") = rsExcel.Fields("Coisa1")
myRec.Fields("Coisa2") = rsExcel.Fields("Coisa2")
myRec.Fields("Coisa3") = rsExcel.Fields("Coisa3")

End Sub

But the ODBC driver reads only the first 16 records and chose for the column 3 (Coisa3) data type String with 255 characters max. And in the worksheet that I have texts with over 255 characters that are not being imported. I can choose the data type of the third column as memo? How?


I'm pulling data from an SQL database into Access2007 through a linked table. There are two time fields: [STOP] and [ACK], both in the form HH:MM:SS, and I have a field called DOWNTIME_SEC that calculates the difference between those times, in seconds, with the formula

CLng(IIf([STOP]="ERR" Or [ACK]="ERR","",Round((CDate([ACK])-CDate([STOP]))*60*60*24,0)))

which gives me accurate results.

My report is grouped by date, and the date footer contains the formula =Sum([DOWNTIME_SEC]), but that causes the error "Data type mismatch in criteria expression."

I've tried CDbl, Val, etc, but to no avail. CInt gives me an overflow error. I've even created another field called [OTHER] and tried converting [DOWNTIME_SEC] to various formats, but I end up with the same problem. I've created another table and typed the data in manually (using the same formula for [DOWNTIME_SEC]), produced a report from it, and THAT works, but I can't do that hundreds of times a day every day. I would like to do this with only regular queries and without resorting to VBA, Macros, or any other 'fancy' stuff. Any suggestions? Attached Thumbnails     Reply With Quote 05-17-2011, 09:05 AM #2 NTC VIP Windows Vista Access 2007 Join Date Nov 2009 Posts 1,271 off the cuff thoughts....

CLng(IIf([STOP]="ERR" Or [ACK]="ERR","",....etc

might change to:
CLng(IIf([STOP]="ERR" Or [ACK]="ERR",0,....etc

and I am just dwelling on casting a date nested inside a cast for long and wondering about that....

sanity check maybe splitting those two steps and eyeballing things....maybe test each half separately

maybe helps - not sure....

In a form in my database the user selects one of several "record types." Records which have the same "record types" usually have a very similar memo field, although there may be slight variations.

What I want to do is to have the memo field automatically filled in with a "default" memo data when the user selects an appropriate "record type." The memo data would then be modified slightly by the user to suit that particular record, or changed entirely, but in most cases few changes will be made.

I know how to do this with text information, but is there any way to include paragraphs in the memo data, as if they were typed in a word document, using the "enter" key to signify a new paragraph? The user would choose a type and then immediately after (after an onUpdate event) the text in the memo field would change to text, but formatted in paragraph form.]

tl;dr, how do you add paragraphs to Strings (or some other appropriate data type)


I've been trying to use VBA to automate some processes we use on a very regular basis in Access. So I've been working with VBA for about 10 days now which means this may be a stupid question. Apologies in advance.

I've got several linked tables and I want to create a new table with certain fields from each. My Create Table query works fine and all my fields are present and the correct data type. The problem occurs when using an Insert statement to populate my table: although there are no errors, the memo fields are populated with non-legible characters.

I've tried changing the destination field types to text but it doesn't help. I then changed the source field types to text (but left the destinations as memo) and the data looked fine.

I originally generated the SQL by creating a query in Access design view and copying (and reformatting) the resulting SQL. This query worked fine, but if I now copy it back into an Access query and make the same formatting changes in reverse, I get the non-legible characters problem again.

Any thoughts?



I have an Access 2010 VBA/Query problem I’d like to see if the forum can address. Basically, I have two problems:

1. My query runs very slowly (over 2.5 hours to pull 2.5M records) since I have 2 custom VBA functions to aid in calculating date ranges. Yes, it’s a lot of records and its growing!
2. The other being that I cannot filter a reports query when the criterion for the filter is applied to a custom VBA function. I get a “Data type mismatch in criteria expression” error.

I'll do my best to give all the relevant information now, apologies if it’s long, I want to be thorough – it’s probably overkill. The gist of the data: ~2.5M records which track line item detail of order shipments. So, multiple lines make an order while a single line is an item in an order. I need to analyze this data and part of that is determining if orders shipped on time, etc. The table has a number of fields but the relevant ones are:

· REQUESTED SHIP DATE – (aka the date the order is received)
· NEW ITEM FLAG – determines if an item is a 90 day exempt item

From these I calculate (Field name – field values: description & formula):

· STATUS – 5, 25 or 90: 5 days if it shipped in 5 days or if 5 days have yet to elapse, 25 days (“backorder”) if it didn’t ship in 5 days or 5 days have already elapsed and 90 days (“exempt”) if the item has an “N” in NEW ITEM FLAG.
· ACTUAL SHIPPING TIME – Positive Integer: uses UDF NETWORKDAYS to calculate shipping time dependent on whether or not the item has shipped.
· TARGET SHIP DATE – Date: uses UDF NETOWRKDAYS to calculate the day it should’ve shipped accounting for weekends and specific holidays.
o TargetShipDate: TargetShipDate([REQUESTED SHIP DATE],[Status])
· ON TIME – On Time, Late, Pending: analyzes the other calculated fields with DATE ORDER SHIPPED/RETURNED to determine if it was on time, late or pending.
o On Time: IIf(([DATE ORDER SHIPPED/RETURNED] Is Null And Now() dtEndDay. If so, then switch the dates If dtStartDay > dtEndDay Then dtNominalEndDay = dtStartDay dtStartDay = dtEndDay dtEndDay = dtNominalEndDay End If 'Here are how many weeks are between the two dates lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay) 'Here are the number of weekdays in that total week lngTotalDays = lngTotalWeeks * 5 'Here is the date that is at the end of that many weeks dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay) 'Now add the number of weekdays between the nominal end day and the actual end day While dtNominalEndDay 0 Then If Left(strField, 1) "[" Then strField = "[" & strField & "]" End If Do strCriteria = strField & _ " = #" & Format(TSD, "mm/dd/yyyy") & "#" rs.FindFirst strCriteria If Not rs.NoMatch Then TSD = TSD + 1 End If Loop Until rs.NoMatch End If End If Loop Until Not IsWeekend(TSD) If Status > 1 Then Status = Status - 1 TSD = TSD + 1 GoTo TestNextDate End If ExitHere: TargetShipDate = TSD 'Debug.Print TSD Exit Function ErrHandler: ' No matter what the error, just ' return without complaining. ' The worst that could happen is that the code ' includes a holiday as a real day, even if ' it's in the table. Debug.Print "Error: " & Err & " - " & Error Resume ExitHere End Function

	Private Function IsWeekend(dtStartDate As Date) As Boolean
'test for weekends
Select Case Weekday(dtStartDate)
   Case vbSaturday, vbSunday
       IsWeekend = True
End Select
End Function

Thank you,


I have a form "Attendance" which displays names in the first column, check boxes in the second. After checking the boxes next to the attendees' names, the user clicks on the Done button. This launches a bit of code that appends a field to the main attendance history table, and transfers the attendance data. For the field type, I specify boolean. What I want to do next is to specify that this new field in the main table must have a Yes/No format. I cannot find any references as to changing a field format using VBA.

Thanks for your help.

With a complete set of Data Type Validation functions now at my disposal, I am poised to work through my Validation classes and finally put code where I left comments "Data Type Validation Here".

Field Control Input Data Type Validation Functions

So, those Validation classes, they have an Attribute for each table column. As-is I have made those variables the same as the database column data type. Thus I already obtain a bit of data type Validation as one can not enter an alpha character into a field which is headed to an integer type database column.

Now I am able to change the Attributes of those Validation classes to something capable of accurately holding any/all data types that may be entered into unbound form field controls.

So... "To String or not to String, that is the question."

Is there any data which may be entered into an unbound field control which a String type VBA variable is unable to represent accurately enough, that would warrant use of Variants for the data type of Validation class attributes?

We use MS Access 2010 with Linked SQL Server 2008 tables. I have few reports working 99% of what Management wants/needs. I am currently testing to get reports to run if user(s) has no data.

When I declare function arguments as Strings, the MLM returns #Error for Medians and the Level2 returns a "blank" value (not sure if empty string or Null) for Medians. I thought wrapping Nz() around median calls would help but I get same results.

When I declare function arguments as Variants, I get a reversed results. The MLM returns a "blank" value whereas the Level2 returns #Error. Again, wrapping Nz() function around both median calls gives same result.

Why? What am I missing. How can I fix this behavior so both calls to the Median function return the "blank" value? I'm assuming I need to include some kind of check within the VBA function code, but I don't know VBA good enough to figure it out. Can someone help me out please?

Only thing different between the two function call querries is that the MLM uses additonal NUM_LINES optional argument whereas the Level2 query call does not. They both use optional PERFORMER argument.

Here is SQL for MLM call:

	SELECT qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES,
AS UserLineMedian, Sum(qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES) AS TotalUserLines
FROM qry_MLMStatsLineCountTimeDurationDetail10Lines
GROUP BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES
ORDER BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES;

Here is SQL for Level2 call:

	SELECT qry_Level2StatsInvoiceTimeDurationDetail.PERFORMER,
Nz(DMedian("qry_Level2StatsInvoiceTimeDurationDetail","SECONDS",[qry_Level2StatsInvoiceTimeDurationDetail].[PERFORMER])) AS
UserInvoiceMedian, Count(qry_Level2StatsInvoiceTimeDurationDetail.DOC_ID) AS TotalUserInvoices
FROM qry_Level2StatsInvoiceTimeDurationDetail
GROUP BY qry_Level2StatsInvoiceTimeDurationDetail.PERFORMER;

Here is the VBA DMedian function code:

	'changed String arguments to Variant to test #Error if user(s) have no data
Public Function DMedian(strDomain As Variant, strField As Variant, Optional strGroup1 As Variant, Optional strGroup2 As
Variant) As Variant
'Public Function DMedian(strDomain As String, strField As String, Optional strGroup1 As String, Optional strGroup2 As String)
As Variant
    'Purpose:   Return median value for a field in a table or query recordset
    'Inputs:    strField: the field
    '           strDomain: the table or query
    '           strCriteria: an optional WHERE clause to apply to the table or query
    '           strGroup1: an optional GROUP BY clause to apply to the table or query
    '           strGroup2: an optional GROUP BY clause to apply to the table or query
    'Calling:   DMedian("numericfieldnametogetmedian", "tableorqueryname", "stringforwhereclause") 
    'Output:    Returns median, if successful; Otherwise, an Error value
    Dim Db As DAO.Database
    Dim rstDomain As DAO.Recordset
    Dim strSQL As String
    Dim varMedian As Variant
    Dim intFieldType As Integer
    Dim intRecords As Integer
    Const errAppTypeError = 3169
    On Error GoTo HandleErr
    Set Db = CurrentDb()
    ' Initialize return value
    varMedian = Null
    ' Build SQL string for recordset
    strSQL = "SELECT " & strField & " FROM " & strDomain
    ' Only include group1 filter in WHERE clause if one is passed in
    If Len(strGroup1) > 0 Then
        strSQL = strSQL & " WHERE PERFORMER = '" & strGroup1 & "'"
        'use group2 as another filter in WHERE clause if one is passed in
        If Len(strGroup2) > 0 Then
            strSQL = strSQL & " AND NUM_LINES = " & strGroup2 & ""
        End If
    End If
    'added to see if it would help with slowness before deciding to use a MakeTable
    'strSQL = strSQL & " AND APS_DATE Between GetStartDate() And GetEndDate()"
    strSQL = strSQL & " ORDER BY " & strField
    'for displaying strSQL in Immediate Window for troubleshooting
    'Debug.Print strSQL
    Set rstDomain = Db.OpenRecordset(strSQL, dbOpenSnapshot)
    ' Check the data type of the median field
    intFieldType = rstDomain.Fields(strField).Type
    Select Case intFieldType
    Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate
        ' Numeric field
        If Not rstDomain.EOF Then
            intRecords = rstDomain.RecordCount
            ' Start from the first record
            If (intRecords Mod 2) = 0 Then
                ' Even number of records
                ' No middle record, so move to the
                ' record right before the middle
                rstDomain.Move ((intRecords  2) - 1)
                varMedian = rstDomain.Fields(strField)
                ' Now move to the next record, the
                ' one right after the middle
                ' And average the two values
                varMedian = (varMedian + rstDomain.Fields(strField)) / 2
                ' Make sure you return a date, even when
                ' averaging two dates
                If intFieldType = dbDate And Not IsNull(varMedian) Then
                    varMedian = CDate(varMedian)
                End If
                ' Odd number or records
                ' Move to the middle record and return its value
                rstDomain.Move ((intRecords  2))
                varMedian = rstDomain.Fields(strField)
            End If
            ' No records; return Null
            varMedian = Null
        End If
    Case Else
        ' Non-numeric field; so raise an app error
        Err.Raise errAppTypeError
    End Select
    DMedian = varMedian
    On Error Resume Next
    Set rstDomain = Nothing
    Exit Function
    ' Return an error value
    DMedian = CVErr(Err.Number)
    Resume ExitHere
End Function

Not finding an answer? Try a Google search.