Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Data type error (2K and XP)

    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?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data type error (2K and XP)

    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.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data type error (2K and XP)

    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.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Data type error (2K and XP)

    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.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data type error (2K and XP)

    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.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Data type error (2K and XP)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •