Count the number of times a character appears in a field

This is a multi-part message in MIME format.

------=_NextPart_000_001B_01CAD018.FC830D40
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

How can I count the number of times the @ symbols appears in a text
field.

------=_NextPart_000_001B_01CAD018.FC830D40
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Dus-ascii" =
http-equiv=3DContent-Type>
<META name=3DGENERATOR content=3D"MSHTML 8.00.7600.16490"></HEAD>
<BODY>
<DIV><SPAN class=3D084285321-30032010><FONT size=3D2 =
face=3DArial>How&nbsp;can I count=20
the number of times the @ symbols appears in a text=20
field.</FONT></SPAN></DIV></BODY></HTML>

------=_NextPart_000_001B_01CAD018.FC830D40--

0
Jay
3/30/2010 9:55:05 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
3778 Views

Similar Articles

[PageSpeed] 16

Jay wrote:

>How can I count the number of times the @ symbols appears in a text
>field.


Len(field) = Len(Replace(field, "@", ""))

-- 
Marsh
MVP [MS Access]
0
Marshall
3/30/2010 10:40:08 PM
Marsh

Won't that provide the length of the field without any "@"s?

Did you mean to use a minus sign between the first Len(field) and the second 
part?

Regards

Jeff Boyce


"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:lcv4r5pmufo4g1gdjcp5ts1fgf2q02qob6@4ax.com...
> Jay wrote:
>
>>How can I count the number of times the @ symbols appears in a text
>>field.
>
>
> Len(field) = Len(Replace(field, "@", ""))
>
> -- 
> Marsh
> MVP [MS Access] 


0
Jeff
3/30/2010 11:17:52 PM
 Thanks. Worked with the minus sign.

-----Original Message-----
From: Jeff Boyce [mailto:nonsense@nonsense.com] 
Posted At: Tuesday, March 30, 2010 4:18 PM
Posted To: microsoft.public.access.queries
Conversation: Count the number of times a character appears in a field
Subject: Re: Count the number of times a character appears in a field


Marsh

Won't that provide the length of the field without any "@"s?

Did you mean to use a minus sign between the first Len(field) and the
second part?

Regards

Jeff Boyce


"Marshall Barton" <marshbarton@wowway.com> wrote in message
news:lcv4r5pmufo4g1gdjcp5ts1fgf2q02qob6@4ax.com...
> Jay wrote:
>
>>How can I count the number of times the @ symbols appears in a text 
>>field.
>
>
> Len(field) = Len(Replace(field, "@", ""))
>
> --
> Marsh
> MVP [MS Access]


0
Jay
3/31/2010 1:52:10 AM
Sheesh,  My bleary eyes saw that as a minus.  Why did they
put two such similar keys next to each other?  Between my
fumble fingered typing and double vision it's really tough
to proof that kind of typo.

Regardless, it's a good thing you caught it before anyone
got frustrated.
-- 
Marsh
MVP [MS Access]


Jeff Boyce wrote:
>Won't that provide the length of the field without any "@"s?
>
>Did you mean to use a minus sign between the first Len(field) and the second 
>part?
>
>
>"Marshall Barton" wrote
>> Jay wrote:
>>
>>>How can I count the number of times the @ symbols appears in a text
>>>field.
>>
>>
>> Len(field) = Len(Replace(field, "@", ""))
>>
0
Marshall
3/31/2010 4:11:37 AM
de nada ... you can return the favor when my big fat fingers next fumble...

Jeff

"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:3ei5r5ljtbbrbomiigdkdg3am2t60p7978@4ax.com...
> Sheesh,  My bleary eyes saw that as a minus.  Why did they
> put two such similar keys next to each other?  Between my
> fumble fingered typing and double vision it's really tough
> to proof that kind of typo.
>
> Regardless, it's a good thing you caught it before anyone
> got frustrated.
> -- 
> Marsh
> MVP [MS Access]
>
>
> Jeff Boyce wrote:
>>Won't that provide the length of the field without any "@"s?
>>
>>Did you mean to use a minus sign between the first Len(field) and the 
>>second
>>part?
>>
>>
>>"Marshall Barton" wrote
>>> Jay wrote:
>>>
>>>>How can I count the number of times the @ symbols appears in a text
>>>>field.
>>>
>>>
>>> Len(field) = Len(Replace(field, "@", ""))
>>> 


