Struggling with where condition in expression

[gojets1721]

I've been using this guide to output a column of data in a table to a single comma separated string in a form's field. It works in general but I can't get the where condition to work.

Here's what I put in the control source of the field in the form:

=FieldAsSeparatedString("EmployeeName","tblEmployees","[ComplaintNumber] =" & [Me].[ComplaintNumber] & "")

I'm trying to use the wherecondition to filter the string down to the matching complaint numbers in the tblEmployees and the form's table. But I'm just getting a #Name error when using the above

Any suggestions on what to change?

 

[theDBguy]

You can't use [Me] in an expression. It only works in VBA.

Just as FYI, here's another option to do the same thing. Cheers!

Code Snippets

 

[gojets1721]

You can't use [Me] in an expression. It only works in VBA.

Just as FYI, here's another option to do the same thing. Cheers!

Code Snippets

Thank you! Getting rid of 'me' worked.

And I switched to that code instead and it worked flawlessly. The delimiter on the other was given me issues.

Thanks so much

 

[theDBguy]

Thank you! Getting rid of 'me' worked.

And I switched to that code instead and it worked flawlessly. The delimiter on the other was given me issues.

Thanks so much

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[gojets1721]

Hi. Glad to hear you got it sorted out. Good luck with your project.

Actually one issue is I'm having is if it's null, I want the field to display 'N/A'. I tried the below but it still is still blank if null. Any suggestions?

=Nz(SimpleCSV("SELECT EmployeeName FROM tblEmployees WHERE [ComplaintNumber] =" & [Forms]![frmComplaintDetails]![ComplaintNumber] & ""), "N/A")

 

[CJ_London]

depends on whether SimpleCSV returns a zls or can return a null. If it can't return a null, then the nz function won't have any effect. Either modify SimpleCSV to return a null or use iif

=iif(SimpleCSV("SELECT EmployeeName FROM tblEmployees WHERE [ComplaintNumber] =" & [Forms]![frmComplaintDetails]![ComplaintNumber])="","N/A",SimpleCSV("SELECT EmployeeName FROM tblEmployees WHERE [ComplaintNumber] =" & [Forms]![frmComplaintDetails]![ComplaintNumber]))

 

[gojets1721]

depends on whether SimpleCSV returns a zls or can return a null. If it can't return a null, then the nz function won't have any effect. Either modify SimpleCSV to return a null or use iif

=iif(SimpleCSV("SELECT EmployeeName FROM tblEmployees WHERE [ComplaintNumber] =" & [Forms]![frmComplaintDetails]![ComplaintNumber])="","N/A",SimpleCSV("SELECT EmployeeName FROM tblEmployees WHERE [ComplaintNumber] =" & [Forms]![frmComplaintDetails]![ComplaintNumber]))

That worked! Thank you!

 

[gojets1721]

@theDBguy sorry one last question. I was curious if you've seen this issue before.

The module and field work perfectly in the form except whenever the form is filtered. If the user filters the form and there's no matching records, a 3075 run time error is shown stating syntax error in the where condition ('ComplaintNumber =').

The debugger then goes to the module and highlights 'Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)'

I was curious if you knew a fix to not have that error pop up

 

[bob fitz]

Perhaps you could catch the error in the error handling code for that procedure

 

[theDBguy]

@theDBguy sorry one last question. I was curious if you've seen this issue before.

The module and field work perfectly in the form except whenever the form is filtered. If the user filters the form and there's no matching records, a 3075 run time error is shown stating syntax error in the where condition ('ComplaintNumber =').

The debugger then goes to the module and highlights 'Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)'

I was curious if you knew a fix to not have that error pop up

Are you able to share a demo db?

 

[gojets1721]

Perhaps you could catch the error in the error handling code for that procedure

That worked! Thanks a bunch!

 

[gms004a]

That worked! Thanks a bunch!

there's is a simpler way. Remember that the "where" condition is a string, Any function returning a string will do here very well.

instead of

=FieldAsSeparatedString("EmployeeName","tblEmployees","[ComplaintNumber] =" & [Me].[ComplaintNumber] & "")


try this

Public Function Wrs$(form_name$, control_name$)
' create it in a module external to the form
' Wrs - Where replace string functin, resurns string
Dim x$, lerr&, f As Form, ctl As Control
x = ""
' x = [Me].[ComplaintNumber] that's what you want here. but [Me] is not good here
On Error Resume Next
x = "forms![" & form_name & "]![" & control_name & "]"
lerr = Err.Number
Err.Clear
On Error GoTo 0
Wrs = x
End Function


and use like this

=FieldAsSeparatedString("EmployeeName","tblEmployees","[ComplaintNumber] =" & Wrs("FormX", "Controlx")


you can also use the control value directly in a query or expression

like this: select field_list from recordset where someting = getval(args)

Public Function getval(form_name$, control_name$)
' getval - returns a value of the control (variant)
Dim lerr&, f As Form
Set f = Forms(form_name)
getval = "" & f.Controls(control_name)
Set f = Nothing
End Function


Good luck