Need help with Query parameters by form

Works like a charm......

Ok...here is another one for you having to do with this same report.

In the sorting and grouping on the report, I have the report grouped by Date,
then Client, then Market. I have the keep together set to whole group for
date and client. I am reading this to mean that if I select whole group that
a new page to the report will start with each change in date (or client...
market is set to no). This is not what is happening.

Is there any way to get the report to start a new page with each change in
date as well as each change in client?

Thank you so much for the help with the report selection. This is a major
deal for me. One of the biggest things that "they" wanted the db to be able
to do.

It is much appreciated.

emily

--
Message posted via http://www.accessmonster.com

Here is the code for the After Update Events. I have a total of 6 combo
boxes on this form with AfterUpdate on the first 5.

Option Compare Database


Private Sub ctrl_ClientSelector_AfterUpdate()
Me.ctrl_CompanySelector.Requery
Me.ctrl_CollectorCompanySelector.Requery
Me.ctrl_CollectorNameSelector.Requery
Me.ctrl_DonorNameSelector.Requery
End Sub


Private Sub ctrl_CollectorCompanySelector_AfterUpdate()
Me.ctrl_CollectorNameSelector.Requery
Me.ctrl_DonorNameSelector.Requery
End Sub


Private Sub ctrl_CollectorNameSelector_AfterUpdate()
Me.ctrl_DonorNameSelector.Requery
End Sub


Private Sub ctrl_CompanySelector_AfterUpdate()
Me.ctrl_CollectorCompanySelector.Requery
Me.ctrl_CollectorNameSelector.Requery
Me.ctrl_DonorNameSelector.Requery
End Sub


Private Sub ctrl_MarketSelector_AfterUpdate()
Me.ctrl_ClientSelector.Requery
Me.ctrl_CompanySelector.Requery
Me.ctrl_CollectorCompanySelector.Requery
Me.ctrl_CollectorNameSelector.Requery
Me.ctrl_DonorNameSelector.Requery
End Sub

Will post other code and info separately.

ea

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200610/1

Here is On Click Event for the command buttons on the form.


CLOSE FORM Command Button

Private Sub Btn_CloseForm_Click()
On Error GoTo Err_Btn_CloseForm_Click
DoCmd.Close
Exit_Btn_CloseForm_Click:
Exit Sub
Err_Btn_CloseForm_Click:
MsgBox Err.Description
Resume Exit_Btn_CloseForm_Click
End Sub


OPEN UPDATE LIST Command Button

Private Sub Btn_OpenUpdateList_Click()
On Error GoTo Err_Btn_OpenUpdateList_Click
Dim strWhere As String
If Not IsNull(Me.ctrl_MarketSelector) Then
strWhere = strWhere & " AND " & _
"Market = '" & Me.ctrl_MarketSelector & "'"
End If
If Not IsNull(Me.ctrl_ClientSelector) Then
strWhere = strWhere & " AND " & _
"Client = '" & Me.ctrl_ClientSelector & "'"
End If
If Not IsNull(Me.ctrl_CompanySelector) Then
strWhere = strWhere & " AND " & _
"Company = '" & Me.ctrl_CompanySelector & "'"
End If
If Not IsNull(Me.ctrl_CollectorCompanySelector) Then
strWhere = strWhere & " AND " & _
"CollectorCompany = '" & Me.ctrl_CollectorCompanySelector & "'"
End If
If Not IsNull(Me.ctrl_CollectorNameSelector) Then
strWhere = strWhere & " AND " & _
"CollectorName = '" & Me.ctrl_CollectorNameSelector & "'"
End If
If Not IsNull(Me.ctrl_DonorNameSelector) Then
strWhere = strWhere & " AND " & _
"DonorName = '" & Me.ctrl_DonorNameSelector & "'"
End If
If Len(strWhere) 4 Then strWhere = Right(strWhere, Len(strWhere) - 5)
DoCmd.OpenForm "Form-UpdateSearch", , , strWhere
Exit_Btn_OpenUpdateList_Click:
Exit Sub
Err_Btn_OpenUpdateList_Click:
MsgBox Err.Description
Resume Exit_Btn_OpenUpdateList_Click
End Sub


