Form filter

Hi,
I created a filter form whereby users select from drop down and/or enter date intervals and click a button to view selected records. If they do not make any selection and click the button, I would like the report to show everything. During the testing process, we identify:
1. I’ve a combo box named “status” with dropdowns 1: closed 2: open 3: pending etc. When pending is selected from the dropdown, the report displays blank even though there are pending cases.
2. Clicking the run query button without making any selections I would like the report to display everything.  But, it doesn’t give me everything.

I used the following code:
Private Sub cmdRunQuery_Click()

Dim strWhere As String

strWhere = " 1=1 "

If Not IsNull(Me.txtBeginRefDT) Then
    strWhere = strWhere & " AND [REFDT] >= #" & txtBeginRefDT & "# "
End If
If Not IsNull(Me.txtEndRefDT) Then
    strWhere = strWhere & " AND [REFDT] <= #" & txtEndRefDT & "# "
End If
If Not IsNull(Me.txtBeginAssignDT) Then
    strWhere = strWhere & " AND [ASSIGNDT] >= #" & txtBeginAssignDT & "# "
End If
If Not IsNull(Me.txtEndAssignDT) Then
    strWhere = strWhere & " AND [ASSIGNDT] <= #" & txtEndAssignDT & "# "
End If
If Not IsNull(Me.cboStatus) Then
   strWhere = strWhere & " AND [STATDESC] = """ & Me.cboStatus & """ "
End If
If Not IsNull(Me.cboInvesgigator) Then
   strWhere = strWhere & " AND [USERNM] = """ & Me.cboInvesgigator & """ "
End If
If Not IsNull(Me.cboInvestigatorType) Then
   strWhere = strWhere & " AND [INVTYPE] = """ & Me.cboInvestigatorType & """ "
End If
If Not IsNull(Me.txtBeginCloseDT) Then
    strWhere = strWhere & " AND [CLOSEDT] >= #" & txtBeginCloseDT & "# "
End If
If Not IsNull(Me.txtEndCloseDT) Then
    strWhere = strWhere & " AND [CLOSEDT] <= #" & txtEndCloseDT & "# "
End If
If Not IsNull(Me.cboCloseReason) Then
  strWhere = strWhere & " AND [CLSREASON] = " & Me.cboCloseReason
End If
If Not IsNull(Me.cboInvestigativeUnit) Then
   strWhere = strWhere & " AND [INVUNTDESC] = """ & Me.cboInvestigativeUnit & """ "
End If
If Not IsNull(Me.txtBeginProsReferredDT) Then
    strWhere = strWhere & " AND [PROSDT] >= #" & txtBeginProsReferredDT & "# "
End If
If Not IsNull(Me.txtEndProsReferredDT) Then
   strWhere = strWhere & " AND [PROSDT] <= #" & txtEndProsReferredDT & "# "
End If
If Not IsNull(Me.cboRefAgency) Then
   strWhere = strWhere & " AND [AGENCYNM] = """ & Me.cboRefAgency & """ "
End If
If Not IsNull(Me.txtBeginAcceptDT) Then
   strWhere = strWhere & " AND [PROSACCPTDT] >= #" & txtBeginAcceptDT & "# "
End If
If Not IsNull(Me.txtEndAcceptDT) Then
   strWhere = strWhere & " AND [PROSACCPTDT] <= #" & txtEndAcceptDT & "# "
End If
If Not IsNull(Me.txtBeginDeclineDT) Then
   strWhere = strWhere & " AND [PROSREJDT] >= #" & txtBeginDeclineDT & "# "
End If
If Not IsNull(Me.txtEndDeclineDT) Then
    strWhere = strWhere & " AND [PROSREJDT] <= #" & txtEndDeclineDT & "# "
End If
If Not IsNull(Me.txtBeginRecovLetteSentDT) Then
    strWhere = strWhere & " AND [LETTERDT] >= #" & txtBeginRecovLetteSentDT & "# "
End If
If Not IsNull(Me.txtEndRecovLetterSentDT) Then
   strWhere = strWhere & " AND [LETTERDT] <= #" & txtEndRecovLetterSentDT & "# "
End If
If Not IsNull(Me.txtBeginFinalSetlmtDT) Then
    strWhere = strWhere & " AND [FINALDT] >= #" & txtBeginFinalSetlmtDT & "# "
End If
If Not IsNull(Me.txtEndFinalSetlmtDT) Then
   strWhere = strWhere & " AND [FINALDT] <= #" & txtEndFinalSetlmtDT & "# "
End If

Debug.Print strWhere   'show me the value

DoCmd.OpenReport "rpt_PICTS_CASES_X_v1", acViewPreview, , strWhere
End Sub

Open in new window


Can you please assist?

TIA

Regards,
OceanCityData Analyst Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Print strWhere to the debug window before the report opens and make sure that there really is data that satisfies the criteria.
0
Ryan ChongCommented:
for cboStatus

it seems that you're mapped to int value instead of string value while you referring to string value in your statement below:

>>strWhere = strWhere & " AND [STATDESC] = """ & Me.cboStatus & """ "

hence, try refer to Me.cboStatus.text instead of Me.cboStatus
0
PatHartmanCommented:
Ryan, the .text and .value properties refer to state.  They have nothing to do with data type.  The .text value is used in the on Change event if you want to analyze the characters one by one as they are being typed into the input buffer.  The .Value property holds the visible value of the control.  The .OldValue property contains the value as it was retrieved from the table or null if this is a new record.  The .Value and .OldValue will be identical on an existing record if no change has been made.  In earlier versions of Access, the .text property was only accessable when the control had the focus and prior to the BeforeUpdate event.  The current version of Access seems to allow other events to reference the .text property but I haven't experimented so I'm not sure when the .text value buffer gets moved to the .Value buffer.

OceanCity, as you have described the combo, the bound column will be numeric and so Status will be numeric and therefore should not be encased in double quotes.  Dates are delimited with #; text with single or double quotes; but numbers are not delimited.

ALSO, always refer to your form fields using Me.  It is more efficient because Access knows immediately which library has the definition of the variable plus, you get intellisense.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gustav BrockCIOCommented:
Most likely, STATDESC is a numerical value, thus try:

If Not IsNull(Me.cboStatus) Then
   strWhere = strWhere & " AND [STATDESC] = " & Me.cboStatus & ""
End If

Open in new window

0
Ryan ChongCommented:
@Pat,

thanks for correction, I was not at my laptop while making that statement. after the verification, it seems i was wrong.

what i mean is if the combo box: cboStatus was bound with an ID (hidden) + Char Value, but somehow it's not referring to its ID value but instead to its Char Value, then we could use: cboStatus.Column(1) instead.

OR as what Gustav's proposing, to use [STATDESC] = " & Me.cboStatus if STATDESC is a numeric field.
29076940.accdb
0
OceanCityData Analyst Author Commented:
Thank you all for your reply posts, and sorry for the late reply as I was out of town due to work assignment.  

I’ve attached screenshot to show the structure of table/query/form etc. As you can see, the person who designed the db didn’t store the numeric ID and text descriptions in the same table. For the filter form I’m working on, users want to see the text description in the dropdown. I thought joining the two tables for the row source will solve the problem

Pat,
Print strWhere to the debug window before the report opens and make sure that there really is data that satisfies the criteria.
Yes, looking at the table, records exist that satisfy the criteria.  For instance:
1. we have a case that is ‘pending assignment’, but when selecting status = pending assignment the report was blank  
2. There is a case assigned on 11/12/17, but I was requesting Assigned between 10/31/17 to 12/31/17 and the report was blank

How do you use strWhere to the debug window to make sure data that satisfies the criteria?


TIA

Regards,
screenshot.docx
0
PatHartmanCommented:
1. we have a case that is ‘pending assignment’, but when selecting status = pending assignment the report was blank  
Is status the ONLY criteria?????  I asked you to print your criteria string for two reasons:
1. to see if there is OTHER criteria in the where clause
2. to see If you are providing a NUMERIC value for status.

Put a stop on the line that opens the report.  Then in the debug window, print strWhere

? strWhere
0
OceanCityData Analyst Author Commented:
Pat,
I’m relatively new to vba can you please tell me how:
Put a stop on the line that opens the report.  Then in the debug window, print strWhere

TIA
Regards,
0
PatHartmanCommented:
If you click in the left margin of that line of code, a circle will appear.  When the code runs, it will stop here and the code window will open.  At that time, you can print the string to the immediate window and post it here.
0
OceanCityData Analyst Author Commented:
Pat, thanks.
Let me know if the following is what you are looking to see...

strWhere.png
0
PatHartmanCommented:
The display is showing

1=1

as the where clause.  I don't think that is your intent as it would always return true and therefore select EVERY record.

However, you put the stop too early.  We STILL are not seeing the entire string.  Please move the stop down to the OpenReport line where I suggested originally.  

Put a stop on the line that opens the report.
The objective is to see the Where clause that is being sent to the report.
0
OceanCityData Analyst Author Commented:
Pat,
Please let me know if the attached screenshot is what you want to see...

TIA

Regard,
strWhere.docx
0
PatHartmanCommented:
Apparently you are printing the string every time the code runs.  Are ANY of the executions working?  Try clearing the immediate window and then running the procedure with nothing selected since that is what you said doesn't work.  That way we will see ONLY the expression that has the problem.

PS - if you have changed the code, you need to also repost that.  I don't see anywhere in the code where you reference PICTSSTAT.
0
OceanCityData Analyst Author Commented:
Thanks Pat.
Are ANY of the executions working?
Mostly yes.  But, aside from the report doesn't display everything when nothing is selected, we noticed blank report when querying the following:
Assign date between 10/31/17 and 12/31/17 or status = pending assignment etc.  We verified records exist that satisfy these criteria.

Below is  the modified code.  The only line I changed to test was the PICTSSTATS (numeric) field.  Attached is screenshot to show strWhere result as well.
Private Sub cmdRunQuery_Click()

Dim strWhere As String

strWhere = " 1=1 "

If Not IsNull(Me.txtBeginRefDT) Then
    strWhere = strWhere & " AND [REFDT] >= #" & txtBeginRefDT & "# "
End If
If Not IsNull(Me.txtEndRefDT) Then
    strWhere = strWhere & " AND [REFDT] <= #" & txtEndRefDT & "# "
End If
If Not IsNull(Me.txtBeginAssignDT) Then
    strWhere = strWhere & " AND [ASSIGNDT] >= #" & txtBeginAssignDT & "# "
End If
If Not IsNull(Me.txtEndAssignDT) Then
    strWhere = strWhere & " AND [ASSIGNDT] <= #" & txtEndAssignDT & "# "
End If
'If Not IsNull(Me.cboStatus) Then
'   strWhere = strWhere & " AND [STATDESC] = """ & Me.cboStatus & """ "
'End If
If Not IsNull(Me.cboStatus) Then
   strWhere = strWhere & " AND [PICTSSTAT] = " & Me.cboStatus & ""
End If

If Not IsNull(Me.cboInvesgigator) Then
   strWhere = strWhere & " AND [USERNM] = """ & Me.cboInvesgigator & """ "
End If
If Not IsNull(Me.cboInvestigatorType) Then
   strWhere = strWhere & " AND [INVTYPE] = """ & Me.cboInvestigatorType & """ "
End If
If Not IsNull(Me.txtBeginCloseDT) Then
    strWhere = strWhere & " AND [CLOSEDT] >= #" & txtBeginCloseDT & "# "
End If
If Not IsNull(Me.txtEndCloseDT) Then
    strWhere = strWhere & " AND [CLOSEDT] <= #" & txtEndCloseDT & "# "
End If
If Not IsNull(Me.cboCloseReason) Then
  strWhere = strWhere & " AND [CLSREASON] = " & Me.cboCloseReason
End If
If Not IsNull(Me.cboInvestigativeUnit) Then
   strWhere = strWhere & " AND [INVUNTDESC] = """ & Me.cboInvestigativeUnit & """ "
End If
If Not IsNull(Me.txtBeginProsReferredDT) Then
    strWhere = strWhere & " AND [PROSDT] >= #" & txtBeginProsReferredDT & "# "
End If
If Not IsNull(Me.txtEndProsReferredDT) Then
   strWhere = strWhere & " AND [PROSDT] <= #" & txtEndProsReferredDT & "# "
End If
If Not IsNull(Me.cboRefAgency) Then
   strWhere = strWhere & " AND [AGENCYNM] = """ & Me.cboRefAgency & """ "
End If
If Not IsNull(Me.txtBeginAcceptDT) Then
   strWhere = strWhere & " AND [PROSACCPTDT] >= #" & txtBeginAcceptDT & "# "
End If
If Not IsNull(Me.txtEndAcceptDT) Then
   strWhere = strWhere & " AND [PROSACCPTDT] <= #" & txtEndAcceptDT & "# "
End If
If Not IsNull(Me.txtBeginDeclineDT) Then
   strWhere = strWhere & " AND [PROSREJDT] >= #" & txtBeginDeclineDT & "# "
End If
If Not IsNull(Me.txtEndDeclineDT) Then
    strWhere = strWhere & " AND [PROSREJDT] <= #" & txtEndDeclineDT & "# "
End If
If Not IsNull(Me.txtBeginRecovLetteSentDT) Then
    strWhere = strWhere & " AND [LETTERDT] >= #" & txtBeginRecovLetteSentDT & "# "
End If
If Not IsNull(Me.txtEndRecovLetterSentDT) Then
   strWhere = strWhere & " AND [LETTERDT] <= #" & txtEndRecovLetterSentDT & "# "
End If
If Not IsNull(Me.txtBeginFinalSetlmtDT) Then
    strWhere = strWhere & " AND [FINALDT] >= #" & txtBeginFinalSetlmtDT & "# "
End If
If Not IsNull(Me.txtEndFinalSetlmtDT) Then
   strWhere = strWhere & " AND [FINALDT] <= #" & txtEndFinalSetlmtDT & "# "
End If

Debug.Print strWhere   'show me the value

DoCmd.OpenReport "rpt_PICTS_CASES_X_v1", acViewPreview, , strWhere

End Sub

Open in new window

TIA
Regards,
strWhere1.docx
0
PatHartmanCommented:
If the report is not showing everything with only  1=1 as the criteria, then there is a different problem with the query.  Does the query join two or more tables?  Are you using inner joins when you should be using outer joins?  Double click on the middle of a join line.  You will see three options.  Read them carefully and choose the option that will give you the results you want.

Assign date between 10/31/17 and 12/31/17 or status = pending assignment etc.  We verified records exist that satisfy these criteria.
This is an AND operator in your criteria.  That means that BOTH conditions must be true in order for a record to be selected.

Check your AssignedDate in the table.  Is it formatted?  It should NOT be.  You never want to format fields in a table because that masks the actual contents.  For example, if the AssignedDate contains time as well as the date, your criteria could miss some records.  Always use Date() when you want only the date and use Now() when you want to also store the time of day.
0
OceanCityData Analyst Author Commented:
Pat, thanks.

All my dates are formatted as short date in table/form.  
Does the query join two or more tables?
yes, screenshot attached
Are you using inner joins when you should be using outer joins?
I'm not certain here, I used the default join (screenshot)

TIA
Regards,
query-screenshot.docx
0
PatHartmanCommented:
Did you notice that the form and property sheet are backward in the pictures? I was able to rotate it but it made me chuckle.

Remove the format property from the table and save it.  Do the dates include time?  That will mess up criteria.  If you don't want the dates, you can run an update query to get rid of them but you'll also have to modify the code that is storing the time and make it store just the date.

When you use an inner join (the default join type) a matching record is REQUIRED in BOTH tables or no row will be returned.  For example, if you have no rows in tblInvestigators for the PICTSID of tblPICCTSDATA, no row will be returned from this query.  If you have no row in tblFINANCIAL for the PICTSID in tblPICTSDATA, no row will be returned from this queyry  So look at all the joins and ask yourself "do I want a row returned even if tblB has no matching data?"  If the answer is yes, you need to change the join type.
0
Ryan ChongCommented:
try use less condition to start the debugging, and then you should able to minimize the issue where caused the problem.

and make sure you have enough data for testing.

upload a sample (mask or put dummy data) could probably help to resolve the issue faster? just a suggestion.
0
OceanCityData Analyst Author Commented:
Thank you both.

Pat,
In the table, property I attempted to remove the date format and I noticed the following:
This property cannot be modified in linked tables

I didn’t modify the join type and used the default join (inner join) as I do not want a row returned when no matching data exist in both table.  
Two issues that I’m currently experiencing:
1. when nothing is selected the report was supposed to display everything, but, it doesn’t
2. Even if we validated data exist that satisfy multiple selections, the report is blank

Ryan,
I'v enough data for validation purposes.  As for the strip down version of the db.  I'll pass it by my supervisor to see if he's okay with it.  
Can you please give me instructions, and/or links how to accomplish this?

TIA
Regards,
0
PatHartmanCommented:
This property cannot be modified in linked tables
That is correct.  You would need to make the change in the BE, whatever that is.

To analyze the problem, you will have to work through the query, table by table.  Apply the criteria to the first table.  Do you get what you expect?   Add the next table using the inner join. Run the query.  Do you get what you expect?  Add the next table, etc.

I'm pretty sure you will find that it is the joins that are causing the problem - unless of course it is the date so start by removing the format on the date so you can see if records include time.
0
OceanCityData Analyst Author Commented:
Pat,

1.  I did remove the formatting in the table, but I didn’t notice any changes.

2. To isolate the issue, I did work through the query table by table.  With out applying creiteria, I ran my query after inserting  a table. The first thing I noticed was that when I inserted either of the following tables (tblAgency & tblFinancial) I didn’t get the complete value for PICTSSTAT field.  3 = pending assignment is not showing in the list.  
However, before adding either of these two tables and run the query, 3 = pending assignment was available.  
Please see attached screenshot.

TIA
Regards,
query-screenshot1.docx
0
PatHartmanCommented:
In a query that joins multiple tables, you will need to use left joins (or right joins) to handle optional values.  If every join line connects columns that are set as required AND you have defined a relationship on the same columns, then you would only ever need to use inner joins (the default join type).

If you change the join type on these two tables to left, then the query should produce the data you expect.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OceanCityData Analyst Author Commented:
Pat, thanks.
Is left join the same as outer join?  So, when I edit the relationship choose the 2nd option, correct?
Before I do that though, I ran into another issue…
I thought it will be easier to copy the DB, and post a strip down version etc. following is the steps I took.  In my Dev. environment I’ve a split DB (be/fe).  

I created a new DB named it SampleDb  
Using external data, I imported my objects that are relevant to my questions, and updated two of the tables.  In the process, I forgot to update the linkage.  As a result, two of my Dev. environment tables are modified.  How do I go about recovering the original tables back to my Dev. environment?
I do have a backup copy that I saved on removable drive.  Or, may be from the production environment?

TIA
Regards,
0
PatHartmanCommented:
left and right are both outer joins.  Choose the option from the list of three that makes sense for what you want.

I don't understand what you did.  You cannot modify linked tables from the FE.  You would have had to go into the BE to modify them.  Did you change the table layout or is a formerly linked table now local or vice versa?
0
OceanCityData Analyst Author Commented:
Pat,
Basically, what I did was copy my Dev environment to a different folder (Test folder) I then modified records in two tables (deleted existing records and edit dummy records).  Since I didn’t relink to the new location, what I did affected the dev environment i.e. table were modified.  
In order to get my Dev environment back I deleted the two tables from my Dev. and re-import them from production environment.  However, when I run my queries, reports etc. I get nothing (blank).

The two tables I modified are: tblPICTSDATA & tblAgency
Below is the SQL for one of my query:

SELECT tblPICTSDATA.PICTSID, tblPICTSDATA.RECIPFTNM, tblPICTSDATA.RECIPLTNM, tblPICTSDATA.PROVNM, tblPICTSDATA.REFDT, tblSTATUS.STATDESC, tblPICTSDATA.ASSIGNDT, tblPICTSDATA.CLOSEDT, tblPICTSDATA.CLSREASON, tblPIUNITS.INVUNTDESC, tblAGENCY.AGENCYNM, tblPICTSDATA.PROSDT, tblPICTSDATA.PROSACCPTDT, tblPICTSDATA.PROSREJDT, tblINVESTIGATORS.INVTYPE, tblLOGIN.USERNM, tblFINANCIAL.FINRRAMT, tblFINANCIAL.LETTERDT, tblFINANCIAL.FINALDT, tblFINANCIAL.SETLMTAMT
FROM tblSTATUS INNER JOIN (tblPIUNITS INNER JOIN (((tblAGENCY INNER JOIN tblPICTSDATA ON tblAGENCY.AGENCYID = tblPICTSDATA.PROSAGENCY) INNER JOIN tblFINANCIAL ON tblPICTSDATA.PICTSID = tblFINANCIAL.PICTSID) INNER JOIN (tblLOGIN INNER JOIN tblINVESTIGATORS ON tblLOGIN.USERID = tblINVESTIGATORS.USERID) ON tblPICTSDATA.PICTSID = tblINVESTIGATORS.PICTSID) ON tblPIUNITS.INVUNTID = tblPICTSDATA.IUID) ON tblSTATUS.STATUSID = tblPICTSDATA.PICTSSTAT;

Open in new window

TIA
Regards,
0
PatHartmanCommented:
Restore your dev backup for the BE.  Don't try to fix it piece meal.
1
OceanCityData Analyst Author Commented:
Pat,
I changed the join type on these two tables to left and the query produced the data I was expecting.
Thank you very much!

Regards,
0
PatHartmanCommented:
You're welcome.
0
PatHartmanCommented:
OceanCity said this is what he did.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.