I have a postcode field on a form that allows 8 characters. e.g. ST10 8BY including the space in the middle.
The only validation I have been able to use thus far is the above but I would like to know how to validate two letters (AA) then between 1 and 2 numbers (11) etc.
Is there any code/built in functions that allows this. I know there is an input mask but if I use that, I can set the poscode format up like ST10 8BY but it thinks that the rule has been broken if the postcode was changed to S10 8BY by removing the T. This is also a valid postcode.
hello all this thing is really bugging me im trying to come up with a post code validation and i have tried dozens, which should have worked really, but just havent so wondering if you guys knew any examples of postcodes would be
BN3 8JH BN41 8JH
theyre the only two types that are bound to be entered really
the first and second characters have to be a letters the 3rd has to be a number the 4th is sometimes a letter (not always present) then theres a space then there has to be a number letter letter
any suggestive codes would be great, as none of mine have worked its in access
I've tried to steal an SQL Postcode validator (This is for UK postcodes) and add it to an AfterUpdate event on a field in MS Access. It doesn't work, obviously,The VBA code is:
Private Sub Postal_Code_AfterUpdate() Dim CHECK_Code As Integer Dim LResponse As Integer If InStr("[A-Z][0-9] [0-9][A-Z][A-Z]", Postal_Code) = 0 Then CHECK_Code = 0
[code]....
Also, how would I put the Check_Code value into the Message box to see what it's bringing back?
I have a customer database and I basically want to find out their geographical distribution. To do this I need to run a query that gives me the sum of customers for each postcode, but this is dependent on only the first few characters of the postcodes (or prefix), eg BH3. The length of the prefix varies between 2 characters and 4 characters with one or two characters followed by one or two numbers.
What I don't want to happen for example is to have postcodes counted as BH1 when in fact they are BH13 or to have postcodes coutned as BH13 when they are actually BH1 3LV.
Does anyone have any suggestions of how I can do this? :confused:
Hi all. As always, thanks in advance for the input.
I'm building a database of housing developments done by my organization. There are a number of rules that I would like to enforce--many of the rules for data entry in a given field, depend on the value entered in a separate field. For example, if someone enters "Residential" in the BldgType field, then the Affordability field cannot be Null (If they enter "Commercial" for BldgType, the Affordability field CAN be Null).
Similarly, if someone enters "LLC" in the PartnerType field, then the value in the PartnerRole field must be either "Member" or "Managing Member", whereas if they enter "LP" in PartnerType, then the value of PartnerRole must be either "Managing General Partner", "General Partner", or "Limited Partner."
I need to know how to be able to make my database accept only postcode format entries like RT24 6QP (english). Like a validation , it would only accept that format. How would i go about doing that. Please explain to be in easy to understand contex as im no expert with access.
I have little problem that I am struggling to find an answer to. When I output a piece of data from my text box, it must be either 10 or 11 characters long when it goes into the receiving data base. I just wonedered if anyone knew of a way of validating the text box to ensure that it met this criteria before output. Input mask is not an option, as other receiving databases may not require the validation.
I am creating a database using Access 2000 that requires validation on a date in a field (it is a short date e.g. 24/11/2005). I need to create a validation rule that only accepts Saturdays. I have an algorithm (see attachment) that can turn a date into a number, which relates to a specific day however I do not know how to implement this using access. Any algorithm tutorials, help or suggestions would be greatly appreciated.
P.S. This is my first post so I am sorry if this tread is in the wrong set of topics
I'm doing something wrong, as this isn't working for me.
I have 2 fields on a continuous subform Quanity and Defect_description, if a Quanity of larger than one is entered then a Defect_description must be selected. Defect_description is a combo box.
I put this on the before update of the Defect_description
if [quantity] >0 then Defect_description notnull msgbox "you entered a quanity - now select a defect description", vbinformation, "Message" Defect_description.set focus else end if end sub
Hi, I am inputing addresses into a massive databse, through a form, does anyone know a way, that i can get it to do a search for the address based on the postcode, like how googlemaps does it or something?
I was wondering if anyone had any experience of integrating a product like QuickAddress (QAS) into an Access Database to aid the completion of address details. ThanksRussG
I have searched the forum, and not really found the answer I`m looking for. I used in my Database, a postcode field, which for 90% of the postcodes I input is is fine.
However, som of the postcodes dont conform to standard post codes.
I`ve found some info in this link, http://www.access-programmers.co.uk/forums/showthread.php?t=89393&highlight=links
but this didnt really help.
Most postcodes ( as set in the database properties ) confirm to thiis standard
SK01 1AA
Which if intered in this format works all OK.
However, some postcodes go in as fiollows
M01 1AA
Which leaves me a digit short, and messes up my search sections of the database, and the link to external map programs like M$ Mappoint.
Is there anyway that anyone knows of that will sort this out, and allow both entries withouht screwing up other programs.
This field is also a REQUIRED field, and must be a data field, or my external program links like M$ Map Point wont work.
I have a table Orders with fields InvoiceNo And Invoice Series.
I want if of course anyone can help me to Validate the next Invoice Number Not To Be Dublicate and to automatically Increment. Meaning that if Last Invoice number was 45 and Invoice Series is A the next to be A 46. If Last Invoice without Series is 40 the next to be 41, and so on. I can manage to increment the values in one field Invoice number but I can not incoporate the Invoice Series.
Any help would appreciated.
Note : I'm not good in VB so please be kind to me.
instead of providing validation at the client side or in the server script.... cant i provide validation in the tables fields... I have a field named YearID, datatype text..
But I want to allow only text like this ==> e.g. 'F-00' 'F-01'
Do I have to put any format property... please do help... thank u.
In a school, a customer ID (in a canteen for example) would be either student administration number or staff initials.... how can i make this customerID field so that it accepts either all numbers (for student admin. no.) or all text with only 3 letters (for staff initials)?
Found some great code, else where that perhaps someone else can use.
Each required fields tag property - change to required Dim blnContinue As Boolean Dim ctl As Control blnContinue = True For Each ctl In Me.Controls If ctl.Tag = "Required" Then If IsNull(ctl) Then MsgBox "Required Information Missing", vbCritical + vbOKOnly + vbDefaultButton1, "You must fill in the x xx xxx xxxx fields" Cancel = True ctl.SetFocus Exit For End If End If Next ctl Set ctl = Nothing End Sub
Just been asked this and not sure if it is possible...
they do not want to pay loads of money to the royal mail for their postcode/address data. but they want a faster way (like typing postcode and address options popup) of entering addresses. There are tons of websites that can be used to find addresses with a postcode, i was wondering if it was possible to somehow link the database to lookup address from a website...?
Am trying to query a customer database to find a how many instances of each postcode exist to determine geographic distribution. Only interested in the first part of the postcode so the following code was suggested:
SELECT Count(owners.Field8) AS Total, Left$([Field8], Len([Field8])-3) AS Code FROM owners;
However it returned a message saying "you tried to execute a query that does not include the specified expression 'Left$([Field8], Len([Field8])-3)' as part of an aggregate funtion.
Hi All, Can anyone help me count postcode instances. I have a query that looks at my customers table (tbl_Customer_Details) post code field (PostCode), at the moment I have got it to strip out the right side of the postcode leaving me the left district side eg. HG12 8EN becomes HG12. I would then like to count how many times each postcode instance occurs so I can create a report on the result so I can track which district the customers are coming from. I hope this explains the problem. Any help would be mich appreciated.
SELECT tbl_Customer_Details.PostCode, Left([Postcode],4) AS Code FROM tbl_Customer_Details GROUP BY tbl_Customer_Details.PostCode;
