Need help with Query parameters by form

[EmAlbritton via AccessMonster.com]

Hello all. I am having a bit of trouble getting my query to work properly.
I am trying to run a query (for my report) based on criteria entered on a
form.

I have two problems with this.

1. I can't get the form fields to update the query so that the report will
provide the correct information.

2. In a somewhat unrelated (I think) situation, one of my two combo boxes
isn't cascading like it should.

My main data entry form has several cascading combos...so I am pretty sure
that I have that setup right...just wondering if there is a problem because
this time around it is unbound.

the SQL for the query follows:

SELECT [Table-CallLogData].DateCallReceived, [Table-CallLogData].Market,
[Table-CallLogData].Client, [Table-CallLogData].Company, [Table-CallLogData].
CallType, [Table-CallLogData].TestReason, [Table-CallLogData].DOTStatus,
[Table-CallLogData].TestType, [Table-CallLogData].CollectorName
FROM [Table-CallLogData]
WHERE ((([Table-CallLogData].DateCallReceived)=[Forms]![Form-ReportSelection]!
[ctrl-DateSelector]) AND (([Table-CallLogData].Market)=[Forms]![Form-
ReportSelection]![ctrl-MarketSelector]) AND (([Table-CallLogData].Client)=
[Forms]![Form-ReportSelection]![ctrl-ClientSelector]));


Oh...one other thing...I don't think I want my criteria to be and....is there
a way to get an and/or in there.....basically, user can select any
combination of the three options, or no options at all which would report the
entire database.

Thanks in advance for your help.

Emily

 

[Nikos Yannacopoulos]

Emily,

On your first question, this kind of situation would usually be treated
with setting the report recordsource to be the table itself, and using
some code in the Click event of the command button opening it (I assume
you have one on the form) to construct the filter string and use it as
the Where condition of the OpenReport method. The code would check each
selection control in turn, and only add a pertinent filter if the user
has actually entered a value. So, the code would be along the lines of:

Dim strWhere As String

If Not IsNull(Me.ctrl-DateSelector) Then
strWhere = strWhere & " AND " & _
"DateCallReceived = #" & Me.ctrl-DateSelector & "#"
End If
If Not IsNull(ctrl-MarketSelector) Then
strWhere = strWhere & " AND " & _
"Market = '" & Me.ctrl-MarketSelector & "'"
End If
If Not IsNull(ctrl-ClientSelector) Then
strWhere = strWhere & " AND " & _
"Client = '" & Me.ctrl-MarketSelector & "'"
End If
If Len(strWhere) > 4 Then strWhere = Left(strWhere, Len(strWhere) - 4)
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

Where I have assumed Market and Client to be text, and DateCallReceived
to be Date/Time type.

On your second question, I couldn't really tell you anything based on
the information provided, other than to check your references and the
selection of an appropriate event to refresh.

HTH,
Nikos

 

[EmAlbritton via AccessMonster.com]

Nikos-

Thanks for the start....but it still isn't working.

Here is the OnClick Event Procedure for the button:

Private Sub Button_RunReport_Click()
On Error GoTo Err_Button_RunReport_Click

Dim strWhere As String

If Not IsNull(Me.ctrl_DateSelector) Then
strWhere = strWhere & " AND " & _
"DateCallReceived = #" & Me.ctrl_DateSelector & "#"
End If
If Not IsNull(ctrl_MarketSelector) Then
strWhere = strWhere & " AND " & _
"Market = '" & Me.ctrl_MarketSelector & "'"
End If
If Not IsNull(ctrl_ClientSelector) Then
strWhere = strWhere & " AND " & _
"Client = '" & Me.ctrl_ClientSelector & "'"
End If
If Len(strWhere) > 4 Then strWhere = Left(strWhere, Len(strWhere) - 4)
DoCmd.OpenReport "Rpt-CorporateReportToClient", acViewPreview, , strWhere



Exit_Button_RunReport_Click:
Exit Sub

Err_Button_RunReport_Click:
MsgBox Err.Description
Resume Exit_Button_RunReport_Click

End Sub


The error message I am getting is:

Syntax error (missing operator) in query expression '( AND DateCallReceived =
#8/23/2006# AND Market = )'.

8/23/2006 was the date that I entered in my date selector.

Any ideas?

Emily

 

[Nikos Yannacopoulos]

Emily,

But of course! My stupid mistake....

If Len(strWhere) > 4 Then strWhere = Left(strWhere, Len(strWhere) - 4)

should be:

