Data type error (2K and XP)

Topic

New Reply

I’ve split my DB into FE/BE, as so many of you have recommended. I ran into a little snag that I’d like to discuss here.

My DB has a form to let the user select an employee by “Payroll_ID” and then to launch a report (via a command button) to display information on that person. After I’d split the DB, I got a data mismatch error when I tried to launch a report. I looked at the underlying table in design view and noted that the Payroll_ID field was of data type Text. That seemed fine to me–text is appropriate for this field. Nonetheless, I suspected the problem lay with this field…and I was right. When I checked the same table in the original version of the DB, I found the Payroll_ID field was of data type Number/Double. I suppose that’s the data type I chose when I started building this DB about nine months ago, although I would not have chosen that data type had I known then what I know now.

Next I went back to my new split DB (into the back-end, of course) and changed the Payroll_ID field’s data type to Number. That fixed the problem. Nonetheless, I have a couple of questions.

1. Did Access, in its wisdom, change my data type to Text as it split my DB?
2. Is there a compelling reason to change the data type back to text? I’m far from finished with the development of this DB, and I’m wondering whether I’m setting myself up for trouble if I leave this field formatted as Number.
3. If the answer to my second question is yes, what else must I change to avoid the data type mismatch errors? That is, might the mismatch problem lie with the command button properties or perhaps in the report properties?

Reply | Quote

Replies

I don’t know how you split it. I would have created a new DB and imported the tables from the current one, deleted the tables from the one to be the FE, then linked those tables to the BE. That should not result in Access changing any table structure as far as I know.

Payroll ID, if it is in reality a text field that could have non numeric characters then formatting it as numeric will give problems as soon as someone enters a non numeric (but valid) character.

Reply | Quote

1. This is strange – as far as I know, the Database Splitter Wizard only moves tables to the backend database, without modifying them.

2. If the contents of the Payroll_ID are numeric, I would leave them that way. Numeric fields are more efficient in general, and sorting is easier (ID’s 1, 2, …, 9, 10, 11, 12 will sort as 1, 10, 11, 12 , 2, …, 9 if it is a text field.) But if you have ID’s like 163007 now, and would perhaps need 163007A or something like that in the future, you would have to convert to text.

Reply | Quote

As Andy and Hans have noted, splitting your database should nt have had any role in changing the data type. And unless you have some specific reason for changing to a text field, I would not. Numeric fields are much easier to work with – for example automatically assigning the next Payroll_ID when a new employee is hired. And your data type mismatch occurred when you tried to use a numeric value as the criteria for a text field – Access will often fix those kind of issues for you, but not always.

Reply | Quote

Just to add my 2 cents to the 6 cents that has already been offered, most likely the data type mismatch error occurred in the code behind the button in the where condition argument of the OpenReport method. The where condition argument is a string and can get tricky when using numeric criteria. Also, a long integer type might be more appropriate rather than a double if your id’s will be whole numbers only. Long Integers take up half the room of doubles and should be more efficiently sorted and indexed, though that last bit is pure supposition on my part.

Reply | Quote

To answer a couple of questions: I used the database splitter. I might go back and do it all over again using Andy’s approach–it looks like doing the job manually would result in a cleaner end-product.

When I changed the text field to number, I let it stand as a long integer. It appears the “double” property had no effect. At any rate, it looks as though I’m better off leaving the field as numeric vs. text anyway. As a side note, my operation has nothing to do with assigning payroll ID numbers–I just import the data and use it in our DB.

Thanks, everybody, for the quick and thorough response!

Reply | Quote