Query by Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello;

I am creating a report form, which will enable a user to specify the
criteria (date, accounts, state....) before running the report. The
underlying query will be based off of the user's criteria. I would like the
query to return all value in a field if the user has not added a criteria.
(Ex: If the user does not specify a State, then the query should return all
states).
I am having difficulty with this part of the query. I tried adding a
Wildcard '*' as the default value in the fields property and having an If
statement in the query, but nothing seems to work.

Any ideas would be much appreciated.

Thanks
 
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.
 
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.
 
Did you ever solve this question? I have the same situation and haven't been
able to find an answer.

Thanks,
Dave
 
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
 
Back
Top