Newbie: Using Left in query for report?

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" wrote in message
...

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".

What determines the letter to show? You've given too small a sample to
help.

--
Joan Wild
Microsoft Access MVP

On Wed, 26 May 2004 14:03:40 -0700, Ed wrote:

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,
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
Please only reply to this newsgroup.
I do not reply to personal email.

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" wrote in message
news
On Wed, 26 May 2004 14:03:40 -0700, Ed wrote:

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,
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
Please only reply to this newsgroup.
I do not reply to personal email.