Len Function

Would appreciate some help as I'm a novice Access user.  I have a table with 
employee numbers.  The majority are 7 characters (text) that start with a 
letter and then 6 numbers.  However the remainder are 9 digit - all numbers.  
I'm trying to extract only the 9 digit employee numbers.  I thought the LEN 
function would do it for me, but it's not working.  Thanks
-- 
JR
0
Utf
1/8/2010 2:24:02 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
766 Views

Similar Articles

[PageSpeed] 28

Paste this in the SQL view of the query design panel.

SELECT tblEmployees.EmplNumber, Len([EmplNumber]) AS LengthOf
FROM tblEmployees
WHERE (((Len([EmplNumber]))=9));

Regards

Kevin


"Jackie" <Jackie@discussions.microsoft.com> wrote in message 
news:530BF3B4-8545-4047-9DD1-10D5109C3625@microsoft.com...
> Would appreciate some help as I'm a novice Access user.  I have a table 
> with
> employee numbers.  The majority are 7 characters (text) that start with a
> letter and then 6 numbers.  However the remainder are 9 digit - all 
> numbers.
> I'm trying to extract only the 9 digit employee numbers.  I thought the 
> LEN
> function would do it for me, but it's not working.  Thanks
> -- 
> JR 


0
kc
1/8/2010 4:05:00 AM
Use criteria of
SELECT *
FROM tblEmployees
WHERE [EmployeeNumber] Like "#########"

Another option would be to select all the records where the employee number 
did not start with a letter.
SELECT *
FROM tblEmployees
WHERE [EmployeeNumber] Like "[!A-z]*"

If you are a novice user then you would enter the criteria in the query under 
the employee number fields as:
   Like "#########"

Or you could use
    Like "[!A-z]*"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

kc-mass wrote:
> Paste this in the SQL view of the query design panel.
> 
> SELECT tblEmployees.EmplNumber, Len([EmplNumber]) AS LengthOf
> FROM tblEmployees
> WHERE (((Len([EmplNumber]))=9));
> 
> Regards
> 
> Kevin
> 
> 
> "Jackie" <Jackie@discussions.microsoft.com> wrote in message 
> news:530BF3B4-8545-4047-9DD1-10D5109C3625@microsoft.com...
>> Would appreciate some help as I'm a novice Access user.  I have a table 
>> with
>> employee numbers.  The majority are 7 characters (text) that start with a
>> letter and then 6 numbers.  However the remainder are 9 digit - all 
>> numbers.
>> I'm trying to extract only the 9 digit employee numbers.  I thought the 
>> LEN
>> function would do it for me, but it's not working.  Thanks
>> -- 
>> JR 
> 
> 
0
John
1/8/2010 2:07:23 PM
Reply:

Similar Artilces:

About =now() function
I put =now() function in a cell in my companies invoice. but it does not change according to clock displayed in tast bar. Can it is possible? Thanks for who will help me in advance. calculation set to automatic.? -- Don Guillett SalesAid Software donaldb@281.com "Rao Ratan Singh" <RaoRatanSingh@discussions.microsoft.com> wrote in message news:E4D8A64B-0CF9-4E82-90A8-0B87F8CEAD2A@microsoft.com... > I put =now() function in a cell in my companies invoice. but it does not > change according to clock displayed in tast bar. > > Can it is possible? > > Thanks ...

Date Functions in receipts
Is there some possibilities to perform date function (example: DATEADD() ) in receipt template language? And if there is, how can I express the result? I need to show on transaction document Transaction.Duedate Thanks, Ene Hi, I am not sure what part of the transaction you want to add the DueDate ? or are you trying to use "LayAway" function where expirydate is there ? There are only limited fields and function can be use in changing the receipts. If you get the copy of "Store Operations XML Template Language Definition version 1.1 " , it will somehow guide you fun...

How to export class member function / data in DLL using def file?
I have several questions about dll: 1. How to export variables in DLL using def file? I can exprot functions using def file. But I do not know how to export variables using def file (I can export it using __declspec(dllexport) ). 2. How to export C++ class using def file? I want to export some of the C++ member functions. how to do that? On Aug 8, 11:43 am, kathy <yqin...@yahoo.com> wrote: > I have several questions about dll: > > 1. How to export variables in DLL using def file? > > I can exprot functions using def file. But I do not know how to > export variabl...

Function help???
Ok. I have three columns of text I'm dealing with here. The first column is a the "Athletes Name" column, which represents the athletes name, the second column is "M or F" which represents Male or Female, and the last column is "Team Name", which represents the team each person is play on. I have to give an analysis based on how many all male teams, female teams and co-ed teams we currently have competing based on the information provided. Is there a function on excel that can pull the teams that only have male athletes, female athletes and teams compri...

