Solved

Where condition

Posted on 2013-12-24
22
302 Views
Last Modified: 2013-12-26
Experts, I have a filter form with an unbound field txtClient.  Why wouldnt the below "see" what I put in txtClient?   I need my results to be filtered for whatever I put in txtClient and if Null then ignore txtClient.  

Please see Line 5

Private Sub cmdOpenRpt_Click()

    Dim strCriteria As String
    
    strCriteria = "([End User] Like '" & Me.[txtClient] & "*' And " _
                       & "[EntryDate]>=#" & Me.[txtdatefrom] & "# And " _
                       & "[EntryDate]<#" & Me.[txtDateTo] + 1 & "# And " _
                       & "[EntryDate] Is Not Null And " _
                       & "[SentForSign]>=#" & Me.[txtdatefrom] & "# And " _
                       & "[SignedDate]>=#" & Me.[txtdatefrom] & "#"
                      
    DoCmd.OpenReport "rptDepartures_other", acViewReport, strCriteria


End Sub

Open in new window

0
Comment
Question by:pdvsa
  • 10
  • 3
  • 3
  • +3
22 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 39738564
VBA might be treating Me.[txtClient] as a field, not a control.

Try using txtClient.Value.
0
 

Author Comment

by:pdvsa
ID: 39738574
imnorie:  that didnt seem to be the solution.  I did not get any errors but the data was not limited to what I typed into me.txtClient.

I have it like this now (notice the .value:
strCriteria = "([End User] Like '" & Me.[txtClient].Value & "*' And " _

any other suggestions?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39738589
Suspecting left parenthesis (:

 "[End User] Like '" ......
0
 

Author Comment

by:pdvsa
ID: 39738595
That didnt seem to be it either.  No errors...just didnt filter the records for what I put in txtClient.    

tblEndUser.[End User] is not a combo box. It is a text field so I think it is being referenced correctly (not needing column(1))

any other ideas?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39738616
Try each part of the filter and see where it fails to output the expected.

like:
1)  strCriteria = "[End User] Like '" & Me.[txtClient] & "*' And TRUE"

2) strCriteria = "([End User] Like '" & Me.[txtClient] & "*' And " _
                       & "[EntryDate]>=#" & Me.[txtdatefrom] & "# And TRUE"
                       
and so on, replacing And by And TRUE to reduce editing.
0
 

Author Comment

by:pdvsa
ID: 39738635
hnasr,

I commented out any dates and left only the
 strCriteria = "([End User] Like '" & Me.[txtClient] & "*' And " _
and tried
 strCriteria = "[End User] Like '" & Me.[txtClient] & "*' And TRUE

there is no filtering at all for what I put in me.txtClient.  

The name of txtClient is correct.  
(see pic of filter form)

what next?  

Filter Form
0
 

Author Comment

by:pdvsa
ID: 39738639
attached  below is a pic of the report.
I do have a field on it with control source [End User]

report
0
 

Author Comment

by:pdvsa
ID: 39738641
[End User] is not a combo box.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39738690
What I expect if you can, to upload a simple database with a sample report showing the issue.

I meant by filtering is the parts of the strCriteria variable.
0
 

Author Comment

by:pdvsa
ID: 39738715
will try tomorrow....thanks


this is how i have it and it is not filtering.
Private Sub cmdOpenRpt_Click()

    Dim strCriteria As String
   
     strCriteria = "[End User] Like '" & Me.[txtClient] & "*' And " _

     
    DoCmd.OpenReport "rptDepartures_other", acViewReport, strCriteria



End Sub
0
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39738770
What happens if you do something like this:

strCriteria = "[End User] Like 'JimBob*'"

Feel free to substitute "JimBob" for any string that would be a good example filter for testing. Does the report give you the expected filter results now? If no, there is a problem NOT related to the filter criteria.

Another good test would be to verify the report by putting the filter criteria into the properties of the report without VBA code. Does it return the expected filtered results?

Sometimes I get stuck chasing ghosts in the forest but I cannot see the forest for all the trees. Trying to eliminate the less obvious.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:pdvsa
ID: 39738811
OK thanks for that tip.  that strCriteria did not filter it.
I took your advice and put the criteria directly in the record source of the query and it does work.  

here is the paste of the VBA:
SELECT tblDepartures.*, tblEndUser.[End User], tblDepartures.Compliance_GOPS_YN, Projects.[Project Name], Projects.ContractAward
FROM (tblDepartures LEFT JOIN Projects ON tblDepartures.ProjectID = Projects.ID) LEFT JOIN tblEndUser ON Projects.EndUserID = tblEndUser.EndUserID
WHERE (((tblEndUser.[End User]) Like "China*"))
ORDER BY tblEndUser.[End User];

What should I do next?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39739082
Tony,

Area all of the text boxes you are using in this criteria unbound?  Try:

    strCriteria = "[End User] Like '" & Me.txtClient & "*'"

