Query based on form shows extra records

[robsworld78]

Hi, I'm having issues with a query, it's based on multiple criteria's, a primary ID and a date field.

I've narrowed the problem down to the end of the criteria for the ID, it's the & "*"

Like [Forms]![SearchBox_CharitiesSalesSummaryReport]![CharityID] & "*"

The criteria I use for the date is this.

Between [Forms]![SearchBox_CharitiesSalesSummaryReport]![StartDate] And [Forms]![SearchBox_CharitiesSalesSummaryReport]![EndDate] Or Like [Forms]![SearchBox_CharitiesSalesSummaryReport]![SingleDate]

The problem is if charityID = 1 in the combo box the query shows the correct record first but also shows

charityID = 10, 11, 12, 13, 14, etc... 100, 101, etc...

if the ID starts with 1 it will show it.

This seems to be happening from & "*" at the end of the criteria for the charityID. If I remove the date criteria and that & "*" the query returns only charityID = 1 as it should. If I have the date criteria also added without the & "*" they don't work together.

I've tried empty quotes and adding the & "*" to the date criteria as well but it doesn't work.

Any ideas?

Thanks

 

[pbaldy]

The wildcard is doing what it's supposed to. Try

=[Forms]![SearchBox_CharitiesSalesSummaryReport]![CharityID]

 

[robsworld78]

Oh wow, thank-you sir! I would have guessed = and Like would be the same. Works as expected now.

 

[pbaldy]

Happy to help!

 

[robsworld78]

Sorry I was wrong, that didn't fix it, it acts the same as Like without the wildcard.

So using what you suggest requires that field to have a valid selection, if its blank and only date criteria used the query is empty.

Something needs to say use one criteria or two, that wildcard did that but returns those extra records as I mentioned.

EDIT:

If I do something like this with the charityID criteria which could be empty, date always has a valid entry, the query doesn't complain but still comes up empty.

IIf(IsNull([Forms]![SearchBox_CharitiesSalesSummaryReport]![CharityID]),=[Forms]![SearchBox_CharitiesSalesSummaryReport]![CharityID])

 

[Pat Hartman]

So far you haven't actually told us what you want. You've only shown us code that doesn't work. So, I'm going to guess at a solution. If the suggestion doesn't work, please tell us what you actually want rather than what doesn't work.

Where (CharityID = [Forms]![SearchBox_CharitiesSalesSummaryReport]![CharityID] OR [Forms]![SearchBox_CharitiesSalesSummaryReport]![CharityID] Is Null) AND (SomeDate Between [Forms]![SearchBox_CharitiesSalesSummaryReport]![StartDate] And [Forms]![SearchBox_CharitiesSalesSummaryReport]![EndDate] Or SomeDate = [Forms]![SearchBox_CharitiesSalesSummaryReport]![SingleDate])

I have no idea what the name of your date field is so change "SomeDate" to whatever that date name is.

FYI - Like is a string operation. It works with STRINGS. It does not necessarily work with numbers or dates. Like is ONLY used when you are going to use wild cards because you are searching for PART of a string. Like almost always prevents the query engine from being able to use indexes so it is quite inefficient when searching a large table. Therefore, do NOT use Like unless you actually want to search for a partial string and you are willing to pay the speed price of not being able to use an index.

 

[robsworld78]

Thanks for the info on Like! Unfortunately that didn't work but I might not have done it right. I see you mixed them so not sure where to put it. The Where gives errors in criteria so I tried in new column with total set to Where and if the "show" wasn't ticked the query ran but gave all results no matter what.

I'm trying to use this for a report. There's an unbound form that opens, it has a single date field (SingleDate) for looking at one day and it has 2 other date fields for a range, StartDate and EndDate, and finally it has the charityID combo box. The 4 fields are unbound as only need the data for the query. Once a date range or single date is added it can be opened and the charityID is optional to drill down.

The field name for date is DateControl

 

[arnelgp]

You almost had it:

IIf(IsNull([Forms]![SearchBox_CharitiesSalesSummaryReport]![CharityID]),[CharityID], [Forms]![SearchBox_CharitiesSalesSummaryReport]![CharityID])

 

[robsworld78]

Thanks arnelgp, that did it. As soon as I read what you posted I knew it would work, I wasn't sure what to put as as true condition.

 

[arnelgp]

Your welcome