How Do I Not Show A Field If The Field Contains Nothing?

Jun 1, 2007

I'm making address labels and within the table that I'm using there are 2 address fields called Address1 and Address2. The majority of the records do not use Address2, but some do. Is there an expression in VB that I can write so that if Address2 is blank, it will not show Address2?



Here's my snippet so far. It's inside a text box inside a list box in the layout of reporting in visual studio 2005.






Code Snippet

=RTrim(Fields!FirstName.Value) & " " & Fields!LastName.Value & vbCrLF &

Fields!Address1.Value & vbCrLF & Fields!Address2.Value & vbCrLF &

RTrim(Fields!City.Value) & ", " & Fields!State.Value & " " & Fields!ZipCode.Value



This code produces the following if there is no Address2:



John Doe

234 W. Nowhere St.



Seattle, WA 77658



How can I make it so that it will know that Address2 is blank and to return this:



John Doe

234 W. Nowhere St.

Seattle, WA 77658



Any help is greatly appreciated!

View 4 Replies


ADVERTISEMENT

How To Show Field Name Or Field Title In Matrix

Nov 19, 2007



Hi,

A very simple question but I cannot find how, please share with me if you know how to display Field Name or Field Title in Matrix at Rows ?

Thanks
best regards,
Tanipar

View 1 Replies View Related

Only Show Field On Last Page

Nov 27, 2006

Hallo,
i would like to show some fields only on the last page on the report. How can i achieve this?
(The field is in the body area)

King regards,
Christian Niehaves

View 9 Replies View Related

Show Field Names/Schema

Jan 15, 2004

I can't seem to find a sample code, either here or on the net - - so I'll go ahead and ask...

What I'm looking for is a sample of how to query a database, so that I can populate a listbox with the table names from a database - - and then, populate another listobx with the field names from the database, in order to build a user-driven sql statement builder....

either of the above (at least the field name part) - either a code sample, or a link, will be greatly appreciated) - -

Thanks

View 1 Replies View Related

Show 0 Values If The Field Is NULL

Apr 22, 2008



Hi All,

I want to show 0, if the field contains NULL values.
I use the following expression.


=IIf(Fields!MTD_TotGrossBKCOAmt.Value = "NULL" , SUM(Fields!MTD_TotGrossBKCOAmt.Value), 0 )


But this works if the field contains only NULL values.
If it has a value, then it shows as #Error

Can anyone tell me how to make this work?

Thanks

View 7 Replies View Related

3 Results From One Field - Show Levels In Right Order

Nov 19, 2007


Hi there

We have a web application (database) that uses one field called Application and another called TicketType.

When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3

Eg TicketType, TicketType2, TicketType3

The extra two levels not being compulsory.

I am using sql server 2005 // Reporting Services

My query is as below:
SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM Ticket INNER JOIN
TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber



End result looks like this:





Application

App 2

App 3

TicketType

Type 2

Type 3


Software

Internal Apps

proACT





SW Other






Office Issues





General




Application

Click Track server



Alert (App)

Service




Network

Other





Network Fault


Software

Internal Apps

Other



User Account

New




Hardware

Network





HW Fault




Application

Click Track server



Alert (App)

Disk space






Office Issues





General






proACT



Configuration

Deployment


Software

Server Software

SharePoint



SW Fault

App Failure (Function)


Software

Server Software

SharePoint



SW Fault

App Failure (Function)


Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.

Can someone help please.


Please ask if I haven't explained myself.

thanks
Dianne

View 9 Replies View Related

Transact SQL :: Return Field When A Field Contains Text From Another Field

Aug 25, 2015

I'm new to SQL and I'm trying to write a statement to satisfy the following:

If [Field1] contains text from [Field2] then return [Field3] as [Field4].

I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.

View 8 Replies View Related

How To Add Date Field And Time Field (not Datetime Field )

May 4, 2006

Good morning...

I begin with SQL, I would like to add a field that will be date like 21/01/2000.

Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.

