How to force data type in Access table?

Question

I am getting a "type mismatch in expression" in Access. I've gone back to the original tables where the data was pulled from and both tables have the data type as Short Text. The data is an Invoice number. After running some queries and code the final table created has the data type as Number - Long Integer.

In the code the lines for Invoice# are as follows:

' Create summary records from detail records

Dim dt_invoice As Variant

Addrec:

 rstOutput![Invoice#] = dt\_invoice 

' Store off this record's values

dt_invoice = rstRenewals![Invoice#]

I found this explanation on learn.microsoft.com:

Generally, numeric Variant data is maintained in its original data type within the Variant. For example, if you assign an Integer to a Variant, subsequent operations treat the Variant as an Integer. However, if an arithmetic operation is performed on a Variant containing a Byte, an Integer, a Long, or a Single, and the result exceeds the normal range for the original data type, the result is promoted within the Variant to the next larger data type. A Byte is promoted to an Integer, an Integer is promoted to a Long, and a Long and a Single are promoted to a Double.

So I would think that the data type should be the same as what it was in the tables that it came from. How would it get to be a Number - Long Integer? How can I force it to be Short Text?

Thanks,

Jessica

Answer 1

My second guess was that you are dealing with at least two data sources. One is the table from which rstOutput retrieves records. The other is the table from which rstRenewals retrieves records. Is it possible that, by retrieving a value from rstRenewals![Invoice#] into the variable, dt_invoice , you are inadvertently picking up a different datatype from that second table.

Answer 2

I didn't write the code. I don't know why it was written the way it was. Both original tables have the field set for Short Text. The explanation from Microsoft says they should keep their data type. I don't know how or why it is changing to Number when the record is added. There are fields with spaces, too. I work with an ERP program's backend, most of the fields I work with have to be bracketed.

So I went into the code and changed that line to be Dim dt_invoice As String. It still came into the table as Number.

Thanks,

Jessica

Answer 3

Variant tends to be the datatype of last resort, IMO. In other words, if you already have a valid datatype -- as here, where the data is stored in a table as short text -- there is probably no compelling reason to push it through a variable of variant datatype. It should be handled in its original datatype.

Why not just Dim it as a string in the first place and let it remain that way?

The only thing I can think of is that your two recordsets have different datatypes for the "Invoice# field through which you are pushing the values. Is that why you used variants?

And, if that's the case, what are the datatypes for Invoice# in the two tables on which the recordsets are based?

As an aside, it is generally recommended that we avoid special characters like the # in object names. You have to wrap them in square brackets everywhere if you do allow special characters in. Not the end of the world, but not ideal.

Answer 4

The mismatch is happening when I try to create a query and join the invoice number fields.

I am working with code that was created years ago to run a process. I'm not trying to recreate it all so was hoping to just be able to find a way to convert it back to Short Text.

Thanks,

Jessica

Answer 5

First question is what expression is throwing the type mismatch error?

Second question is why are you defining the variable as a Variant, when its Short Text in the table?