Excluding Alpha character in text field by filtering.

I have a query based on a table./

I have one numeric field that has alphanumber entries:  A2000-000; 
101010010,A2004, etc..

I what the filter to exclude all records that have an apph beginning - ie. 
in eg. above exclude: A2000-000,A2004.

How do I do this?

Your assistance is greatly appreciated.



0
Grace
4/19/2007 4:13:09 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
2574 Views

Similar Articles

[PageSpeed] 29

You could use

WHERE Left$(AlphanumericField, 1) NOT BETWEEN "A" AND "Z"

or

WHERE Val(AlphanumeriField) <> 0

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Grace" <fhsmith7@bellsouth.net> wrote in message 
news:DyMVh.8723$vD4.7194@bigfe9...
>I have a query based on a table./
>
> I have one numeric field that has alphanumber entries:  A2000-000; 
> 101010010,A2004, etc..
>
> I what the filter to exclude all records that have an apph beginning - ie. 
> in eg. above exclude: A2000-000,A2004.
>
> How do I do this?
>
> Your assistance is greatly appreciated.
>
>
> 


0
Douglas
4/19/2007 4:28:23 PM
Thanks a lot for updater. Tried the first opiton without success. Something 
wrong with string.



"Grace" <fhsmith7@bellsouth.net> wrote in message 
news:DyMVh.8723$vD4.7194@bigfe9...
>I have a query based on a table./
>
> I have one numeric field that has alphanumber entries:  A2000-000; 
> 101010010,A2004, etc..
>
> I what the filter to exclude all records that have an apph beginning - ie. 
> in eg. above exclude: A2000-000,A2004.
>
> How do I do this?
>
> Your assistance is greatly appreciated.
>
>
> 


0
Grace
4/19/2007 6:22:49 PM
On Thu, 19 Apr 2007 11:13:09 -0500, "Grace" <fhsmith7@bellsouth.net> wrote:

>I have a query based on a table./
>
>I have one numeric field that has alphanumber entries:  A2000-000; 
>101010010,A2004, etc..
>
>I what the filter to exclude all records that have an apph beginning - ie. 
>in eg. above exclude: A2000-000,A2004.

Use a criteirion of 

LIKE "[!A-Z]*"

That is - the first character is NOT (! means NOT in this context) in the
range A to Z, the rest of the string can be anything (*).


             John W. Vinson [MVP]
0
John
4/19/2007 7:07:47 PM
Hi John:

Thanks a bunch, tried the LIKE "[!A-Z]*" string and all is well in River 
City.

Thanks again for your professional assistance.



"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:iaff23t2c658dgv5l4sijgv3lhjk1banb8@4ax.com...
> On Thu, 19 Apr 2007 11:13:09 -0500, "Grace" <fhsmith7@bellsouth.net> 
> wrote:
>
>>I have a query based on a table./
>>
>>I have one numeric field that has alphanumber entries:  A2000-000;
>>101010010,A2004, etc..
>>
>>I what the filter to exclude all records that have an apph beginning - ie.
>>in eg. above exclude: A2000-000,A2004.
>
> Use a criteirion of
>
> LIKE "[!A-Z]*"
>
> That is - the first character is NOT (! means NOT in this context) in the
> range A to Z, the rest of the string can be anything (*).
>
>
>             John W. Vinson [MVP] 


0
Grace
4/19/2007 10:37:24 PM
Reply:

Similar Artilces:

attachment filtering
Can we filter emails with attachments? I would like to block some emails with attachments based upon file type. Is this possible? You can block attachments: http://support.microsoft.com/kb/263297/ http://support.microsoft.com/kb/555001/en-us Nue "Brandon" <whocares@you.com> wrote in message news:e$%23ifsUbGHA.504@TK2MSFTNGP03.phx.gbl... > Can we filter emails with attachments? I would like to block some emails > with attachments based upon file type. Is this possible? > I want to block attachments based upon file extension. How do I accomplish that? &q...

Datedif exclude weekends
Hello newsgroup gurus and users. Your kind assistance please. This question has probably been asked a millions times but I cant find it. Does any one have a solution to this. I want the date difference between two dates but exclude weekends. Many thanks Paul Paul, The NETWORKDAYS function will return the number of days, excluding weekend and optionally holidays, between two dates. See help for NETWORKDAYS for more information. This function is part of the Analysis Tool Pack add-in so you must have that add-in loaded; otherwise, you'll get a #NAME error. -- Cordially, Chip Pe...

