If Statement

[LDA via AccessMonster.com]

Hello,
I need help writing the following for my report
I am trying to relace the ReportedResult field with BRL if it is less than
the RDL (Reported Result is a text field and rdl is a number field.)

Under the Print Event I have :

Me.ReportedResult="*<" Then
Me. ReportedResult = "BRL"

this did not work

I also tried in the conditional statement in the query under ReportedResult:

If([ReportedResult]="*<")"BRL")

which also didn't work....

Any suggestions are help is appreciated..

Thanks

 

[Duane Hookom]

Is BRL a field or is it a hard-coded string?
Can all ReportedResult field values be coverted to numbers? If they are all
numeric and BRL is a field, you might want to try:

=IIf(Val([ReportedResult])<[RDL],[BRL],[ReportedResult])

 

[Noëlla Gabriël]

Hi,

if you want to use the if function in a query in Access, you need the IIf
function:
syntax:
Iif(test, value if true, value if false)

 

[misschanda via AccessMonster.com]

thanks for the reply Duane
BRL is the text I want to replace ReportedREsult with if it is a number
proceed by the less than symbol so for example <0.001.
The ReportedResult is a text field and can't be converted to a number field,
due to legacy data.

Could I try in a textbox
=IIF(Val([reportedresult])="*<", [ReportedREsult]="BRL")??

Is BRL a field or is it a hard-coded string?
Can all ReportedResult field values be coverted to numbers? If they are all
numeric and BRL is a field, you might want to try:

=IIf(Val([ReportedResult])<[RDL],[BRL],[ReportedResult])

Hello,
I need help writing the following for my report

[quoted text clipped - 17 lines]

Thanks

 

[misschanda via AccessMonster.com]

Thanks for the reply.
However I don't follow...
I am trying to replace the field [reportedresults] when the produce value is
for example <0.001, or basically whenever it has a less than...

Hi,

if you want to use the if function in a query in Access, you need the IIf
function:
syntax:
Iif(test, value if true, value if false)

Hello,
I need help writing the following for my report

[quoted text clipped - 17 lines]

Thanks

 

[Noëlla Gabriël]

Hi,

example: the following syntax:

Iif([reportedresults] < 0.001, 100, [reportedresults])

will give the value 100 if the value in the field [reportedresults] is less
then 0.001, if not it returns the value of the field [reportedresults]

so if [reportedresults] = 0.000005 or 0 or -10 it will return 100
if [reportedresults] = 15 it will return 15

Hope this helps
--
Kind regards
Noëlla

Thanks for the reply.
However I don't follow...
I am trying to replace the field [reportedresults] when the produce value is
for example <0.001, or basically whenever it has a less than...

Hi,

if you want to use the if function in a query in Access, you need the IIf
function:
syntax:
Iif(test, value if true, value if false)

Hello,
I need help writing the following for my report

[quoted text clipped - 17 lines]

Thanks

 

[misschanda via AccessMonster.com]

Please be patience with my lack of skill

I incorporated the following:

=IIf([ReportedResult]="*<","BRL",[ReportedResult]).

I used "*<" because the value isn't always 0.001 or etc.
I changedd 100 to BRL b/c thats what I want it to read.

When I ran the query the 5 records I returned earlier were decreased to one...
The only record that had a < ( I still would like all records to return)
Also the text BRL did not replace the <.

Hi,

example: the following syntax:

Iif([reportedresults] < 0.001, 100, [reportedresults])

will give the value 100 if the value in the field [reportedresults] is less
then 0.001, if not it returns the value of the field [reportedresults]

so if [reportedresults] = 0.000005 or 0 or -10 it will return 100
if [reportedresults] = 15 it will return 15

Hope this helps

Thanks for the reply.
However I don't follow...

[quoted text clipped - 12 lines]

 

[Duane Hookom]

Where are you using this expression? Is this in a criteria in a query (this
is a query news group) or a field in a query or a control source in a text
box?

If this is for a text box, I would use:
Control Source:
=IIf(Left([ReportedResult],2)="*<","BRL",Val([ReportedResult]))

