This expression is typed incorrectly, or too complex (1 Viewer)

[Prashant]

Hi,

I am working on a billing database, where I have created a "Find" form where the user can select the criterias for the output of the report. I have used a public function which is as under and used in a query to populate the result :

Option Compare Database
Option Explicit
Public Function SelRec(billdate As Date, Customername As String, billmonth As String, Plan As String, Remarks As String) As Boolean
    SelRec = False
    
    If Not IsNull([Forms]![Find]![Start]) Then
        If billdate < [Forms]![Find]![Start] Then Exit Function
    End If
    If Not IsNull([Forms]![Find]![End]) Then
        If billdate > [Forms]![Find]![End] Then Exit Function
    End If
    
    If Not IsNull([Forms]![Find]![Customer]) Then
        If Customername <> [Forms]![Find]![Customer] Then Exit Function
    End If
    
    If Not IsNull([Forms]![Find]![Month]) Then
        If billmonth <> [Forms]![Find]![Month] Then Exit Function
    End If
    
    If Not IsNull([Forms]![Find]![Planname]) Then
        If Plan <> [Forms]![Find]![Planname] Then Exit Function
    End If
    
    If Not IsNull([Forms]![Find]![Comments]) Then
        If Remarks <> [Forms]![Find]![Comments] Then Exit Function
    End If
    
    SelRec = True
End Function

And the query code goes as below :

Field : Selrec([billdate],[customername],[billmonth],[plan],[remarks])

Criteria : [Forms]![Find]![frmOptions]

However, I am getting the following error "This expression is typed incorrectly, or it is too complex to be evaluated."

I am attaching the database as well :

The table name is "Cust" from where the output result needs to be filtered.

Can anyone highlight what is wrong.

Thanks in advance.

Prashant

 

[CJ_London]

possibly one of the field values you are passing through is null - try wrapping them in the nz function.

Not sure if you have other code you have removed from your function but the same can be achieved using OR in your sql so the function is not required

SELECT *
FROM myTable
WHERE billdate between nz([Forms]![Find]![Start]) AND nz([Forms]![Find]![End])
    OR Customername =nz([Forms]![Find]![Customer])
    OR billmonth =nz([Forms]![Find]![Month])
    OR Plan =nz([Forms]![Find]![Planname)
    OR Remarks =nz([Forms]![Find]![Comments])

 

[Prashant]

Hi CJ,

First of all thanks for your reply, it did work, but just for one criteria. For e.g. if i am selecting the name, it is giving me the desired result. However, if I am selecting more than one criteria its giving me the wrong output.

My requirement :

If there is no selection, I should able to get all the data stored in the table.
If I am selecting multiple criterias, the result should only be based on the criterias what I have selected.

I am not good with SQL, hence thought of writing a function in vba.

Expecting you should be able to help me further.

Prashant

 

[CJ_London]

If I am selecting multiple criterias, the result should only be based on the criterias what I have selected.

Can you explain a bit more how everything works?

for example - does the user select their options and then click a button to open the query in a subform or separate form?

and to be clear -
if a user only types in a customer name, the query is to return all records with that customer name

if a user types in a customer name and remarks, the query is to return all records with that customer name AND that remark? or is it to return all records with that customer name OR that remark?

 

[Prashant]

Hi CJ,

Sorry for confusing you.

Here is what I am looking for :

1) If the user does not select any option - Should be able to get all the data from the table

2) If the user is selecting criteria - Should be able to get the data based only on the selection (and not all the data)

In all I have six criteria where the user can select :

Start Date
End Date
Customer
Month
Plan
Remarks

So, for e.g. if the user selects all the above six criteria, I should be able to get the data based only on the criteria selected.

Hopefully, I am making some sense in explaining what I really want.

Thanks.

Prashant

 

[CJ_London]

thanks for that, but please answer this as well

for example - does the user select their options and then click a button to open the query in a subform or separate form?

 

[Prashant]

Hi CJ,

Yes, this is based on click of a button, once the user has selected the criteria. This will open the query for viewing and printing.

Presently, I have assigned a button to open the query.

Basically, after selecting the criteria the user will verify the information and thereafter will give a print command.

Any other suggestions are welcome.

Thanks.

Prashant

 

[CJ_London]

You wouldn't (or shouldn't) open a query directly so I've assumed you mean a report. Your code to open the report will be.

I've left the names as you have supplied but note that 'End' and 'Month' are reserved words and using them can result in unexpected results. I recommend you change them - perhaps to the same names as your table fields

    dim wherestr as string
     
    wherestr=""
    If Not IsNull([Start]) Then
        wherestr= "billdate > " & [Start] & " AND "
    End If
    
     If Not IsNull[End]) Then
        wherestr= wherestr & "billdate < " & [End] & " AND "
    End If
    
    If Not IsNull([Customer]) Then
        wherestr= wherestr & "Customername = '" & [Customer]  & "' AND "
     end if
     
    If Not IsNull([Month]) Then
        wherestr= wherestr & "billmonth = " & [Month] & " AND "
     end if
     
    If Not IsNull([Planname]) Then
        wherestr= wherestr & "Plan ='" & [Planname] & "' AND "
     end if
     
    If Not IsNull([[Comments]) Then
        wherestr= wherestr & "Remarks = '" & [Comments] & "' AND "
    End If
 
    docmd.openreport "myqueryname",,,left(wherestr,len(wherestr)-5)

 

[The_Doc_Man]

As a side note, you have more than one reserved word involved in your query. In such cases, Access is noted for being a bit ... picky, shall we say? End, Month, and Comments are all dangerous choices for field names.