Reading from one field and printing results in another.

[kjspear]

Hello everyone,

I have a question about using the iif statement. I imported a CSV file into a table in Access. No problems with the import. But one of the fields contain a persons non-schedule work days. The field has seven characters 'Y' and 'N', where 'Y' represents the non-schedule day. The format in this field is 'NYYNNNN'. Of course days vary depending on the person. What I'm trying to do is create another field that would print out the days. In other words, the new field would check the non-schedule field and depending on where the 'Y' is positioned for example the second letter is a 'Y' , that would print Sunday and the next 'Y' found it would print Monday. So basically its where the 'Y' is positioned. Here the begining of the week starts on a Saturday. Is using the iif statement the correct statement to use? Or is there a better way?

Thank you.

 

[Remou]

Perhaps:

Function ScheduleDays(strFld)

astrDays = Split("Sun,Mon,Tue,Wed,Thu,Fri,Sat", ",")

If Len(strFld) <> 7 Then
    'Problem
Else
    For i = 0 To 6
        If Mid(strFld, i + 1, 1) = "Y" Then
            strDayList = strDayList & "," & astrDays(i)
        End If
    Next
End If

ScheduleDays = Mid(strDayList, 2)
End Function

 

[TheAceMan1]

How are ya kjspear . . .

Perhaps this:

[blue]Public Function NonSchedDays(strDays As String)
   Dim Dy As String, Pack As String, idx As Integer
   
   For idx = 1 To Len(strDays)
      Dy = Choose(idx, "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri")
      
      If Mid(strDays, idx, 1) = "Y" Then
         If Pack <> "" Then
            Pack = Pack & ", " & Dy
         Else
            Pack = Dy
         End If
      End If
   Next
   
   NonSchedDays = Pack
   
End Function[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[kjspear]

That worked.

Thanks a million!

 

[Aaswi]

Thanx alot TheAceMan1 and all other technical personnel who replied. I have tried the refined codes of TheACEMan1, but i still have the problem. Actually i want to apply filter on all of the employees in a particular scheme. It works fine for small schemes and give the above Runtime error msg when employees list exceeds. I don;t know the allowable quota of Filter.
Please help.
Thanx again