Criteria in Querys from Froms

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

Good morning!
I have a form that the user can chose different restraints
to be printed out on a report(Date range, Squre Foot
Range, State....) Each text box in linked to criteria in
a Query that is liked to a report. The problem is if the
user does not fill in one of the restaints, then the
report prints out blank. I need it so if State is left
blank, then all the states are choosen for the criteia in
the Query and the rest of the criteria is met from the
other restraints. I hope this makes sense, and thank you
for your help.
Example txtstate = ______
txtminDate = 3/3/00
txtmaxDate = 3/3/03
The report would print out all the states information from
3/3/00 to 3/3/03
 
Hi Stuart,
I don't know if you are familiar with writing Access code or SQL statements,
but I think that is the answer. (I assume you may already have a SQL
statement someplace, which is telling the report which data to display.)

The user selects each of the fields, then clicks a button to bring up the
report, right?
In the "On Click" event of the button, you can write yor code to check the
value of each user-selected field, and then build the SQL statement
accordingly.
example:

'(first set your variables...sq is SQL statement, sw is SQL Where clause...)
dim sq as string
dim sw as string

'(now begin building SQL statement)
sq = "Select * from myTableOrQuery "
If not Is Null (me.State) then
sw = sw & "State = " & me.State
end if
If not Is Null (me.txtMinDate) then
if sw = "" then
sw = sw & " Date > " & me.txtMinDate
else
sw = sw & " AND Date > ' " & me.txtMinDate & " ' "
end if
end if
' ( if the SQL where clause is not blank, add it together with the SQL
statement to finish the query statement
If sw > "" then sq = sq & " Where " & sw

Now at this point, if the state field was not used, and the txtMinDate field
was set to 3/3/00, your SQL statement would read:
"Select * From MyTableOrQuery Where Date > '3/3/00' "

Then you would open your report with something like:
DoCmd.OpenReport MyReportName , , sq

If no fields were filled in at all, the report would simply open all records
in the entire table or query.

I hope that helps. Let me know if you have questions.
 
Good Morning Andy!

I am slightly fimilar with writing Access Code thru
classes many years ago. I also have John Viescas "Running
Microsoft Access 2000" book to reference.

I tried the code you sent me, made the appropriate
changes, and I keep getting errors.
I changed the date field to Square Feet. In the future,
once I can understand the basic code, the user is going to
pick date, squre feet, state or region , and job type.
One thing at a time for now!
The error is happening in the docmd.openreport saying the
expression i entered was of the wrong data type.
Thanks for you help, Stuart


Private Sub Command77_Click()
Dim sq As String
Dim sw As String

sq = "Select * from FORMS![frmsort Jobs By Date] "
If Not IsNull(Me.cmbStates) Then
sw = sw & "State = " & Me.cmbStates
End If
If Not IsNull(Me.txtMinSQFT) Then
If sw = "" Then
sw = sw & " Date > " & Me.txtMinSQFT
Else
sw = sw & " AND Date > ' " & Me.txtMinSQFT & " ' "
End If
End If

If sw > "" Then sq = sq & " Where " & sw

DoCmd.OpenReport Reports![RptSearchByDate], acViewPreview,
sq
 
Back
Top