I never wrap my references to controls in brackets [ ].  When I see brackets, I think recordset fields, not controls.  The entire thing, assuming all of those references are controls, and not fields in the recordset should look like:

    strCriteria = "([End User] Like '" & Me.txtClient & "*' And " _
                       & "[EntryDate]>=#" & Me.txtdatefrom & "# And " _
                       & "[EntryDate]<#" & cdate(Me.txtDateTo) + 1 & "# And " _
                       & "[EntryDate] Is Not Null And " _
                       & "[SentForSign]>=#" & Me.txtdatefrom & "# And " _
                       & "[SignedDate]>=#" & Me.txtdatefrom & "#"

Notice that I added the cDate( ) function on the third line.  Access may interpret the value in that unbound textbox as text, not as a date, so to make sure that the +1 works, I think you will need to do the cDate conversion of that value before adding one to the date.
0
 

Author Comment

by:pdvsa
ID: 39739127
that didnt seem to do it either.  

I have attached a pared down db.  
The filter form autoopens.

You can enter "China" in the Client box and press one of the buttons for date entry.  The report doesnt seem to filter for only China.

thank you for the help.
EE---Copy.accdb
0
 
LVL 11

Accepted Solution

by:
Technodweeb earned 167 total points
ID: 39739156
Got you a solution...
I changed your strCriteria line to his:
strCriteria = "(((qryDepartures.[End User]) Like '" & Me.txtClient & "*'))" 

Open in new window

I also added a line to apply the filter:
DoCmd.SetFilter WhereCondition:=strCriteria

Open in new window

These were modified on the Button OnClick method for the Form

I am attaching your database so you can see the changes.
EE---Copy.accdb
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39739164
The very simple method is to use:

  strCriteria = "([End User] Like '" & Nz(Me![txtClient],[End User]) & "*' And " _

/gustav
0
 

Author Comment

by:pdvsa
ID: 39739214
gustav, I seem to get an error (see pic)

error
Technoweb:
OK that works however I also need to have the dates where conditions below it.  I take out the single quote (line 5) before the And " _ but I think something is not correct as Access adds another double quote.  

do you see how to incorporate the Dates Where condition with the txtClient?  thank you

Private Sub cmdOpenRpt_Click()

    Dim strCriteria As String
    
 strCriteria = "(((qryDepartures.[End User]) Like '" & Me.txtClient & "*'))" ' And " _
                       & "[EntryDate]>=#" & Me.txtdatefrom & "# And " _
                       & "[EntryDate]<#" & CDate(Me.txtDateTo) + 1 & "# And " _
                       & "[EntryDate] Is Not Null And " _
                       & "[SentForSign]>=#" & Me.txtdatefrom & "# And " _
                       & "[SignedDate]>=#" & Me.txtdatefrom & "#"
      
    DoCmd.OpenReport "rptDepartures_other", acViewReport
    DoCmd.SetFilter WhereCondition:=strCriteria
    


End Sub

Open in new window

0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 133 total points
ID: 39739269
Tony,

Try:

strCriteria = "(qryDepartures.[End User] Like '" & Me.txtClient & "*') And " _
                       & "[EntryDate]>=#" & Me.txtdatefrom & "# And " _
                       & "[EntryDate]<#" & CDate(Me.txtDateTo) + 1 & "# And " _
                       & "[EntryDate] Is Not Null And " _
                       & "[SentForSign]>=#" & Me.txtdatefrom & "# And " _
                       & "[SignedDate]>=#" & Me.txtdatefrom & "#"
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
ID: 39739278
Yes, sorry, too fast. Try this:

strCriteria = IIf(IsNull(Me![txtClient]), "True", "[End User] Like '" & Me![txtClient] & "*'") & " And " _

/gustav
0
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39739323
Took me a bit to figure this one out...
Two things...
1. Your query is fine with the remaining date item tests, HOWEVER, one of your columns is not named right. You have [SentForSign] and the column is actually [SentForSig]

2. The last two tests will always produce null resultants because in your tblDepartures, neither column has any dates input. To get results to display, I arbitrarily filled in a date in the columns for  [SentForSig] and [SignedDate] that falls after the txtDateFrom date specified on the form.

The query below should work:
strCriteria = "(qryDepartures.[End User] Like '" & Me.txtClient & "*') And " _
                       & "[EntryDate]>=#" & Me.txtdatefrom & "# And " _
                       & "[EntryDate]<#" & CDate(Me.txtDateTo) + 1 & "# And " _
                       & "[EntryDate] Is Not Null And " _
                       & "[SentForSig]>=#" & Me.txtdatefrom & "# And " _
                       & "[SignedDate]>=#" & Me.txtdatefrom & "#"
0
 

Author Closing Comment

by:pdvsa
ID: 39739443
that was a schooling.  I appreciate the responses.  

Gustav:  that worked and not sure why because it didnt make reference to qrydepartures like techno and fyed did and it seemed like the reason those didnt work is because they didnt ref to qrydepartures.  
Techno:  that works
Fyed:      that works

thank you....happy holidays!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39739788
That's because:

> The very simple method is ...

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sub Reports 8 23
Syntax error [Missing Operator] in query expression 7 33
append to an ms access field 6 26
Query design issue 2 24
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now