CLEAR SELECTIONS Command Button

Private Sub Btn_ClearSelections_Click()
Me.ctrl_MarketSelector = Null
Me.ctrl_ClientSelector = Null
Me.ctrl_CompanySelector = Null
Me.ctrl_CollectorCompanySelector = Null
Me.ctrl_CollectorNameSelector = Null
Me.ctrl_DonorNameSelector = Null
End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200610/1

Row Source for Combo Boxes on form.

Hopefully with all this information you can tell me which direction to go in
so that I can select any combination of these 6 boxes and click my open form
button. I have this open form button directed to a continuous form that will
show multiple records. From here, an "update" button that will bring up the
full detail of the record to make changes.

Thanks again for all the help you have provided me.

ea


ctrl-MarketSelector

SELECT DISTINCT [Table-CallLogData].Market
FROM [Table-CallLogData]
ORDER BY [Table-CallLogData].Market;


ctrl-ClientSelector

SELECT DISTINCT [Table-CallLogData].Client
FROM [Table-CallLogData]
WHERE ((([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]![ctrl-
MarketSelector]))
ORDER BY [Table-CallLogData].Client;


ctrl-CompanySelector

SELECT DISTINCT [Table-CallLogData].Company
FROM [Table-CallLogData]
WHERE ((([Table-CallLogData].Client)=[Forms]![Form-UpdateSrch]![ctrl-
ClientSelector]) AND (([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]!
[ctrl-MarketSelector]))
ORDER BY [Table-CallLogData].Company;


ctrl-CollectorCompany

SELECT DISTINCT [Table-CallLogData].CollectorCompany
FROM [Table-CallLogData]
WHERE ((([Table-CallLogData].Client)=[Forms]![Form-UpdateSrch]![ctrl-
ClientSelector])
AND (([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]![ctrl-
MarketSelector])
AND (([Table-CallLogData].Company)=[Forms]![Form-UpdateSrch]![ctrl-
CompanySelector]))
ORDER BY [Table-CallLogData].CollectorCompany;


ctrl-CollectorNameSelector

SELECT DISTINCT [Table-CallLogData].CollectorName
FROM [Table-CallLogData]
WHERE ((([Table-CallLogData].Client)=[Forms]![Form-UpdateSrch]![ctrl-
ClientSelector])
AND (([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]![ctrl-
MarketSelector])
AND (([Table-CallLogData].Company)=[Forms]![Form-UpdateSrch]![ctrl-
CompanySelector])
AND (([Table-CallLogData].CollectorCompany)=[Forms]![Form-UpdateSrch]![ctrl-
CollectorCompanySelector]))
ORDER BY [Table-CallLogData].CollectorName;


ctrl-DonorNameSelector

SELECT DISTINCT [Table-CallLogData].DonorName
FROM [Table-CallLogData]
WHERE ((([Table-CallLogData].Client)=[Forms]![Form-UpdateSrch]![ctrl-
ClientSelector])
AND (([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]![ctrl-
MarketSelector])
AND (([Table-CallLogData].Company)=[Forms]![Form-UpdateSrch]![ctrl-
CompanySelector])
AND (([Table-CallLogData].CollectorCompany)=[Forms]![Form-UpdateSrch]![ctrl-
CollectorCompanySelector])
AND (([Table-CallLogData].CollectorName)=[Forms]![Form-UpdateSrch]![ctrl-
CollectorNameSelector]))
ORDER BY [Table-CallLogData].DonorName;

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200610/1

OK... one trick is to change the the criteria in the WHERE clause of the
SQL expressions so they also work with nulls; here's the idea: where
you originally had:

SELECT DISTINCT [Table-CallLogData].Client
FROM [Table-CallLogData]
WHERE ((([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]![ctrl-
MarketSelector]))
ORDER BY [Table-CallLogData].Client;

Change the WHERE clause to:

WHERE [Table-CallLogData].Market) Like
Iif(IsNull([Forms]![Form-UpdateSrch]![ctrl-MarketSelector]), "*",
[Forms]![Form-UpdateSrch]![ctrl-MarketSelector])

(watch out for wrapping in the newsreader, there should be no line feed
in there!)

If this is too long to grasp, look at it this way:

WHERE Market Like Iif(IsNull([Combo1]), "*", [Combo1])

The trick is that if nothing is selected in the first combo, so it
returns null, you effectively get Market = "*" , i.e. all Markets,
otherwise you get Market = [Combo1], i.e. matches.
Note the use of operator Like instead of =, it won't work with the latter.

Change all references to preceding combos in all SQL expressions in this
fashion, and the job is done.

HTH,
Nikos

In the sorting and grouping on the report, I have the report grouped by Date,
then Client, then Market. I have the keep together set to whole group for
date and client. I am reading this to mean that if I select whole group that
a new page to the report will start with each change in date (or client...
market is set to no).
Wrong reading. This means that if a group is bigger than the space
available on the page it is supposed to start in (middle of page, not
beginning), then it will be forced to start on a new page, so as to be
in one page (provided it fits in one).


Is there any way to get the report to start a new page with each
change in
date as well as each change in client?
While in report design, click on the bar that reads Date Header or on an
empty spot in the Date header section (so as to select the section), and
display its properties; the one you want is Force New Page, top one on
the Format tab; experiment with it and see what you get. Likewise for
the other sections.

HTH,
Nikos

P.S. Having solved one problem and moving on to another, it is advisable
to start a new thread, for two reasons: (a) the subject reflects the
actual question, and so helps others potentially having the same
question locate it and benefit form the answers you get, and (b) it
greatly increases your chances of getting many more people's attention,
as it moves to the top of the heap (whereas now it seems you're stuck
with me, and willing as I may be to help I might not aways have the best
answer, or I might get overloaded with work and disappear for a week or
two).

I am back again. Something isn't working right. I made the changes to the
Where clause on all my combos. Now, when I try to select any of them (other
than market...which is the first in the "pecking" order) I get an "Enter
Parameter Value" box. The info above the text entry area looks to be a
double quote.

I have tried to look at whether or not I messed up the SQL somewhere, but it
all looks like it should.

Here is the SQL for all the combos. I hope you have an idea of what to do to
make it work because I haven't.

Thanks, Em


ctrl-MarketSelector

SELECT DISTINCT [Table-CallLogData].Market
FROM [Table-CallLogData]
ORDER BY [Table-CallLogData].Market;


ctrl-ClientSelector

SELECT DISTINCT [Table-CallLogData].Client
FROM [Table-CallLogData]
WHERE (([Table-CallLogData].Market) Like IIF(IsNull([Forms]![Form-
UpdateSearch]![ctrl-MarketSelector]),“*�,[Forms]![Form-UpdateSearch]![ctrl-
MarketSelector]))
ORDER BY [Table-CallLogData].Client;


ctrl-CompanySelector

SELECT DISTINCT [Table-CallLogData].Company
FROM [Table-CallLogData]
WHERE (([Table-CallLogData].Client) Like IIF(IsNull([Forms]![Form-
UpdateSearch]![ctrl-ClientSelector]), “*�, [Forms]![Form-UpdateSearch]![ctrl-
ClientSelector])
AND (([Table-CallLogData].Market) Like IIF(IsNull([Forms]![Form-UpdateSearch]!
[ctrl-MarketSelector]), “*�, [Forms]![Form-UpdateSearch]![ctrl-MarketSelector]
)))
ORDER BY [Table-CallLogData].Company;


ctrl-CollectorCompany

SELECT DISTINCT [Table-CallLogData].CollectorCompany
FROM [Table-CallLogData]
WHERE (([Table-CallLogData].Client) Like IIF(IsNull([Forms]![Form-
UpdateSearch]![ctrl-ClientSelector]), “*�, [Forms]![Form-UpdateSearch]![ctrl-
ClientSelector])
AND (([Table-CallLogData].Market) Like IIF(IsNull([Forms]![Form-UpdateSearch]!
[ctrl-MarketSelector]), “*�, [Forms]![Form-UpdateSearch]![ctrl-MarketSelector]
)
AND (([Table-CallLogData].Company) Like IIF(IsNull([Forms]![Form-UpdateSearch]
![ctrl-CompanySelector]), “*�, [Forms]![Form-UpdateSearch]![ctrl-
CompanySelector]))))
ORDER BY [Table-CallLogData].CollectorCompany;


ctrl-CollectorNameSelector

SELECT DISTINCT [Table-CallLogData].CollectorName
FROM [Table-CallLogData]
WHERE (([Table-CallLogData].Client) Like IIF(IsNull([Forms]![Form-
UpdateSearch]![ctrl-ClientSelector]), “*�, [Forms]![Form-UpdateSearch]![ctrl-
ClientSelector])
AND (([Table-CallLogData].Market) Like IIF(IsNull([Forms]![Form-UpdateSearch]!
[ctrl-MarketSelector]), “*�, [Forms]![Form-UpdateSearch]![ctrl-MarketSelector]
)
AND (([Table-CallLogData].Company) Like IIF(IsNull([Forms]![Form-UpdateSearch]
![ctrl-CompanySelector]), “*�, [Forms]![Form-UpdateSearch]![ctrl-
CompanySelector])
AND (([Table-CallLogData].CollectorCompany) Like IIF(IsNull([Forms]![Form-
UpdateSearch]![ctrl-CollectorCompanySelector]), “*�, [Forms]![Form-
UpdateSearch]![ctrl-CollectorCompanySelector])))))
ORDER BY [Table-CallLogData].CollectorName;


ctrl-DonorNameSelector

SELECT DISTINCT [Table-CallLogData].DonorName
FROM [Table-CallLogData]
WHERE (([Table-CallLogData].Client) Like IIF(IsNull([Forms]![Form-
UpdateSearch]![ctrl-ClientSelector]), “*�, [Forms]![Form-UpdateSearch]![ctrl-
ClientSelector])
AND (([Table-CallLogData].Market) Like IIF(IsNull([Forms]![Form-UpdateSearch]!
[ctrl-MarketSelector]), “*�, [Forms]![Form-UpdateSearch]![ctrl-MarketSelector]
)
AND (([Table-CallLogData].Company) Like IIF(IsNull([Forms]![Form-UpdateSearch]
![ctrl-CompanySelector]), “*�, [Forms]![Form-UpdateSearch]![ctrl-
CompanySelector])
AND (([Table-CallLogData].CollectorCompany) Like IIF(IsNull([Forms]![Form-
UpdateSearch]![ctrl-CollectorCompanySelector]), “*�, [Forms]![Form-
UpdateSearch]![ctrl-CollectorCompanySelector])
AND (([Table-CallLogData].CollectorName) Like IIF(IsNull([Forms]![Form-
UpdateSearch]![ctrl-CollectorNameSelector]), “*�, [Forms]![Form-UpdateSearch]!
[ctrl-CollectorNameSelector]))))))
ORDER BY [Table-CallLogData].DonorName;

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200610/1

disregard this.....I got it to work.

Since I figured it would be a few hours until you replied (based on your
previous help) I did a search on the boards for "Like Is Null" and found an
old post and tried what it suggested.

I changed you suggestion a bit.....

made it read

Select....
Where.....table.field = combo OR combo IS NULL.


works like a charm now.

good for me because I have a status meeting on Monday and it will be good
that this is working now.

Thanks so much for all your help over the past week or so. You have been a
real life saver for me.

Emily

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200610/1