UtterAccess.com
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    
 
   
phisaw
post Feb 16 2018, 01:01 PM
Post#1



Posts: 276
Joined: 3-April 08



Hello,

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\#".
CODE
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.
Thanks,
Pam
Go to the top of the page
 
ranman256
post Feb 19 2018, 07:29 AM
Post#2



Posts: 861
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
 
projecttoday
post Feb 19 2018, 08:37 AM
Post#3


UtterAccess VIP
Posts: 9,686
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

My company's website
Go to the top of the page
 
phisaw
post Feb 19 2018, 02:20 PM
Post#4



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,
Pam
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 03:44 AM