Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing with Input Masks (2000)

    When I try to run an Append query Access informs me that records will not be appended because of Validation Rules. On checking further I discover that the problem is that I have input masks in place for Mobile Phone Nos and NI Nos and the records I am trying to Append have no data in these fields. I can resolve it by deleting the input masks, but is there a way I can do this without removing these? Otherwise, I'll be unable to attach the Append Query to a macro. Why does this happen anyway, if blank data is acceptable in the main table? Andy.

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

    Re: Importing with Input Masks (2000)

    An input mask by itself does not necessarily prevent blank values. Are the values in the records to be appended really blank, or do they perhaps consist of one or more spaces?

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing with Input Masks (2000)

    I exported the table to Excel, and later re-imported it, but they were always blank. Unless Excel put spaces in for me?! Shouldn't the Input Mask be ignored anyway, except for new records? Andy.

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

    Re: Importing with Input Masks (2000)

    >> Shouldn't the Input Mask be ignored anyway, except for new records?
    Umm, yes, that's true. So there must be something else the matter with the records. But why would removing the input mask allow you to import then? This is mysterious...
    Could you perhaps post a zip file with
    a) A database with just an empty copy of the table (no records); compact it before zipping
    [img]/forums/images/smilies/cool.gif[/img] A copy of the spreadsheet with only a few rows, and with sensitive information removed or altered.
    That would allow Loungers to investigate the problem at first hand.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing with Input Masks (2000)

    I've attached two small sample files in a zip. The data was exported to Excel and then imported. I've also tried previously with the Primary Key (AutoNumber) removed and obtained the same error message: cannot append due to V.Rules.
    Attached Files Attached Files

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Importing with Input Masks (2000)

    The Input Mask property is a red herring in this case. If the destination table has any text fields where the source field may contain a null value or zero-length string, you need to change the "Allow Zero Length" property to "Yes". I tested this with the sample db attached to your msg. After deleting existing records from tblStaff, tried to run qryAppendBook1DataTo_tblStaff. None of the 21 records were appended due to the validation errors. Then opened tblStaff in design mode & changed Allow Zero Length for NINO field to "Yes". Did not change input mask. Reran append query, all 21 records were appended. Recommend trying this & see if resolves problem. I've had this problem in past and resetting AZL property usually resolved issue.

    HTH

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing with Input Masks (2000)

    Great.. although I'm sure I managed to append the data at work after deleting the Input Mask but without changing the AZL property?! Anyway.. So is Excel converting Nulls to zero-length strings? I have similar problems with date fields when exporting/importing with Excel, in that it imports them as text fields. I've also seen Excel puting an extra space in front of text fields!! Andy.

Posting Permissions

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