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
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
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.
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;
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.
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
Between [Forms]![Navigation Form]![AdminPage].[Form]![
The main form is [Navigation Form] and the 1st subform is [AdminPage] and the subform the date fields are on is frmAuditDate