pdvsa
asked on
Where condition
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
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
ASKER
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?
I have it like this now (notice the .value:
strCriteria = "([End User] Like '" & Me.[txtClient].Value & "*' And " _
any other suggestions?
Suspecting left parenthesis (:
"[End User] Like '" ......
"[End User] Like '" ......
ASKER
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?
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?
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.
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.
ASKER
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?
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?
ASKER
[End User] is not a combo box.
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.
I meant by filtering is the parts of the strCriteria variable.
ASKER
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
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
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.
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.
ASKER
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_G OPS_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?
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_G
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?
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The very simple method is to use:
strCriteria = "([End User] Like '" & Nz(Me![txtClient],[End User]) & "*' And " _
/gustav
strCriteria = "([End User] Like '" & Nz(Me![txtClient],[End User]) & "*' And " _
/gustav
ASKER
gustav, I seem to get an error (see pic)
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 & "#"
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 & "#"
ASKER
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!
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!
That's because:
> The very simple method is ...
/gustav
> The very simple method is ...
/gustav
Try using txtClient.Value.