Cant get Function work?

[Tmat]

I have a drop down list of a few fields that populates text boxes on a from that no one
Sees I use these text boxes to populate a criteria field in a query. I am trying then to run the info in the text box through this function but my function is not correct and I cant get it to work. I don’t know if you can put the result right into the criteria query ? Maybe if it references the function. I figured I would put the result in another textbox (Text26) kind of redundant but don’t know know better. This is one version of what I tried running along with a few others that don’t work. Any help would be appreciated.

Public Function textbx26(Text24) As String
Dim strText26 As String
If Text24 = M Then Me!Text26 = "Monday"
ElseIf Text24 = T Then Me!Text26 = "Tuesday"
ElseIf Text24 = W Then Me!Text26 = "Wednesday"
ElseIf Text24 = R Then Me!Text26 = "Thursday"
ElseIf Text24 = F Then Me!Text26 = "Friday"
ElseIf Text24 = S Then Me!Text26 = "Saturday"
ElseIf Text24 = U Then Me!Text26 = "Sunday"
ElseIf Text24 = MW Then Me!Text26 = "Monday" And "Wednesday"
ElseIf Text24 = U Then Me!Text26 = "Tuesday" And "Thursday"
End If

 

[DougP]

Put this function in a Module not a form or report.

Public Function WhatDay(MyValue) As String

' Think of a function as a black box. You can pass
' several parameters to it and it returns an answer.
' It's best not to reference a specific control
' on a form or report
' Then you can use it anywhere in your database.
' Now to call it in your text24 box do this:
' = WhatDay(me!Text26) 'then text24 should have the correct information in it.

If MyValue = M Then WhatDay = "Monday"
ElseIf MyValue = T Then WhatDay = "Tuesday"
ElseIf MyValue = W Then WhatDay = "Wednesday"
ElseIf MyValue = R Then WhatDay = "Thursday"
ElseIf MyValue = f Then WhatDay = "Friday"
ElseIf MyValue = S Then WhatDay = "Saturday"
ElseIf MyValue = U Then WhatDay = "Sunday"
ElseIf MyValue = MW Then WhatDay = "Monday" And "Wednesday"
ElseIf MyValue = U Then WhatDay = "Tuesday" And "Thursday"
End If

End Function



DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[MichaelRed]

Just for ease of reading/understanding (not to mention typing) I would put Doug's logic in the 'Select Case' format.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Durkin]

There are a few things still wrong with this function. Firstly, when you say 'MyValue = M' you should be saying 'MyValue = "M"' otherwise the compiler thinks that M is a variable. (You should really have 'Option Explicit' set to avoid these errors - check it out in the help.) Secondly, when you pass in MW you want to return 'Monday and Wednesday', so put this string between double quotes otherwise you will get a type mismatch error. If you want to concatenate strings use the '&' operator. Finally, you need to structure your If statement better. In the first line of it, you use the single line structure of the If statement which has an implicit End If in it, so when the compiler gets to the next statement it has an Elseif with no starting If. The following routine should do the trick.

Public Function WhatDay(MyValue as String) As String

If MyValue = "M" Then
WhatDay = "Monday"
ElseIf MyValue = "T" Then
WhatDay = "Tuesday"
ElseIf MyValue = "W" Then
WhatDay = "Wednesday"
ElseIf MyValue = "R" Then
WhatDay = "Thursday"
ElseIf MyValue = "f" Then
WhatDay = "Friday"
ElseIf MyValue = "S" Then
WhatDay = "Saturday"
ElseIf MyValue = "U" Then
WhatDay = "Sunday"
ElseIf MyValue = "MW" Then
WhatDay = "Monday" & "Wednesday"
ElseIf MyValue = "U" Then
WhatDay = "Tuesday" & "Thursday"
End If

End Function
Durkin
alandurkin@bigpond.com