Returning a colored value in If function
Hello all, =CONCATENATE(IF(C9>1.15*D8,"SELL"," "),IF(AND((D8*1.15>=C9),(D8*1.05<=C9)),"HOLD"," "),IF(AND((D8*1.05>C9),(D8*0.95<=C9)),"APPROACHING BUY"," "),IF(C9<0.95*D8,"BUY"," ")) How can i return a bold red "Sell" for this function? formula cannot do that but conditional formatting will. "ExcelQuestion" wrote: > Hello all, > > =CONCATENATE(IF(C9>1.15*D8,"SELL"," > "),IF(AND((D8*1.15>=C9),(D8*1.05<=C9)),"HOLD",&quo...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

Looking for more trendline regression functions
I would like to fit data to other functions besides the standard ones available in Excel (linear, logarithmic, exponential, power, moving average, and polynomial). I know that data can generally be fit to any function using the SOLVER add-in (by solving for the equation parameters that minimize the error), but I prefer to have some commonly used stock functions in addition to the ones mentioned above which I can simply click on, like we do with the standard functions. Advise please. -- ================================================= Do you like wine? Do you live in South Florida? Visit ...

function result noob question
I inserted a function to merge the text in 2 columns into one column Now I want the new cell to contain the result as text and not as th formula. Ex: column A column B column C Phil Tyler PhilTyler I want the cell in column C to be PhilTyler not the formula column plus column B remove the function keep thee result in the cel -- Message posted from http://www.ExcelForum.com Hi Phil, With your formula cells selected, try: Edit Copy | Edit Paste Special | Check Values | OK | Esc --- Regards Norman "philtyler >" <<philtyl...

Outlook 2002 Change Password function
I want users to be able to change password from the Outlook Login Page, but it does noet seem to work. Outlook 2002 Sp1 and Windows Server 2003 (AD) and Exchange 2003 SP1 Important: Outlook client is not member of the domain... There are not many Microsoft Articles about this. Anyone? -- Regards, Menko den Ouden Netherlands Menko den Ouden schreef in deze nieuwsgroep op 23-6-2005 15:28: > I want users to be able to change password from the Outlook Login Page, but > it does noet seem to work. > > Outlook 2002 Sp1 and Windows Server 2003 (AD) and Exchange 2003 SP1 > ...

modeless CPrpertysheet: calling parent window function
i have a modeless CPropertySheet, being invoked from a dialog based application and the calling dialog is made parent of the sheet. when i try to call some function of the calling dialog class through some event handler in the sheet (by using GetParent()), a runtime error occurs. Why? Are you calling the Sheet's GetParent() or the Pages GetParent(). From the Page you will have to do ASSERT(GetParent()); ASSERT(GetParent()->GetParent()); GetParent()->GetParent()->xxxx(....); AliR. "pravin.chaudhary" <pravin.chaudhary@gmail.com> wrote in message news:117940228...

USB function driver Data toggle Issue
Hi, We are developing USB function driver with Active Sync support for our USB device. The Control Transfer is working fine. But in Bulk IN/Out transfer, I have faced some problems. My observations are, 1. After control transfer, the standard host issues Bulk IN (6 bytes) , Bulk OUT (12bytes) , Bulk IN (37bytes). Above all transfers contains "DATA0" data toggle and have proper ACK. 2. After 37bytes Bulk IN, host issues the same Bulk IN (37 bytes) for more than 7 times with interval of 3seconds and after that the host does not send anything to device. 3. I have one usb...

Lookup function help please
I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help Remove the quotations marks around the 1 in AU17's formula: =IF(VC1="U",1, IF(VC1="...

Web access to money functions
Hi, I have my Money 2004 installed on my "always on" pc @ home. Does Money have any "web serving" functionality which enables me to access its data base through the web ? Do you know if it is planned ? Thanks, Gur Yes. Sync with the Web enables you to access a copy of some of the data that it serves up on the web and then resynchronize any updates back into the locally stored file. Start by reading about "Synchronize with MSN Money" in online help. M05 expands on this and calls this file sharing but it most certainly isn't that. You could also, as...

how to use the function sorting in excel
can you help me on how to use the function sorting in excel. thank you "excel2003" wrote: > can you help me on how to use the function sorting in excel For a good start, suggest you try Excel's help, Click Help>Microsoft Excel Help, search for: sorting The fundamentals are all there. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Data>Sort then accomadate your data accordinly. Or Data>Filter>Autofilter in order to use dropdown list to sort. -- Can''''t hear the Rain ? Then listen to it. "excel2003" wrote: &...

ROUND function syntax question (hopefully simple)
I want to round the results of the following formula to a single decimal place. Can anyone tell me the correct syntax to use the ROUND function? Putting all the commas and parentheses in the right place has always been my downfall where long, complicated Excel functions are concerned! The formula is: =IF(J7>0,D7/J7*100,"") One way: =IF(J7>0,ROUND(D7/J7*100,1),"") In article <1193928344.343925.50970@k79g2000hse.googlegroups.com>, Nick Xylas <nickxylas@wmconnect.com> wrote: > I want to round the results of the following formula to a single >...

VBA function changing name
OK, here's a strange one - well strange to me anyway. I've declared a function in a module using mixed case: Function TMDE_Category (FormName As Form) The function is called by the VBA code behind two different forms. I noticed the other day that it appeared in the module as Function tmde_category(FormName As Form) Obviously, this isn't preventing the database application from running, but I can't figure out why it changed. I changed it back to the mixed case declaration, saved the module, exited the app, reopened it and looked. The function had changed back t...

Outlook "Find" function
Can someone please tell me how I can get the Outlook find function to work like the find function in the Palm desktop software? What I need to be able to do is have the "find" function search all contacts, appointmnets, notes, tasks, inbox, etc (ie everything in outlook) by default everytime. It also needs to look at all of the text for all of the above listed items during the search. Thanks, Brad Have you tried using the Advanced Find feature? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgro...

functions help
can someone help me? x 0 x 0 x 1 x 1 y 0 y 1 y 0 z 1 z 1 z 1 z 1 i want x = 2, y = 1 e z =4 thx to all to just count, use COUNTIF. To sum column B, use SUMIF. Have a look in HELP index for both -- Don Guillett SalesAid Software donaldb@281.com "orda2000" <.:NOSPAM:.orda2000@libero.it> wrote in message news:2kdc77F113a0U1@uni-berlin.de... > can someone help me? > > x 0 > x 0 > x 1 > x 1 > y 0 > y 1 > y 0 > z 1 > z 1 > z 1 > z 1 > > i want x = 2, y = 1 e z =4 &g...

How to combine a vlookup with a sumif function!!!
I've been trying to combine a vlookup function with a sumif function for weeks now, however, without success. This is my problem. Instead of looking up a single value, i.e. =vlookup(A1, rang, 4, false), I like to do this for an entire range and suming up that range. Something like this: 1 2 3 4 5 6 Table 1 10� 35� 50� 23� 60� 50� Jack 10� 35� 50� 23� 60� 50� Brian 10� 35� 50� 23� 60� 50� Juergen 10� 35� 50� 23� 60� 50� Feff 10� 35� 50� 23� 60� 50� So, I like to look-up the sum of these...

SEND TO function does not work unless Outlook is open
I have several computers that cannot use the SEND TO (AS FILE ATTACHMENT) function. For example, if they are in Excel 2003 and they do NOT have Outlook open, they can click on SEND TO and they get the correct message window on the screen where they can type in the address of the person to send the message to. It will even connect to their Exchange profile and allow them to verify the recipient's name. When they click send though they get the following two errors: "The action could not be completed." Click OK and get this: "General Mail Failure. Quit Microsoft Offic...

Function based on criteria
Hello Again, I've been using the lookup and countif functions alot, my favorite functions! Anyways i would like to advance these fuctions a little. A B C D E F 1 09/01/03 08/13/03 1 A 200 300 2 08/17/03 1 A 300 100 3 09/05/03 1 A 100 300 4 09/08/03 2 B 200 500 5 09/14/03 1 A 200 300 6 0...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

MFC/SDI How to access view's function from mainframe
Hi, This is my first time doing SDI applications so this doc/view/ mainframe thing is pretty new to me. Now i'm doing a program where i access to sql database thru ODBC and put data into recordset and everything works fine. However now i need to close that recordset when user quits the program. What i understood that the mainframes OnClose()-function is the one that handles quitting from File/Quit and the X-button, so i need to access to the Views recordset from the mainframes OnClose()-function. And there is other cleanup jobs i need to do when quitting so accessing to View would be good...

*major* functional issue with MOSS/WSS?
We are looking to move a big site over to using MOSS in a big as an alternative to shared network drives (ugh). One of the biggest issues is sending docs as attachments via email for others to edit. We'd like to get everyone to use the 'send a link' functionality from thew Sharepoint DL BUT when the user receives the email and clinks on the embedded link the doc opens read only. They then cant edit and save their changes. Seems a big an counter intuitive functional flaw. Are we missing something here? Is there any easy (for the end user) way to send those links to a doc ...

if and or functions
If A17 = horiz curve and C17 = 90 then answer should return what is in C10, but if A17 = horiz curve and C17 = 45 then answer should return = C11 how do I write a formula for this? Darryl =IF(AND(A17="horiz curve",C17=90),C10,IF(AND(A17="horiz curve",C17=45),C11,"")) -- Gary''s Student - gsnu201001 "Darryl" wrote: > If A17 = horiz curve and C17 = 90 then answer should return what is in C10, > but if A17 = horiz curve and C17 = 45 then answer should return = C11 > > how do I write a formula for this? > ...