Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: More help. Validating Car reg.

    if instr("IJQTUZ", left$(nz([testPlateNumber]," "), 1)) > 0 then 'bad news

    if instr("IQZ", left$(nz([testPlateNumber]," "), 2)) > 0 then 'bad news

    if instr("IZ", right$(nz([testPlateNumber]," "), 1)) > 0 then 'bad news

    Where do i past this and what bits do i ahve to change i put it in expression builder as pasrt of the validation rule thing on table design am i doing this wrong i am preety dumb at this so please someoen help

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Teddy's function with my algorithm becomes:

    Public Function ValidatePlate(s As String) As Boolean
    Dim IsValid As Boolean
    IsValid = True

    if instr("IJQTUZ", left$(nz(s," "), 1)) > 0 then isvalid = false
    if isvalid then
    if instr("IQZ", left$(nz(s," "), 2)) > 0 then isvalid = false
    endif
    if isvalid then
    if instr("IZ", right$(nz(s," "), 1)) > 0 then isvalid = false
    endif

    ValidatePlate = IsValid
    End Function

    now in your field validation rule you look for the [...] then functions then functions belonging to your application. select ValidPlate... it is waiting for a parameter "s" ...give it the name of your field.

    done!

    izy

    ...this function needs to be declared Public as above, and it needs to sit in a module (old-speak: "global module")
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    two post deleted co they were bu££$#1t

    hopefully the final version:

    Public Function ValidatePlate(s As String) As Boolean
    Dim IsValid As Boolean
    IsValid = True

    if instr("IJQTUZ", left$(nz(s," "), 1)) > 0 then isvalid = false
    if isvalid then
    if instr("IQZ", mid$(nz(s," "), 2, 1)) > 0 then isvalid = false
    endif
    if isvalid then
    if instr("IZ", right$(nz(s," "), 1)) > 0 then isvalid = false
    endif

    ValidatePlate = IsValid
    End Function
    currently using SS 2008R2

  4. #4
    Join Date
    Mar 2004
    Posts
    15

    Thanks

    Thats helps do i put it in a module then open it or just put it straight in expression builder

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Uhhh... It's a public function.. you call it...

    That would go in it's own module.

    Then when you want to evaluate a string, you can call the function from another event, say the On Click event for a button:

    ValidatePlate(yourTxtBoxThatHasAValueInIt)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it just sits there in the module.

    the table validation rule will call the function when it needs it.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Mar 2004
    Posts
    15

    Help

    I ahve tryed what tyou ahve sed do either of you ahve msn s i can send it over for you to do. I am willing to return the favour.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hey Teddy! it doesn't work.
    you can't call a user-defined function in table validation (A2k)!


    boyblundell
    back to you in a while with an iif() construction.


    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Mar 2004
    Posts
    15

    Exclamation THought it wasn't me

    I was going to my form and adding it on and that wasn't working. Please help as i am really getting rathe pi**ed off with it. But you help is really good and i actually understand it.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    He didn't specify it had to be a table level validation in the original post. I'm not touching that iif() statement, knock yourself out.

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    (deep breath)

    assuming your table field is called "plateNum", here we go:

    iif(not instr("IJQTUZ",left$(nz([plateNum]," "), 1)) >0, iif(not instr("IQZ", mid$(nz([plateNum]," "), 2, 1)) > 0, iif(not instr("IZ", right$(nz([plateNum]," "), 1)) > 0, true, false)))

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Mar 2004
    Posts
    15

    error NZ

    does understand the nz
    is there neway i could email it or send it.

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    lookup nz() in the help documentation.








    ps: This is a forum for help, not free work. If you would like to hire someone to do your work for you, there is a forum for that.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and the last depressing thought for the evening: that iif() validation rule is not enough - it does not require AAA99..99A format!

    you have many happy hours ahead of you playing with iif() looking for isnumeric(), not isnumeric() in various mid$()

    the week is but young: happy monday!

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    (Teddy will hate me for this) which version of access are you using? nz() has been around for quite a while.

    in any case, you can drop the nz() - and set a not-null requirement on the field. the various instr() then look like:

    instr("IJQTUZ",left$([plateNum], 1)) >0

    izy
    currently using SS 2008R2

Posting Permissions

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