--
Duane Hookom
Microsoft Access MVP

Please be patience with my lack of skill

I incorporated the following:

=IIf([ReportedResult]="*<","BRL",[ReportedResult]).

I used "*<" because the value isn't always 0.001 or etc.
I changedd 100 to BRL b/c thats what I want it to read.

When I ran the query the 5 records I returned earlier were decreased to one...
The only record that had a < ( I still would like all records to return)
Also the text BRL did not replace the <.

Hi,

example: the following syntax:

Iif([reportedresults] < 0.001, 100, [reportedresults])

will give the value 100 if the value in the field [reportedresults] is less
then 0.001, if not it returns the value of the field [reportedresults]

so if [reportedresults] = 0.000005 or 0 or -10 it will return 100
if [reportedresults] = 15 it will return 15

Hope this helps

Thanks for the reply.
However I don't follow...

[quoted text clipped - 12 lines]

Thanks

 

[misschanda via AccessMonster.com]

I placed the expression in the query under the field.

Once i placed it in and executed the query I recevied the error Data Type
Mismatch.

Where are you using this expression? Is this in a criteria in a query (this
is a query news group) or a field in a query or a control source in a text
box?

If this is for a text box, I would use:
Control Source:
=IIf(Left([ReportedResult],2)="*<","BRL",Val([ReportedResult]))

Please be patience with my lack of skill

[quoted text clipped - 27 lines]

 

[Duane Hookom]

When I read "under the field", it suggests in the criteria of the query.

Do you actually want to filter/limit the records in the query? I think you
actually want to substitute one value for another in the returned records.

If you want to replace a value, then don't put the expression in the
criteria, create a new column in the query with something like:

NewReportedResult: IIf(Left([ReportedResult],2)="*<","BRL",[ReportedResult])


--
Duane Hookom
Microsoft Access MVP

I placed the expression in the query under the field.

Once i placed it in and executed the query I recevied the error Data Type
Mismatch.

Where are you using this expression? Is this in a criteria in a query (this
is a query news group) or a field in a query or a control source in a text
box?

If this is for a text box, I would use:
Control Source:
=IIf(Left([ReportedResult],2)="*<","BRL",Val([ReportedResult]))

Please be patience with my lack of skill

[quoted text clipped - 27 lines]

Thanks

 

[misschanda via AccessMonster.com]

I have tried your suggestion and in Microsoft ACCESS 2007, I am not getting
the desired result..
The new column is just repeating the field of ReportResult.

When I read "under the field", it suggests in the criteria of the query.

Do you actually want to filter/limit the records in the query? I think you
actually want to substitute one value for another in the returned records.

If you want to replace a value, then don't put the expression in the
criteria, create a new column in the query with something like:

NewReportedResult: IIf(Left([ReportedResult],2)="*<","BRL",[ReportedResult])

I placed the expression in the query under the field.

[quoted text clipped - 14 lines]

 

[Duane Hookom]

Please respond with your answer to my question:"Do you actually want to
filter/limit the records in the query? I think you actually want to
substitute one value for another in the returned records."

Then, please type in about 5-10 values from the ReportResult field and the
value you would like to see, for example:
ReportResult I Want
======== =======
4.4444 4.4444
<1000 BRL
11234.4 11234.4
<1 BRL

--
Duane Hookom
Microsoft Access MVP

I have tried your suggestion and in Microsoft ACCESS 2007, I am not getting
the desired result..
The new column is just repeating the field of ReportResult.

When I read "under the field", it suggests in the criteria of the query.

Do you actually want to filter/limit the records in the query? I think you
actually want to substitute one value for another in the returned records.

If you want to replace a value, then don't put the expression in the
criteria, create a new column in the query with something like:

NewReportedResult: IIf(Left([ReportedResult],2)="*<","BRL",[ReportedResult])

I placed the expression in the query under the field.

[quoted text clipped - 14 lines]

Thanks

 

[misschanda via AccessMonster.com]

You are correct. I want to subsitute the value for another in the returned
records.

The values I have now are:


ReportResult I Want

======== =======
.2
<0.01 BRL
.31
<.45 BRL

thanks

Please respond with your answer to my question:"Do you actually want to
filter/limit the records in the query? I think you actually want to
substitute one value for another in the returned records."

Then, please type in about 5-10 values from the ReportResult field and the
value you would like to see, for example:
ReportResult I Want
======== =======
4.4444 4.4444
<1000 BRL
11234.4 11234.4
<1 BRL

I have tried your suggestion and in Microsoft ACCESS 2007, I am not getting
the desired result..

[quoted text clipped - 15 lines]

 

[Duane Hookom]

You can create a new column/field in you query with an expression like:
NewReportedResult: IIf(Left([ReportedResult],1)="<","BRL",Null)

You could also use the expression in the control source of a text box on
your report:
=IIf(Left([ReportedResult],1)="<","BRL",Null)

Are you sure you don't wan to display anything if the ReportedResult doesn't
begin with "<"?

--
Duane Hookom
Microsoft Access MVP

You are correct. I want to subsitute the value for another in the returned
records.

The values I have now are:


ReportResult I Want

======== =======
.2
<0.01 BRL
.31
<.45 BRL

thanks

Please respond with your answer to my question:"Do you actually want to
filter/limit the records in the query? I think you actually want to
substitute one value for another in the returned records."

Then, please type in about 5-10 values from the ReportResult field and the
value you would like to see, for example:
ReportResult I Want
======== =======
4.4444 4.4444
<1000 BRL
11234.4 11234.4
<1 BRL

I have tried your suggestion and in Microsoft ACCESS 2007, I am not getting
the desired result..

[quoted text clipped - 15 lines]

Thanks

 

[misschanda via AccessMonster.com]

If it doesn't begin with < I would like for the value to stay as is...

so if it was 1.34 it will remain 1.34... I just want the code to change
things that are <X.XX.

thanks again for all your help...

You can create a new column/field in you query with an expression like:
NewReportedResult: IIf(Left([ReportedResult],1)="<","BRL",Null)

You could also use the expression in the control source of a text box on
your report:
=IIf(Left([ReportedResult],1)="<","BRL",Null)

Are you sure you don't wan to display anything if the ReportedResult doesn't
begin with "<"?

You are correct. I want to subsitute the value for another in the returned
records.

[quoted text clipped - 28 lines]

 

[Duane Hookom]

So you don't actually want:
ReportResult I Want
======== =======
.2
<0.01 BRL
.31
<.45 BRL

You do want this?
ReportResult I Want
======== =======
.2 .2
<0.01 BRL
.31 .31
<.45 BRL

If so, try this expression in your query as a new column:
NewResult: IIf(Left([ReportedResult],1)="<","BRL",[ReportedResult])

--
Duane Hookom
Microsoft Access MVP

If it doesn't begin with < I would like for the value to stay as is...

so if it was 1.34 it will remain 1.34... I just want the code to change
things that are <X.XX.

thanks again for all your help...

You can create a new column/field in you query with an expression like:
NewReportedResult: IIf(Left([ReportedResult],1)="<","BRL",Null)

You could also use the expression in the control source of a text box on
your report:
=IIf(Left([ReportedResult],1)="<","BRL",Null)

Are you sure you don't wan to display anything if the ReportedResult doesn't
begin with "<"?

You are correct. I want to subsitute the value for another in the returned
records.

[quoted text clipped - 28 lines]

Thanks

 

[misschanda via AccessMonster.com]

it all worked!!!! thanks alot

So you don't actually want:
ReportResult I Want
======== =======
.2
<0.01 BRL
.31
<.45 BRL

You do want this?
ReportResult I Want
======== =======
.2 .2
<0.01 BRL
.31 .31
<.45 BRL

If so, try this expression in your query as a new column:
NewResult: IIf(Left([ReportedResult],1)="<","BRL",[ReportedResult])

If it doesn't begin with < I would like for the value to stay as is...

[quoted text clipped - 18 lines]