473,324 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Not showing empty field and character in string concatenate

Hey Y'All,

I'm trying to do the following. I have create a query to retrieve values from 4 tables. In table employee, has the employee first name, middle name and last name.

In my query i select the following fields [first name] & "_" & [middle name] & "_" & [last name] As EmployeeNameComplete FROM Employee.

For example the field [middle name] can be empty and if it is, EmployeeNameComplete will look like this "BOB__RADO". Now having the 2 underscores doesn't look very good. If the middle name field is empty what can do in my query to avoid getting "BOB__RADO" as EmployeeNameComplete ?
Mar 23 '07 #1
3 5046
ADezii
8,834 Expert 8TB
Hey Y'All,

I'm trying to do the following. I have create a query to retrieve values from 4 tables. In table employee, has the employee first name, middle name and last name.

In my query i select the following fields [first name] & "_" & [middle name] & "_" & [last name] As EmployeeNameComplete FROM Employee.

For example the field [middle name] can be empty and if it is, EmployeeNameComplete will look like this "BOB__RADO". Now having the 2 underscores doesn't look very good. If the middle name field is empty what can do in my query to avoid getting "BOB__RADO" as EmployeeNameComplete ?
Expand|Select|Wrap|Line Numbers
  1.  [FirstName] & "_" & IIf(IsNull([MI]),"",[MI] & "_") & [LastName]
Mar 23 '07 #2
Expand|Select|Wrap|Line Numbers
  1.  [FirstName] & "_" & IIf(IsNull([MI]),"",[MI] & "_") & [LastName]

Wow.. works like good. thank. Code like " IIf " what is it Visual basic commands or SQL commands? and where can find more of it?
Mar 23 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
Have a look at some of the articles here.
Mar 25 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Wayno | last post by:
My php logs are coming up empty. I have done all I can think of, and all that made sense to me. Can someone take a look at my php.ini please and tell me what you think may be the problem. I...
3
by: tornado | last post by:
Hi all, I am pretty new to PHP. I was reading PHP manual and trying out the example from 2nd chapter (A simple Tutorial). When i try to print the variable as given in the example it returns...
2
by: Mark | last post by:
I am trying to populate a field in a SQL table based on the values returned from using substring on a text field. Example: Field Name = RecNum Field Value = 024071023 The 7th and 8th...
8
by: mark | last post by:
Access2000 How do I write a query that combines the CTC field from each record below into one record? I need to concatenate the CTC field with a separator, like below: ...
19
by: David Logan | last post by:
We need an additional function in the String class. We need the ability to suppress empty fields, so that we can more effectively parse. Right now, multiple whitespace characters create multiple...
4
by: mchlle | last post by:
I have this code that will run as long as all the fields below have data in them. How can I get the code to run if at least one field has data in it. strBCCEmail = "1" & & "@faxmail.com; " &...
10
by: H | last post by:
Hi, I have the following address fields in a table: flat_number house_name_or_number street village postal_town county postcode
2
by: Jay | last post by:
I have a SQL Server table with nvarchar type column which has not null constraint. I am inserting empty string ("") from Java to the table column. When I export this table into .csv file using bcp...
3
by: GazK | last post by:
I am hoping someone can steer me towards an elegant solution to a simple problem. I have a query result which returns an array of details about a physical building, including its address. The...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.