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])

 

[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