IF AND MID FORMULA DOESNT WORK-WHY??

[Guest]

I have a table named STOPS Test. In this table there is a column named DOS
which stands for days of service. In the DOS column, when ever service is
performed on SMTWTFS, the correct combination is returnded in that cell.

For example if service was performed on Sunday, Monday Wednesday and FRiday,
SM_W_F_ will show up in the DOS column. I am trying to capture sunday
service. As result, I am trying to write an expression in a query that will
count all of the "S" that appear in the serventh text position in column DOS.
This way I could see how many times we provided service on Saterday.

I wrote the below, however I keep getting errors and I dont know why:
I dont know what is the best way to capture Saterday service, maybe instead
of 1 I should tell the formula to sum? Please help.

Saterday: IIf(Mid([STOPS Test]![DOS],7,1)="S",1,"")


thank you in advance for those who offer assistance.

 

[Ken Snell \(MVP\)]

What error(s) do you get?

--

Ken Snell
<MS ACCESS MVP>

"Socal Analyst looking for help"

 

[John Vinson]

I have a table named STOPS Test. In this table there is a column named DOS
which stands for days of service. In the DOS column, when ever service is
performed on SMTWTFS, the correct combination is returnded in that cell.

For example if service was performed on Sunday, Monday Wednesday and FRiday,
SM_W_F_ will show up in the DOS column.

Are the days without service recorded with blanks, or with literal
underscore characters? If it's blanks, your Mid() function will fail,
because Access doesn't store trailing blanks.

If it's underscores, I don't understand why the Mid() isn't working!

John W. Vinson[MVP]

 

[Gary Walter]

I have a table named STOPS Test. In this table there is a column named DOS
which stands for days of service. In the DOS column, when ever service is
performed on SMTWTFS, the correct combination is returnded in that cell.

For example if service was performed on Sunday, Monday Wednesday and
FRiday,
SM_W_F_ will show up in the DOS column. I am trying to capture sunday
service. As result, I am trying to write an expression in a query that
will
count all of the "S" that appear in the serventh text position in column
DOS.
This way I could see how many times we provided service on Saterday.

I wrote the below, however I keep getting errors and I dont know why:
I dont know what is the best way to capture Saterday service, maybe
instead
of 1 I should tell the formula to sum? Please help.

Saterday: IIf(Mid([STOPS Test]![DOS],7,1)="S",1,"")

Hi,

Temporarily add criteria to your query

WHERE
Len([STOPS Test].[DOS]) >= 7

Do you get any errors now?

I may be thinking wrongly, but I don't
believe I would ever use MID on a variable
length field w/o first testing if I have the "length"
needed to complete the MID.

good luck,

gary

 

[Guest]

thank you for your response John. The days not serviced are recorded with
blanks. I used the underscore here for ease of understanding. Any
suggestions?

 

[John Vinson]

thank you for your response John. The days not serviced are recorded with
blanks. I used the underscore here for ease of understanding. Any
suggestions?

Well, using Mid() to extract the seventh byte of a six-byte-long
string will indeed get you an error. It's like taking the thirteenth
egg out of a dozen box!

Try

Mid([DOS] & " ", 7, 1)

that is, concatenate seven literal blanks to the string in DOS.


John W. Vinson[MVP]

 

[John Spencer]

John,

Pardon me, but I don't understand your statement about mid generating an
error.

If the start position is greater than the number of characters in the
string, Mid returns a zero-length string ("").

I know this doesn't directly apply to a solution to the post.

I see other problems with the DOS field. If the user is going to continue
to use this DOS to store multiple items of data I would suggest that he/she
add a terminating character to the string (for example, an underscore or
period for End) so that there is always an 8 character string consisting of
the day letters and spaces.

Currently if the field has S as the last character (^ represents a space)
that could be

S (Sunday only) or
^^^^^^^S (Saturday only)

and NOW I see why you are adding spaces to the DOS.

In the words of Emily Latella - "Never Mind"

thank you for your response John. The days not serviced are recorded with
blanks. I used the underscore here for ease of understanding. Any
suggestions?

Well, using Mid() to extract the seventh byte of a six-byte-long
string will indeed get you an error. It's like taking the thirteenth
egg out of a dozen box!

Try

Mid([DOS] & " ", 7, 1)

that is, concatenate seven literal blanks to the string in DOS.


John W. Vinson[MVP]