Junk Email Filter with IMAP Not Working
The Junk Email Filter in Outlook 2003 has never worked for me. I am using an IMAP account, and according to Microsoft the Junk Filter DOES support IMAP accounts. Apparently you need to download whole message not just the header so I have enabled 'Download Complete Item including Attachments' instead of 'headers only' in Send Recieve Options. But still no joy! What am I doing wrong? Are there any other settings that need to be set to get it to work? I have set the Junk Filter to high, also added email address's to blocked senders list but still messages are not...

Replace a certain Character in a column
To make a long story short, I have a lot of serial numbers (for example SWGMM036478, and I need to remove all of the "S" character automatically. Is there anyway that I can do this easily in Excel. have over 600 serial numbers and they all have the letter "S" at th beginning and they are not supposed to be there. Any tips? Thanks -- Message posted from http://www.ExcelForum.com Select the cells that contain the serial numbers. Choose Data>Text to Columns Select Fixed Width, click Next In the Data Preview window, click after the S, to add a break line. Click N...

gci and -include and -exclude
I am trying get a limited set of files from a directory tree. The following is what i want but it doesn't work. I get everything. get-childitem -LiteralPath \rm5_0_code_h\source -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -Recurse I have even tried to exclude what i don't want but it doesn't seem to matter. get-childitem -LiteralPath $path -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -recurse -exclude *alter*.sql,*.dll,*.pdb,*.ncb,*.tlb,*.scc,*.bmp,*.ico i still get them all. what am i doing wrong? dan you have to a...

Custom format with text
I want the number 20 to display as 20 WEEKS I have tried (re Help) 0;;;" WEEKS"@ and various combinations of ; spaces, the lot. Nothing works. Where is the error? Thanks, Hi Pierre, Select the column in which your numbers will be entered. Right-click the mouse on the selection, select Format Cells, then the number tab and select Custom from the list. In the Type box, enter: #,##0 "WEEKS". This will now display 20 WEEKS if the number 20 is entered into any of the cells in the range selected (and any other number of course). Ewan "PierreL" wrote: > I...

Extender Field
I created an extender field type "LIST" and entered about 300 records. When I try to add more records it not saving for somehow. Is there a limitation on the number of records you can add on field type "LIST"? regards, What version of Great Plains are you using? Are you getting any error messages? On Apr 23, 5:04 pm, davidv <dav...@discussions.microsoft.com> wrote: > I created an extender field type "LIST" and entered about 300 records. > When I try to add more records it not saving for somehow. > Is there a limitation on the number of recor...

permissible characters
Newbie question: exactly which characters may be used in names of a) tables, forms, reports,...? b) mdb file names? TQ Becky On Tue, 6 Apr 2010 15:10:01 -0700, Becky wrote: > Newbie question: exactly which characters may be used in names of a) tables, > forms, reports,...? b) mdb file names? > > TQ > Becky Access Help is your friend. Name + Guidelines for naming fields, controls, and objects Guidelines for naming fields, controls, and objects Names of fields, controls, and objects in Microsoft Access: Can be up to 64 characters long. Can in...

Doubt in getting the record count of customised field..
Hi, In MSCRM i want to get the total count of contact records according to a database status field or according to my own customised field. how can do it. fromSDK i got this code snippet but how can i mention the code number that i want to retrieve.(ex) select count(myfield) from contacts where myfield='5'. How can i convert this query as xml query. code which i got from sdk is. String strQuery; strQuery = "<fetch mapping='logical' aggregate='true'>" strQuery += " <entity name='Contact'>" strQuery += " <attribute...

Text-To-Columns Fixed Width
When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed Width criteria and PRE-assigns the width. In most of the cases I work with Excel is wrong 99% of the time. Is there a way to force Excel to NOT pre-assign the width (leave everything blank)? On the first panel of the wizard click Delimited, then on the second panel click space as the delimiter. Hope this helps. Pete On Feb 4, 12:50=A0am, TP <T...@discussions.microsoft.com> wrote: > When using Text-To-Columns, Excel "recognizes" that the data fits the Fix= ed > Width ...

