Formula too long or…

Topic

New Reply

I am recreating Report from Crystal.
Crystal had this formula entered and I had adapted it for the Access and entered into Control Source of the text box.
However it gives me numbers instead of Names.

Can you see why? I must have this formula for each sub report. Is there another way to use it?

=IIf([Full/Part Time]=”F” And [Transfer]=0 And [New Hire]=0,([LAST NAME] & “,” & [FIRST NAME] & “-” & )) Or IIf([Transfer]=0 And [New Hire]=0,[LAST NAME] & “,” & [FIRST NAME] & ” (” & [FULL/PART TIME] & “) ” & “- ” & ) Or IIf([New Hire]=0,”~” & [LAST NAME] & “,” & [FIRST NAME] & “-” & [Prior SUB] & ” to ” & ,”+” & [LAST NAME] & “,” & [FIRST NAME] & ” (” & [FULL/PART TIME] & “) ” & “- ” & )

***************************8

Thanks

Reply | Quote

Replies

The maximum length of an expression varies depending on the verison of Access you are using. See specifications in the Help files for details about your version. One way to solve the issue is to put that expression as a field in the query that the subreport is based on. Try that and see if it solves your problem.

Reply | Quote

I am using Access 2003.

Where do I enter in a query? Thanks

Reply | Quote

I am using Access 2003.

Where do I enter in a query? Thanks

I think WendellB means to enter the formula in the field of a query instead of entering it in the textbox on the form.

I don’t understand the formula. Can you explain in English words with you are trying to achieve ?
Can you post a stripped version of the db with only the tables and a few record with fictive data ?

Reply | Quote

I would love to – however I do not have an example – this is what is happening…

I took the formula from Crystal Report and I want to re-do it for Access Report.

What I am trying to do is following:

=IIf([Full/Part Time]=”F” And [Transfer]=0 And [New Hire]=0
*display Name with SUB A Joe, Doe – ERT
([LAST NAME] & “,” & [FIRST NAME] & “-” & ))

IIf([Transfer]=0 And [New Hire]=0
*display Name with FT or PT and SUB A like Joe, Doe (F) – ERT
[LAST NAME] & “,” & [FIRST NAME] & ” (” & [FULL/PART TIME] & “) ” & “- ” & )

Or IIf([New Hire]=0
*display Name with tilda in front ~ Joe, Doe – ERT to PRT
“~” & [LAST NAME] & “,” & [FIRST NAME] & “-” & [Prior SUB] & ” to ” & ,”+” & [LAST NAME] & “,” & [FIRST NAME] & ” (” & [FULL/PART TIME] & “) ” & “- ” & )

else
+” & [LAST NAME] & “,” & [FIRST NAME] & ” (” & [FULL/PART TIME] & “) ” & “- ” & )
*display Name like + Joe, Doe (F) – PRT
________________________________________________________

If this was not 100% accurate – below is Crystal’s formula:

if {TABLE1.FULL/PART TIME} = “F” and {TABLE1.Transfer} = 0 and {TABLE1.New Hire} = 0
then
{TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME} & “-” & {TABLE1.SUB LOB}
else
if {TABLE1.Transfer} = 0 and {TABLE1.New Hire} = 0
then
{TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME}& ” (” & {TABLE1.FULL/PART TIME} &”) “& “- ” & {TABLE1.SUB LOB}
else
if {TABLE1.New Hire} = 0
then
“~” & {TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME} & “-” & {TABLE1.Prior SUB} & ” to ” & {TABLE1.SUB LOB}
else
“+” &{TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME} & ” (” & {TABLE1.FULL/PART TIME} &”) “& “- ” & {TABLE1.SUB LOB}

*********THANKS SO MUCH!!!

Reply | Quote

This is an attempt to rewrite the formula with IIF’s.
I have break the formula in different columns to make it easy to write the formula
Can you check if Query1 is what you want ?

Reply | Quote

You people are awesome! I can’t thank you enough. THANKS A MILLION for your help!

Reply | Quote

I have one more task to complete that is strange.

I need to color those names
those with tilda in front should be pink, those with plus sign – purple. Is it doable? Thanks

Reply | Quote

those with tilda in front should be pink, those with plus sign – purple. Is it doable? Thanks

This is possible in a form or report, with conditional formatting for the textbox for Result

Reply | Quote

Thanks, it is awesome!

I want to report a slight problem.

The original query from Crystal looked like following:

if {TABLE1.FULL/PART TIME} = “F” and {TABLE1.Transfer} = 0 and {TABLE1.New Hire} = 0
then
{TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME} & “-” &{TABLE1.SUB LOB}

else if
{TABLE1.Transfer} = 0 and {TABLE1.New Hire} = 0
then
{TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME}& ” (” & {TABLE1.FULL/PART TIME} &”) “& “- ” & {TABLE1.SUB LOB}

else if
{TABLE1.New Hire} = 0 then
“~” & {TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME} & “-” & {TABLE1.Prior SUB} & ” to ” & {TABLE1.SUB LOB}

else
“+” &{TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME} & ” (” & {TABLE1.FULL/PART TIME} &”) “& “- ” &{TABLE1.SUB LOB}

************************************************************************

When we re-did it for the Access we lost else if and else and now I am getting records that are both – tilda and plus etc…

When I am using OR – it makes my names into -1 for some reason.

I think Crystal gets each part as a remander of the previous.
Like a cieve effect. It extracts following portion from left over data after first portion was extracted.
Am I confusing you completely? Sorry.

Reply | Quote

Am I confusing you completely?

Yes I’m confused.
Nested IIf statements are the same as an If…Then…Else If…Then…Else…End If
I don’t understand why you want to use Or. You can’t put an Or between the different IIf statements. You can only use Or in the condition part of the IIf’s.

and now I am getting records that are both – tilda and plus etc

Can you provide sample data where the formula goes wrong ?

Reply | Quote

My apology, I was wrong. I forgot some conditions I had entered manually and it made your formula seems off.

How do I enter into conditional formatting that
If Name has + – color it green? What do I chose – Expression Is field or Field Value Is?

Thanks so much and good week to all!!!

Reply | Quote

Doesn’t Francois’ reply [post=”767771″]Post 767771[/post] higher up in this topic answer that?

Reply | Quote

Sorry, it did, exactly! Thanks so much.

Reply | Quote

I am using Access 2003.

Where do I enter in a query? Thanks

Each of your subreports must have a data source – either Table1 or an embedded or saved query. If it’s a table click the builder button and create a query that has all of the fields of Table1. Then paste the following into an empty column at the end of the query:

MyControl: IIf([Full/Part Time]=”F” And [Transfer]=0 And [New Hire]=0,([LAST NAME] & “,” & [FIRST NAME] & “-” & )) Or IIf([Transfer]=0 And [New Hire]=0,[LAST NAME] & “,” & [FIRST NAME] & ” (” & [FULL/PART TIME] & “) ” & “- ” & ) Or IIf([New Hire]=0,”~” & [LAST NAME] & “,” & [FIRST NAME] & “-” & [Prior SUB] & ” to ” & ,”+” & [LAST NAME] & “,” & [FIRST NAME] & ” (” & [FULL/PART TIME] & “) ” & “- ” & )

Then run the query and you should see the data that will be created by the query. Then set the Data Source for the control on the subreport to be MyControl

Reply | Quote