[mso] Re: Excel Function to Access-FINAL SOLUTION
- From: "Jim Pettit" <j_e_pettit@xxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Fri, 4 Jun 2004 10:37:42 -0700
Glenda/All--
Very clever use! The ability to call intrinsic or custom VBA functions
from within a query is an extremely powerful component of both Access
and Excel Query.
Sprite is tickly, yes...and 0 calories! But, alas, it has no caffeine.
Therefore, I drink Diet Mountain Dew, the weight-aware programmer's
beverage of choice. ;-)
--Jim
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Glenda Wells
Sent: Friday, June 04, 2004 10:16 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Excel Function to Access-FINAL SOLUTION
Just in case anyone wants to know what I'm doing with Jim's elegant
solution and advice...
Rather than making an AfterUpdate event in a form which I'm taking is
for data entry, I made a query result since, in my jungle, the data
already exists and I need to deal with it after the fact.
The data gets put into a system over which we have no control. We use
Excel Microsoft Query to extract the data so I want the email validation
as a tool to help our operations folks find and correct existing data on
an ongoing basis (after which an Oracle process is invoked to move the
data from the Access DB to our local administrative system).
The DB links to an email worksheet in a workbook residing on our
network. The workbook is refreshed daily. I made a query to display
only the bad email addresses and will add whatever pieces of information
operations needs to locate and fix offending records. In it's most
basic form, here is my solution which works! (Out of 3,086 records, 6
display as invalid):
SELECT email.Number, IIf(IsEmailValid([Number])=3D-1,"","INVALID") AS
Valid FROM email WHERE
(((IIf(IsEmailValid([Number])=3D-1,"","INVALID"))>"a"))
WITH OWNERACCESS OPTION;
ahhhh...Diet Sprite...the bubbles tickle my nose
/glenda
http://www.nutters.org/docs/more-monkeys
-----Original Message-----
From: Jim Pettit [mailto:j_e_pettit@xxxxxxxxxxx]
Sent: Friday, June 04, 2004 12:04 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Excel Function to Access
Glenda/All--
Sorry; I didn't mean to cause any consternation with that whole Regular
Expressions thing; I was just showing a different way of approaching the
same problem: validating an email address. Either function (yours or
mine) can be called from within any form or standard module in Access.
Lemme 'splain:
1) In your Access MDB, click on the 'Modules' tab, and then select
'New'.
2) Enter (or paste) your function (or, you know, mine).
3) Save the module using whatever name you want ('modValidate' would be
fine, but that's my preference).
4) Create a form with a textbox bound to the underlying email address
field.
5) In that textbox's 'AfterUpdate' event, place your code. Here's an
oversimplified version that assumes your textbox is named 'txtEmail':
Private Sub txtEmail_AfterUpdate()
If RegExpTest(Me!txtEmail) Then
MsgBox "You entered a properly formatted email address.",
vbOKOnly + vbInformation, "Success!"
Else
MsgBox "You entered an improperly formatted email address; try
again.", vbOKOnly + vbExclamation, "Failure!"
Me!txtEmail =3D ""
Me!txtEmail.SetFocus
End If
End Sub
6) Toast yourself with a glass of champagne.
Because we placed the function in a module, it's available anyplace in
the application. Simple, no?
You know, Shakespeare we're not, we're all, simply, monkeys. It's just
that some of us have been hanging out in this dank corner of the zoo
longer than have others, so we've had more time to peck away at our
thousand keyboards. ;-)
--Jim
_____ =20
<< ella for Spam Control >> has removed 10905 Spam messages and set
aside 0 Newsletters for me You can use it too - and it's FREE!
www.ellaforspam.com=09
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the
quotes) in the subject line.
Or, visit the group's homepage and use the dropdown menu. This will
also allow you to change your email settings to digest or vacation (no
mail). //www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group,
send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an
invitation with instructions. Once you are a member of the files group,
you can go here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
