Why can't get query to return records

SteveL13
SteveL13 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

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
Top Expert 2016
Commented:
try this syntax

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

Author

Commented:
Nope.  Still get

enter parameter value form twice
Ensure you’re charging the right price for your IT

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

Author

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
Top Expert 2016

Commented:
upload a working db.. your BE have only one table

Author

Commented:
Are you sure?  I think they are both in the zip file.

Author

Commented:
But here it is again just in case.
Example.accdb
Example_BE.accdb
Top Expert 2016

Commented:
your BE have only ONE table
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

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;
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It does. I did check it out in your example database.

/gustav

Author

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;
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I've attached the working form and query - using dates of 2015-09-01 and 2015-10-01.

/gustav
Example.accdb
Example_BE.accdb

Author

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.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I can't tell. That form can only be opened in your environment.

/gustav
Top Expert 2016

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.

Author

Commented:
Please try the attached.  I am sorry for the confusion.
Example.accdb
Example_BE.accdb
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I see. The form is opened as a subform in the navigation form.
Then you must use the syntax as Rey explained.

/gustav

Author

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.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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))

Author

Commented:
Awarding to both of you for all the effort and putting up with my confusing you.  That last suggestion worked perfectly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial