Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    3

    Unanswered: [MSACCESS07][Newbie] Control on numerical values

    Hi guys, I have an easy question:

    Let's suppose I have a table with three fields Client, Information and Value.
    The first 2 are text, the 3rd is numerical. So the table would look like:

    Client1 Info1 Value
    Client1 Info2 Value
    Client1 Info3 Value
    Client2 Info1 Value
    Client2 Info2 Value
    Client3 Info1 Value
    ...

    Where Value is numeric.
    Now:
    for Info1 the value should always be a positive number
    for Info2 the value should always be a negative number
    for Info3 positive or negative.

    How do I set up an additional table with these constraints??


    Thanks
    Gerrold

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    y'dont
    assuming you are using JET, the default database for Access (if you don't know what JET is you are using it), then set a validation rule on each column as required. that validation rule will then be pulled trhough on all future foirm designs.

    if you have already designed the form, then you have two choices, eitehr design a new form or put some code in the relevant controls on change, on lost focus and before update events.
    you may be able to clear this problem by deleting the existing controls for these columns, then adding them back to a pre-exisitng form.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2009
    Posts
    3

    No form

    I am not sure I get the answer.

    I have no form (since the table with the data are coming form another source). The number of records are in the order of thousends and there is no hope to make a form to input the data 1 by 1.

    For me it would be fine if values coming with wrong sign (for example) are considered incompatible and discarded (or an eror is produced, or whatever).

    But they are not written in the database.

    So the information is "lost" but at least I do not have "wrong" numbers.

    Maybe it doesn't make a lot of sense for some application, but for statistical purposes it's better.

    G.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so the data is coming from a third party, which you have no control over
    you need to decide what you want to do with duff data... do you bin it or do you leave it there to be edited so it can be accpeted
    do you stuff it into the db and filter it out using a query.

    ultimately its up to you as to how you value the data, and how you use the data.

    if you want to filter the data on input you could either use a VBA procedure to validate records and stuff calid ones into the table, and stuff invalid ones into another holding tank..

    given that the acceptable range for the vlaue is dependent on the row type then I suspect a validation rule in plain vanilla JET won't work.

    so it has to be a VBA function or a bit of deft work with SQL
    you could import all the data into , say, a temporary table
    then use append query to transfer all records whic meet the criteria

    or you could import the data into a table and then use a delete query to remove invalid data.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2009
    Posts
    3
    I see.

    Thanks
    G.

Posting Permissions

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