Outlook 2000 *No texte and no picture*
Hi, I have a little problem with outlook 2000. Presently we use windows XP pro with Office 2000. The problem is, when i send a email (we use a template with logo, etc.) at the reception i never see the template .. all picture is in attachtment and time to time i cant see the message. if i do reply the message appear. I already checked the setting .. it's the same as all other computer. it's only 2-3 computer that have this problem. So, for now i cant find why the problem appear. PLZ if someone can help me this will be very appreciated =) Have a good day Fr�d�ric ...

Format for % in a concatenated field
I have a calculated field in a report that includes a percentage. ="30 =" & (30/(Count([surname]))*100) & "%" How do I format the percentage to two decimal places? Thanks, Robin Chapple One way: ="30 =" & TEXT(30/Count([surname]), "0.00%") In article <srhtg0tc4si4vedrdcjgdnmhen7io9vhof@4ax.com>, Robin Chapple <robinsky@westnet.com.au> wrote: > I have a calculated field in a report that includes a percentage. > > ="30 =" & (30/(Count([surname]))*100) & "%" > > How do I forma...

Update Text In Shape
I am using visio automation to create dynamic flow charts. I have a one text box that I drop at the top of the screen, but need to modify the description on the fly as I drop new shapes in my diagram. Can someone tell me how to save the ID number of the first shape I dropped, and then reference that ID number so I that I can keep replacing the text? Thanks! Barry Just name the shape DIM shp1obj as Visio.Shape Set shp1obj = ActivePage.DrawRectangle(0, 0, 1, 1.5) shp1obj.text = "new text" John... Visio MVP Need stencils or ideas? http://visio.mvps.org/3rdparty.htm Need VBA...

text label on scale
I have a combination area/ column chart. I would like to have part of my scale in text form - part numeric. Is there a way to do this? I've looked at appspro.com and no luck so far and the XY labeler doesn't offer the option. I could be missing the obvious though... Hi, Have a look at Jon Peltier's example. http://peltiertech.com/Excel/Charts/ArbitraryAxis.html Cheers Andy Maureen D. wrote: > I have a combination area/ column chart. I would like to have part of my > scale in text form - part numeric. Is there a way to do this? I've looked > at appspro.c...

Find a text
Hi all I am trying to find a text and delate a Row if the text is in the row. But I am not sure how to do it. Can someone help me? Option Explicit Sub FindText() Dim Cell As Range For Each Cell In ActiveSheet.UsedRange If Cell = "Samtals hreyfing:" Then ‚the text Samtals hreyfing sin in in the column E:E ‚If the text Samtals hreyfing: is in the row then I want to delete the Row End If Next Cell End Sub I think you may be looking for this... Sub FindText() Dim Cell As Range For Each Cell In Intersect(ActiveSheet.UsedRange, Columns("E")) ...

Keyboard characters switching in Gmail
I'm running WindowsXP. Quite often recently, while typing messages in Gmail, I must inadvertently hit a keyboard function combination which switches keyboard characters, so that what I'm typing is total gibberish. Trouble is, I don't know what key combination I've inadvertently hit -- maybe it changes keyboard from Qwerty to Dorvak or such. Has anyone else experienced this? Or found a keyboard combo to change it back? So far the only way I can regularise things is to quit Gmail and start all over again. Very frustrating, when typing in the heat of the moment..... Is ...

text box #7
I have created a text box, but when I type in something, and hit enter, I get the error message "REFERENCE NOT VALID". Please help. Thank you We require some more details. Where have you created the textbox, in the sheet or a userform. Are you using the control from the Control Toolbox menu. Are there any macros you are using. Mangesh "Marlis" <Marlis@discussions.microsoft.com> wrote in message news:6C6C6F42-E6CB-4DEB-BB46-C8F923A343C0@microsoft.com... > I have created a text box, but when I type in something, and hit enter, I get > the error message "...

