Using IIF and TRIM together in a query

[Karen]

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.

 

[Van T. Dinh]

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?

 

[Jeff Boyce]

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]

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.

 

[Karen]

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

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

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.

 

[Van T. Dinh]

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.

 

[Jeff Boyce]

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

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

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

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.

 

[JK]

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

 

[Karen]

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.

 

[JK]

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

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.

 

[Karen]

Thank JK, works perfectly!
--
Thanks, Karen

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

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.

 

[JK]

My pleasuer

Thank JK, works perfectly!
--
Thanks, Karen

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

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.