combo and text boxes for query criteria

[jzacharias]

I have an unbound form with two combo boxes (one for badge number and the other for last name) and two text boxes with a date picker. Everything was working fine until I put the two text boxes in and now it isn't returning the correct information. I'm sure I have something wrong in the query design, but my brain is tapped.

Field CitationDate: Between [forms]![CriteriaInput]![StartDate] And [forms]![CriteriaInput]![EndDate]

[CitationDate]=[forms]![CriteriaInput]![StartDate] Or [forms]![CriteriaInput]![StartDate] Is Null

[CitationDate]=[forms]![CriteriaInput]![EndDate] Or [forms]![CriteriaInput]![EndDate] Is Null

 

[jzacharias]

I have it to where I am getting the correct name or badge number back, but not limiting between the two dates.

 

[jzacharias]

I believe it is ignoring the criteria for the date. Anyone have any suggestions?

 

[jdraw]

Tell us about the form and the combo and text boxes.
What is the rowsource of the combo box?
Please show the SQL view of the query.

 

[jzacharias]

Unbound form named criteriainput
Combo 1 named BadgeNumberSearch, Row Source is SELECT [tblDeputy].[Deputy_ID], [tblDeputy].[BodyNumber] FROM tblDeputy;

Combo 2 named SearchName, Row Source is SELECT [tblDeputy].[Deputy_ID], [tblDeputy].[Last], [tblDeputy].[First], [tblDeputy].[Middle] FROM tblDeputy ORDER BY [Last], [First];

Text 1 named StartDate
Text 2 named EndDate

SELECT tblCitation.Citation_ID, tblCitation.Shift, tblCitation.Rotation, tblDistrict_Contract.District_Contract, tblOffenses.OffenseType, tblOffenses.Offense, tblCitation.CitationDate, Year([CitationDate]) AS CitationYear, [tblDeputy].[Last] & ", " & [tblDeputy].[First] & " " & [tblDeputy].[Middle] AS Expr1, tblDeputy.BodyNumber, tblDeputy.Last, tblDeputy.First, tblDeputy.Middle
FROM ((tblShift INNER JOIN ((tblCitation INNER JOIN tblDistrict_Contract ON tblCitation.District_ID = tblDistrict_Contract.District_ID) INNER JOIN tblDeputy ON tblCitation.Deputy_ID = tblDeputy.Deputy_ID) ON tblShift.Shift_ID = tblDeputy.Shift_ID) INNER JOIN tblOffenses ON tblCitation.Offenses_ID = tblOffenses.Offenses_ID) INNER JOIN tblRotation ON tblDeputy.Rotation_ID = tblRotation.Rotation_ID
WHERE (((tblCitation.CitationDate) Between [forms]![CriteriaInput]![StartDate] And [forms]![CriteriaInput]![EndDate]) AND (([CitationDate]=[forms]![CriteriaInput]![StartDate] Or [forms]![CriteriaInput]![StartDate] Is Null)=True) AND (([CitationDate]=[forms]![CriteriaInput]![EndDate] Or [forms]![CriteriaInput]![EndDate] Is Null)=True) AND (([BodyNumber]=[forms]![CriteriaInput]![BodyNumberSearch] Or [forms]![CriteriaInput]![BodyNumberSearch] Is Null)=True) AND (([Last]=[forms]![CriteriaInput]![SearchName] Or [forms]![CriteriaInput]![SearchName] Is Null)=True)) OR (((tblDeputy.BodyNumber)=[forms]![CriteriaInput]![BodyNumberSearch])) OR (((tblDeputy.Last)=[forms]![CriteriaInput]![SearchName]));

 

[jzacharias]

The combo boxes work fine....It's just the Between [forms]![CriteriaInput]![StartDate] And [forms]![CriteriaInput]![EndDate] that doesn't seem to work

 

[jdraw]

Can you post a dumbed down version of your database (remove anything confidential)?

Are the combos related in any manner?
Usually easier if you start simple and work up to multi conditions.

 

[jzacharias]

Here is my db...just has names, nothing confidential.

 

[jdraw]

Why are there 2 combo boxes? It's confusing.
I have to go out for a few hours.

 

[jzacharias]

Ever try reading an officers signature at the bottom of a ticket? Lol. Sometimes you have to search by the body number if you can't read the name and vice versa if the body number isn't on the ticket.

 

[jdraw]

What exactly is a body number?
I can see officer number or Badge Number, but Body Number is a new one.

Are your combos related somehow, or are they totally independent?

Perhaps you could describe the purpose of the from and each of the controls for clarity.

 

[jzacharias]

For some reason, it's been called body number with us. You can think of it as a badge number. The combo boxes are tied to the same table. I made two of them so someone can search for a deputy either by their name or body number then create a report showing citation totals for a date range.

 

[jdraw]

I modified the Form to search by Body/Badge Number (top combo)
and modified the Rowsource by showing the Body number in the drop down rather that the Deputy Id, if you really refer to BodyNumber rather than DeputyId.

Do BadgeNumbers/Body Numbers get changed? Are they assigned and always associated with a specific officer and removed if that officer leaves?

So as I understand it, you

Search by Badge or by Name
and you can include a Start and end date or not?

Seems you can not leave Badge or Name empty and just get a report based on Start/End dates???

I don't use macros so can not help there.

I'm not very familiar with the 2010 interface, and had a few anomalies.
The Between Dates in the Where clause did not limit the records???
When I hard coded the dates in the underlying query, the Between worked as expected??

Perhaps someone else more familiar with 2010 and macros can be more help.

 

[jzacharias]

I found a way for it to work at this link: http://office.microsoft.com/en-us/a...ers-with-queries-and-reports-HA001117077.aspx

The only thing I can see that made a difference was setting "Group By" in the query.