I guess this is pretty fundamental but I cant get it right! I want to search for a member by post code so I need to have validation for my table field post code so that when my parameter query accepts the post code input records will be returned covering all input types e.g. Users can put in CF72 (space)9AD or CF729AD and still get the same records or alternately how do I make users enter Post Codes with the correct UK spacing i.e 4 characters a space then the remaining characters?
The problem with UK postcodes is that there are 6 different formats. These are:
A9 9AA A99 9AA AA9 9AA AA99 9AA A9A 9AA AA9A 9AA
I've seen on similar threads where people have just forced the field into capitals, but my data requires that it is a valid postcode and I cant find an input mask that allows all UK postcode.
I have problem loading data from an excel file in to a table. I usually import the excel file in to a table via VBA script (because there are some changes I need to do before importing).It was working fine until this week when the data in a cell for a particular column in the excel file changed from being 3 letters to 4 letters. The column in the table is defined as text.
The data in this column usually looks like 001, 002, 003 etc but one of the cells in the excel file has data as P001.So now I get an error Runtime Error 3349 even though I have defined the column as text. When I change the data P001 to 004 similar to the other data, it works just fine. Is there a format or Input Mask I can set so that the data are correctly loaded in to the table?Or am I missing something?
Hiya everyone, I'm try to do a database for items with part numbers. & I want to be able to set the field to only accept data which has two letters at the begining, but any other characters after that (no fixed size).
ie. part num1: pn047589sdf8m3 part num2: pn6583 part num3: pnfdgmj5973m part num4: pn-fdgmj-5973m
Is this possible to do?
Cos all I can do at the moment, is to put a fixed number of characters. ie. is the field size is 30characters then I'd have to do this: >LLCCCCCCCCCCCCCCCCCCCCCCCCCCCC
but this isn't good.
I just need something like: >LL*
* mean any character for as many characters in length.
I am creating a database where the serialID can be a number of different 'formats'. TBH, I don't know if this can even be done in the first place, but on the off chance that it can....:p
Firstly, i'm not sure whether to use Input Mask or Format...
But the serial may be '1234567AB', '12345678AB' or '12AB1234AB'.
Or to put another way
'7 numbers 2 letters',
'8 numbers 2 letters'
or '2 numbers, 2 letters, 4 numbers, 2 letters'
I have tried creating a mask such as '00LL0000LL' (can't remember exactly, had some '9's in there too some where...) but just keep hitting a brick wall, whereby it not satisfying all of the types.
I have a query which is pulling its data from a form, which in turn is pulling data from a table. When I select 0.82 on my form, my query runs fine. But when I select 0.826856 from my form the query does not return any result.
0.826856 is available in the table and the pull down list in the form. The data format for all my table, form and query is General and decimal places is set to 8.
Could you plz tell me why I am not able to run the query using 0.826856 ????
My Querie: I have a relational database, that is basically made up of a few tables, I will simplify for this to two. TblFieldNames and TblFieldValues
Basically using a form you can fill in a field (TblFieldNames.FieldName) and it is then appended to TblFieldValues.FieldValue.
On the form the properties of the input text box are changed according to the record selected in TblFieldNames. Ie. ListBox with FieldNames from TableFieldNames ListBox_AfterUpdate sets properties of TxtValue
The Issue: I have a Field in TblFieldNames that has a setting of Currency. (TblFieldNames.FieldType = "Currency")
I have changed the TxtValue.Format = "Currency"
It doesn't: A. Allow entry as a currency, ie. $ sign and thousand seperators B. Append the details to the TblFieldValues Table, which it is going across to into a field that is set as Text (this can't change)
I have tried: Changing format of input Txt box Changing Input Mask for inptu Txt Box "$###,###,###" but doesn't work for me.... "/$###,###,###" "/$###,###,###!"
I want it to allow me to enter in the Value as a Currency and append the value as a currency
ie. Key Press = 12345 Field Shows = $12,345 Appends this value as Text?
Key Press = 1234567890 Field Shows = $1,234,567,890
I have the following input mask for a certain field in an ACCESS 2010 table : 00-000-a-a-a;0;-
It works fine. That is when I tab down to it and if I know that the format is : xx-xxx-x-x-x
but if someone does not know the format , things can be out of position.how do I code the field such that the format (xx-xxx-x-x-x) is displayed in the input field awaiting the actual keying of the data?
Is there a Input Mask or Rule Validation that would automatically or require user to input data into Access field in a proper case format? Such as my text would be "My Text" when viewing the actual table? If so how would you do it when in Design View of Table.
I saw a demo that the user could copy and paste a JPG format file physically to ACCESS, and then something happened, and ACCESS could read the JPG length and width size (e.g. 1.3 meter length and 1.15 meter width) into 2 individual ACCESS cells which is acted the same as user input to that 2 clells.
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.
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.
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 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.
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;
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:
