Newbie: Using Left in query for report?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

A co-worker and I are muddling our way through Access to try and build a
report. I do Word, Excel, and VBA, and have tied myself in knots with
Access before; he does PASCAL and a few others. But we're both stumped on
this one.

We're building the query which will return the values that will show in the
report. One field has a text value, and we only want to see one letter of
that filed in the report. For example, if it's THIS, we want to see "T"; if
it's THAT, we want to see "H".

We've tried using IIF and LEFT in Criteria in the query, but it's not
working; we only get blanks. I presume this is because we're querying that
field for the one letter, but the field has the whole word. Needless to
say, we quickly ran our limited supply of ideas to the bottom. Any chance
of a drop-kick in the right direction?

TIA
Ed
 
Ed said:
We're building the query which will return the values that will show in the
report. One field has a text value, and we only want to see one letter of
that filed in the report. For example, if it's THIS, we want to see "T"; if
it's THAT, we want to see "H".
Click to expand...

What determines the letter to show? You've given too small a sample to
help.
 
A co-worker and I are muddling our way through Access to try and build a
report. I do Word, Excel, and VBA, and have tied myself in knots with
Access before; he does PASCAL and a few others. But we're both stumped on
this one.

We're building the query which will return the values that will show in the
report. One field has a text value, and we only want to see one letter of
that filed in the report. For example, if it's THIS, we want to see "T"; if
it's THAT, we want to see "H".

We've tried using IIF and LEFT in Criteria in the query, but it's not
working; we only get blanks. I presume this is because we're querying that
field for the one letter, but the field has the whole word. Needless to
say, we quickly ran our limited supply of ideas to the bottom. Any chance
of a drop-kick in the right direction?

TIA
Ed
Click to expand...

Ed,
You've got me confused.
In your second paragraph, you state that you have a field with a text
value and you wish to see just one letter of that value in the report.

In the next paragraph, you talk of using 'IIf and Left in the criteria
in the query'.

The criteria of a query restricts the number of returned records to
those that match the criteria.

I think what you mean to do is print the one letter in the report.
If so, add an unbound control to the report.
Set it's control source to:
=IIf([FieldName]="This","T", IIf([FieldName]= "That","H","Neither This
nor That"))

You could also use the IIf in the query, instead of in the report:
NewColumn:IIf([FieldName]="This","T", IIf([FieldName]=
"That","H","Neither This nor That"))

There are other ways to do the above, depending upon the number of
possible choices. I've used 3 possibilities (it's This or That or
neither) to show how it's done for just a few choices. Other ways
include using the Switch() or Choose() functions or, using code,
Select Case.
Look those up in VBA Help files.
 
Fred: You think I confused *you* - you should have seen how I confused
*US*! 8>) But I think you've given us what we need. Thank you so much.

Ed

fredg said:
A co-worker and I are muddling our way through Access to try and build a
report. I do Word, Excel, and VBA, and have tied myself in knots with
Access before; he does PASCAL and a few others. But we're both stumped on
this one.

We're building the query which will return the values that will show in the
report. One field has a text value, and we only want to see one letter of
that filed in the report. For example, if it's THIS, we want to see "T"; if
it's THAT, we want to see "H".

We've tried using IIF and LEFT in Criteria in the query, but it's not
working; we only get blanks. I presume this is because we're querying that
field for the one letter, but the field has the whole word. Needless to
say, we quickly ran our limited supply of ideas to the bottom. Any chance
of a drop-kick in the right direction?

TIA
Ed
Click to expand...

Ed,
You've got me confused.
In your second paragraph, you state that you have a field with a text
value and you wish to see just one letter of that value in the report.

In the next paragraph, you talk of using 'IIf and Left in the criteria
in the query'.

The criteria of a query restricts the number of returned records to
those that match the criteria.

I think what you mean to do is print the one letter in the report.
If so, add an unbound control to the report.
Set it's control source to:
=IIf([FieldName]="This","T", IIf([FieldName]= "That","H","Neither This
nor That"))

You could also use the IIf in the query, instead of in the report:
NewColumn:IIf([FieldName]="This","T", IIf([FieldName]=
"That","H","Neither This nor That"))

There are other ways to do the above, depending upon the number of
possible choices. I've used 3 possibilities (it's This or That or
neither) to show how it's done for just a few choices. Other ways
include using the Switch() or Choose() functions or, using code,
Select Case.
Look those up in VBA Help files.
Click to expand...
 
Back
Top