Acces 2007 and expression builder

[artur223]

Hello,

I have a problem with IF(IIf) statement in query expression builder(criteri a field).
It should be like :
If [Forms]![frmReportManage r]![cmbAgentName] = "" then criteria field should be filled [tblAgent]![AgentName] else criteria field should be filled [Forms]![frmReportManage r]![cmbAgentName].

I have googled almost 2 days for now and I cant sort it out. Maybe its possible to do it in VB?

Many thanks for your help,
Artur

[DonRayner]

Did you try it like this?

= IIf ([Forms]![frmReportManager]![cmbAgentName] = "",[tblAgent]![AgentName],[Forms]![frmReportManager]![cmbAgentName])

[artur223]

I cant enter this, this displays error message: The expression you enterned contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.
After clicking OK, the comma is selecter from criteria field?
What should I do dirrerently?

Thanks,
Artur

[ChipR]

What is [tblAgent]![AgentName] supposed to be?

[artur223]

What is [tblAgent]![AgentName] supposed to be?

[tblAgent]![AgentName] is a row named [AgentName] in [tblAgent] table.
If I put only [tblAgent]![AgentName] to criteria field, then it works like it should work if this [Forms]![frmReportManage r]![cmbAgentName] = "". eg field is empty.

Thanks

[DonRayner]

I cant enter this, this displays error message: The expression you enterned contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.
After clicking OK, the comma is selecter from criteria field?
What should I do dirrerently?

Thanks,
Artur

Try this then

= IIf (IsNull([Forms]![frmReportManager]![cmbAgentName]),[tblAgent]![AgentName],[Forms]![frmReportManager]![cmbAgentName])

[artur223]

Try this then

= IIf (IsNull([Forms]![frmReportManager]![cmbAgentName]),[tblAgent]![AgentName],[Forms]![frmReportManager]![cmbAgentName])

Still the same error :(

[FishVal]

This doesn't look like criteria. It looks pretty like calculated field.

[DonRayner]

Try this;

In a new module, place the following code

Public Function FormCriteria() as string
If IsNull(forms!frmReportManager!cmbAgentName) Then
FormCriteria = "*"
Else
FormCriteria = forms!frmReportManager!cmbAgentName
End If
End Function

Then use this as the criteria in your query

Like FormCriteria()

[artur223]

Try this;

In a new module, place the following code

Public Function FormCriteria() as string
If IsNull(forms!frmReportManager!cmbAgentName) Then
FormCriteria = "*"
Else
FormCriteria = forms!frmReportManager!cmbAgentName
End If
End Function

Then use this as the criteria in your query

Like FormCriteria()

Are you trying to use "*" as a wildcard? AFAIK VB does not support wildcards.

Thanks

[DonRayner]

What is the value in your listbox when you get that error? place a messagebox command as the first line in the function to see what value you are passing to it.

msgbox forms!frmReportManager!cmbAgentName

On line 2 you could try changing it to

If IsNull(forms!frmReportManager!cmbAgentName) or forms!frmReportManager!cmbAgentName = "" Then

The function is developing a string statement to pass into the criteria of the query, the "*" part is just making the criteria statement to look like

Like *

[artur223]

What is the value in your listbox when you get that error? place a messagebox command as the first line in the function to see what value you are passing to it.

msgbox forms!frmReportManager!cmbAgentName

Run time error '94'
Invalid use of null

On line 2 you could try changing it to

If IsNull(forms!frmReportManager!cmbAgentName) or forms!frmReportManager!cmbAgentName = "" Then

The function is developing a string statement to pass into the criteria of the query, the "*" part is just making the criteria statement to look like

Like *

"You entered an expression that has no value"

If I add Forms!frmReport Manager!cmbAgen tName = "" to form load then I'm back at "You entered an expression that has no value"


Thanks

[DonRayner]

I think that we are just missing passing the quotes along with the string. Try changing the function to this

Public Function FormCriteria() as string 
If IsNull(forms!frmReportManager!cmbAgentName) Then 
    FormCriteria = "Like ""*""" 
Else 
    FormCriteria = "Like " & "" & forms!frmReportManager!cmbAgentName & ""
End If 
End Function

[ChipR]

I think you mean:
FormCriteria = "Like """ & forms!frmReport Manager!cmbAgen tName & """"

[artur223]

DonRayner, ChipR, sitll same "You entered an expression that has no value". :(

Thank you