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: 875
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,060
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.

Robert Crouser
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    22nd September 2018 - 12:16 AM