asked on

Insert the same field twice (continued)

This question follows this one: https://www.experts-exchange.com/questions/21837369/Insert-the-same-field-twice.html

I have a date field in my report and I want it to look like this:

John 12 1/1/01 13 1/3/01
Bill 12 2/1/03
Tom 13 3/1/04

Is this possible?
Frodoman maybe you can help again?:)

Thanks

Mike McCracken

You could try this

Add a group to the report on the NAME field
Add a sort on the number field with the 12s and 13s
Add formulas to the report

In the report header
Name - DeclVars
Formula
Global StringVar G_Footer := '';

In the group header
Name - ResetVars
Formula
Global StringVar G_Footer;
G_Footer := '';

In the group
Name - BuildFooter
Formula
Global StringVar G_Footer;
if {NumberField} = 12 then
G_Footer := "12 " & {DateField};

if {NumberField} = 13 then
if G_Footer = '' then
G_Footer = " 13 " & {DateField}
else
G_Footer = G_Footer & " 13 " & {DateField};
''

In the group footer
Name - DispFooter
Formula
Global StringVar G_Footer;
G_Footer

Group footer will have
{NameFIeld} {@DispFooter}

mlmcc

Mike McCracken

Add WhilePrintingRecords; as the first line of each function

In the report header
Name - DeclVars
Formula
WhilePrintingRecords;
Global StringVar G_Footer := '';

mlmcc

cadkins

ASKER

I did exactly what you told me but it only shows the 12s and their dates.
One other thing. Even if this works my understanding is that everything will be in one column (e.g. 12 1/1/01 13 1/3/01). Is it possible to have 4 columns? I want to export this report to excel and it will be more helpful if that data is in 4 columns instead of 1.

Thanks!

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cadkins

ASKER

I get 4 columns now but it still shows only the 12s and their dates(the first 2 columns...3rd and 4th column are always empty).
And something else...i used mm/dd/yyyy and I always get 00 for mm.(eg. 00/12/2004)
Thanks

Mike McCracken

Try MM or nn. mm may be for minutes

Did you get any 13s in the string or was it always 12 date nothing or just nothing?

Are the 12 and 13 in the same field?

mlmcc

Mike McCracken

Can there be multiple 12s or 13s for a given name?

What SQL are you using?

mlmcc

cadkins

ASKER

MM works fine.
I get 12 & date or nothing (if there is no 12).
12 and 13 are in the same field.
There can be only one 12 and one 13 for a given name.
I am using SQL Server 2000.

Thanks

cadkins

ASKER

I figured out how to do it in a completely different way! I still gave you the points because you tried to help me:)

Thanks!

Mike McCracken

What worked?

Glad i could help

mlmcc