X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Return Form Records From Report By Month, Access 2013    
post Feb 16 2018, 01:01 PM

Posts: 276
Joined: 3-April 08


I have done an extensive search on the internet and can't seem to find a solution to exactly what I'm looking for.

I have a report that groups records by RequestedDate (Month/Year) and Amount, as follows:

01/2017 $500
02/2017 $1000
03/2017 $750

I can click on the date field on the report to open the form "fOpenOrders", but I need it to filter the RequestedDate of the form to show records within the month selected.

For example, if 01/2017 was clicked on the report, I need the form to open with the records for the month of January 2017 that make up the $500.

The following code opens the form without the strWhere and I've tried adding the strWhere with various Format(...., "mmmm yyyy" and Format( ... "\#mm/dd/yyyy\#".
Dim strWhere As String
Dim DocName As String

DocName = "fOpenOrders"
strWhere = "[requestedmonth]='" & Me.Text4 & "'"

DoCmd.OpenForm DocName, acNormal, , strWhere
End Sub

Any help is greatly appreciated.
Go to the top of the page
post Feb 19 2018, 07:29 AM

Posts: 894
Joined: 25-April 14

if using dates, ask on the date field:
where [prodDate] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

or in a query add an extra field for Month:Month([prodDate])
where [Month] = forms!myForm!cboMonth

(months are numeric, not string)
This post has been edited by ranman256: Feb 19 2018, 07:29 AM
Go to the top of the page
post Feb 19 2018, 08:37 AM

UtterAccess VIP
Posts: 10,649
Joined: 10-February 04
From: South Charleston, WV

If you're using the Month function, you'll need to check the month and the year.
Go to the top of the page
post Feb 19 2018, 02:20 PM

Posts: 276
Joined: 3-April 08

Thanks to both Ranman and Projecttoday for responding.

Ranman - I understand how to use a query with the startdate and enddate form fields to query for specific records - I actually use this on several of my forms filters.

I'm trying to filter an existing form with a date field [RequestedDate] that has a short date format (01/15/2018) from a report where the dates (txtRequestedDate) are grouped and formatted by month and year (01/2018).

So in my report I have 01/2018 with a total of $500.

I want to click on the date (01/2018) and have the existing form open with records for dates only in the range of 01/01/2018 and 01/31/2018 that makeup the $500.

Do you have any suggestions I can try? I think I've included just about every variation I can find online.
Any help is greatly appreciated!!
Thanks again,
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    26th March 2019 - 11:28 AM