Need help with Query parameters by form

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

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

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

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

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

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

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

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

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

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

--
Message posted via http://www.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

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

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

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