Selecting Letters
Hi There is two parts to me questionPart 1 I need to populate a field with certain letters obtained from a clients surname and first name. Letters required are the 2nd, 3rd and 5th letter of the surname and then the 2nd, 3rd letter of their first name. And if there is no letter the a number "2" needs to be used instead.examples are as follows:Name Field RequiredJo Smith miho2Andrew Christopher hrsndJohn Rex ex2ohPart 2As there are already clients in the database i need to update all the client records to establish this Field. Thanks for your help
|
0 |
|
Utf
3/19/2007 2:21:03 AM
access
16762 articles. 
3 followers.
9 Replies
761 Views
On Sun, 18 Mar 2007 19:21:03 -0700, Andrew C<AndrewC@discussions.microsoft.com> wrote:>Hi >>There is two parts to me question>>Part 1 >I need to populate a field with certain letters obtained from a clients >surname and first name. Letters required are the 2nd, 3rd and 5th letter of >the surname and then the 2nd, 3rd letter of their first name. And if there is >no letter the a number "2" needs to be used instead.>>examples are as follows:>>Name Field Required>Jo Smith miho2>Andrew Christopher hrsnd>John Rex ex2oh>>Part 2>As there are already clients in the database i need to update all the client >records to establish this Field. >>Thanks for your helpIif(Len([Surname]) >= 2, Mid([Surname], 2, 1), "2")& IIF(Len([Surname]) >= 3, Mid([Surname], 3, 1), "2")& IIF(Len([Surname]) >= 5, Mid([Surname], 5, 1), "2")& IIF(Len([Firstname]) >= 2, Mid([Firstname], 2, 1), "2")& IIF(Len([Firstname]) >= 3, Mid([Firstname], 3, 1), "2")I hope you're not assuming that this mess of a composite, redundant, derivedvalue will be unique. "John Johnson", "Johanna Johnston", "Johann Johns" areall OHSOH but they're different people... John W. Vinson [MVP]
|
|
0 |
|
John
3/19/2007 2:45:47 AM
Hi John This may be a stupid question on my behalf but where abouts do i put the code you gave me. As i need to store the code in a field called "HACC Linkage"I understand this wont be a unique code. Unforunate its sometime our Health Body has devised.will this also update all records or can i put it in as an event procedure that when the client form is opened it updates that field then.thanks for you help"John W. Vinson" wrote:> On Sun, 18 Mar 2007 19:21:03 -0700, Andrew C> <AndrewC@discussions.microsoft.com> wrote:> > >Hi > >> >There is two parts to me question> >> >Part 1 > >I need to populate a field with certain letters obtained from a clients > >surname and first name. Letters required are the 2nd, 3rd and 5th letter of > >the surname and then the 2nd, 3rd letter of their first name. And if there is > >no letter the a number "2" needs to be used instead.> >> >examples are as follows:> >> >Name Field Required> >Jo Smith miho2> >Andrew Christopher hrsnd> >John Rex ex2oh> >> >Part 2> >As there are already clients in the database i need to update all the client > >records to establish this Field. > >> >Thanks for your help> > Iif(Len([Surname]) >= 2, Mid([Surname], 2, 1), "2")> & > IIF(Len([Surname]) >= 3, Mid([Surname], 3, 1), "2")> & > IIF(Len([Surname]) >= 5, Mid([Surname], 5, 1), "2")> & > IIF(Len([Firstname]) >= 2, Mid([Firstname], 2, 1), "2")> & > IIF(Len([Firstname]) >= 3, Mid([Firstname], 3, 1), "2")> > I hope you're not assuming that this mess of a composite, redundant, derived> value will be unique. "John Johnson", "Johanna Johnston", "Johann Johns" are> all OHSOH but they're different people...> > John W. Vinson [MVP]>
|
|
0 |
|
Utf
3/19/2007 3:34:11 AM
On Sun, 18 Mar 2007 20:34:11 -0700, Andrew C<AndrewC@discussions.microsoft.com> wrote:>Hi John >>This may be a stupid question on my behalf but where abouts do i put the >code you gave me. As i need to store the code in a field called "HACC >Linkage"No. You don't need to store it ANYWHERE. If the person's name changes (say awoman gets married, or anyone has a legal name change for some other reason),should the HACC code stay the same? or should it reflect the person's actualname? If the latter, simply calculate it on demand, by putting the expressionin a vacant Field cell, or in the Control Source property of a Form or Reporttextbox.>I understand this wont be a unique code. Unforunate its sometime our Health >Body has devised.Mental health not being considered it would seem... <g>>will this also update all records or can i put it in as an event procedure >that when the client form is opened it updates that field then.If you calculate it on demand as suggested, you don't NEED to do so. If youmust store it (redundantly) in a table, use the Form's BeforeUpdate event to"push" it into the bound textbox: set Me![HACC Linkage] to the expressionwithin the before-update code. John W. Vinson [MVP]
|
|
0 |
|
John
3/19/2007 4:59:14 AM
Thanks JohnI get the required letters which is great. But im having a problem pushing it to the "HACC Linkage" field. I have never had to do that before. Below is the code i have for the forms before update procedure, have i got it rightPrivate Sub Form_BeforeUpdate(Cancel As Integer)Me.[HACC Linkage] = Me.[HACCcode]End Sub"John W. Vinson" wrote:> On Sun, 18 Mar 2007 20:34:11 -0700, Andrew C> <AndrewC@discussions.microsoft.com> wrote:> > >Hi John > >> >This may be a stupid question on my behalf but where abouts do i put the > >code you gave me. As i need to store the code in a field called "HACC > >Linkage"> > No. You don't need to store it ANYWHERE. If the person's name changes (say a> woman gets married, or anyone has a legal name change for some other reason),> should the HACC code stay the same? or should it reflect the person's actual> name? If the latter, simply calculate it on demand, by putting the expression> in a vacant Field cell, or in the Control Source property of a Form or Report> textbox.> > >I understand this wont be a unique code. Unforunate its sometime our Health > >Body has devised.> > Mental health not being considered it would seem... <g>> > >will this also update all records or can i put it in as an event procedure > >that when the client form is opened it updates that field then.> > If you calculate it on demand as suggested, you don't NEED to do so. If you> must store it (redundantly) in a table, use the Form's BeforeUpdate event to> "push" it into the bound textbox: set Me![HACC Linkage] to the expression> within the before-update code.> > John W. Vinson [MVP]>
|
|
0 |
|
Utf
3/19/2007 6:48:05 AM
On Sun, 18 Mar 2007 23:48:05 -0700, Andrew C<AndrewC@discussions.microsoft.com> wrote:>Thanks John>>I get the required letters which is great. But im having a problem pushing >it to the "HACC Linkage" field. I have never had to do that before. >>Below is the code i have for the forms before update procedure, have i got >it right>>Private Sub Form_BeforeUpdate(Cancel As Integer)>Me.[HACC Linkage] = Me.[HACCcode]>End SubASSUMING (and knowing the derivation of that word) that you have an unboundtextbox named HACCcode with my expression as its Control Source; that it isworking correctly, using some other controls that have the surname and firstname; and that there is a bound textbox named [HACC Linkage] on the form,bound to the HACC Linkage table field - this should work.If it isn't working, what does happen when you enter a name, and then move offthe record or close the form? Do you get an error message? Does the field notupdate? John W. Vinson [MVP]
|
|
0 |
|
John
3/19/2007 7:22:04 PM
You have assumed correctly. There is no error message the field is left blank (not updating) when closing the form or saving the record."John W. Vinson" wrote:> On Sun, 18 Mar 2007 23:48:05 -0700, Andrew C> <AndrewC@discussions.microsoft.com> wrote:> > >Thanks John> >> >I get the required letters which is great. But im having a problem pushing > >it to the "HACC Linkage" field. I have never had to do that before. > >> >Below is the code i have for the forms before update procedure, have i got > >it right> >> >Private Sub Form_BeforeUpdate(Cancel As Integer)> >Me.[HACC Linkage] = Me.[HACCcode]> >End Sub> > ASSUMING (and knowing the derivation of that word) that you have an unbound> textbox named HACCcode with my expression as its Control Source; that it is> working correctly, using some other controls that have the surname and first> name; and that there is a bound textbox named [HACC Linkage] on the form,> bound to the HACC Linkage table field - this should work.> > If it isn't working, what does happen when you enter a name, and then move off> the record or close the form? Do you get an error message? Does the field not> update?> > John W. Vinson [MVP]>
|
|
0 |
|
Utf
3/19/2007 11:55:25 PM
On Mon, 19 Mar 2007 16:55:25 -0700, Andrew C<AndrewC@discussions.microsoft.com> wrote:>You have assumed correctly. >>There is no error message the field is left blank (not updating) when >closing the form or saving the record.Please post the names of the relevant controls, their Recordsources, and yourBeforeUpdate code. John W. Vinson [MVP]
|
|
0 |
|
John
3/20/2007 12:24:11 AM
Here is the Controls and there sourceForm Record source = ClientSurname Control = SurnameFirst Name Control = FirstHACC Linkage Control = HACC LinkageHACCcode Control = =IIf(Len([Surname])>=2,Mid([Surname],2,1),"2") & IIf(Len([Surname])>=3,Mid([Surname],3,1),"2") & IIf(Len([Surname])>=5,Mid([Surname],5,1),"2") & IIf(Len([First])>=2,Mid([First],2,1),"2") & IIf(Len([First])>=3,Mid([First],3,1),"2") Code you gave me and works correctHere is the code for the Before update event (This is the only VB code on this form)Option Compare DatabasePrivate Sub Form_BeforeUpdate(Cancel As Integer)Me.HACC_Linkage = Me.HACCcodeEnd SubIm not sure if this makes a difference but i have the HACC Linkage field properties enabled = nolocked = yesthis is so people can view it but not be able to edit it.The HACCcode field is a not visable on the formThanks for your help much appreciatedAndrew"John W. Vinson" wrote:> On Mon, 19 Mar 2007 16:55:25 -0700, Andrew C> <AndrewC@discussions.microsoft.com> wrote:> > >You have assumed correctly. > >> >There is no error message the field is left blank (not updating) when > >closing the form or saving the record.> > Please post the names of the relevant controls, their Recordsources, and your> BeforeUpdate code.> > John W. Vinson [MVP]>
|
|
0 |
|
Utf
3/20/2007 12:39:00 AM
On Mon, 19 Mar 2007 17:39:00 -0700, Andrew C<AndrewC@discussions.microsoft.com> wrote:>Option Compare Database>Private Sub Form_BeforeUpdate(Cancel As Integer)>Me.HACC_Linkage = Me.HACCcode>End SubThat's the problem, I suspect!The name of the control is HACC Linkage (with a space). Your code isattempting to update a nonexistant control HACC_Linkage (with an underscore).Either rename the control or useMe.[HACC Linkage] = Me.HACCcode John W. Vinson [MVP]
|
|
0 |
|
John
3/20/2007 3:53:53 AM
Similar Artilces:
How can I change which cell is selected next, other than Down, Le.
I know there should be a simple answer to this question, but I can't find it on the "Help" menu. Hi Bethany tools / options / edit "move selection after enter" -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Bethany" <Bethany@discussions.microsoft.com> wrote in message news:50CD805D-F385-4457-9363-5C3A3EF7825D@microsoft.com... >I know there should be a simple answer to this question, but I can't find >it > on the "Help" menu. tools->options->edit and select one of...
"one or more of the selected senders could not be added to your blocked senders list"
Hey all. I get this when ever I try to killfile anyone at all, regardless of who they are. I'm on OE6 in XP Home and this has only just started happening. My KF only has a few spammers in it so I don't think it's full. Does anyone know what's going on? Thanks "Ayatollah of rock 'n' roller" <thisisf@lse.co.ck> wrote in message news:gGxDn.40$Bm7.30@newsfe29.ams2... > Hey all. I get this when ever I try to killfile anyone at all, regardless of > who they are. I'm on OE6 in XP Home and this has only just started > happen...
Select Records from One Table that Aren't in Another Table
I have two tables: one is a ParksUIC table containing a list of drywells. The other is a Lookup table containing all known potable discharge wells. what i want to do is create a query that will only select records from the ParksUIC table that ARE NOT in the Lookup table. This is easy to do for when I am trying to select records that ARE in both tables (just create a relational link between common fields) but what about when you want to invert the selection? Can this be done in one query? Any suggestions are welcome. Thank you, Ruben. On Wed, 20 Jun 2007 14:18:05 -0700, Jose Ruben Gonz...
SQL Case Select
Hope you can help, I thought this would work but doesn't, SELECT getdata= case(@who) When 'me' then (select col1, col2, col3) When 'him' then (select col4, col5, col6) When 'them' then (select col1, col3, col5) else '' end From dbo.mytbl Where ID= @ID End Thanks for any help! CASE expressions return a scalar value with singe data type. If your columns are compatible data type then you can write the query as follows: SELECT CASE @who WHEN 'me' THEN col1 WHEN 'him' THEN col4 ...
Saving to table from Combobox selection
Hi Everybody, I have a combobox named (cboxItemMaster) that pulls from a table that I have.I have several of these combobox's on a form. Combobox 2 pulls filtered data from 1, then 3 from 2, then 4 from 3 respectively. This works fine. Now once all the selections are made I have it saving to a new table, an item setup table. This works fine. My question is lets say for combobox1 it pulls table1's data, table1's data has 3 fields of information, 1 is autokey, 2 is item number, 3 is classification of item. in the combobox I have it where it only shows the item name with the column w...
random selection of cells in excel
Dear All, I need to randomly select 1 out of n subjects (n=2,...5 etc). I know the typical approach is to assign random numbers to all n subjects, and pick the one corresponding to the first random number in the string of numbers (i.e. I get 3, 4, 1, 2 as a string of random numbers, this means that I need to select subject/cell number 3). Can somebody please advise if the following is acceptable: assign random numbers and then select the one that corresponds to the maximum (or minimum). I'm doing this in excel, so the random numbers generated have values between 0 and 1 (rand() function)...
Auto Responder messages to select domains
I am removing a bunch of aliases from several old domains that we have. I'd like to setup an autoresponder for all emails addressed to those domains. Currently all emails for the old domains are coming in on a separate SMTP relay which is filtering and virus checking all messages. Those messages are then forwarded to the Exchange server. Is there anything I can configure in the SMTP server of Exchange that will send out a message of my choice to only emails addressed to these domains? You would probably need to have an Event Sink developed. There is now way to do this out-of-t...
any way to erase all "objects" within a selected cell range
I pasted in a table from a web page. Wanted to to work with the numbers in the web table, in Excel. Fine! But, it turns out there were also a bunch of little objects in the web page. Totally small, a few pixels i think. You don't even see them on the sheet, but i noticed them when mousing over, and the mouse changed either to the 4-way pointer, or to a pointing hand. Clear...all does not clear these objects; just clears the cells. So, i moused [very slowly] around the cell area where i had done the paste, and one by one, right clicked and deleted each one. Very tedious! Is th...
X Y with Letter grade Labels
HI, I have a graph where I ask departments to rate their talent pool from 1 - 10. Then I ask them to grade their department from A+ to F. So for an xy chart x is the grade and y is the talent pool number. How do I get the scale to show A+ down to F instead of numbers? Any help would be appreciated. Thanks. An XY chart is used to plot numbers not categories. Use a Line chart best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "KenRamoska" <KenRamoska@discussions.microsoft.com> wrote in message news:607F72AD-2DDD-4759-AA39-E6A09F73ACA0@mic...
Defining if a cell has an letter / number with in it using IFstate
In a IF statement how do i determine a letter or a number within a cell using Excel 2007 ie IF(D3 = letter; then 1 ; else 0) Thanks =3Dif(istext(d3),1,0) & =3Dif(isnumber(d3),1,0) On Nov 18, 9:42=A0pm, Washeacrim <Washeac...@discussions.microsoft.com> wrote: > In a IF statement how do i determine a letter or a number within a cell u= sing > Excel 2007 > > ie =A0 =A0 =A0IF(D3 =3D letter; then 1 =A0; else 0) > > Thanks =IF(ISNUMBER(D3), "It's a number", "It's text or Boolean") =IF(ISNUMBER(D3), 1,0) But the IF is not needed w...
I need a letter for creditors explaining I can't afford to pay
I have tried very hard to keep up my monthly payments on my credit cards, now it has come to I just need a few months to catch up on other expenses which does not include credit card payments. I would like to however explain the each credit card company that I due to cash flow and economics I have no money and can no longer afford to make monthly payments at this time. Is there a letter out there for this?????? ...
Column references changed from letters to numbers
I have a tracking list in an Excel 2003 spreadsheet (posted in Sharepoint) so other folks can add a single row of information to the list. Somehow, the column references changed from A, B, C ... to 1,2,3 ... while the row references are still at 1,2,3 ... Is this valid? Or is my spreadsheet corrupt? Anyone know how this could have happened ... and how do I change it back? The sheet was protected and the folks entering the data didn't have the password. Is there some way to prevent it from happening again? Thanks, -- JoAnn Tools - Options - General Tab. Uncheck R1C1 Reference styl...
select text in Calendar
I'm working on a calendar in Publisher. I'm adding text into a cell by spacing down 1 point, then typing in 8 points. Doing fine until April. At that time, I can only select cells (whole days). It will not let me select the text. Help is no help. Any hints out there. Did I accidentally select something to do this? Bonnie Have you added a picture to the cell or a shape? If you have send it to the back, make the cell no fill and try again. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "GoBonnieGo" <GoB...
Select cell by color
MAC Powerbook G4, OS 10.5.8 Office 2004, Excel 2004 Version 11.5.6 I would like to do: =If(cell A7 colorindex=7, "Do something", "Do something else") Don't know how to format the if "logical test" to use the color of the cell Gene In article <C783808F.EB0C%gene@WirelessEngineeringCorp.com>, Gene Augustin <gene@WirelessEngineeringCorp.com> wrote: > MAC Powerbook G4, OS 10.5.8 > Office 2004, Excel 2004 Version 11.5.6 > > > I would like to do: > > =If(cell A7 colorindex=7, "Do something",...
Range Selection for Dropdown List
Where would I find instructions for creating a dropdown list that uses a value from another field in the record being populated e.g, if "A" is selected is the first dropdown only entries associated with "A" display as choice in the next list. Indirect is the function in Excel. Thanks! Do a search on 'cascading combos'. This is often asked. Here's a link to a sample database http://www.rogersaccesslibrary.com/forum/topic389.html Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "S. gordon" <S. gordon@discussio...
selecting rows #3
hi there, kindly let me have a solution for selecting all the even\odd rows or columns in an excel sheet, regards ahmad nahar wrote: > kindly let me have a solution for selecting all the > even\odd rows or columns in an excel sheet, Here's one way, but it may seem a bit odd. First some preparation. Do this part only once. In an unused column, paste =MOD(ROW(),2) in each row that has data in it. Select the column, and use Data > Filter > Auto filter With this preparation done, you'll see a pull-down triangle at the top of the column. Each time you ...
Sales Invoice Return Item Selection
What permissions need to be granted to allow access to this window? I have a Sales Guy who is assigned to process returns, previously it was accounting. I am using GP V10 sp4. I granted him the RT Agent and RT Manager secrurity roles, but still does not have access to open the "Sales Invoice Return Item Selection" window. Accounting has no problem opening and processing. That window does not show as a Security Task for with RT Agent or RT Manager security Role. Thanks in advance for any assistance. Jim, The 'Create Return' window that you access from the ...
In excel, how do you asign numeric values to text or letters, eg..
How can I asign numeric values for text of letters, eg. A=1, B=2, C=3, etc. Hi if you have this kind of order try (letter in cell A1): =CODE(A1)-64 -- Regards Frank Kabel Frankfurt, Germany adefreitas wrote: > How can I asign numeric values for text of letters, eg. A=1, B=2, > C=3, etc. ...
invoices selected for payment report
is there a way to create a report of invoices that are due for payment. ---------------- 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://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=c45b1991-84e1-4ff0-b559-60091ec32b15&dg=microsoft...
Query to select from list
I have a table which contains one field "connector" which could contain one name or more than one name. Is there a way to build a query that will travel through the table and select the records that contain that person's name (John Smith)? Any help you can offer is greatly appreciated. Are you saying you have multiple names in a single field? Or is that field for the name may have multiple records? The first is the wrong way to store data but if that is what you have then you can use this as criteria -- Like "*" & [Enter search name] & "...
Force Account Selection on Sending Mail
I have quite a few email accounts setup in my Outlook. From time to time I need email to go out through a specific account. Most of the time I forget to pick that account from the "Accounts" drop down menu. Is there a way to force Outlook to prompt me for the account to use when sending new mail? I'm open to third party software if that's an option. thanks, ..brit Try looking at http://www.slipstick.com for third party add-ins. Outlook cannot force an account choice. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All u...
Workstation sending cc when none selected
I have one user's computer that is doing the oddest thing... it always sends a cc to one particular person! And the cc field in the message is always blank... but does show as a cc was sent in the recipient's (the one who got the copy) mail. HELP! ...
When a cell is merely selected, executed an evaluation?
My goal is to set up a situation so that with a cell is merely touched/selected; it adds some values for reconciliation. My example has many complex calculations that range over rows 10-19. The cell I want to have selected in at Q10. However, I want to replicate rows 10-19 many times (20-29, 30-39, etc…) I first though using a checkbox at Q10 would be the best solution. A simplified example of the code looked like this: =IF(Q10,B18=E18) and when the checkbox was selected then Q10 evaluated TRUE. But when I replicate the rows, the checkbook does not replicate correctly. It copies the c...
Using excel to select only matching images...
We have an online storefront. Our products are all in an excel spreadsheet, arranged by part number. I have about 30,000 images of products. I only want to select about 15,000 images of these 30,000 images. How do I select out of my image folder (containing 30,000 images) only the 15,000 images I need (that actually match the 15,000 products in our excel product listing)? All images and products are arranged via their " part number ". My product listing is in (MS Excel 2000) by part number. My images are in their folder and named by part number. It's like selecting only the...
I cannot select any text or elements on my page in Publisher 2007
When trying to select text elements and graphic elements on a page of a multi page document, I am unable to select anything although I have been editing the document previously. Can the file be locked in any way You may have a border around the pages that sits in front preventing your selecting the objects. You can send the border to the back. Open the "Graphics Manager" in the Tools menu. Use the drop down to select the pictures. -- Mary Sauer http://msauer.mvps.org/ "Bluesea124" <Bluesea124@discussions.microsoft.com> wrote in message news:B1CE9B5A-D171-40B9...
I know there should be a simple answer to this question, but I can't find it on the "Help" menu. Hi Bethany tools / options / edit "move selection after enter" -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Bethany" <Bethany@discussions.microsoft.com> wrote in message news:50CD805D-F385-4457-9363-5C3A3EF7825D@microsoft.com... >I know there should be a simple answer to this question, but I can't find >it > on the "Help" menu. tools->options->edit and select one of...
"one or more of the selected senders could not be added to your blocked senders list"
Hey all. I get this when ever I try to killfile anyone at all, regardless of who they are. I'm on OE6 in XP Home and this has only just started happening. My KF only has a few spammers in it so I don't think it's full. Does anyone know what's going on? Thanks "Ayatollah of rock 'n' roller" <thisisf@lse.co.ck> wrote in message news:gGxDn.40$Bm7.30@newsfe29.ams2... > Hey all. I get this when ever I try to killfile anyone at all, regardless of > who they are. I'm on OE6 in XP Home and this has only just started > happen...
Select Records from One Table that Aren't in Another Table
I have two tables: one is a ParksUIC table containing a list of drywells. The other is a Lookup table containing all known potable discharge wells. what i want to do is create a query that will only select records from the ParksUIC table that ARE NOT in the Lookup table. This is easy to do for when I am trying to select records that ARE in both tables (just create a relational link between common fields) but what about when you want to invert the selection? Can this be done in one query? Any suggestions are welcome. Thank you, Ruben. On Wed, 20 Jun 2007 14:18:05 -0700, Jose Ruben Gonz...
SQL Case Select
Hope you can help, I thought this would work but doesn't, SELECT getdata= case(@who) When 'me' then (select col1, col2, col3) When 'him' then (select col4, col5, col6) When 'them' then (select col1, col3, col5) else '' end From dbo.mytbl Where ID= @ID End Thanks for any help! CASE expressions return a scalar value with singe data type. If your columns are compatible data type then you can write the query as follows: SELECT CASE @who WHEN 'me' THEN col1 WHEN 'him' THEN col4 ...
Saving to table from Combobox selection
Hi Everybody, I have a combobox named (cboxItemMaster) that pulls from a table that I have.I have several of these combobox's on a form. Combobox 2 pulls filtered data from 1, then 3 from 2, then 4 from 3 respectively. This works fine. Now once all the selections are made I have it saving to a new table, an item setup table. This works fine. My question is lets say for combobox1 it pulls table1's data, table1's data has 3 fields of information, 1 is autokey, 2 is item number, 3 is classification of item. in the combobox I have it where it only shows the item name with the column w...
random selection of cells in excel
Dear All, I need to randomly select 1 out of n subjects (n=2,...5 etc). I know the typical approach is to assign random numbers to all n subjects, and pick the one corresponding to the first random number in the string of numbers (i.e. I get 3, 4, 1, 2 as a string of random numbers, this means that I need to select subject/cell number 3). Can somebody please advise if the following is acceptable: assign random numbers and then select the one that corresponds to the maximum (or minimum). I'm doing this in excel, so the random numbers generated have values between 0 and 1 (rand() function)...
Auto Responder messages to select domains
I am removing a bunch of aliases from several old domains that we have. I'd like to setup an autoresponder for all emails addressed to those domains. Currently all emails for the old domains are coming in on a separate SMTP relay which is filtering and virus checking all messages. Those messages are then forwarded to the Exchange server. Is there anything I can configure in the SMTP server of Exchange that will send out a message of my choice to only emails addressed to these domains? You would probably need to have an Event Sink developed. There is now way to do this out-of-t...
any way to erase all "objects" within a selected cell range
I pasted in a table from a web page. Wanted to to work with the numbers in the web table, in Excel. Fine! But, it turns out there were also a bunch of little objects in the web page. Totally small, a few pixels i think. You don't even see them on the sheet, but i noticed them when mousing over, and the mouse changed either to the 4-way pointer, or to a pointing hand. Clear...all does not clear these objects; just clears the cells. So, i moused [very slowly] around the cell area where i had done the paste, and one by one, right clicked and deleted each one. Very tedious! Is th...
X Y with Letter grade Labels
HI, I have a graph where I ask departments to rate their talent pool from 1 - 10. Then I ask them to grade their department from A+ to F. So for an xy chart x is the grade and y is the talent pool number. How do I get the scale to show A+ down to F instead of numbers? Any help would be appreciated. Thanks. An XY chart is used to plot numbers not categories. Use a Line chart best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "KenRamoska" <KenRamoska@discussions.microsoft.com> wrote in message news:607F72AD-2DDD-4759-AA39-E6A09F73ACA0@mic...
Defining if a cell has an letter / number with in it using IFstate
In a IF statement how do i determine a letter or a number within a cell using Excel 2007 ie IF(D3 = letter; then 1 ; else 0) Thanks =3Dif(istext(d3),1,0) & =3Dif(isnumber(d3),1,0) On Nov 18, 9:42=A0pm, Washeacrim <Washeac...@discussions.microsoft.com> wrote: > In a IF statement how do i determine a letter or a number within a cell u= sing > Excel 2007 > > ie =A0 =A0 =A0IF(D3 =3D letter; then 1 =A0; else 0) > > Thanks =IF(ISNUMBER(D3), "It's a number", "It's text or Boolean") =IF(ISNUMBER(D3), 1,0) But the IF is not needed w...
I need a letter for creditors explaining I can't afford to pay
I have tried very hard to keep up my monthly payments on my credit cards, now it has come to I just need a few months to catch up on other expenses which does not include credit card payments. I would like to however explain the each credit card company that I due to cash flow and economics I have no money and can no longer afford to make monthly payments at this time. Is there a letter out there for this?????? ...
Column references changed from letters to numbers
I have a tracking list in an Excel 2003 spreadsheet (posted in Sharepoint) so other folks can add a single row of information to the list. Somehow, the column references changed from A, B, C ... to 1,2,3 ... while the row references are still at 1,2,3 ... Is this valid? Or is my spreadsheet corrupt? Anyone know how this could have happened ... and how do I change it back? The sheet was protected and the folks entering the data didn't have the password. Is there some way to prevent it from happening again? Thanks, -- JoAnn Tools - Options - General Tab. Uncheck R1C1 Reference styl...
select text in Calendar
I'm working on a calendar in Publisher. I'm adding text into a cell by spacing down 1 point, then typing in 8 points. Doing fine until April. At that time, I can only select cells (whole days). It will not let me select the text. Help is no help. Any hints out there. Did I accidentally select something to do this? Bonnie Have you added a picture to the cell or a shape? If you have send it to the back, make the cell no fill and try again. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "GoBonnieGo" <GoB...
Select cell by color
MAC Powerbook G4, OS 10.5.8 Office 2004, Excel 2004 Version 11.5.6 I would like to do: =If(cell A7 colorindex=7, "Do something", "Do something else") Don't know how to format the if "logical test" to use the color of the cell Gene In article <C783808F.EB0C%gene@WirelessEngineeringCorp.com>, Gene Augustin <gene@WirelessEngineeringCorp.com> wrote: > MAC Powerbook G4, OS 10.5.8 > Office 2004, Excel 2004 Version 11.5.6 > > > I would like to do: > > =If(cell A7 colorindex=7, "Do something",...
Range Selection for Dropdown List
Where would I find instructions for creating a dropdown list that uses a value from another field in the record being populated e.g, if "A" is selected is the first dropdown only entries associated with "A" display as choice in the next list. Indirect is the function in Excel. Thanks! Do a search on 'cascading combos'. This is often asked. Here's a link to a sample database http://www.rogersaccesslibrary.com/forum/topic389.html Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "S. gordon" <S. gordon@discussio...
selecting rows #3
hi there, kindly let me have a solution for selecting all the even\odd rows or columns in an excel sheet, regards ahmad nahar wrote: > kindly let me have a solution for selecting all the > even\odd rows or columns in an excel sheet, Here's one way, but it may seem a bit odd. First some preparation. Do this part only once. In an unused column, paste =MOD(ROW(),2) in each row that has data in it. Select the column, and use Data > Filter > Auto filter With this preparation done, you'll see a pull-down triangle at the top of the column. Each time you ...
Sales Invoice Return Item Selection
What permissions need to be granted to allow access to this window? I have a Sales Guy who is assigned to process returns, previously it was accounting. I am using GP V10 sp4. I granted him the RT Agent and RT Manager secrurity roles, but still does not have access to open the "Sales Invoice Return Item Selection" window. Accounting has no problem opening and processing. That window does not show as a Security Task for with RT Agent or RT Manager security Role. Thanks in advance for any assistance. Jim, The 'Create Return' window that you access from the ...
In excel, how do you asign numeric values to text or letters, eg..
How can I asign numeric values for text of letters, eg. A=1, B=2, C=3, etc. Hi if you have this kind of order try (letter in cell A1): =CODE(A1)-64 -- Regards Frank Kabel Frankfurt, Germany adefreitas wrote: > How can I asign numeric values for text of letters, eg. A=1, B=2, > C=3, etc. ...
invoices selected for payment report
is there a way to create a report of invoices that are due for payment. ---------------- 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://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=c45b1991-84e1-4ff0-b559-60091ec32b15&dg=microsoft...
Query to select from list
I have a table which contains one field "connector" which could contain one name or more than one name. Is there a way to build a query that will travel through the table and select the records that contain that person's name (John Smith)? Any help you can offer is greatly appreciated. Are you saying you have multiple names in a single field? Or is that field for the name may have multiple records? The first is the wrong way to store data but if that is what you have then you can use this as criteria -- Like "*" & [Enter search name] & "...
Force Account Selection on Sending Mail
I have quite a few email accounts setup in my Outlook. From time to time I need email to go out through a specific account. Most of the time I forget to pick that account from the "Accounts" drop down menu. Is there a way to force Outlook to prompt me for the account to use when sending new mail? I'm open to third party software if that's an option. thanks, ..brit Try looking at http://www.slipstick.com for third party add-ins. Outlook cannot force an account choice. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All u...
Workstation sending cc when none selected
I have one user's computer that is doing the oddest thing... it always sends a cc to one particular person! And the cc field in the message is always blank... but does show as a cc was sent in the recipient's (the one who got the copy) mail. HELP! ...
When a cell is merely selected, executed an evaluation?
My goal is to set up a situation so that with a cell is merely touched/selected; it adds some values for reconciliation. My example has many complex calculations that range over rows 10-19. The cell I want to have selected in at Q10. However, I want to replicate rows 10-19 many times (20-29, 30-39, etc…) I first though using a checkbox at Q10 would be the best solution. A simplified example of the code looked like this: =IF(Q10,B18=E18) and when the checkbox was selected then Q10 evaluated TRUE. But when I replicate the rows, the checkbook does not replicate correctly. It copies the c...
Using excel to select only matching images...
We have an online storefront. Our products are all in an excel spreadsheet, arranged by part number. I have about 30,000 images of products. I only want to select about 15,000 images of these 30,000 images. How do I select out of my image folder (containing 30,000 images) only the 15,000 images I need (that actually match the 15,000 products in our excel product listing)? All images and products are arranged via their " part number ". My product listing is in (MS Excel 2000) by part number. My images are in their folder and named by part number. It's like selecting only the...
I cannot select any text or elements on my page in Publisher 2007
When trying to select text elements and graphic elements on a page of a multi page document, I am unable to select anything although I have been editing the document previously. Can the file be locked in any way You may have a border around the pages that sits in front preventing your selecting the objects. You can send the border to the back. Open the "Graphics Manager" in the Tools menu. Use the drop down to select the pictures. -- Mary Sauer http://msauer.mvps.org/ "Bluesea124" <Bluesea124@discussions.microsoft.com> wrote in message news:B1CE9B5A-D171-40B9...