data value in Form field if no table entry

I have a form with a field which pulls through and concentenates 2 fields 
called [ContactFirstname] and [ContactLastName]from my table

There are however some customers for whom I do not have names and therefore 
instead I would like Sir/Madam to appear in the field in the form

I think I have seen this done somewhere using ELSE? but can't find it

Any help/ideas gratefully received 


0
Pete
3/21/2007 8:39:23 AM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
1018 Views

Similar Articles

[PageSpeed] 26

Perhaps something like this:
    Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam")

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pete and Jeannette Smith" <pete.netty@tiscali.co.uk> wrote in message
news:4600ef61$1_4@mk-nntp-2.news.uk.tiscali.com...
>I have a form with a field which pulls through and concentenates 2 fields 
>called [ContactFirstname] and [ContactLastName]from my table
>
> There are however some customers for whom I do not have names and 
> therefore instead I would like Sir/Madam to appear in the field in the 
> form
>
> I think I have seen this done somewhere using ELSE? but can't find it
>
> Any help/ideas gratefully received 

0
Allen
3/21/2007 9:24:59 AM
Many thanks Allen that works a treat

Pete Smith
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:OPiZNr5aHHA.4948@TK2MSFTNGP05.phx.gbl...
> Perhaps something like this:
>    Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam")
>
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Pete and Jeannette Smith" <pete.netty@tiscali.co.uk> wrote in message
> news:4600ef61$1_4@mk-nntp-2.news.uk.tiscali.com...
>>I have a form with a field which pulls through and concentenates 2 fields 
>>called [ContactFirstname] and [ContactLastName]from my table
>>
>> There are however some customers for whom I do not have names and 
>> therefore instead I would like Sir/Madam to appear in the field in the 
>> form
>>
>> I think I have seen this done somewhere using ELSE? but can't find it
>>
>> Any help/ideas gratefully received
> 


0
Pete
3/21/2007 11:58:46 AM
Hi guysngals!

not sure on what your doing exactly but maybe look at the following:-

If isnull([ContactFirstname]) then        '<-- you may play with this to 
determine if only one part is missing ie if you have the surname only etc
    ConcatenatedName = "Sir/Madam"
Else
    ConcatenatedName = [ContactFirstname] & " " & [ContactLastName]
Endif

cheers
jeff

"Pete and Jeannette Smith" <pete.netty@tiscali.co.uk> wrote in message 
news:4600ef61$1_4@mk-nntp-2.news.uk.tiscali.com...
>I have a form with a field which pulls through and concentenates 2 fields 
>called [ContactFirstname] and [ContactLastName]from my table
>
> There are however some customers for whom I do not have names and 
> therefore instead I would like Sir/Madam to appear in the field in the 
> form
>
> I think I have seen this done somewhere using ELSE? but can't find it
>
> Any help/ideas gratefully received
> 


0
jeff
3/22/2007 12:57:43 AM
Reply:

Similar Artilces:

multivalue fields
I'm trying to combine many databases with exactly the same fields, but two of the fields in one of three tables are multi value fields. I can do two of the three tables with an "insert. . .union select" statement but that doesn't work with the multi value fields. How do I combine the tables with the multi value fields into one table? Ideally, you don't. If you have a multi-value field, you are violating the 1st Normal Form of relational database design (each field should contain one, and only one, piece of information. If your data is actually related this way,...

value from 2 field joined in one field...( help designing form)
Hi again All, This time I come with a question that maybe sounds strange. I have 3 textboxes on form F1,let say TBL and TBH their data type are numeric and also TBN with text data.Sample record. TBL TBH TBN 0 3 H 1 9 R 2 0 L: Now I want to return both of TBL and TBH values to textbox TBS on form F2 and TBN value to TBK ,with condition If TBK value Then TBS value : H 3 R 1-9 L 2 specially for recor...

Find Recordset field number using field value
I am very new to access and have a question regarding a recordset. I would like to access a field in a recordset based on an index but I am not sure of my starting index number. If I have the value of a field can I figure out what the recordset field index number is? For example lets say I haev a table with all the months as column headers: Jan, FEb, Mar, Aprl........ If I have the value "MAR" can I find out that this would be field(3) of the recordset?? I have a table with sales figure broken down my month. I have to step forward and backward through the columns and I think it ...

help matching a field in a query
Hello all I have a table that has the complete name for each user. I would like to run a query to match the FirstName LastName with the CurrentUser() and return a user_code. This is the query that im using now: SELECT users.user_code, users.name FROM users GROUP BY users.user, users.name HAVING users.name Like (CurrentUser()); Everything works fine when the Logon Name is the exact as the name from my table BUT the problem comes when the user forgot to type caps to its Logon Name, when this happends then my query will return the user_code. Also I want to be able to match any word from the C...

MAJOR PIVOT TABLE PROBLEMS
Hi, Trying to make an inventory ledger that displays all products, with all their transaction no's and dates subcategorised under the products using excel pivot tables. The problem was that If i kept row fields as product, under that transaction date and under that transaction no then i could not get the running total to work properly. I made another cell in my source data called transaction date & transaction no, and put that under product and finally my running total started working properly. removed individual transaction date and transaction product row fields. I got another pr...

