Why can't get query to return records

I have a form named frmAuditData which is a NavigationSobForm on the form "Navigation Form".  On the frmAuditData I have two fields... txtStartDate and txtEndDate.

Then in a query design I have this code which is supposed to display the records in tblAuditData but it doesn't work:

Between [Forms]![frmAuditData]![txtStartDate] And [Forms]![frmAuditData]![txtEndDate]

I've also tried:

Between Forms![Navigation Form]!frmAuditData.Form!txtStartDate and Forms![Navigation Form]!frmAuditData.Form!txtEndDate

But that doesn't work either.  When I run the query I get an enter parameter value form twice.. once for txtStartDate and once for txtEndDate


Nota that the date fields in the table look like:   9/8/2015 1:42:39 PM  if that matters.
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveL13Author Commented:
My latest attempt:

Between [Forms]![Navigation Form]![AdminPage].[Form]![frmAuditDate].[Form]![txtStartDate] And [Forms]![Navigation Form]![AdminPage].[Form]![frmAuditDate].[Form]![txtEndDate]

The main form is [Navigation Form] and the 1st subform is [AdminPage] and the subform the date fields are on is frmAuditDate
Rey Obrero (Capricorn1)Commented:
try this syntax

Between Forms![Navigation Form]![NavigationSubform].[Form]!frmAuditData!txtStartDate And Forms![Navigation Form]![NavigationSubform].[Form]!frmAuditData!txtEndDate + 1
SteveL13Author Commented:
Nope.  Still get

enter parameter value form twice
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SteveL13Author Commented:
Stripped down version attached.  Please unzip, Open front-end file.  Runtime error - Click [End].  Go to "Admin Page'.   Go to [Audit Trail].  Enter 9/8/2015 in both date fields.

The result should be four records that show up in a query window.  Instead, I get enter parameter value.

???
Example.zip
Rey Obrero (Capricorn1)Commented:
upload a working db.. your BE have only one table
SteveL13Author Commented:
Are you sure?  I think they are both in the zip file.
SteveL13Author Commented:
But here it is again just in case.
Example.accdb
Example_BE.accdb
Rey Obrero (Capricorn1)Commented:
your BE have only ONE table
Gustav BrockCIOCommented:
You have to use the correct reference for the controls:

SELECT
    ...
FROM
    audCustomer
WHERE
    (audCustomer.audDate
         Between
             [Forms]![frmAuditData]![txtStartDate]
         And
             [Forms]![frmAuditData]![txtEndDate]+1)
ORDER BY
     audCustomer.audDate;

/gustav
SteveL13Author Commented:
Rey:  There is only one table  --  audCustomer.  That is the only table I am querying.

Gustav:  I tried this and it still doesn't work:

SELECT audCustomer.audID, audCustomer.audType, audCustomer.audDate, audCustomer.audUser, audCustomer.Customer_ID, audCustomer.UPC, audCustomer.Description, audCustomer.Package, audCustomer.Brand, audCustomer.CaseQty, audCustomer.VenCode, audCustomer.Sub_Dept, audCustomer.Customer_Type_ID, audCustomer.Cost, audCustomer.Discount, audCustomer.Deposit, audCustomer.Freight, audCustomer.Conversion, audCustomer.COG, audCustomer.Each, audCustomer.Tax, audCustomer.Retail, audCustomer.Margin, audCustomer.Case_RetailC, audCustomer.Case_Retail, audCustomer.Case_MarginS, audCustomer.Case_Margin, audCustomer.Note, audCustomer.Margin_Tool, audCustomer.Cal_Margin
FROM audCustomer
WHERE (((audCustomer.audDate) Between [Forms]![frmAuditData]![txtStartDate] And [Forms]![frmAuditData]![txtEndDate]+1))
ORDER BY audCustomer.audDate;
Gustav BrockCIOCommented:
It does. I did check it out in your example database.

/gustav
SteveL13Author Commented:
Using this?....

SELECT audCustomer.audID, audCustomer.audType, audCustomer.audDate, audCustomer.audUser, audCustomer.Customer_ID, audCustomer.UPC, audCustomer.Description, audCustomer.Package, audCustomer.Brand, audCustomer.CaseQty, audCustomer.VenCode, audCustomer.Sub_Dept, audCustomer.Customer_Type_ID, audCustomer.Cost, audCustomer.Discount, audCustomer.Deposit, audCustomer.Freight, audCustomer.Conversion, audCustomer.COG, audCustomer.Each, audCustomer.Tax, audCustomer.Retail, audCustomer.Margin, audCustomer.Case_RetailC, audCustomer.Case_Retail, audCustomer.Case_MarginS, audCustomer.Case_Margin, audCustomer.Note, audCustomer.Margin_Tool, audCustomer.Cal_Margin
 FROM audCustomer
 WHERE (((audCustomer.audDate) Between [Forms]![frmAuditData]![txtStartDate] And [Forms]![frmAuditData]![txtEndDate]+1))
 ORDER BY audCustomer.audDate;
Gustav BrockCIOCommented:
I've attached the working form and query - using dates of 2015-09-01 and 2015-10-01.

/gustav
Example.accdb
Example_BE.accdb
SteveL13Author Commented:
Yes.  If I just open the form, frmAuditData it works.  But if I go to Navigation Form > Admin Page > [Audit Trail] and enter 9/8/2015 in both date fields it does not work.
Gustav BrockCIOCommented:
I can't tell. That form can only be opened in your environment.

/gustav
Rey Obrero (Capricorn1)Commented:
@steve

you have to use the syntax like what i posted at http:#a40967894 

just check the names of the controls, subform control name and apply to the syntax accordingly.
SteveL13Author Commented:
Please try the attached.  I am sorry for the confusion.
Example.accdb
Example_BE.accdb
Gustav BrockCIOCommented:
I see. The form is opened as a subform in the navigation form.
Then you must use the syntax as Rey explained.

/gustav
SteveL13Author Commented:
Frustrated.  I tried:

Between [Forms]![Navigation Form]![frmFirstPage].[Form]![frmAuditData]![txtStartDate] And [Forms]![Navigation Form]![frmFirstPage].[Form]![frmAuditData]![txtEndDate]+1

and

Between [Forms]![Navigation Form]![frmAuditData].[Form]![txtStartDate] And [Forms]![Navigation Form]![frmAuditDate].[Form]![txtEndDate]+1

And neither one works.
Gustav BrockCIOCommented:
Oh, you have a subform in a subform - with the same name for the subform control.
Then it is:

Between
[Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![txtStartDate] And
[Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![txtEndDate]+1))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
Awarding to both of you for all the effort and putting up with my confusing you.  That last suggestion worked perfectly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.