Dates, Forms, and Queries

[hapm]

Simply add a condition to your querys: ... AND (IsNull([Forms]![YourFormsName]![inputDate]) OR [Forms]![YourFormsName]![inputDate] <= [YourTablesDateFieldName])


Don't miss the brackets because AND has precedence over OR and you don't want that here.

[Ace2014]

Simply add a condition to your querys: ... AND (IsNull([Forms]![YourFormsName]![inputDate]) OR [Forms]![YourFormsName]![inputDate] <= [YourTablesDateFieldName])
Don't miss the brackets because AND has precedence over OR and you don't want that here.

Cool, thanks.

So I on my form with the inputDate selected, I chose the Event:After Update
I used the expression builder and entered:

= SELECT 
FROM [qry1_PartPricingAggregation]
AND (IsNull([Forms]![SelectScope]![inputDate]) 
OR ([Forms]![SelectScope]![inputDate] <= [qry1_PartPricingAggregation].[Date])

And I got this error

[hapm]

The code I gave you is SQL. You should add it to the where clause of the query that is filtered by your comboboxes. No need to add any vba code or event macro here.

[Ace2014]

I don't know how to do that. I'm not sure I understant it either.

So I opened up my query in which the report is based off of.

I highlighted the Date field in red.
So in it's place it should read:

FROM [tbl1_PartPricingDataAggregation.Date]
Where 
AND (IsNull([Forms]![SelectScope]![inputDate]) 
OR ([Forms]![SelectScope]![inputDate] <= [tbl1_PartPricingDataAggregation.Date]

Query sql:

SELECT 
tbl3_HierarchySeg.L1, 
tbl3_HierarchySeg.L2, 
tbl3_HierarchySeg.L3, 
tbl2c_PartneoClientAcc.Client, 
tbl2b_PartneoSeg.PFC, 
tbl2b_PartneoSeg.PFN, 
tbl2b_PartneoSeg.PSFC, 
tbl2b_PartneoSeg.PSFN, 
tbl2a_PPNCharacteristics.PartStatus, 
tbl2a_PPNCharacteristics.Competitivity, 
tbl2a_PPNCharacteristics.PNDescription, 
tbl2a_PPNCharacteristics.PivotPart, 
tbl2a_PPNCharacteristics.PPN, 
tbl1_PartPricingDataAggregation.PNAggregation, 
tbl1_PartPricingDataAggregation.Brand, 
tbl1_PartPricingDataAggregation.Price, 
tbl1_PartPricingDataAggregation.Currency, 
tbl1_PartPricingDataAggregation.Market, 
tbl1_PartPricingDataAggregation.Range, 
tbl1_PartPricingDataAggregation.Date, 
tbl1_PartPricingDataAggregation.MatchStatus, 
tbl1_PartPricingDataAggregation.Comments, 
tbl1_PartPricingDataAggregation.Source, 
tbl1_PartPricingDataAggregation.Photo
FROM 
(tbl1_PartPricingDataAggregation 
LEFT JOIN (tbl2a_PPNCharacteristics 
LEFT JOIN (tbl2b_PartneoSeg 
LEFT JOIN tbl2c_PartneoClientAcc 
ON tbl2b_PartneoSeg.PAID = tbl2c_PartneoClientAcc.PAID) 
ON tbl2a_PPNCharacteristics.PAID_PFC_PSFC = tbl2b_PartneoSeg.PAID_PFC_PSFC) 
ON tbl1_PartPricingDataAggregation.PPN = tbl2a_PPNCharacteristics.PPN) 
LEFT JOIN tbl3_HierarchySeg 
ON tbl2b_PartneoSeg.L2_L3 = tbl3_HierarchySeg.L2_L3;

[hapm]

How do you apply the other filters in your form? using DoCmd.OpenReport? I can't find a reference to the other form fields in your query, so it looks like you're not filtering anything at all.

[Ace2014]

Oh wow.

I didn't realize the form was supposed to be linked to a query.
That's what I was doing in the beginning, but then someone showed me this way and I just figured it did the same thing.
Although that would make sense, since eventually, I'd want to create a table and export it into excel and then clear all traces of the process.

So the query I just showed above, that is where all the data with segmentations is what I want the users to have access to.

I'm trying to make an interface that allows the users to narrow the scope of the data they have access to and to then export it into excel.

Originally I had a process that alllowed users to input values into a table from a form and then a query would run to reduce the data scope with each added criteria.

Ex:
FormL1 - select L1
Upon pressing F9, table.L1 would be updated
Premade queryL1 linking table.L1 to accumulationQuery.L1 would run, reducing data scope
It would open FormL2
User selects L2
Upon pressing F9, table.L2 would be updated
Premade queryL2 linking tableL2 to queryL1.L2 would run, reducing data scope
It would open FormL3
Repeat process
For Level 4, I have dates and need to ask for either all dates (if isNull, return all, if not allDates>inputDate)
Level 5, I need the users to be able to select none, or one, or multiple brands based off previous queries (for some reason multiple rows couldn't be added)
Level 6, I need the users to be able to select none, one, or multiple available markets based off previous queries

Did that make sense?
Is there any easier way to do that?

I mean, I don't know if I need all the tables, I could create a query with a where statement saying where isNull"Form""FormName""Formname.ColumnHeading" (return all values) false("Form""FormName""Formname.ColumnHeading"=cor responding ColumnHeading in the query/table.
But then I'd need to be able to select the query for the next criteria.

[hapm]

You can make a query that filters all criterias at the same time extending the filter I gave you. No need to update a table or create multible queries. You can cascade multiple queries of course, if you prefere this for the sake of overview. I mostly try to reduce the number of queries to a minimum, and don't have queries for interim results, but thats only a preference of mine and not a must have imho.