Calling any MS Access geniuses!
- Thread starter mrapoc
- Start date
name='chudley' said:I thought SQL was the underpinning function in access as they all use SQL?
In regards to your question you could use auto correct to replace 447 with 07 just hope no one has a phone number with 447 in the middle
Yea it is, you can just achieve more complex things easier using the SQL input option in access
name='mrapoc' said:
I wouldnt say you HAVE to no. Just might be easier to find how through the SQL path.
Might I ask what the phone number will be used for? If it will be displayed or read out rather than processed you could split the number into two sections. The first having error correction applied and the second being left as inputted (obviously with some formatting). The two could then be displayed as one with some form design fiddling ?
Doesn't MS Access come with any basic VB scripting?
EDIT: this might be what ur after http://support.microsoft.com/kb/210465
EDIT: this might be what ur after http://support.microsoft.com/kb/210465
Can do that with SQL easily too
UPDATE table name
SET fieldname = new value
WHERE fieldname = value (can be any field in the entry as the target field is specified above)
Could apply that to a seperate table containing the number prefixes, linked to the table containing the number body.
example:
UPDATE prefixes
SET prefix = "0"
WHERE ownsphone = "yes"
would change all number prefixes to 0
UPDATE table name
SET fieldname = new value
WHERE fieldname = value (can be any field in the entry as the target field is specified above)
Could apply that to a seperate table containing the number prefixes, linked to the table containing the number body.
example:
UPDATE prefixes
SET prefix = "0"
WHERE ownsphone = "yes"
would change all number prefixes to 0
RollerCam540
New member
You can accomplish this task many ways and I can easily help you to do it!
There are many considerations that you need to make, therefore I have many questions:
Answer me those questions first and I will provide you with your answers.
There are many considerations that you need to make, therefore I have many questions:
- How will the user (hereafter reffered to as "the client") enter the information into the database? For example, will the client use a form, a datasheet, or both? Different methods of entry will require different methods of validation, forgetting that datasheet validation is inherently limited.
- How much access experience do you have?
- Would you feel comfortable using VBA?
- If you are using a form then please provide me with all relevant object member names so that I can write code for you that you can simply cut and paste into the Visual Basic Module.
- The length of a phone number, including area and/or country code, for the appropriate places.
Answer me those questions first and I will provide you with your answers.
The client will be putting data into the database via a form for simplicity but initial data will be imported with a spreadsheet so both i guess (college project so not for proper use, just demonstration it will work)
I am pretty much rubbish at access but i can follow instructions
VBA being visual basic or something? Ive done a bit of VB before
I havent designed the form yet, i am still building entity. Object member names are:
Customer ID (pk), Customer first name, Customer other names, customer surname, customer date of birth, customer house number/name, customer address 1, customer address 2, customer address 3, customer county, customer telephone number, customer mobile number, customer email address
mobile number should be in the format 07(9digits) but some people may use 447(9digits)
so the problem is replacing 44 with 0
I am pretty much rubbish at access but i can follow instructions
VBA being visual basic or something? Ive done a bit of VB before
I havent designed the form yet, i am still building entity. Object member names are:
Customer ID (pk), Customer first name, Customer other names, customer surname, customer date of birth, customer house number/name, customer address 1, customer address 2, customer address 3, customer county, customer telephone number, customer mobile number, customer email address
mobile number should be in the format 07(9digits) but some people may use 447(9digits)
so the problem is replacing 44 with 0
RollerCam540
New member
If the client is going to enter information directly through a form, disable their ability to enter information via a datasheet. Why? You're going to "test for data validation" through the form and not the datasheet, thereby preventing clients from bypassing that validation by entering data through a datasheet.
Truthfully, you can easily set up validation, in this case, for a datasheet as well but you generally want to avoid giving clients datasheet access because validation is very limited within datasheets.
With regards to importing the data from excel: how were you planning on doing that? Would it be done with the click of a button that resides on a form, removing the client from the importation process completely? NOTE: for this step, the fastest and easiest solution would be to write a SQL statement to "adjust" the numbers.
With regard to the numbers themselves -- I'm not from the UK and I know nothing about it -- is there any way that a mobile number could find itself in a table, not contain the "UK Code" and still be 9 characters long? I want to make sure we can differentiate between a phone number with a definite "UK Code" and another number that just happens to start with "447". Maybe you could give me a quick crash course in how phone numbers work over there, because they must be unique regardless of how they were given, otherwise you wouldn't be to dial a telephone and correctly reach the person you dial if phone numbers weren't unique.
Also, I want to be clear on something: if the mobile number is "447123456" and you replace the "447" with "07" then the number will be "07123456". Is that correct? If so, the original number was 9 characters and the "new fixed number" will be 8 characters -- is that what you want?).
Also, by object members I also meant the names of the textboxs' and buttons' within the form itself. Will you be using bound or unbound form objects?
How many users will enter data into the database simultaneously?
Truthfully, you can easily set up validation, in this case, for a datasheet as well but you generally want to avoid giving clients datasheet access because validation is very limited within datasheets.
With regards to importing the data from excel: how were you planning on doing that? Would it be done with the click of a button that resides on a form, removing the client from the importation process completely? NOTE: for this step, the fastest and easiest solution would be to write a SQL statement to "adjust" the numbers.
With regard to the numbers themselves -- I'm not from the UK and I know nothing about it -- is there any way that a mobile number could find itself in a table, not contain the "UK Code" and still be 9 characters long? I want to make sure we can differentiate between a phone number with a definite "UK Code" and another number that just happens to start with "447". Maybe you could give me a quick crash course in how phone numbers work over there, because they must be unique regardless of how they were given, otherwise you wouldn't be to dial a telephone and correctly reach the person you dial if phone numbers weren't unique.
Also, I want to be clear on something: if the mobile number is "447123456" and you replace the "447" with "07" then the number will be "07123456". Is that correct? If so, the original number was 9 characters and the "new fixed number" will be 8 characters -- is that what you want?).
Also, by object members I also meant the names of the textboxs' and buttons' within the form itself. Will you be using bound or unbound form objects?
How many users will enter data into the database simultaneously?
RollerCam540
New member
Seeing that I haven't gotten a response from you and that I'll be in school all day tomorrow, I figured I'd leave you with some code to play with.
When you import data, seeing that you can import an unlimited number of rows and changing each of the values for each of these rows individually would prove very inefficient, the fastest way that I can think of to make the desired changes during importation is to do so like this:
UPDATE tblNmbr SET tblNmbr.mblNmbr = "07" + Right(tblNmbr.mblNmbr,9)
WHERE (((tblNmbr.mblNmbr) Like "447*") AND ((Len(tblNmbr.mblNmbr))=12));
NOTE: A big assumption is being made here: a number can only be nine characters in length when it has an area code. Meaning that a number, in addition to an area code, can actually start with 447 but the length of that number neglecting the area code must only be 9 characters long -- AND -- the length of a number with an area code MUST only be 12 characters long, no more and no less! YOU NEED TO UNDERSTAND THIS COMPLETELY, BECAUSE YOU CORRUPT DATA IF YOU DON'T. THE COMPUTER WILL DO WHAT YOU TELL IT, EVEN WHEN YOU TELL IT TO DO SOMETHING WRONG.
Make sure you adjust the underlined text as appropriate. NOTE: If the "447" number is 9 characters in length, after changing "447" to "07" it will be 8 characters in length!!!
ALSO: I'm not a big fan of "magic numbers" and you could probably make some tables and then use the relations to eliminate magic numbers while adding an extra layer of flexibility to that statement.
With regard to the form, when the user goes to submit the record, the following code goes in the proper event.
Const BAD_NMBR_PREFIX as String = "447"
Const BAD_NMBR_PREFIX_WILDCARD as String = BAD_NMBR_PREFIX & "*"
Const BAD_NMBR_LEN As Integer = 9
Const GOOD_NMBR_PREFIX as String = "07"
If txtMblNmbr Like BAD_NMBR_PREFIX_WILDCARD And Len(txtMblNmbr) = BAD_NMBR_LEN Then
End If
I can help you adjust that, augment it, or accomplish it differently depending on how much you know and what you might like to do differently.
When you import data, seeing that you can import an unlimited number of rows and changing each of the values for each of these rows individually would prove very inefficient, the fastest way that I can think of to make the desired changes during importation is to do so like this:
UPDATE tblNmbr SET tblNmbr.mblNmbr = "07" + Right(tblNmbr.mblNmbr,9)
WHERE (((tblNmbr.mblNmbr) Like "447*") AND ((Len(tblNmbr.mblNmbr))=12));
NOTE: A big assumption is being made here: a number can only be nine characters in length when it has an area code. Meaning that a number, in addition to an area code, can actually start with 447 but the length of that number neglecting the area code must only be 9 characters long -- AND -- the length of a number with an area code MUST only be 12 characters long, no more and no less! YOU NEED TO UNDERSTAND THIS COMPLETELY, BECAUSE YOU CORRUPT DATA IF YOU DON'T. THE COMPUTER WILL DO WHAT YOU TELL IT, EVEN WHEN YOU TELL IT TO DO SOMETHING WRONG.
Make sure you adjust the underlined text as appropriate. NOTE: If the "447" number is 9 characters in length, after changing "447" to "07" it will be 8 characters in length!!!
ALSO: I'm not a big fan of "magic numbers" and you could probably make some tables and then use the relations to eliminate magic numbers while adding an extra layer of flexibility to that statement.
With regard to the form, when the user goes to submit the record, the following code goes in the proper event.
Const BAD_NMBR_PREFIX as String = "447"
Const BAD_NMBR_PREFIX_WILDCARD as String = BAD_NMBR_PREFIX & "*"
Const BAD_NMBR_LEN As Integer = 9
Const GOOD_NMBR_PREFIX as String = "07"
If txtMblNmbr Like BAD_NMBR_PREFIX_WILDCARD And Len(txtMblNmbr) = BAD_NMBR_LEN Then
txtMblNmbr = GOOD_NMBR_PREFIX & RIGHT(txtMblNmbr, BAD_NMBR_LEN - LEN(BAD_NMBR_PREFIX))
End If
I can help you adjust that, augment it, or accomplish it differently depending on how much you know and what you might like to do differently.
Similar threads
