iif statement help (1 Viewer)

[Bendiesel]

Hi All-

Kind of a novice with Access, but here goes the question:

I am trying to write a query that will test for an entry into a form text box. If that box has a value, then the query will return anything between the range of dates.

When I was working with the iif statement it looked something like this:

iif([Forms]![SalesData]![StartDatetxt] is null, [SalesDate], [SalesDate]>=[Forms]![SalesData]![StartDatetxt] AND [SalesDate]<=[Forms]![SalesData]![EndDatetxt])

I realize now that an iif statement isnt appropriate, but I thought that this might help illustrate what Im trying to do.


Thanks in advance,

 

[EMP]

You can set up the criteria in a new column in the query grid like the following:-

---------------------------
Field: IIf([Forms]![SalesData]![StartDatetxt] Is Null, True, [SalesDate] Between [Forms]![SalesData]![StartDatetxt] And [Forms]![SalesData]![EndDatetxt])

Show: uncheck

Criteria: True
---------------------------


See Jon K's thread in the Samples forum
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[Bendiesel]

Follow up Question...

Worked wonderfully, thank you.

Im now trying to create a pivot from the query above, but running into some errors. At first I had an error from passing in variables from the forms. Since then, I added the form fields under "parameters. Now it tells me that the expression is typed incorrectly, or is too complicated to be displayed.


Any more help would be GREATLY APPRECIATED.

Thanks again.

*********************************
Here is the SQL from the Pivot table query:
*********************************

PARAMETERS [Forms]![PurchasingPEDReport_DBTracking]![BatchTxt] Text ( 255 ), [Forms]![PurchasingPEDReport_DBTracking]![TeamDDL] Text ( 255 ), [Forms]![PurchasingPEDReport_DBTracking]![BuyerDDL] Text ( 255 ), [Forms]![PurchasingPEDReport_DBTracking]![SupplierNumTxt] Text ( 255 ), [Forms]![PurchasingPEDReport_DBTracking]![TargetPEDStartTxt] DateTime, [Forms]![PurchasingPEDReport_DBTracking]![TargetPEDEndTxt] DateTime, [Forms]![PurchasingPEDReport_DBTracking]![PricedPEDStartTxt] DateTime, [Forms]![PurchasingPEDReport_DBTracking]![PricedPEDEndTxt] DateTime, [Forms]![PurchasingPEDReport_DBTracking]![DrillDownDDL] Text ( 255 );
TRANSFORM Count(Purchasing_PEDReport_DB.[Batch #]) AS [CountOfBatch #]
SELECT Purchasing_PEDReport_DB.Status
FROM Purchasing_PEDReport_DB
GROUP BY Purchasing_PEDReport_DB.Status
PIVOT Purchasing_PEDReport_DB.Status;

****************************
And the data from the parent query:
****************************

SELECT [NIA TRACKING DB].Status, [NIA TRACKING DB].[Batch #], [NIA TRACKING DB].[Supplier Name], [NIA TRACKING DB].Team, [NIA TRACKING DB].[Buyer Alignment_Buyer], [NIA TRACKING DB].[Purch Grp], [NIA TRACKING DB].[PTIS Name], [NIA TRACKING DB].[Product Manager], [NIA TRACKING DB].[Addition Type], [NIA TRACKING DB].PMgr_Replaces, [NIA TRACKING DB].[WWG #], [NIA TRACKING DB].[Mfg Model #], [NIA TRACKING DB].[CPIR Sales Status], [NIA TRACKING DB].[Batch Certified], [NIA TRACKING DB].[FPP Create Date], [NIA TRACKING DB].[Date Confirmed PED], [NIA TRACKING DB].[Buyer Confirmed PED], [NIA TRACKING DB].[Price Template Rec'd], [NIA TRACKING DB].Pricing_Timestamp, [NIA TRACKING DB].[Target PED], [NIA TRACKING DB].[Priced PED], [NIA TRACKING DB].[Labeling & Packaging Complete], [NIA TRACKING DB].PC_Label, [NIA TRACKING DB].PC_Pack, [NIA TRACKING DB].[Catalog #], [NIA TRACKING DB].[Supplier #], [NIA TRACKING DB].[Date for Pivot], IIf(Forms!PurchasingPEDReport_DBTracking!SupplierNumTxt Is Null,True,[NIA TRACKING DB]![Supplier #]=Forms!PurchasingPEDReport_DBTracking!SupplierNumTxt) AS Expr4
FROM [NIA TRACKING DB]
WHERE (((IIf([Forms]![PurchasingPEDReport_DBTracking]![TargetPEDStartTxt] Is Null,True,[NIA TRACKING DB]![Target PED] Between [Forms]![PurchasingPEDReport_DBTracking]![TargetPEDStartTxt] And [Forms]![PurchasingPEDReport_DBTracking]![TargetPEDEndTxt]))=True) AND ((IIf([Forms]![PurchasingPEDReport_DBTracking]![PricedPEDStartTxt] Is Null,True,[Priced PED] Between [Forms]![PurchasingPEDReport_DBTracking]![PricedPEDStartTxt] And [Forms]![PurchasingPEDReport_DBTracking]![PricedPEDEndTxt]))=True) AND ((IIf([Forms]![PurchasingPEDReport_DBTracking]![DrillDownDDL] Is Null,True,[NIA TRACKING DB]![Status]=[Forms]![PurchasingPEDReport_DBTracking]![DrillDownDDL]))=True) AND ((IIf([Forms]![PurchasingPEDReport_DBTracking]![BatchTxt] Is Null,True,[NIA TRACKING DB]![Batch #]=[Forms]![PurchasingPEDReport_DBTracking]![BatchTxt]))=True) AND ((IIf([Forms]![PurchasingPEDReport_DBTracking]![TeamDDL] Is Null,True,[NIA TRACKING DB]![Team]=[Forms]![PurchasingPEDReport_DBTracking]![TeamDDL]))=True) AND ((IIf([Forms]![PurchasingPEDReport_DBTracking]![BuyerDDL] Is Null,True,[NIA TRACKING DB]![Buyer Alignment_Buyer]=[Forms]![PurchasingPEDReport_DBTracking]![BuyerDDL]))=True) AND ((IIf([Forms]![PurchasingPEDReport_DBTracking]![SupplierNumTxt] Is Null,True,[NIA TRACKING DB]![Supplier #]=[Forms]![PurchasingPEDReport_DBTracking]![SupplierNumTxt]))=True));