How to do for having date and time in two different field.

Sorry for my english....

Cordially

A newbie

View 3 Replies View Related

Multi Value Values In Varchar Field, To Show Each Value Separately In A View

Jul 20, 2005

Hi thereGot a interesting problem (depends on a point of view.....).Background :-Agent (within Domino) is run daily basis which extracts data fromDomino Notes application to SQL Server 2000 database. Agent firstremoves ALL contents and then appends ALL data.Reporting Tool is SQL Reporting Services (very cool !!).Problem :-Within Domino Notes, it can have a field which is mult-value fieldi.e. contain multi valuese.g.(from Helpdesk application)!HowTo!!Access Email;!HowTo!!Access the Web;etc..Need to create a view, then use sql to create stored proc, to be usedas the dataset for report within SQL Reporting services.Format. (using above as data as example)!HowTo!!Access Email; (1 row) WWL/SDR/04023/010 (DocID)!HowTo!!Access the Web; (2 row) WWL/SDR/04023/010 (DocID)I have a unique KEY within table called DocID. The report will have 8separate datasets (i.e. using subreports), all linked back to UniqueKey. That's easy.The dataset causing me hassle is the 1 above. How do u split outvalues as separate rows ?Name of field called --> "ImpFunctionsImpacted".Also, notes expert, who's working on the agent, tell's me the data canbe split either as a comma or semi-colon.Any suggestions most welcome.

View 2 Replies View Related

Show/hide A Field Based On The Logged In User In Reports

Apr 24, 2007

Hi,



I have some sensitive and non-sensitive info/fields in one of my reports. Is there a way to hide/show the info based on the logged in user? or do I have to create separte reports for each type of info like one for sensitive and another one for non-sensitive info.



Thanks inadvace for your help!



View 3 Replies View Related

Transact SQL :: Query To Show Most Recent Value Based On Date Field

Jun 18, 2015

I simply need to list the most recent value for each employee here... the value at each person's maximum date

Sample Detail Data:

EmpID                   Date                      Value
1                              1/1/14                  27
1                              2/12/15                333
2                              5/5/15                   255
3                              5/4/15                   110  
3                              1/1/13                   67                          
3                              3/2/14                   80

[Code] ....

What is the most efficient way to display the most recent value for each employee ID via MS SQL.?

View 4 Replies View Related

Nested CASE WHEN - Create A Field To Show If Measure Hits Threshold Or Not To Be Later Picked Up In SSRS

Oct 28, 2015

I am trying to script a case when to achieve the following.

I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?

CASE WHEN M.[Threshold Direction] = '>' THEN
CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN'
CASE WHEN A.[Value] < M.[Threshold] THEN 'RED'
ELSE ''
END END END AS 'Condition'Is this at all possible?

View 2 Replies View Related

Problem Importing Data From An Access Memo Field Into A SQL Server Ntext Field.

Jul 11, 2005

I'm using DTS to import data from an Access memo field into a SQL Server ntext field.  DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!

View 4 Replies View Related

Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert

Dec 30, 2003

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please help

View 6 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

Informix Date Type Field To SQL Server Datetime Field Error

Oct 17, 2007



I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part

I tried a couple of things:

Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.

Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.

What options do I have that will work?

View 1 Replies View Related

Odbc - Binding Sql Server Binary Field To A Wide Char Field Only Returns 1/2 The Daat

Jul 23, 2005

Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn

View 2 Replies View Related

Converting Datetimeoffset Field To Datetime Field / Why Milliseconds Value Is Incorrect

Nov 17, 2012

DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________

The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect

View 2 Replies View Related

Function To Return Remaining Of Field After It Finds A Character In The Field.

Feb 20, 2007

Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen

View 1 Replies View Related

Transact SQL :: NVARCHAR Field Getting Truncated After Increasing Field Length?

Aug 28, 2015

I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.

It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:

Inserted Value -> Result in table

