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?
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
0
Rey Obrero (Capricorn1)Commented:
try this syntax

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

enter parameter value form twice
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
0
Rey Obrero (Capricorn1)Commented:
upload a working db.. your BE have only one table
0
SteveL13Author Commented:
Are you sure?  I think they are both in the zip file.
0
SteveL13Author Commented:
But here it is again just in case.
Example.accdb
Example_BE.accdb
0
Rey Obrero (Capricorn1)Commented:
your BE have only ONE table
0
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
0
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;
0
Gustav BrockCIOCommented:
It does. I did check it out in your example database.

/gustav
0
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;
0
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
0
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.
0
Gustav BrockCIOCommented:
I can't tell. That form can only be opened in your environment.

/gustav
0
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.
0
SteveL13Author Commented:
Please try the attached.  I am sorry for the confusion.
Example.accdb
Example_BE.accdb
0
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
0
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.
0
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))
0

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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.