no email address in the from field
gday when sending a message - no email address is inserted in the "from " field. so the emial sits in the out box if i open the message in the outbox and enter an email address in the "from" field and try and re-send - message still will not send. pls advise fix thanks muchly sam What version of Outlook are you using, and if 98 or 2000, what mail support mode? If you're not sure, look at the second line of Help | About Microsoft Outlook -- it should say "Internet Mail Only" or "Corporate/Workgroup". (Outlook 97, 2002, and 2003 don't h...

removing text from a jpeg file
i have a church floor plan in a jpeg file. need to change the room descriptions.what's the best/quickest way to do this and have a working file? Thanks realeast wrote: > i have a church floor plan in a jpeg file. need to change the room > descriptions.what's the best/quickest way to do this and have a > working file? Thanks =================================== When you say "Working File" are you saying it needs to be editable in Publisher? Maybe you could place new text boxes over the current ones. Typing Ctrl+T will toggle a selected text box from transparent to wh...

Exclude a cell
Excel 2002 Any one know how I can sum all the cell values in a column except the value of the last cell to be entered which could be say half-way down the column? Is there a function to exclude a particular cell from "sum" ? I know a lot of ppl are far more advanced than I am but we all start somewhere lol Thanks for your help Dale To exclude cell A16: =SUM(A:A)-A16 Not very elegant, but effcient! Cheers, -- AP "DF2507" <nospamthx@blueyonder.co.uk> a �crit dans le message de news: xB6sg.51172$181.12831@fe3.news.blueyonder.co.uk... > Excel 2002 > &...

text editor
Hello, I am writing Text editor. How can I transfer data (CString) from dialog to main window? I have used MFC Wizard and CEditView class. Well that's kinda of an open ended question. It all depends on who is opening the dialog. The first thing to do is to have a CString member in your dialog that has the text in it. Write a GetText method in your dialog. Then: CTextDialog Dlg; if (Dlg.DoModal() == IDOK) { Text = Dlg.GetText(); } I don't know of too many text editors that need to do this kind of thing. What does the dialog box do? AliR. <petra980@gmail.com> wrote i...

Using text in an IF statement?
Using Excel 2002 This is what I want to do... A1 = a number or text A2 = a number A3 = A1*A2 If A1 is text, what is the correct IF statement in A3 so I don't get #Value as the answer? Thanks, On Jun 12, 9:44=A0pm, The Hun <thehuni...@hotmail.com> wrote: > A1 =A0=3D =A0a number or text > A2 =A0=3D =A0a number > A3 =A0=3D =A0A1*A2 > If A1 is text, what is the correct IF statement > in A3 so I don't get #Value as the answer? Depends on what result you want when A1 is text. One way: =3Dif(istext(A1), "", A1*A2) Another way: =3Dn(A1)*A2 If you ...

Create field from append query based on linked table name
Here's the setup: Two linked tables called 'PHD' and 'XANS' bring in daily data from two CSV files. A union table-query puts the common data in both into the same name fields. This table-query is called 'SOLS_DATA_MERGE'. I then created a new table called 'SOLS_MAIN' and I ran an append query called 'SOLS_DATA_APPEND' to append the data in the table-query, 'SOLS_DATA_MERGE' into the new table, 'SOLS_MAIN'. The main reason for this was so that I could assign my data a primary key. Even though I have achieved my goal of merging the da...

Consolidating Redundant Text
My issue: I've a worksheet with 17 columns of data. Total is roughly 10,000 rows. Of those 10,000 rows, I am dealing with roughly 1000 different items. That being the case, each item has roughly 10 rows, where the first 15 of 17 pieces of data are identical. For strictly aesthetic purposes, I would like to have the redundant text appear only once. Simple illustration - and I apologize ahead of time - I know the formatting won't look right, but I'm hoping you'll get the idea. BEFORE Joe West Div Sales Dept 1 Joe West Div Sales Dept 2 ...

Parsing on an unprintable character
Could anyone please advise on how I can perform a "text to columns" or "parse" using an unprintable character as the delimiter - in this case a paragraph mark. The cell data split has got to happen at the point where data in the originating cell "wraps" (i.e. goes onto a new line within the cell). Thanks in advance. Bob Cameron I've never found a way to do it manually, but it is possible in code: Just put this line in a subroutine, change it to the ranges you wan and execute it. Sheets("Sheet1").Range("A1:A10").TextToColumns _ Desti...