Select Query not picking up dates from Form

[Phred]

I have a report that is based on a one table "Tbl_OGCombined6" select query. The report and query work fine. When the select query is run I need it to pick up four dates "ComStartDt" "ComEndDt" "ReqStartDt" "ReqEndDt" from the form and add the dates to the query. When I add the statement to pick up the dates from the form it doesn't pull the data in. I get "Expr1:" but the fields are empty.

Only one date is shown below, for simplicity, as the same problem is happening with each date.

Access 2007 query builder:

Expr1: [Forms]![Frm_Main]![Sub_Frm_InventoryTabs].[Form]![ComStartDt]

The path is correct. Main Form --> Nested Sub Form --> Field ComStartDt

SQL Statement from query builder View SQL:

SELECT Tbl_OGCombined6.VendorName, Tbl_OGCombined6.ItemName, Tbl_OGCombined6.UnitYield, Tbl_OGCombined6.IUMeasure, Tbl_OGCombined6.ProductionQuantity, Tbl_OGCombined6.PUOnHand, Tbl_OGCombined6.PURequired, Tbl_OGCombined6.PUCommitted, Tbl_OGCombined6.Roundup, Tbl_OGCombined6.ItemNumber, Tbl_OGCombined6.PUPrice, Tbl_OGCombined6.Cost, [Forms]![Frm_Main]![Sub_Frm_InventoryTabs].[Form]![ComStartDt] AS Expr1
FROM Tbl_OGCombined6;

Any suggestions?

Thanks,

Phred

[June7]

Referencing subforms does get tricky. I always give subform/subreport container control a name different from the object it holds, like ctrInventory.

Is ComStartDt a fieldname in the subform RecordSource?

Try:

[Forms]![Frm_Main]![ctrInventory]![ComStartDt]

The query should pull date from the subform current record and show the same date value for every record. Is that what you want?

[Phred]

The ComStartDt field is an unbound field located on the subform. The user inputs four dates and then clicks a button to create the report.

The query Qry_OGreport is the select query I am having problems with.

I did try [Forms]![Frm_Main]![ctrInventory]![ComStartDt] but no luck.

The fields are located on the Sub Form Expr1: [Forms]![Frm_Main]![Sub_Frm_InventoryTabs].[Form]![ComStartDt]

I have a different query, "Qry_OGCommitted1", running from this same date field on the form.

Qry_OGCommitted1:

>=[Forms]![Frm_Main]![Sub_Frm_InventoryTabs].[Form]![ComStartDt] And <=[Forms]![Frm_Main]![Sub_Frm_InventoryTabs].[Form]![ComEndDt]

SQL for the above query that works fine.

SELECT "Qry_OGCommitted1" AS Qry, Date() AS OGDate, Tbl_ScheduledMealItems.InventoryID, Tbl_ScheduledMealItems.DefaultMealCount, Tbl_ScheduledMealItems.ItemName, [Portion]*[DefaultMealCount] AS ProdQTY, Tbl_ScheduledMealItems.IUperPU, Tbl_ScheduledMealItems.IUMeasure, Tbl_ScheduledMealItems.YieldPerIU, Round(([ProdQTY]/[YieldPerIU]),4) AS PortionOfIU, Tbl_ScheduledMealItems.DayAssigned INTO Tbl_OGCommitted1
FROM Tbl_ScheduledMealItems
WHERE (((Tbl_ScheduledMealItems.DayAssigned)>=[Forms]![Frm_Main]![Sub_Frm_InventoryTabs].[Form]![ComStartDt] And (Tbl_ScheduledMealItems.DayAssigned)<=[Forms]![Frm_Main]![Sub_Frm_InventoryTabs].[Form]![ComEndDt]))
ORDER BY Tbl_ScheduledMealItems.ItemName;

For some reason this one works perfectly. The other doesn't.

Yes, the query should execute and place the same date all the way down for every record.

You have advised me several times and I really appreciate it.

Thanks again.

[June7]

Mysterious!

Want to provide db for analysis? Follow instructions at bottom of my post.

[Phred]

Hi June7.

Query Name Qry_OGReport Select Query

Report Rpt_OGReport

Database contains test data so use these exact dates on the Order Guide form.

OGComStartDt = 11/5/2012
OGComEndDt=11/9/2012
OGReqStartDt=11/12/2012
OGReqEndDt=11/16/2012

Open Frm_Main
Click Inventory Tab
Click Order Guide

It operates right now and generates the report I just can't get in the date range for the report.

Proprietary data has been removed.

Thanks for the time, PhredMenuPro PhaseIIA 30 - Copy.zip

[June7]

The query that works is not using the form textboxes to create fields, they are filter criteria references.

I can make a reference to main form control work but not getting the subform syntax. I give up on that.

What does work is textboxes on report referencing the subform controls:
="Committed Period: " & [Forms]![Frm_Main]![Sub_Frm_InventoryTabs].[Form]![ComStartDt] & " - " & [Forms]![Frm_Main]![Sub_Frm_InventoryTabs].[Form]![ComEndDt]
="Required Period: " & [Forms]![Frm_Main]![Sub_Frm_InventoryTabs].[Form]![ReqStartDt] & " - " & [Forms]![Frm_Main]![Sub_Frm_InventoryTabs].[Form]![ReqEndDt]

[Phred]

June7, thanks for trying. I hadn't thought of the textboxes on the report. That will work just as well. As long as the date gets there it works.

Thanks,

I will close this out.

Phred