0
Jeff
3/31/2010 4:17:52 PM
.... or my fat, slow brain ... <g>

J

"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:3ei5r5ljtbbrbomiigdkdg3am2t60p7978@4ax.com...
> Sheesh,  My bleary eyes saw that as a minus.  Why did they
> put two such similar keys next to each other?  Between my
> fumble fingered typing and double vision it's really tough
> to proof that kind of typo.
>
> Regardless, it's a good thing you caught it before anyone
> got frustrated.
> -- 
> Marsh
> MVP [MS Access]
>
>
> Jeff Boyce wrote:
>>Won't that provide the length of the field without any "@"s?
>>
>>Did you mean to use a minus sign between the first Len(field) and the 
>>second
>>part?
>>
>>
>>"Marshall Barton" wrote
>>> Jay wrote:
>>>
>>>>How can I count the number of times the @ symbols appears in a text
>>>>field.
>>>
>>>
>>> Len(field) = Len(Replace(field, "@", ""))
>>> 


0
Jeff
3/31/2010 4:18:14 PM
Reply:

Similar Artilces:

counting data from a series
I have from a suvey in a 3 column table, where COL1=survey number, COL2=question number, and COL3=response, as such: COL1 COL2 COL3 a Q1 2 b Q1 3 c Q1 1 a Q2 3 b Q2 3 c Q2 1 a Q3 2 b Q3 3 c Q3 1 etc.. How can I calculate all the "3" responses for all Q2s? For example, based on the above sample table, I would expect the formula to return "2". =SUMPRODUCT(--(B1:B9="Q2"),--(C1:C9=3)) Hope this helps! In article ...

User distribution lists do not appear in GAL
Hello All, Today I had a problem with my Exchange 2003 server for the distribution lists and their GAL (Global Address List) listings. I solved the *my* problem and I would like to share it as a reference..(The problem might be different than yours but hope it may help somebody) ---- The Problem --- The distribution groups that are created under the "Active directory Users and Computers" Panel do not appear in GAL , even after the RUS (Recipent Update Service) is updated over the accounts. Additional info A ) The groups were apperaring under the All Global Address List > Defa...

Calculating time #3
I know this question has been asked before, but the answers that I have found do not seem to work for me for some reason. I am probably doing something wrong. What I need to do is calculate hours worked for the day. I have set up a spreadsheet to include the following: C3 is the time in D3 is the time out E3 is .5 hours (this is lunch) F3 is the total number hours worked for the day. The formula I have put in this cell is: =HOUR(D3-C3)-E3 [this is exactly how the formula is typed in] Now, if someone put their time in as 7:30 a.m. and their time out at 4:00 p.m. and they take the .5...

Error can't empty clipboard appears when trying to copy.
When I try to copy and paste a table from Excel into Word I am getting the error "can't empty clipboard" appears after hitting the copy function...This has happened a couple of times but only after extended use of the program. When I pull up the clipboard there is nothing on it at all...I opened the clipboard on every program I have running to see if its full somewhere else but it isn't. ...

How to retrieve number of files
What's the correct way to retrieve the number of jpg files in a directory? Do I have to loop through each file, in order to count them up? >What's the correct way to retrieve the number of jpg files in a directory? >Do I have to loop through each file, in order to count them up? Yes - use FindFirstFile (or CFileFind) to filter on *.jpg and loop/count them. Dave Yes. use ::FindFirstFile/::FindNextFile/::FindClose or use the CFileFind class of MFC. joe On Sun, 4 Nov 2007 01:59:12 -0800, Charles Tam <CharlesTam@discussions.microsoft.com> wrote: >What's the ...

How do I find which forms have a particular field which is blank?
I have created a database using Access 2007 but it really confuses me. I used to have Access 2000 and it was really easy to create a query which found all the forms which had the "Date Completed" field blank, so i could find which records were still ongoing. It even made a little list for me in a seperate sheet. I have no idea how to do this in Access 2007 and the help forums don't make much sense to me. Can anyone help? many thanks, Bunximo. Open up the database. Go to the Create tab. Next go to Other box. Select Query Design. Pick out the table that has...

