Using Validation Rule

I currently use Access to maintain an mailing list. Bulk mailing address
requirements only allow capital letters, no punctuation or other characters.
I have limited the capitalization in the function. 

I would like to use the validation rule below. The problem is it does not
allow the use of space between text as needed
 

Rule: Is Null Or Not Like "*[!((a-z) or (0-9))]*"
Accepts A to Z and 0 to 9, but no punctuation or other characters.

0
smay
5/24/2007 5:52:53 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
1106 Views

Similar Articles

[PageSpeed] 47

Thanks for your help.
I tried this in the Properties-Validation Rule box. I get an "invalid syntax"
error message. Unfortunately I have no experience writing expressions so I'm
not sure what needs to be corrected.



Klatuu wrote:
>Here is a function that will perform that test.  It allows only uppercase 
>characters, digits, and spaces.  It returns True if the string passes the 
>test and false if it does not.
>
>Public Function CapsAndNumsOnly(ByVal strTest As String) As Boolean
>Dim lngCtr As Long
>Dim lngLength As Long
>Dim intAscii As Integer
>    
>    lngLength = Len(strTest)
>    For lngCtr = 1 To lngLength
>        intAscii = Asc(Mid(strTest, lngCtr, 1))
>        If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And 
>intAscii <= 90) _
>                Or (intAscii = 32) Then
>            CapsAndNumsOnly = True
>        Else
>            CapsAndNumsOnly = False
>            Exit For
>        End If
>    Next lngCtr
>
>End Function
>
>Now, to use it, call it in the Before Update event of the text box where you 
>enter it:
>
>    If Not CapsAndNumsOnly(Me.txtSomeContro) Then
>        MsgBox "Invalid Characters In String", vbExclamation
>        Cancel = True
>    End If
>
>> I currently use Access to maintain an mailing list. Bulk mailing address
>> requirements only allow capital letters, no punctuation or other characters.
>[quoted text clipped - 6 lines]
>> Rule: Is Null Or Not Like "*[!((a-z) or (0-9))]*"
>> Accepts A to Z and 0 to 9, but no punctuation or other characters.

-- 
Message posted via http://www.accessmonster.com

0
smay
5/25/2007 8:45:25 PM
Re read my previous post.  This does not belong in the validation rule.  I 
don't use validation rules.

> >Now, to use it, call it in the Before Update event of the text box where you 
> >enter it:
> >
> >    If Not CapsAndNumsOnly(Me.txtSomeContro) Then
> >        MsgBox "Invalid Characters In String", vbExclamation
> >        Cancel = True
> >    End If

Put the code in the form module.

-- 
Dave Hargis, Microsoft Access MVP


"smay via AccessMonster.com" wrote:

> Thanks for your help.
> I tried this in the Properties-Validation Rule box. I get an "invalid syntax"
> error message. Unfortunately I have no experience writing expressions so I'm
> not sure what needs to be corrected.
> 
> 
> 
> Klatuu wrote:
> >Here is a function that will perform that test.  It allows only uppercase 
> >characters, digits, and spaces.  It returns True if the string passes the 
> >test and false if it does not.
> >
> >Public Function CapsAndNumsOnly(ByVal strTest As String) As Boolean
> >Dim lngCtr As Long
> >Dim lngLength As Long
> >Dim intAscii As Integer
> >    
> >    lngLength = Len(strTest)
> >    For lngCtr = 1 To lngLength
> >        intAscii = Asc(Mid(strTest, lngCtr, 1))
> >        If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And 
> >intAscii <= 90) _
> >                Or (intAscii = 32) Then
> >            CapsAndNumsOnly = True
> >        Else
> >            CapsAndNumsOnly = False
> >            Exit For
> >        End If
> >    Next lngCtr
> >
> >End Function
> >
> >Now, to use it, call it in the Before Update event of the text box where you 
> >enter it:
> >
> >    If Not CapsAndNumsOnly(Me.txtSomeContro) Then
> >        MsgBox "Invalid Characters In String", vbExclamation
> >        Cancel = True
> >    End If
> >
> >> I currently use Access to maintain an mailing list. Bulk mailing address
> >> requirements only allow capital letters, no punctuation or other characters.
> >[quoted text clipped - 6 lines]
> >> Rule: Is Null Or Not Like "*[!((a-z) or (0-9))]*"
> >> Accepts A to Z and 0 to 9, but no punctuation or other characters.
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> 
0
Utf
5/25/2007 8:57:01 PM
"smay" <u34495@uwe> wrote in message <72aa68dd025d5@uwe>:
> I currently use Access to maintain an mailing list. Bulk mailing
> address requirements only allow capital letters, no punctuation or
> other characters. I have limited the capitalization in the function. 
>
> I would like to use the validation rule below. The problem is it does
> not allow the use of space between text as needed
>  
>
> Rule: Is Null Or Not Like "*[!((a-z) or (0-9))]*"
> Accepts A to Z and 0 to 9, but no punctuation or other characters.

