Avatar of SteveL13
SteveL13
Flag for United States of America asked on

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.
Microsoft Access

Avatar of undefined
Last Comment
SteveL13

8/22/2022 - Mon
SteveL13

ASKER
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
SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SteveL13

ASKER
Nope.  Still get

enter parameter value form twice
SteveL13

ASKER
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rey Obrero (Capricorn1)

upload a working db.. your BE have only one table
SteveL13

ASKER
Are you sure?  I think they are both in the zip file.
SteveL13

ASKER
But here it is again just in case.
Example.accdb
Example_BE.accdb
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

your BE have only ONE table
Gustav Brock

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
SteveL13

ASKER
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;
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Gustav Brock

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

/gustav
SteveL13

ASKER
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 Brock

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

/gustav
Example.accdb
Example_BE.accdb
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SteveL13

ASKER
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 Brock

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

/gustav
Rey Obrero (Capricorn1)

@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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SteveL13

ASKER
Please try the attached.  I am sorry for the confusion.
Example.accdb
Example_BE.accdb
Gustav Brock

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

/gustav
SteveL13

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SteveL13

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