How do I count several non-numerical values in Excel?
I am trying to create a formula to add the number of \ and L in a row. It seems simple but COUNT does not seem to want to do it. Help please!!! =COUNTIF(A4:F4,"\")+COUNTIF(A4:F4,"L") Change the ranges to suit your situation........... Vaya con Dios, Chuck, CABGx3 "Yuccaman" <Yuccaman@discussions.microsoft.com> wrote in message news:3D6983C4-05FC-4C3B-8C6D-46CC98D75215@microsoft.com... > I am trying to create a formula to add the number of \ and L in a row. It > seems simple but COUNT does not seem to want to do it. Help please!!! ...

how can i combine 2 columns in excel into 1 without losing data?
Assuming columns A and B In C1 enter =A1 & B1 Double-click on the fill handle(black lump at bottom right corner)of C1 to increment the formula down the column as far as you have data in column B. If you would like a space between the data........ =A1 & " " & B1 Gord Dibben Excel MVP On Mon, 9 May 2005 16:26:03 -0700, sturville <sturville@discussions.microsoft.com> wrote: Not much to go on here regarding cell contents and formatting, but I'm assuming that you have text in the cells and you just want to make on long text string. (If it was numerical...

tables
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Bob - thank you for this. It takes me a step closer. I now realize that I want to create a table of results where the variables influence the outcome earlier in the chain but are not cells references in the final function. Do I need to re-configure the spreadsheet or is there another way to do this quickly? ...

Data Encryption
Is there a way to anonymize say a column of employee numbers in Excel? I want it done in such a way that for each year of data the number is encrypted the same way so i can use the encrypted value to look up data for that employee without knowing who the person is? Thanks for any assistance you can provide. What's the end purpose of this hiding/encrypting of the employee numbers? Just to hide them from you, or to hide from other people viewing the reports/output later? In either case you could 'hide' them by building a separate sheet that has 2 columns: a series ...

New Email To: Field shows previous names
When I create a new email, I start to type the persons name in the To field. If I type Ste, a list pops up showing me names all starting sith "Ste". This is great except, there are names in this list that are no longer valid. How can I remove names from this pop-up list? Some of the names popping up are not in my contact list. I suspect that the names popping up are based on me receiving previous emails from the person. Thanks, Steve Steve <anonymous@discussions.microsoft.com> wrote: > When I create a new email, I start to type the persons > name in the To fiel...

Calculation from results of form fields in VBA
I have stumbled (literally) on a solution for calculating the result of a formfield using the results of 2 others: If aDocForm("SupInfBox").Result = "inf" Then aDocForm("IsoSuggest").Result = ((aDocForm("LLWireSetup").Result - aDocForm("ULWireSetup").Result) / 2) + aDocForm("ULWireSetup").Result End If and it works. However, when I change simply the + to a - If aDocForm("SupInfBox").Result = "sup" Then aDocForm("IsoSuggest").Result = ((aDocForm("LLWireSetup").Result + aDocForm(&qu...

Text Wrap from Copied Access Data
When I copy data from Access to Excel, Excel automatically wraps the text. Is there a fix for this, so when I copy the data it does not wrap the text? Thanks! ...

MS Access Field List Export to Excel
Probably the wrong Forum/Group but Spread the word right? Ok. Here's the deal. The wonderful creator's of MS Access have not given a way to Print or Export a "Table" or "Query" field list very easily. I have a work around that works. No Coding Involved. Step 1: Open the Database to be worked with Step 2: Click on your Table's View Step 3: Right Click on the Table to be worked with Step 4: Click "Export: Step 5: Click "Save As" Type Dropdown Step 6: Select "Text Files" Step 7: Give it a bogus name if it does not have one already. Step 8...

Copy value
I want to use shortcut key to copy value in spreadsheet. The VBA code is: Sub Paste_Value() ' shortcut key: Ctrl+q ActiveCell.Select Selection.PasteSpecial Paste:=xlValues End Sub But if I don't copy cell and I use shortcut key, the macro doesn't work. How can I modify this macro? Artur Hi Artur! Try: Sub Paste_Value() ' shortcut key: Ctrl+q ActiveCell.Copy Selection.PasteSpecial Paste:=xlValues End Sub You need to copy before you paste special. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Li...

Opening/importing data from database takes forever after upgrading office!
Hello. I recently reinstalled a users computer and upgraded his office version from 2000 to xp. Now when he imports data from a database it takes forever. It can take several minutes and before the reinstallation the operation only took seconds. What could be wrong? I did the same on his coworkers computer with the same result so there must be something that can solve the problem. I just can't figure it out. The run windows 2000 sp4. Please help. :^) //Johan Anyone? It never hurts to empty the windows temp folder (with excel closed), but it may not help. You may want to take...

Data Validation #40
How can I restrict entry to ceels between the times of 18:00 & 06:00, this must allow 22:00 etc This is so it covers the night shift in 24hr format Pete Select the cells Choose Data>Validation From the Allow dropdown, choose Custom In the Formula box, type a formula that refers to the active cell, e.g.: =OR(B5>=0.75,B5<=0.25) Click OK Pete wrote: > How can I restrict entry to ceels between the times of > 18:00 & 06:00, this must allow 22:00 etc > > This is so it covers the night shift in 24hr format > > Pete -- Debra Dalgleish Excel FAQ, Tips...

Double Clicking On Cell To Edit Data
Hello, I modify existing cells contents quite frequently. Using the mouse and double clicking seems to me to be extra work. Is there any way via the keyboard to get into a cell to edit its contents? TIA Jim Mac Millan Jim Cursor to, F2 Beege "Jim Mac Millan" <fly_boy_jim@yahoo.moc> wrote in message news:OOiJF%23cLGHA.604@TK2MSFTNGP14.phx.gbl... > Hello, > > I modify existing cells contents quite frequently. Using the mouse and > double clicking seems to me to be extra work. > > Is there any way via the keyboard to get into a cell to ...

web page form
I would like to use a form from an access db on a web site that can be filled out and the information retained in the access db. I'm new to this and would appreciate any help in achieving this. We are running server 2003 and access 2003. Access forms don't convert well to web forms. Do some research on Active Server Pages. http://www.asp101.com is a good start. "Mark" <rmwatrich@hotmail.com> wrote in message news:075794C3-2AE3-4A8A-9BCF-504ED319C5E7@microsoft.com... >I would like to use a form from an access db on a web site that can be >filled out ...

Data validation listbox still not working after troubleshooting
Hi All, The list box in each cell in ranges I've named are not working--only the top cell or record in the list box can be used for some reason. The list box, when clicked, does not even show the top value--just a blank field. Does anyone know why this would happen? It does not appear to be associated with: XP, corrupt wkbook, frames or window split, etc. I've searched this site and cannot find an answer. This boggles my mind... Thanks, Arnold Hi How did you define your data validation list? Did you enter values directly, or did you refer to some cell range on same sheet, or...

Form/Subform In Continuous Form
Is there a way to put a main form/subform in a continuous form so I can scroll through all the records in the main form? Thanks! I think I saw a sample db like what you're asking for here http://www.daiglenet.com/MSAccess.htm Synch Combo Continuous Sample database to show different method for synchronizing combos on continuous forms. I hope this helps. "Steve" wrote: > Is there a way to put a main form/subform in a continuous form so I can > scroll through all the records in the main form? > > Thanks! > > > Sandra's website has been...

Problems with Vanguard Fund Data
I believe I have set up my accounts correctly and download active money statements from Vanguard, but although I have a total of about 6 different funds, the transactions all get lumped into 2 or 3 of them. I have to manually move them to correct funds. Does anyone else have this problem with Vanguard funds, or a solution? I have the same problem...even when I have shown one of the accounts as closed.Also, the transactions don't add up to the account balance...really weird. Whats the point of having online updates that don't work. any suggestions?? "Jim" wrote: > ...

How much space do Calendar entries take?
Hi all- I'm struggling with capacity limits in Exchange 5.5 standard. How much space do Calendar entries take up in the priv info store? Are old entries ever purged? If not, what happens several years from now? Would it free up any meaningful resources to purge old years' calendar entries on everybody's mailbox? Thanks for your comments. They won't go away on their own, they will simply continue to increase the mailbox size. You can use the mailbox manager (located in the support directory of service pack 4) to clean up old calendar appointments, tasks, journal etc quite ea...

limiting chart source data with criteria?
I know for a series you choose: Values: =A2:A10 is there anyway to limit it further by saying something to the effect of Values: =A2:10 but not equal to 0, like Values =A2:A10 <> 0 (I know that doesnt work, already tried it, but its kinda what I want to accomplish) I can go in and manually ctrl click through to limit the data source, but thats a pain, I'd rather choose 1 big block and have some statement exclude 0 values. I could sort the data....but according to my boss thats not an option, because she wants the order to be the same quarter to quarter no matter what the values....

SUM 2 fields and show the result in another
Hi; I need sum 2 field values(textboxes) and show the resul in another field. I´m programming this, but not work.. OnChange { var value1 = crmForm.all.estimatedvalue.DataValue; var value2 = crmForm.all.CFCMonto_de_Venta_Servicio.DataValue; var result = value1 + value2; crmForm.all.actualvalue.Datavalue= result; } What is wrong?, How is? Miguel Have you forgotten to use the complete custom field schema names including prefix eg:- crmForm.all.actualvalue.Datavalue should actually be crmForm.all.new_actualvalue.Datavalue (if new_ is your custom attribute prefix) Peter "...

Customer ordering forms
Is there any option available in exel, where customer can just click and select the item they want. Yes. You most likely want Data Validation. http://www.theofficeexperts.com/excel.htm#DropdownLists **************** Anne Troy www.piersontech.com www.mrexcel.com "Josephine" <Josephine@discussions.microsoft.com> wrote in message news:520A8041-EBC1-4C9F-9883-12AB85B15806@microsoft.com... > Is there any option available in exel, where customer can just click and > select the > item they want. ...