On my setup, that test does allow spaces between words, but it also
allows parenthesis (). Have you tried something like this

Is Null Or Not Like "*[!a-z 0-9]*"

If this is at table level, and there's any chance you'll be adding data
to the table through ADO, I'd recommend one step further

Is Null Or (Not Like "*[!a-z 0-9]*" And Not Like "%[!a-z 0-9]%")

which is taking into consideration the ADO wildchard character '%'

-- 
Roy-Vidar


0
RoyVidar
5/26/2007 8:41:52 AM
Reply:

Similar Artilces:

Sent Items using Outlook 2007 with Exchange mailbox + hotmail
Hi, I am currently using Outlook 2007 with an Exchange 2003 mailbox. I recently added my Hotmail mailbox so I don't need to go through IE for my personnal emails. Everytime I send and email, the sent email goes to both my exchange mailbox sent items AND in my Hotmail sent items. Since I am using hotmail for my personal communications, I'd like them to be added only in my Hotmail sent items. I don't want to mix up my personal and business communications. Is that possible? Regards. ...

How to update a link field with preserve format using VBA?
HI All, How to update a link field with preserve format using VBA? Anthony Without knowing more about what you are trying to do it is difficult to advise. You could for example switch to print preview and back or you could use the example code at http://www.gmayor.com/installing_macro.htm both of which will update fields in your document. If, as your e-mail address suggests, this is a link to an Excel range then I suspect your concern may be more to do with maintaining the formatting than updating the field. That will depend on the type of link you have inserted. If you ...

Import Excel to GP table using Dexterity
Hello all, Does anyone knows how to import excel datas in to GP table using Dexterity? If possible provide any sample code for this. Regards, Prakash Prakash, You should be able to read a file name with the SanScript GetFile function and define a Data Type as Reference, using a Reference Type of COM Object, and defining the COM Object Type as Excel.Application object. I do not have any samples at this time, but I am sure you can figure it out with the Dexterity Programmer's Guide. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http:/...

Multiple reports use same queries, how do I avoid rerunning querie
I'm working on several reports in an Access database and need some advice. I have three reports that are all based on a series of queries, and only the last one is specific to each report. It starts with a broad select query, then queries those results to reformat some data, then queries those results as a totals query and finally the report specific query runs to filter those results down to only that necessary for that specific report. I've built a macro to run all three reports, but each report calls all the underlying queries to be run. Because of the redundant querying, ...

add values using vlookup over multi sheets
I have a workbook which contains about 20 worksheets. In sheet1 I have a list of numbers which could be also on the other 19 sheets. What I need to do is look at each of the other 19 sheets to see if they have the number which is on sheet1 and if so I need to get the numerical data in the 5th column to be returned and added together. ie sheet1 a1 = 2001 sheet4 a5 = 2001 e5 = 2 sheet6 a21 = 2001 e21 = 5 therefore sheet1 b1 = e5 + e21 = 7 Hopefully someone could help me. Thanks Noemi Try... =SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("2:20"))&&q...

Change Filter Rules of Default Global Address List
Is there any way to change the Filter Rules of the Default Global Address List in Exchange 2003. The Modify button is greyed out. I want to hide External Contacts from the GAL (by only displaying Exchange mailboxes or mail enabled users), but still have those contacts appear under All Contacts. Any help would be greatly appreciated. Greg Greg: You could do this by editing the Default Global Address List object in ADSEdit. I think (if memory serves me correctly), the attribute that holds the query is the purportedSearch attribute. However, messing with attributes of default ...

