Using a validation rule with an update query (1 Viewer)

M7_Lee

New member
Local time
Today, 05:10
Joined
Aug 15, 2019
Messages
1
Hi all,


I have an query that updates a single line table that later gets exported as a CSV ready for upload into our Royal Mail system,

The table contains name, address, etc from our customer details tables, and weight, parcel type, and service from a form based on our despatch ID.

We occasionally have an issue where a foreign delivery mistakenly gets scanned through onto a domestic service.

I have now set up a validation rule and warning text using the postcode field and this all works fine if you manually update the field i.e. I get a warning if the postcode doesn't match "Right([Post_Code],3) Like '#[a-z][a-z]'" but if I update the field via my query I don't get any warning message and the update fails,

My question is, is there a way to get the validation warning to work when updating via the query?


Many Thanks
Lee
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:10
Joined
Feb 19, 2013
Messages
16,553
you need to set up validation in your table

or if appropriate you can use your validation in your query to exclude them e.g.

WHERE Right([Post_Code],3) Like '#[a-z][a-z]'

or perhaps have another query to report them with the criteria

WHERE Right([Post_Code],3) Not Like '#[a-z][a-z]'

you will also probable need some additional code to protect against postcode being less than 3 characters
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:10
Joined
May 7, 2009
Messages
19,169
using a User Define Function, you can.
create a public function in module:
Code:
Public Function ValidPostCode(Byval p_code As Variant) As Boolean
    If IsEmpty(p_code) Or IsNull(p_code) Then
        Exit Function
    End If
    p_code = p_code & ""
    ValidPostCode = (Right(p_code, 3) Like "#[a-z][a-z]"
    If ValidPostCode = False
       Msgbox "Invalid post code"
    End If
End Function

you write your query:

Update yourTable Set [Post Cide]="New Post Code Here" Where ValidPostCode("New Post Code Here")
 

Users who are viewing this thread

Top Bottom