Filtering - by option boxes multiple

[Chimp8471]

I am trying to design a form which will provide my users multiple reporting options using the same query.

my query contains the following fields:

Daycode
WeekNumber
PeriodNumber
Line
Machine
Eventcode
TotalTime
Occur
Avg

i want the user to choose in this order :

a time period to run the report.....either Daycode, WeekNumber,PeriodNumber

then select:

a Line,

then select either Totaltime, Occur or Avg (this will require to be sorted Desecnding..

then finally to decide wheter to have all the data remaining, or sorted by machine.

DB is here--------> Click Here

 

[llkhoutx]

Build the SQL string for the query dynamically, first testing the various possible parameters for a Null value in which event a filter for that Null parameter is not added to the SQL query, e.g.

SQL = "SELECT * from tbTable WHERE "
SQLADD = ""
if not isnull(me!Parameter1) then
SQLADD = SQLADD & "Parameter1=" & me!Parameter1
end if
if not isnull(me!Parameter2) then
if not isnull(SQLADD ) then
SQLADD = SQLADD & " AND Parameter2=" & me!Parameter2
ELSE
SQLADD = SQLADD & " Parameter2=" & me!Parameter2
END IF
end if

etc.

SQL = SQL & SQLAD

Of course, take into consideration the datatype of the variable parameter value.

 

[Chimp8471]

hi, thanks but you lost me,

any further help would be much appreciated...

any freelancers might wanna considered a price...i need to sort this..

cheers

Andy