Form search by keywords contain expression builder from qry

[sseiharath]

Dear all,

Who can help me, I have Form search by keywords, the source data from query that contain expression builder from formula when I type search keywords the columns that contain formula appear error name#?, Please help, I need calculation fields, because is the importance data for me.

Thanks.

 

[Ranman256]

then your formula is wrong. But we need to see your formula to know.
pls post.

 

[jdraw]

Further to ranman's response, it would be helpful if you describe WHAT you are trying to do in plain English.
A sample of your search set up could help readers understand the issue.

 

[sseiharath]

The Formulas are:
1). DaysLeft: ([IssueDate]+([YearNumber]*365))-Now()
First formula I would like to know expired date for licensing.

2). Status: IIf([DaysLeft]<0,"Expired",IIf([DaysLeft]<30,"Expired Soon",""))
The 2nd formula I wand access alert about status of giving licensing.
Thank a lot for helping me.
Hopefully.

 

[sneuberg]

I've found that doing math directly with dates as you are doing in the DaysLeft expression doesn't always work well. I suggest redoing the expression using DateAdd.

 

[sseiharath]

Thanks Steve, errors still there, but may be not compactable expression builder formulas with search form by keywords, Do exist any ways beside expression builder formulas in query?
Thanks

 

[sneuberg]

I don't understand your statement

still there, but may be not compactable expression builder formulas with search form by keywords

Could you express this statement differently.

You could put the code in a public function in a standard module, for example.

DaysLeft: ([IssueDate]+([YearNumber]*365))-Now()

could be a function like:

Public Function Daysleft(IssueDate As Variant, YearNumber As Variant) As Variant

Daysleft = DateAdd("d", YearNumber * 365, IssueDate) - Now()

End Function

and

Status: IIf([DaysLeft]<0,"Expired",IIf([DaysLeft]<30,"Expired Soon",""))

could be a function like

Public Function Status(IssueDate As Variant, YearNumber As Variant) As Variant

Dim lngDaysleft As Long
lngDaysleft = Daysleft(IssueDate, YearNumber)
If lngDaysleft < 0 Then
    Status = "Expired"
ElseIf lngDaysleft < 30 Then
    Status = "Expired Soon"
Else
    Status = ""
End If

End Function

You would use these in a query like:

DaysLeft: Daysleft([IssueDate],[YearNumber])

and

Status: Status([IssueDate],[YearNumber])

But the only thing this buys you is the ability to debug the code, but that doesn't seem to be the problem as both your original expressions and these functions seem to be working ok in the attached test database.

Could you upload your database?

 

[sseiharath]

Hello Sneuberg (Steve),

Now it work, Thank you very much for your great help to me.
Thanks

 

[sseiharath]

Hi, Sneuberg,

Try to type Keywords in the form, please give me ideas how to make this search form work without errors in columns [daysleft], [Status]...[Unitnumber],
Thanks.

 

[sneuberg]

The problem is that the follow code in your form changes the record source to tblServiceDataEntry which doesn't have these expressions. If you change tblServiceDataEntry in this code to a query like qryServiceInfo that has the expressions you won't have the #Name problem.

Private Sub cmdSearch_Click()
Dim strSearch As String
Dim strText As String
strText = Me.txtSearch.Value
strSearch = "SELECT * from[COLOR="Blue"] tblServiceDataEntry[/COLOR] where ((ServNameEng like ""*" & strText & "*"")Or(ServiceCatID like ""*" & strText & "*"")Or(ServProvince like ""*" & strText & "*"")Or(IssueDate like ""*" & strText & "*"")Or(OwnerNameEng like ""*" & strText & "*"")Or(OwnPhone like ""*" & strText & "*""))"
Me.RecordSource = strSearch
End Sub

Private Sub cmdShowAll_Click()
Dim strSearch As String
strSearch = "SELECT * From [COLOR="blue"]tblServiceDataEntry[/COLOR]"
Me.RecordSource = strSearch
End Sub

 

[sseiharath]

Wow, Thanks you very much for great advice, it now work.
Thanks Sneuberg.