The Webpage Fetch Method I've Been Using Has Quit Working
I have used the following function to fetch webpages for a long time and in several applications. hOpenUrl = InternetOpenUrl(hOpen, sUrl, vbNullString, 0, _ INTERNET_FLAG_RELOAD, 0) bDoLoop = True While bDoLoop sReadBuffer = vbNullString bRet = InternetReadFile(hOpenUrl, sReadBuffer, _ Len(sReadBuffer), lNumberOfBytesRead) sBuffer = sBuffer & Left$(sReadBuffer, _ lNumberOfBytesRead) If Not CBool(lNumberOfBytesRead) Then bDoLoop = False Wend If hOpenUrl <> 0 Then InternetCloseHandle (hO...

use screen captures to show icons etc
I can't find the send button in outlook, help has been useless, referring only to the button, not showing where it is. I would like help to use screen captures to show graphically the information they refer to. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http:...

Changing a Number in a Column Using Arrays?
I am trying to change a number in the left column to the number in the adjacent column. My setup looks like this. Number to Change Change to this Number 182001 182000 182002 182000 193002 193000 Code New Code (from the Change to this Number column) 181000 181000 182000 182000 182001 182000 182002 18...

Use sound as a trigger to advance slides
Is it possible to use a sound file in this way? I'd like the slide to advance at the end of the sound file. I'm probably out of luck but please someone tell me I don't have to sit here and figure out how long each sound file is... Sorry, you're out of luck. But if you insert the sound, you can right-click the icon and choose Properties to see the playing time. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Ki...

Index using a defined name array
I have defined 3 arrays using DefineNames: Boys_11_12 Boys_13_14 Boys_15_16 I then have defined a Combo dropdown with the following choices: Boys_11_12 Boys_13_14 Boys_15_16 When the user selects the Boys age group using the Combo dropdown, I then use the selection in an Index function: Index(Boys_(one of the three),...) I get an error when using the Combo dropdown, but it works fine when I type in the array name directly into the Index function. Its as if it knows the array when typed in, but not using the Combo dropdown. I created the Combo dropdown by typing in the arr...

using MS Query with iSeries ODBC and special caracter
I need help. I found problems using MS Query with iSeries ODBC driver when there are table o library named with special caracters ("$" or "_") ...

Validation Confusion-Excel 2003
On a timesheet I need to ensure that when I enter in the times of a the DAY shift that it is on or after 6:00 am. The finish time is not important as the start time dictates the shift. For example, someone may start at 7:30 and work thro to 3:30 (or 15:50), this equals a day shift. I may have someone else who starts at 6:00 am and finishes at 10:00 am, this also equals a day shift. I also need an AFTERNOON shift that starts at 12:00 (Noon). I believe if I have the answer to the day, this will come easy to me (that is if the Noon time doesn't become a hinderance??) I also need ...

Using Outlook 2003 with Office XP
I have gone through multiple messages regarding the use of Outlook 2003 and the remaining components of Office XP, but I am unable to get a clear cut answer regarding their compatibility, recommended installation flow etc. I am using an authoring systems which requires Word Xp and Powerpoint XP, however I'd like to be able to use Outlook 2003 (the authoring system does not require a specific Outlook version!). My questions are: (i) Can I install Office XP without the Outlook component (i.e., Word, Powerpoint etc.), and following that install just the Outlook component from the Off...

in CRM Mobile, Can we assign / apply rules ?
Both in CRM Mobile and Mobile Express, I didn't see any function about assign / apply rules / convert ... These functions are not available out of box. You can however, create workflow rules that "watches" for some changes to field records so it can be triggered wheneven those fields are updated by CRM Mobile or Mobile Express. Frank Lee, Microsoft CRM MVP Workopia, Inc. http://microsoft-crm.spaces.live.com "Goldensun" wrote: > Both in CRM Mobile and Mobile Express, I didn't see any function about assign > / apply rules / convert ... ...

Using money on another computer
I am using Microsoft Money 2003 D&B. I have my computer networked with my secretaries computer. I want her to pay my bills on her computer. I tried installing money 2003 on her computer (after uninstalling on mine) and it appeared to install properly but when I try and start it using the desktop icon that was installed it pops on and then off with no error message. I use Windows 98 on her computer as there are some old programs I use that only work on that operating system. Any solutions or suggestions. Also is there anyway to use this program over the network so that it ca...

Use of OpenArgs
I am opening a form called frmFindPerson from a form called frmEditPerProj. I am passing the form name of the first form to the second form by placeing it in openArgs in doCmd.OpenForm as "frmEditPerProj". I want to select a record from the second form and pass a field value back to the first using the form name passed in OpenArgs to define the field to be filled with this data. I was not sure whether I could use OpenArgs dircetly so I have set up a variable and assigned the value of OpenArgs to it as follows Private Sub cmdSelect_Click() Dim strFormName As String strFormName = For...

Best data type to use in a document to handle a CListCtrl?
I am using a CListCtrl on a CFormView and I want to know what is the best way, or data type, to use in the doc class to represent the data,(or rows and columns) of the CListCtrl. Thanks a bunch. CLEAVEAGE It is very much application dependent -- but here is a technique that i use from time to time: Assume a bunch of data that exists in a database that i would like to visualize - and i have chosen the list-view control. In the CDoc class I would make the connection to the database (using either ADO or more likely OCI if the database is Oracle hosted). Somewhere in the CDoc class I would...

Shared use of excel work books
Hi, Can anyone tell me "if I have an excel workbook (with multiple worksheets that report to the first worksheet) on a shared drive whether it can be set up for multiple users to access it and use it at the same time?" These users will be accessing their single worksheet to update it periodically with the first worksheet being the project leaders sheet where he gets to see all the other worksheets on his one sheet. -- Ian Hi Ian, You can do this by selcting "shareworkbook" from the tools menu and checking the 'allow changes by more than one use'. Beware th...

How to use Post-Callouts
Hi, I wanted to use the post-callouts option in MS CRM.to get notifications when an account or lead is created. I read the SDK, where it says, we have to create a COM+ service which implements the ICRMCallout Interface. In which DLL does this ICRMCallout Interface is defined. I guess, certainly not in Microsoft.Crm.Proxy.Dll since I already checked in it. Any help would be greatly appreciated Thanks Rojer I don't know if you've checked this article: http://msdn.microsoft.com/library/en-us/dnmbscrm/html/mbs_crmpostcallouts.asp I haven't read it either so I don't know ...

Using CArray in app (Release Unicode), pass a var of this type to dll (Release)
Hi all i have a problem, can anyone help me? // use a CArray CArray<CString, CString> sGroupList; // call a function of dll (release mode) getGroupList(&sGroupList); and i got error at line CArray<TYPE, ARG_TYPE>::~CArray() { ... delete[] (BYTE*)m_pData; ... } Assume that any attempt to mix release and debug executables and DLLs is doomed. Work from that as your basic premise. joe On Wed, 7 Mar 2007 09:58:22 +0700, "Duy Trinh" <duy.trinh@mobinex.com> wrote: >Hi all > >i have a problem, can anyone help me? > >// use a CArr...

using time
Having a problem using time in my calulation. Trying to find out the number of people that past a point for 1,2,3,4,5,6,7,8,9,10,15,20,30 minutes in columns head while I have 55, 100, 160 and 200 in rows. I have sum=(55*(60/col)) Col 1 2 3 4 5 6 7 8 9 10 15 20 30 minutes 55 3300 102 155 300 Then I want to use 5,000 divided by the rows/cols further down the sheet using the same time figures. I try doing sum=(5000/row/(60/col)) where the results should climb as the time gets higher, but results is off/ Col 1 2 3 4 5 6 7 8 9 10 15 20 30...

Making Changes Using Calendar Wizard
I have used one of the Calendar Templates and made changes to the initial page. I then tried to do the "Change Date Range" to add more "months". The only change that stayed was the new Logo I inserted using the Logo Options. Colors and other editing was lost. Is there any way to get the subsequent months to "take" the changes I made on the initial single January page? Thanks! Create all the months from the get go, open the color schemes taskbar, either use one color scheme already defined or create your own. You will have to manually add holidays and speci...

Using an unbound textbox next to each record in form, with all records showing?
I am working with an inventory system, and would like to have a form that shows all products and their current inventory levels, and next to each of them have a textbox for entering in new inventory levels. Is this possible somehow? I can't seem to so it with continuous forms. Is it possible to have run-time created textboxes? Any ideas would be greatly appreciated. -- justme0010 justme, I'm not sure how the setup you described would be used in an inventory system. You'll have to give us more information as to what data you have, and how you plan to calculate invento...

Using Out of Office Assistant with .PST file
Our exchange server is set-up so mail gets delivered to .pst files. I do not want to change to having mail delivered to mailboxes. When employees are going to be out....they set-up the Out of Office Assistant. However, this only seems to work for internal use. If someone from within the exchange server sends an email to this person....they get the reply email back stating that they are out of the office. However, if someone from outside our company sends an email...they don't get the automatic email. Is there a workaround to this? is something not set-up properly on our server? any...