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: 883
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: 10,352
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
 
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    11th December 2018 - 08:23 AM