Query by Form

[Guest]

hi,
I had the same problem a while back.
I solved it you creating a report form. It has 5 check
boxes on it labled by item id, by vendor, by date, by
month, all.
I added code so that only 1 check box could be checked at
a time.
sub chkbox1()
If chkbox1 = true then
chkbox2 = false
chkbox3 = false
chkbox4 = false
chkbox5 = false
end if
each check box has it own sub which turn off all the other
check boxes if it is checked.
I had to write 5 different queries, 1 for each criteria.
but only 1 report. I programicly set the record source to
which query based on which check box was checked.
me.recordsource = "query1"
on the form are text boxes for all of the criteria.
depending on which check box is check, i enabled (lit up)
any text box that pertained to the qery selected and
disabled (grayed out) any text box that didn't pertain to
the query.
Textbox1.enabled = true (or false)
anyway this was my solution
hope it helps.
I'm off work in 2 minutes. i'll check this post tommorrow
if you have any questions.
good luck.

 

[ACase]

Thanks for the response, but I'm working with txt boxes, an Option Group, and
Combo Boxes. I have about 10 fields on a form (Name, ID, State, Type....).
When a user opens the form all fields are blank. There is a button on the
form which enables the user to run the report. If he clicks on the button
without entering any information in the form, then the report should bring
back 'all' Merchants. (It's a Business db to keep track of Merchants). If
he enters lets say state = NY and Type = 'C' on the form, then the report
should bring back all Merchants who are from NY and are 'C' customers.

My problem is that if the user does not enter any information into these txt
boxes, the report/query will view the criteria as " ", rather then *.

Any help would be much appreciated.

 

[dave k]

Did you ever solve this question? I have the same situation and haven't been
able to find an answer.

Thanks,
Dave

 

[Duane Hookom]

I would use code to build a Where clause for the DoCmd.OpenReport method.
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboState) Then
strWhere = strWhere & " And [State] = '" & Me.cboState & "' "
End If
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " And [DateField] >= #" & Me.txtStartDate & "#
"
End If
'etc
DoCmd.OpenReport "rptYourReport", acViewPreview, , strWhere