123456-8 -> 123456-8      *Correct
1234567-9 -> 1234567-     *Wrong
123456789 -> 123456789    *Correct
1234567#9 -> 1234567#     *Wrong
1234567a9 -> 1234567a9    *Correct

Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?

I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.

View 3 Replies View Related

How To Matrix: Conditional Data-field Bg-color When Field Is Subtotal

Mar 25, 2008

How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???

View 3 Replies View Related

How Can I Print A Field That Is In The Dataset On Each Page And Show The Table Hearder On Each Page?

Apr 16, 2007

How can I print a field that is in the dataset on each page? I added a textbox in the Page Header and use =Fields!ProjectName.value in the value property. I got an error "Fields cannot used in page header and footer."



How can I have the table header shows on each page? Currently if the data goes to the second page, there is no table header.



Thanks.

DanYeung

View 8 Replies View Related

Reporting Services :: Formatting A Date Field To Just Show Date Not Minutes?

Jul 6, 2015

A date field in a report returns the date value as:

2015-07-01 13:30:27.000

Is there any way I can script this to appear as:

01 July 2015 (or 01-07-2015 or 01/07/2015)  - basically to cut out the hours, minutes and seconds?

The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:

01 July 2015 13:30

View 9 Replies View Related

How To Define Field Attribute For A Numeric Field In SQL Table?

Jan 19, 2005

I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?

View 12 Replies View Related

Access Memo Field To SQL Server Text Field

Nov 19, 2006

Hi,

I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).

I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.

Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.

View 14 Replies View Related

SQL Server 2008 :: Selecting Field That Contains Data From Another Field

May 28, 2015

We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.

ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY

what I need to do is identify where the Brand is in the Description field ...

I have tried ;

select * from Table
where Description Like Brand

not very successful.

View 3 Replies View Related

Update Field With Trigger Only If A Specific Field Is Updated

Nov 11, 2013

I want to update a field with a trigger only if a specific field is updated.

When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?

ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

[Code] .....

View 4 Replies View Related

Importing Of Varchar Field Data In Number Field

Dec 5, 2007

i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?


jto it

View 5 Replies View Related

Multiple Foreign Keys On Same Field, Based On Other Field

Jul 23, 2005

I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.

View 26 Replies View Related

Create Date Field From Substring Of Text Field

Jul 20, 2005

I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark

View 2 Replies View Related

Separating One Field Into Two Fields Based On A Character In The Field

Jul 20, 2005

I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?

View 1 Replies View Related

MS Access Memo Field To SQL Server Text Field

Aug 20, 2006

Hi all,



i've a reasonable amount of experience with MS Access and less
experience with SQL Server. I've just written an .NET application that
uses an SQL Server database. I need to collate lots of data from around
the company in the simplest way, that can then be loaded into the SQL
Server database.



I decided to collect the info in Excel because that's what most people
know best and is the quickest to use. The idea being i could just copy
and paste the records directly into the SQL Server database table (in
the same format) using the SQL Server Management Studio, for
example.



Trouble is, i have a problem with line feed characters. If an Excel
cell contains a chunk of text with line breaks (Chr(10) or Chr(13))
then the copy'n'paste doesn't work - only the text up to the first line
break is pasted into the SQL Server database cell. The rest is not
pasted for some reason.



I've tried with MS Access too, copying and pasting the contents of a
memo field into SQL Server database, but with exactly the same problem.
I've tried with 'text' or 'varchar' SQL Server database field formats.



Since i've no experience of using different types of databases
interacting together, can someone suggest the simplest way of
transferring the data without getting this problem with the line feeds?
I don't want to spend hours writing scripts/programs when it's just
this linefeed problem that is preventing the whole lot just being
cut'n'pasted in 5 seconds!



cheers

Dominic

View 6 Replies View Related

Convert Field From VarChar To Int With Speical Characters In Field

Aug 29, 2007

Hello,

I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.

How can I remove all speical characters and then convert that field to an int?

Also I tried the following query which did not work as well, same error about conversion.

UPDATE myTable SET field = CAST(field AS int)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved