Multi Criteria Searchform

[cap.zadi]

Hey Sir,Happy New year, Thanks both of you and its working.

while i was testing so just found a small run time error which occurs beaucse of:

when i select a product or any other field and search the record and it give the results. now if i change the criteria w/o clearing so will be fine but if i clear once the search fields and select again new one so its showing Run time error. i think some problem with Clear command.? Please advise. thanks.

[cap.zadi]

Sir, can i ask another question here now:

i have many kinds reports and for sure this searchform is required for all so what is the best way out to manage?

what i feel is:

- On top of this searchform can be a combo box which shows the report list so 1st select the report and then criteria for record?

- Having a report list through system table and when i select the report so this searchform must appear first then records based on selection?

- is there any best way to manage this?

thanks

[June7]

Show the code or provide latest version of project.

The combobox listing reports is one way I offer report options.

[cap.zadi]

hey sir,

how to go for this report option? can you give a short overview?

here is the updated file of searchform.

thanks

[cap.zadi]

Sir, waiting for your kind reply please?

thanks

[June7]

Search form appears to be working. The reports option could be a listbox on the search form. User clicks on report. Code in the Print button event would open the selected report by reference to the listbox. If the listbox lists reports with their exact names, simply:

If IsNull(Me.listboxname) Then
MsgBox "Must select report first."
Else
DoCmd.OpenReport Me.listboxname, acViewPreview
End If

[cap.zadi]

Hey Sir, yes it is working nice but as i told u the problem was if i select a record and then clear it from clear command then again select a new record so there will be run time error.


Yes i have made a combo box for reports and it is working fine. just confuse about the search form how it will work with those reports. Could we have a single search form to work with all reports or each report should having own form? please advise.


thanks

[June7]

In the Clear button procedure, instead of setting each control to empty string "" set to Null.
Me.cbotestdate1.Value = Null

I guess you could have a single search form work for those reports if reports include these fields in RecordSource. Are they all using the QSAT13 query as RecordSource? Why would multiple reports have same RecordSource? I just don't have enough info about the reports. Build them and I will look at your project again.

[cap.zadi]

Hey, sir thanks for advise.

No sir each report having its own query and setup of tables. so we can say each report having own query. well sure. thanks

[cap.zadi]

thanks for help.

[cap.zadi]

Hello Sir,

I hope all of you fine there.

when i run this database in network on there's same window 7 pc's so having this problem while opening search form to operate reports.

"Undefined function 'date' in expression"

here is query SQL Statement.

SELECT tblMT11.TestNo, tblMT11.TestDate, tblMT11.ItemID, tblMT11.LengthMtr, tblMT11.Technician, tblMT11.Note, tblRT11.ResultNo, tblRT11.ProdnDate, tblRT11.Kiln, tblRT11.CarNo, tblRT11.CrushingLoad, tblRT11.WaterCondition, Round([CrushingLoad]/[LengthmTR],2) AS CrushingStrength, IIf([CrushingStrength]<[CrushingStandard],"Failed","Passed") AS Result, tblCrushingSTD.CrushingStandard
FROM (tblCrushingSTD INNER JOIN tblMT11 ON tblCrushingSTD.Product = tblMT11.ItemID) INNER JOIN tblRT11 ON tblMT11.TestNo = tblRT11.TestNo
WHERE (((tblMT11.TestDate) Between Nz([Forms]![lbrsf1].[cbotestdate1],#1/1/1900#) And Nz([Forms]![lbrsf1].[cbotestdate2],Date())) AND ((tblMT11.ItemID) Like [forms]![lbrsf1].[cboproduct] & "*") AND ((tblRT11.ProdnDate) Between Nz([Forms]![lbrsf1].[cboprdndate1],#1/1/1900#) And Nz([Forms]![lbrsf1].[cboprdndate2],Date())) AND ((tblRT11.Kiln) Like [forms]![lbrsf1].[cbokiln] & "*"));

This problem is not appearing at same pc where is database back end exists but on others it shows this problem.

please advise.

thanks

zee

[alansidman]

I parsed your SQL Statement and it appears that there is an extra open parens at the beginning of your WHERE statement. I am not sure if this is a typo when you copied the SQL statement or the issue is something else.

Is the SQL statement exactly the same on the back end PC as on the front end PCs?

[cap.zadi]

Sir welcome and was waiting for u for the other thread which was imp for me tonight.

This is the copied one SQL Statement from the Query window from front end. there is only tables in back end no queries.

[alansidman]

This problem is not appearing at same pc where is database back end exists but on others it shows this problem.

If there are only tables on the back end, then what does this mean? And what about the extra open Parens. Did you investigate?

My apologies, but I will not have time today to look at your bigger issue. It is too time consuming for me to look into today and I have other priorities. Hopefully, someone with more time will be able to assist you.

[cap.zadi]

really i did not understand why it happens. As it is not having while on same System but it happens when front end install on other system and operate. This is query was build by access design view and the parameters which i put in was purposed in this thread by Mr. June.

Is it because of Window-7 date format? i have already change for all pc's from 1/20/2012 to 20/1/2012 and its works fine on same pc but not on others.