CString to BSTR question dealing with nul characters
I'm creating a C++ DLL for a VB application. I'm using VC7 for creating the DLL application. I'm trying to return a string from this function, here is a simple example extern "C" DLL_API int __stdcall GetString(BSTR* myStr) { int rtnCode = PASS; // PASS is defined as 1 somewhere else CString temp = "Hello"; *str = temp.AllocSysString(); return rtnCode; } >From my test VB 6 app I declare Private Declare Function GetString GetStr "My.dll" (ByRef myStr As String,) As Integer Dim tempStr as String Dim rtnCode as Integer rtnCode = G...

Time calculations for Scheduled Time vs. Actual Time Worked
I am trying to make a simple schedule worksheet for a manger to use to calculate the time he schedules an employee to clock in and out and what it will cost him in regards to his allocated budget and then to be able to compare it after the job is completed. BUT time calculations have thrown me for loop and I am stuck for hours on trying to calculate time fields. PLEASE DEAR GOD can someone help me. Below is a simple example of my worksheet. A2 (Time IN) = 1:00 PM A3 (Time OUT) = 5:00 PM A4 (Time Worked) = 4:00 (h:mm format) Formula A3...

Time line in excel graph
I need a vertical line in my graph to show that the graph information after the line is the future predicted outcomes. I've tried countless different options and cant seem to find how to do this! Hi, There are various ways of addressing this problem. See the following for examples of adding lines to charts. http://peltiertech.com/Excel/Charts/AddLine.html http://tushar-mehta.com/excel/charts/straight_lines/index.html http://www.andypope.info/charts/averageline.htm Cheers Andy Wilkesy50 wrote: > I need a vertical line in my graph to show that the graph information after > th...

Leading Zero as value not appearance
When i use formatting cell custom with 0000 i manage to add leading zero but the problem is the leading zero only as appearance. Is it possible to make it as value in the cell Format the cell as Text. Select the Range>>Do Right Click>>Format Cells>>Number>>Category>>Text and give ok. Now input your leading zero value in the text formatted range. But the Values are not treated as real numbers and it is considered as Text Values. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "zy...

Pivot Table row fields
Is there any way to uncheck all of the boxes at once in my row field drop down box. Example: I have dragged years into the row field. A drop down arrow shows but everything is checked. I do not want to have to uncheck these one at a time. Thank you for your help. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ There's some sample code here that shows/hides items in a pivot table: http://www.contextures.com/xlPivot03.html#Show97 Clys wrote: > Is there ...

Help with counting user entered text values
Hi, Version: Excel 2000 Situation: In Column G, I will have testers entering in their initals. I would like to display the tester's initals in column H of another worksheet with the number of times that set of intals appeared in column G on column I of the other worksheet. The catch is that I do not have a list of the testers initals ahead of time, so I'll need to pull them from column H. Thanks for any and all help in advance, David Hi David, H10: DAL i10: =COUNTIF(G:G,H10) or if you don't want to see 0 if H10 is empty I10: =IF(H10="","",COU...

How to use/extract only a portion of a field
Help, oh please help using Excel 2007. If it is possible, I need to know how to use only a portion of a cell. Please keep your answer simple if possible. I'm pretty much a novice as I read answers of the forum:) My project is to create computer login cards for elementary grade students. For example, I've been given a file with Column 1 is a six digit student number (987654) Column 2 is the student's first name (Jane) Column 3 is the student's last name (Doe) What I need is to create is a combination of that information so it will read JD987654. Is there a type of formul...

CJK character
I wanted to write an MFC app that deals with Unicode CJK character. But the user may input character with different coding (GB, BIG-5, Shift-JIS...), is there any way I can detect the coding and convert it to Unicode? Thanks. > I wanted to write an MFC app that deals with Unicode CJK character. > But the user may input character with different coding (GB, BIG-5, > Shift-JIS...), is there any way I can detect the coding and convert it to > Unicode? Normally the user can input characters in two ways only: - Unicode, if your application is unicode - ANSI code page, if the applica...

insert number in column #2
I have a column with threedigits for telephone extensions. I want to insert a digit before the three throughout the column as the extension has been increased to 4 digits. Any easy way.../ Thanks. AFD One way: In a blank column (say column H), enter H1: =1 & A1 or, if you want them as numbers rather than text: H1: =1000+A1 copy down as far as necessary. Select the column and copy it. Select the original column and choose Edit/Paste Special/Values. In article <Om6vrJpEIHA.3548@TK2MSFTNGP06.phx.gbl>, "afdmello" <afdmello@hotmail.com> wrote: &...

Time Format to Text Output
Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 1...

