Checking a value in an array.

[DanMurphy]

Hello

I have a textbox on a form for entering addresses into a table, It is so that a user can copy and paste an address into a textbox, and it will split the string into the fields I have on a table (So that I can copy and paste an address from an email into my database quickly). A picture of what I hope to make is attached. Basically I copy and paste into the textbox on the left and it autofills the fields in my table on the right.

The last field in the array that will be created (Split function using Newline Charachter as a delimiter) is a UK postcode. Basically, I am looking to program it to check if the last field looks like a postcode and if not, fill that value into the second last field rather than the last one.

It is possible to check a value just to see if at least two numbers and letters are present?

I have tried google but only seemed to get guides to check if the entire value is a number or not. I am a newbie to Access and VBA, and probably that guy tech support tell stories about. I am using Access 2007 but also have Access 2003 if that makes a difference.

I am not even sure if the solution to this is in VBA, but if anyone could point me in the right direction that would be greatly appreciated.

Thanks in advance.

 

[NigelShaw]

Hi

Just recently there was a thread on postcode validation which had an example db that tested it for a postcode format. I think you will need more than a post code validation though as addressesvcan be different. For example-

Mr Daniel Murphy
10 Downing Street
Westminster
London
SW1 2AA

Is different to

Mr Daniel Murphy
Flat 6 runcorn house
Pettinger gardens
Southall
London
W4 7PP

And different to

Mr Daniel Murphy
10 Downing Street
London
SW1 2AA

You can't always control how people write their address so if any instances above occur, you may get data entered into the wrong field.

Hth


Nidge

 

[ajetrumpet]

Hello

I have a textbox on a form for entering addresses into a table, It is so that a user can copy and paste an address into a textbox, and it will split the string into the fields I have on a table (So that I can copy and paste an address from an email into my database quickly). A picture of what I hope to make is attached. Basically I copy and paste into the textbox on the left and it autofills the fields in my table on the right.

The last field in the array that will be created (Split function using Newline Charachter as a delimiter) is a UK postcode. Basically, I am looking to program it to check if the last field looks like a postcode and if not, fill that value into the second last field rather than the last one.

It is possible to check a value just to see if at least two numbers and letters are present?

I have tried google but only seemed to get guides to check if the entire value is a number or not. I am a newbie to Access and VBA, and probably that guy tech support tell stories about. I am using Access 2007 but also have Access 2003 if that makes a difference.

I am not even sure if the solution to this is in VBA, but if anyone could point me in the right direction that would be greatly appreciated.

Thanks in advance.

follow up to Nigel. The split function works fine. the array will have a set number of elements. there is no fail safe either, because people can write address anyway they want. if you use the linefeed as the delimeter though, use the UBOUND() function of the array as the argument to check the last element that is entered after SPLIT() happens. you can always go char by char in that element to see what you got. you can use ISNUMERIC(), CHR() to check the validation and MID(), LEN() and an integer variable to loop it one char at a time.