Like function with form data

[proctk]

Hi all,

This my first post and I am a novice to Access. I started learning it over the few weeks through reading blogs and watching YouTube videos. I am stuck with a problem that I have not be able to find an answer to.



I am trying to setup a search form that allows multiple search criteria to be entered. I found a video but it does not quite cover what I want to acomplish.

Here is what I have like *.* &>=[Forms]![frmSBQry]![BegQuarterComb] And <=[Forms]![frmSBQry]![EndQuartComb] & *.*

This gives me a syntax error. any ideas how to fix it... note if I remove the *.* from each end it does work. I want it to show all records if no form value was selected.

[June7]

Why the . character? Are these fields text type? Show sample data.

[proctk]

I typed it wrong... it should have been "*" on each end.. I am not sure how to copy and past data into the forum.

[proctk]

The goal to query by the value or values entered. Not al values have to be entered. example show only November in fiscal 2012.

[June7]

Can go to Advanced post editor and attach files to post or build tables in the post editor.

All 3 fields are numeric?

Try:

BETWEEN Nz([Forms]![frmSBQry]![BegQuarterComb], 1) And Nz([Forms]![frmSBQry]![EndQuartComb], 4)

BETWEEN Nz([Forms]![frmSBQry]![BegMonthComb], 1) And Nz([Forms]![frmSBQry]![EndMonthComb], 12)

BETWEEN Nz([Forms]![frmSBQry]![BegYearComb], 1900) And Nz([Forms]![frmSBQry]![EndYearComb], 2900)

Why is this a GROUP BY query?

[proctk]

Thank you for the reply. It not working like I want. Is the code to be entered in the same criteria row or staggered like I had it.

Let's see if I can clarify. If user selects Begging month = 1 and ending months = 5 then show all records between those two month, but if year is selected then only show the record for the year range selected. A person could only select year values, month values or just quarter values.

I Have GROUP by query selected because there are many records for each year, month and quarter with there own fields i the table. These values are pulled from weekly results. I don't want to see each weeks results in my query / report.

Cheers,

[proctk]

I have attached a copy of my form. I want the options to work independently or in conjunction with each other based on the query.

[kagoodwin13]

I think the problem is that you have conflicting pieces of information. You can't filter for quarter and then month in this example. For example, what if you choose Q2 and December? December isn't in Q2, so you'll either get a jumbled mess, or no results at all, neither of which is what you want.

Rather than offering an option to have a beginning month/quarter/year, just offer a beginning date and end date. The query will summarize based on the date range given to it. The query then won't be confined to a month, quarter, or year. For example, you can query between 7/1/2014 and 7/31/2014 for July, or between 7/1/2014 and 9/30/2014 for Q3, or 1/1/2014 to 12/31/2014 for all of 2014.

Also, use a text box with a date picker, not a combo box. It will make more sense for you and your users, I think.

[June7]

Good points kagoodwin. I should have seen that as well.

[proctk]

There is 3 years of weekly data. The goal of selecting ranges was to allow comparisons. So if I select Q1 to Q1 then I would be able to identify trends for the three quarters. The only time really that a person would select a combinations of options if they pick a month to month then year to year.

If the query resulted in no results that is fine.. the person would have to try again . 95% of the time it will be using the database.

[June7]

Try my suggested criteria all on one row to invoke the AND operator.

If still need help, provide db for analysis and testing. Follow instructions at bottom of my post.

[kagoodwin13]

There is 3 years of weekly data. The goal of selecting ranges was to allow comparisons. So if I select Q1 to Q1 then I would be able to identify trends for the three quarters. The only time really that a person would select a combinations of options if they pick a month to month then year to year.

If the query resulted in no results that is fine.. the person would have to try again . 95% of the time it will be using the database.

I don't think you would be able to get that result in the current way that your data and query are set up. Can you provide an example of the data, or the database itself? Follow June7's instructions below.