How do I change the size of a text form field box in Word?
I f more text is typed in the text form field box then its standard size the other form fill boxes to the right all move over to accommodate the additional text. Is there a way to increase the text form field box? Place your form fields inside (border-less) table cells whose dimensions are fixed. -- Stefan Blom Microsoft Word MVP "Blink" <Blink@discussions.microsoft.com> wrote in message news:383D7E9E-5A0E-4F3A-9586-CAC98B0CD776@microsoft.com... >I f more text is typed in the text form field box then its standard size >the > other form fill...

Sequential numbers in a formula?
I have a few formulas (OFFSET & MATCH) which are the same (with relative references chanfing accordingly) and occupy cells B2:B500. In each formula one of the function arguments refers to a single cell in the adjacent column i.e A1:A500 which contains the numbers 1 to 500 (and the column is hidden as the numbers are needed only in the formulas and not to be displayed). The numbers are used to give the relative row number in the ranges used by OFFSET. So, the first formula uses 1 (via B2) and the second formula uses 2 (via B3) and so on. My question is: Can I somehow refer to a ...

Adding a time period selection for cash flow forcast
I'm using Money 2004. When I go to cash flow forcast, there are set time periods to choose from, such as 30 days, 2 paychecks, ect. I would like to add a 6 month selection without having to choose custom and putting in the dates. Is there any way to do this? Thanks, Mike Sorry no. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes http://www.microsoft.com/uk/support/money/feedback I do not respon...

Project Time & Expense Entry
We are looking at switching our third party PA to GP PA. We need the use of Business Portal Time and Expense entry. The product looks good however we need the expense entry to write to AP instead of Payroll (using outside service). Anyone experienced this? On my GP 8 system, employee expenses feed AP. In fact, when you go to the employee card and select the option that says "Files employee expense" a vendor record is created with the vendor id equal to the employee id. -- Charles Allen, MVP "Lisa" wrote: > We are looking at switching our third party PA to...

Update field within a text box
I'm using Word 2007. I created a template with a REF field in a text box that refers back to a bookmark created with an ASK field earlier in the document. When I create a new document using this template, all of the ASK and FILLIN prompts come up, but the REF field in the text box does not update automatically. If I click in the text box and hit F9, it will update, but I would like it done automatically. Is this possible? TIA David No, even Ref fields that aren't in text boxes don't update automatically. You can go to Print Preview and back to update all the ...

Compulsory field
Hi, I have a database with a query with patient referral details plus some calculated fields. We need to monitor whether the patients are referred on fast enough. There are the following fields (in addition to others): date referral received, date referral passed on, today's date, date when referral has breached and patient not suitable for surgery. I have a form that collects and displays all this information. There is a combo box with reason for breach. I need to make it compulsory for the data entry clerk to fill in that box if the following criteria are true: 1. Breach date is...

Calculated Fields in SQL2005 from Access 2000?
Hi, In our old Access DB we had query with 2 calculated fields that displayed % ie; NewColumnA: [ColumnB]*[ColumnC]/100. This works well in Access but after using the upsizing wizard its clear SQL doesnt like the fact that NewColumnA doesnt already exist. Is there a way I can recreate this query in SQL? When I run the query below: SELECT TblGenesisCommission.[_CommMonth], TblGenesisCommission. [_Firm_Name], TblGenesisCommission.[_ClientName], TblGenesisCommission. [_BranchNo], TblGenesisCommission.[_Reference], TblGenesisCommission. [_StartDate], TblGenesisCommission.[_CommType], T...

Variable in text field
I have a spreadsheet where I place some information at the top in a cell like 'See Row 16 for more detail'. When I insert a row before row 16 it is now farther down in the spreadsheet and I need to correct the text information. Is there a way to reference the row such that it will update the text to reference the correct row automatically? Thanks. Michael ="See row " & ROW(A16) & " for more detail" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Michael&qu...

Null Field to Interupt Closing
I am using this code to check for blank fields in BeforeUpdate: If NZ(Trim(Me!Date),vbnullstring) = vbnullstring then Msgbox "PLEASE ENTER DATE",,"Missing date" Me!Date.setfocus Cancel = True Exit Sub End if It almost works, except the form goes ahead and closes anyway. The user clicks the close button and the message box pops up if the field is empty, they click OK and instead of keeping the form open and then setting focus on the empty field, it closes the form. How can I get it to interupt the close and keep the form open? Thank you in advance for any...