If Len(strWhere) > 4 Then strWhere = Right(strWhere, Len(strWhere) - 5)

so it drops off the leading " AND ", instead of truncating the end of
the string, which is what my foolish mistake did (also, I got my
counting wrong, " AND " is five chars long, not four!).

Hope it works this time. Rgds,
Nikos

 

[EmAlbritton via AccessMonster.com]

Nikos-

I am back again....just got back around to this issue in my DB.

I made the change you suggested in your last post and it still isn't working.
The error message received is as follows:

Syntax error (missing operator) in query expression '( AND DateCallReceived =
#8-23-2006# AND Market = 'Saint Louis, MO' AND Client = 'Conce)'.

I don't really know where to go from here. I know that my data table
contains records that contain the specified information, but selecting them
in the combo boxes (and date selector/text box) then clicking the report
button gives me the above error.

Help!

This function is one of the biggies that everyone is waiting on. This is
basically one of the main examples I gave in "selling" this project in the
first place.

Thanks,
Emily

 

[Nikos Yannacopoulos]

Emily,

The error message suggests you didn't make the change, or made it
temporarily but didn't save; otherwise the strWhere string would not
start with " AND " before DateCallReceived, nor would it have the Client
name truncated by 5 characters, as I suspect is happening here. Please
check again, and if in doubt repost the whole event code.

HTH,
Nikos

 

[EmAlbritton via AccessMonster.com]

Hello there again.

Ok...I went back and looked, and I have no idea really what I did to it...but
I redid the OnClick to read as follows:

Private Sub Button_RunReport_Click()
On Error GoTo Err_Button_RunReport_Click

Dim strWhere As String

If Not IsNull(Me.ctrl_DateSelector) Then
strWhere = strWhere & " AND " & _
"DateCallReceived = #" & Me.ctrl_DateSelector & "#"
End If
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_MarketSelector & "'"
End If
If Len(strWhere) > 4 Then strWhere = Right(strWhere, Len(strWhere) - 5)
DoCmd.OpenReport "Rpt-CorporateReportToClient", acViewPreview, , strWhere

Exit_Button_RunReport_Click:
Exit Sub

Err_Button_RunReport_Click:
MsgBox Err.Description
Resume Exit_Button_RunReport_Click

End Sub



Now...the report opens, but it is blank. I am using a date, market, client
combination that I know exists in the source table. I really am at a loss
now.

If I select just a market, the report runs for that market. If I select just
the date, the report runs for the date. I can select the date and the market
and it runs fine. If I try and add the client, the report runs blank.

I don't know if this might be a problem because of the cascade on the market
to the client. I have a Where clause on the client combo so that the only
clients listed are based on the market that is selected.

Of course...I have probably just made this all confusing as ***.

I appreciate all the help you have already provided and will be grateful if
you can help more so that I can get this to work.

Emily

 

[EmAlbritton via AccessMonster.com]

Nikos-

I need your help on another somewhat related issue.

I have "mastered" the art of the cascading combo box...but can't figure out
how to get around nulls. Basically, I want to select a value in cbo2
dependent on cbo1 if bco1 is not null. If cbo1 is null, then I want to
select a value in cbo2 based on the table that is driving it all. Does that
make any sense at all?

cbo1
cbo2
cbo3
cbo4
cbo5

right now, if cbo1 is null, then none of the rest of them work....if cbo1 is
ok, then cbo2 works....if cbo2 is null....then 3, 4, & 5 don't work.

I have searched for cascading combos with null values, and didn't read
anything that I thought would solve my issue.

Any assistance is greatly appreciated.

Thanks again,
Emily

 

[Nikos Yannacopoulos]

Alright, you can shoot me... another coding error on my part (always a
risk with untested code): in the third If in the strWhere construction
section, my code reads the value of ctrl_MarketSelector, not
ctrl_ClientSelector as it should! Fix that and you should be fine.

HTH,
Nikos

 

[Nikos Yannacopoulos]

What method (macro? code?) do you use to requery the combos? How do you
assign the rowsources? Please post the SQL expression for each, if
static, and/or any code involved, if dynamic.

Nikos

 

[EmAlbritton via AccessMonster.com]

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

 

[EmAlbritton via 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

 

[EmAlbritton via AccessMonster.com]

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

 

[EmAlbritton via AccessMonster.com]

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;

 

[Nikos Yannacopoulos]

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

 

[Nikos Yannacopoulos]

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).

 

[EmAlbritton via AccessMonster.com]

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;

 

[EmAlbritton via AccessMonster.com]

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