Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2019, 01:20 AM   #1
M7_Lee
Newly Registered User
 
Join Date: Aug 2019
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
M7_Lee is on a distinguished road
Using a validation rule with an update query

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

M7_Lee is offline   Reply With Quote
Old 08-15-2019, 01:34 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,132
Thanks: 40
Thanked 3,611 Times in 3,486 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Using a validation rule with an update query

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 08-15-2019, 01:40 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,966
Thanks: 64
Thanked 2,536 Times in 2,436 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Using a validation rule with an update query

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")

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
After update validation rule GraemeG Forms 3 02-05-2011 11:47 AM
Validation rule query and question dave123 Tables 1 04-11-2008 08:38 AM
[SOLVED] Help with validation rule in a query Chris-TT Queries 2 08-13-2006 03:33 PM
Validation Rule with Query mjeep01 Queries 5 10-26-2004 09:39 AM
Append query validation rule Mansoor Ahmad Queries 3 02-05-2003 02:12 AM




All times are GMT -8. The time now is 12:24 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World