Function Question 1

[Noel2]

I have a problem where my function is not working as I would like it to. Below is the function:

Function freedajuice(ByVal strfld As String, strcd As String, strtru As String, strfls As String) As Variant
Dim gstrfld As String

gstrfld = Forms![daily_list]![strfld]

freedajuice = IIf((gstrfld) = strcd, strtru, strfls)

End Function

I am calling it using just a normal statement in the debug window saying:

?freedajuice("List4","","","&quot

What the error that I keep receiving is that Microsoft Access cannot find the field referred to in your expression. Please let me know if you have any ideas.

Thanks!
Noel

 

[SgtJarrow]

Try changin:

gstrfld = Forms![daily_list]![strfld]

to:

gstrfld = Forms("daily_list&quot(strfld)

This takes advantage of the INDEX properties a form exposes... ****************************
Only two things are infinite, the
universe and human stupidity, and
I'm not sure about the former.
(Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[Noel2]

Perfect! That worked really well. For the function I have updated it to reflect the following:


Function freedajuice(ByVal strjuci As String, strcd As String, strtru As String, strfls As String) As Variant
Dim gstrfld As String

gstrfld = Nz(Forms("daily_list&quot(strjuci))

freedajuice = IIf((gstrfld) = strcd, strtru, strfls)

End Function

The form that I am using has a date field named List4 and a name field named List9. Those put together create the record source for a box called List6. The code I have below is activated on Open and on update for each field on the Form. Do you know how to simplify this and make it faster? Here it is, I hope that you can help me with this:

Sub BuildRecSource()

Dim getRowSce As String
Dim getOrdrcls As String
Dim getWhrcls As String
Dim getCond As String
Dim stdCod As String
Dim stdCdm As String
Dim stdCCm As String
Dim stdWhrCls As String

If (Nz(Forms![daily_list]![List4])) = "" Then
stdCdm = ""
stdCCm = ""
If (Forms![daily_list]![List9]) = "All" Then
stdWhrCls = ""
Else
stdWhrCls = " WHERE "
End If
Else
stdCCm = &quot"
stdWhrCls = " WHERE "
If (Forms![daily_list]![List9]) = "All" Then
Else
stdCdm = "("
End If
End If

stdCod = freedajuice("List4", "", "", " AND &quot

getRowSce = "Select DISTINCTROW daily.id, daily.techid, daily.dt, daily.addr, daily.wrk_ordr_num, daily.dscrpt, daily.cde FROM daily"
getWhrcls = stdWhrCls & stdCdm & freedajuice("List4", "", "", "((daily.dt)=" & "#" & [Forms]![daily_list]![List4] & "#)&quot
getCond = freedajuice("List9", "All", "", stdCod & "(daily.techid)='" & ([Forms]![daily_list]![List9]) & "'" & stdCCm)
getOrdrcls = " ORDER BY daily.techid, daily.wrk_ordr_num;"

List6.RowSource = getRowSce & getWhrcls & getCond & getOrdrcls

End Sub

Thank you,
Noel