Using IIF and TRIM together in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to use a query to trim the data in a table while using the IIF
statement at the same time. How do I write the expression using a field
called DBE % to get this info?

If the DBE% field has 3 digits, then show it. If the DBE% field has less
than 3 digits, then trim and show me the 2 right digits. All else, show
nothing (i.e., " ").

All help is appreciated.
 
Please post the Field Type and Field Size of the field [DBE%].

Do you want the trimmed value to display in the datasheet view of the Query
or do you actually want to change the data in the Table?
 
Karen

You might get some argument about your definition of nothing (" ") -- you
showed a blank or space, but nothing could also be a zero-length string
(""), or even a Null.

You might use something like the following (your syntax may vary):

IIF(Len([DBE%])=3,[DBE%],IIF(Len...

By the way, if the length of DBE% is less than three, how do you propose to
"trim" it to the right-most 2 characters?<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Currently its for text and allows for 3.
No, I won't change the table data. I want it to show in the query result.
--
Thanks, Karen


Van T. Dinh said:
Please post the Field Type and Field Size of the field [DBE%].

Do you want the trimmed value to display in the datasheet view of the Query
or do you actually want to change the data in the Table?

--
HTH
Van T. Dinh
MVP (Access)



Karen said:
I'd like to use a query to trim the data in a table while using the IIF
statement at the same time. How do I write the expression using a field
called DBE % to get this info?

If the DBE% field has 3 digits, then show it. If the DBE% field has less
than 3 digits, then trim and show me the 2 right digits. All else, show
nothing (i.e., " ").

All help is appreciated.
Click to expand...
Click to expand...
 
It is a text field that allows for 3 digits.
--
Thanks, Karen


Jeff Boyce said:
Karen

You might get some argument about your definition of nothing (" ") -- you
showed a blank or space, but nothing could also be a zero-length string
(""), or even a Null.

You might use something like the following (your syntax may vary):

IIF(Len([DBE%])=3,[DBE%],IIF(Len...

By the way, if the length of DBE% is less than three, how do you propose to
"trim" it to the right-most 2 characters?<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

Karen said:
I'd like to use a query to trim the data in a table while using the IIF
statement at the same time. How do I write the expression using a field
called DBE % to get this info?

If the DBE% field has 3 digits, then show it. If the DBE% field has less
than 3 digits, then trim and show me the 2 right digits. All else, show
nothing (i.e., " ").

All help is appreciated.
Click to expand...
Click to expand...
 
SELECT
IIf(Val([DBE%]) >= 100, [DBE%],
IIf(Val([DBE%] >= 10, Format(Val([DBE%], "0"), ""))

(I made a few assumptions on what you meant)

Examples of data and output:

Data Output
999 999
100 100
99 99
099 99
10 10
010 10
009 {Empty String}

09 {Empty String}
9 {Empty String}

--
HTH
Van T. Dinh
MVP (Access)



Karen said:
Currently its for text and allows for 3.
No, I won't change the table data. I want it to show in the query result.
--
Thanks, Karen


Van T. Dinh said:
Please post the Field Type and Field Size of the field [DBE%].

Do you want the trimmed value to display in the datasheet view of the
Query
or do you actually want to change the data in the Table?

--
HTH
Van T. Dinh
MVP (Access)



Karen said:
I'd like to use a query to trim the data in a table while using the IIF
statement at the same time. How do I write the expression using a
field
called DBE % to get this info?

If the DBE% field has 3 digits, then show it. If the DBE% field has
less
than 3 digits, then trim and show me the 2 right digits. All else,
show
nothing (i.e., " ").

All help is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Karen

Your original post mentioned trimming a string that was less that 3
characters to the right-most two characters. That's what I was asking
about.

Review my first response. You can adapt the IIF() statement to handle
strings of length 3, 2, 1 or zero.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Karen said:
It is a text field that allows for 3 digits.
--
Thanks, Karen


Jeff Boyce said:
Karen

You might get some argument about your definition of nothing (" ") -- you
showed a blank or space, but nothing could also be a zero-length string
(""), or even a Null.

You might use something like the following (your syntax may vary):

IIF(Len([DBE%])=3,[DBE%],IIF(Len...

By the way, if the length of DBE% is less than three, how do you propose
to
"trim" it to the right-most 2 characters?<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

Karen said:
I'd like to use a query to trim the data in a table while using the IIF
statement at the same time. How do I write the expression using a
field
called DBE % to get this info?

If the DBE% field has 3 digits, then show it. If the DBE% field has
less
than 3 digits, then trim and show me the 2 right digits. All else,
show
nothing (i.e., " ").

All help is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Karen,

I believe that this is what you are looking for:

IIf(DCount("[DBE%]","yrTable","Len([DBE%])=" & 2)>0,Left([DBE%],2),[DBE%])

Regards/JK
 
Thanks Van. I was a bit unclear initially I think.

Assuming there's always three digits in the field, if there are any
preceding zeros there, trim them. So, if it has 009, show 9; if 100, show
100; if 010, show 10.

--
Thanks, Karen


Van T. Dinh said:
SELECT
IIf(Val([DBE%]) >= 100, [DBE%],
IIf(Val([DBE%] >= 10, Format(Val([DBE%], "0"), ""))

(I made a few assumptions on what you meant)

Examples of data and output:

Data Output
999 999
100 100
99 99
099 99
10 10
010 10
009 {Empty String}

09 {Empty String}
9 {Empty String}

--
HTH
Van T. Dinh
MVP (Access)



Karen said:
Currently its for text and allows for 3.
No, I won't change the table data. I want it to show in the query result.
--
Thanks, Karen


Van T. Dinh said:
Please post the Field Type and Field Size of the field [DBE%].

Do you want the trimmed value to display in the datasheet view of the
Query
or do you actually want to change the data in the Table?

--
HTH
Van T. Dinh
MVP (Access)



I'd like to use a query to trim the data in a table while using the IIF
statement at the same time. How do I write the expression using a
field
called DBE % to get this info?

If the DBE% field has 3 digits, then show it. If the DBE% field has
less
than 3 digits, then trim and show me the 2 right digits. All else,
show
nothing (i.e., " ").

All help is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Keren,

Sorry I misunderstood you below, If all you want to trim leading zeros *no*
IF statement is require

Val(Nz(DBE%],"0")) will give you a numeric field
Format(Val(Nz([DBE%],"0")),"0") will give you the same as string

Regards/JK


Karen said:
Thanks Van. I was a bit unclear initially I think.

Assuming there's always three digits in the field, if there are any
preceding zeros there, trim them. So, if it has 009, show 9; if 100, show
100; if 010, show 10.

--
Thanks, Karen


Van T. Dinh said:
SELECT
IIf(Val([DBE%]) >= 100, [DBE%],
IIf(Val([DBE%] >= 10, Format(Val([DBE%], "0"), ""))

(I made a few assumptions on what you meant)

Examples of data and output:

Data Output
999 999
100 100
99 99
099 99
10 10
010 10
009 {Empty String}

09 {Empty String}
9 {Empty String}

--
HTH
Van T. Dinh
MVP (Access)



Karen said:
Currently its for text and allows for 3.
No, I won't change the table data. I want it to show in the query
result.
--
Thanks, Karen


:

Please post the Field Type and Field Size of the field [DBE%].

Do you want the trimmed value to display in the datasheet view of the
Query
or do you actually want to change the data in the Table?

--
HTH
Van T. Dinh
MVP (Access)



I'd like to use a query to trim the data in a table while using the
IIF
statement at the same time. How do I write the expression using a
field
called DBE % to get this info?

If the DBE% field has 3 digits, then show it. If the DBE% field has
less
than 3 digits, then trim and show me the 2 right digits. All else,
show
nothing (i.e., " ").

All help is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Thank JK, works perfectly!
--
Thanks, Karen


JK said:
Keren,

Sorry I misunderstood you below, If all you want to trim leading zeros *no*
IF statement is require

Val(Nz(DBE%],"0")) will give you a numeric field
Format(Val(Nz([DBE%],"0")),"0") will give you the same as string

Regards/JK


Karen said:
Thanks Van. I was a bit unclear initially I think.

Assuming there's always three digits in the field, if there are any
preceding zeros there, trim them. So, if it has 009, show 9; if 100, show
100; if 010, show 10.

--
Thanks, Karen


Van T. Dinh said:
SELECT
IIf(Val([DBE%]) >= 100, [DBE%],
IIf(Val([DBE%] >= 10, Format(Val([DBE%], "0"), ""))

(I made a few assumptions on what you meant)

Examples of data and output:

Data Output
999 999
100 100
99 99
099 99
10 10
010 10
009 {Empty String}

09 {Empty String}
9 {Empty String}

--
HTH
Van T. Dinh
MVP (Access)



Currently its for text and allows for 3.
No, I won't change the table data. I want it to show in the query
result.
--
Thanks, Karen


:

Please post the Field Type and Field Size of the field [DBE%].

Do you want the trimmed value to display in the datasheet view of the
Query
or do you actually want to change the data in the Table?

--
HTH
Van T. Dinh
MVP (Access)



I'd like to use a query to trim the data in a table while using the
IIF
statement at the same time. How do I write the expression using a
field
called DBE % to get this info?

If the DBE% field has 3 digits, then show it. If the DBE% field has
less
than 3 digits, then trim and show me the 2 right digits. All else,
show
nothing (i.e., " ").

All help is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
My pleasuer

Karen said:
Thank JK, works perfectly!
--
Thanks, Karen


JK said:
Keren,

Sorry I misunderstood you below, If all you want to trim leading zeros
*no*
IF statement is require

Val(Nz(DBE%],"0")) will give you a numeric field
Format(Val(Nz([DBE%],"0")),"0") will give you the same as string

Regards/JK


Karen said:
Thanks Van. I was a bit unclear initially I think.

Assuming there's always three digits in the field, if there are any
preceding zeros there, trim them. So, if it has 009, show 9; if 100,
show
100; if 010, show 10.

--
Thanks, Karen


:

SELECT
IIf(Val([DBE%]) >= 100, [DBE%],
IIf(Val([DBE%] >= 10, Format(Val([DBE%], "0"), ""))

(I made a few assumptions on what you meant)

Examples of data and output:

Data Output
999 999
100 100
99 99
099 99
10 10
010 10
009 {Empty String}

09 {Empty String}
9 {Empty String}

--
HTH
Van T. Dinh
MVP (Access)



Currently its for text and allows for 3.
No, I won't change the table data. I want it to show in the query
result.
--
Thanks, Karen


:

Please post the Field Type and Field Size of the field [DBE%].

Do you want the trimmed value to display in the datasheet view of
the
Query
or do you actually want to change the data in the Table?

--
HTH
Van T. Dinh
MVP (Access)



I'd like to use a query to trim the data in a table while using
the
IIF
statement at the same time. How do I write the expression using
a
field
called DBE % to get this info?

If the DBE% field has 3 digits, then show it. If the DBE% field
has
less
than 3 digits, then trim and show me the 2 right digits. All
else,
show
nothing (i.e., " ").

All help is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top