If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
John Spencer
What I mean by not working is that the report runs, as does the query, and
returns no records, it comes back with nothing but field names and page number, I tried this previously with using parameters declared on the parameter screen, didn't make a difference. Here is the SQL of the query. SELECT AdultCommunityEducation.FVF100Adults, "Adults/General Population" AS [Community Education], Sum(AdultCommunityEducation.FVF100Numberpresentati ons) AS [Total Number of Presentations], Sum(AdultCommunityEducation.FVF100AdultParticipant s) AS [Total Number of Participants] FROM AdultCommunityEducation WHERE (((AdultCommunityEducation.FVF100Presentationdate) Between CDate([Forms]![frmReports]![txtFirstBegin]) And CDate([Forms]![frmReports]![txtFirstEnd]))) GROUP BY AdultCommunityEducation.FVF100Adults, "Adults/General Population"; I see that I didn't use the value property in the query, I only referenced it when setting the value of the text boxes on the form. Thanks for the help! |
#2
|
|||
|
|||
John Spencer
Sorry, this was intended as a reply to the "Confused and Lost" topic I posted
earlier "GovCoder" wrote: What I mean by not working is that the report runs, as does the query, and returns no records, it comes back with nothing but field names and page number, I tried this previously with using parameters declared on the parameter screen, didn't make a difference. Here is the SQL of the query. SELECT AdultCommunityEducation.FVF100Adults, "Adults/General Population" AS [Community Education], Sum(AdultCommunityEducation.FVF100Numberpresentati ons) AS [Total Number of Presentations], Sum(AdultCommunityEducation.FVF100AdultParticipant s) AS [Total Number of Participants] FROM AdultCommunityEducation WHERE (((AdultCommunityEducation.FVF100Presentationdate) Between CDate([Forms]![frmReports]![txtFirstBegin]) And CDate([Forms]![frmReports]![txtFirstEnd]))) GROUP BY AdultCommunityEducation.FVF100Adults, "Adults/General Population"; I see that I didn't use the value property in the query, I only referenced it when setting the value of the text boxes on the form. Thanks for the help! |
#3
|
|||
|
|||
John Spencer
This should work.
Parameters [Forms]![frmReports]![txtFirstBegin] DateTime, [Forms]![frmReports]![txtFirstEnd] DateTime; SELECT AdultCommunityEducation.FVF100Adults , "Adults/General Population" AS [Community Education] , Sum(AdultCommunityEducation.FVF100Numberpresentati ons) AS [Total Number of Presentations] , Sum(AdultCommunityEducation.FVF100AdultParticipant s) AS [Total Number of Participants] FROM AdultCommunityEducation WHERE (((AdultCommunityEducation.FVF100Presentationdate) Between CDate([Forms]![frmReports]![txtFirstBegin]) And CDate([Forms]![frmReports]![txtFirstEnd]))) GROUP BY AdultCommunityEducation.FVF100Adults, "Adults/General Population"; Since it doesn't I am going to guess there is a problem with the values in txtFirstBegin and txtFirstEnd. You can run a little test query as follows SELECT Distinct [Forms]![frmReports]![txtFirstBegin] , [Forms]![frmReports]![txtFirstEnd] , IsDate([Forms]![frmReports]![txtFirstBegin]) , IsDate([Forms]![frmReports]![txtFirstEnd]) FROM AdultCommunityEducation That should return the dates and -1 (True) for the IsDate() functions. If it does not returns dates and returns false for either then you need to work on what is happening there. If it does return true then further exploration is needed. Such as are there actually records in the specified date range. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County GovCoder wrote: Sorry, this was intended as a reply to the "Confused and Lost" topic I posted earlier "GovCoder" wrote: What I mean by not working is that the report runs, as does the query, and returns no records, it comes back with nothing but field names and page number, I tried this previously with using parameters declared on the parameter screen, didn't make a difference. Here is the SQL of the query. SELECT AdultCommunityEducation.FVF100Adults, "Adults/General Population" AS [Community Education], Sum(AdultCommunityEducation.FVF100Numberpresentati ons) AS [Total Number of Presentations], Sum(AdultCommunityEducation.FVF100AdultParticipant s) AS [Total Number of Participants] FROM AdultCommunityEducation WHERE (((AdultCommunityEducation.FVF100Presentationdate) Between CDate([Forms]![frmReports]![txtFirstBegin]) And CDate([Forms]![frmReports]![txtFirstEnd]))) GROUP BY AdultCommunityEducation.FVF100Adults, "Adults/General Population"; I see that I didn't use the value property in the query, I only referenced it when setting the value of the text boxes on the form. Thanks for the help! |
#4
|
|||
|
|||
John Spencer
Hey John, thanks for that. It's pretty close to what I had done several
weeks ago and was unable to get results. Well, I know now what the problem is. My sample data was 2009 and I forgot that. I was entering dates into the text boxes for 2010, stupidly. No wonder I could never get results! Thank you for essentially wacking me over the head with the stupid stick! Jeez, I've been beating my head over this all week and it was right to begin with, I just wasn't thinking about it right. "John Spencer" wrote: This should work. Parameters [Forms]![frmReports]![txtFirstBegin] DateTime, [Forms]![frmReports]![txtFirstEnd] DateTime; SELECT AdultCommunityEducation.FVF100Adults , "Adults/General Population" AS [Community Education] , Sum(AdultCommunityEducation.FVF100Numberpresentati ons) AS [Total Number of Presentations] , Sum(AdultCommunityEducation.FVF100AdultParticipant s) AS [Total Number of Participants] FROM AdultCommunityEducation WHERE (((AdultCommunityEducation.FVF100Presentationdate) Between CDate([Forms]![frmReports]![txtFirstBegin]) And CDate([Forms]![frmReports]![txtFirstEnd]))) GROUP BY AdultCommunityEducation.FVF100Adults, "Adults/General Population"; Since it doesn't I am going to guess there is a problem with the values in txtFirstBegin and txtFirstEnd. You can run a little test query as follows SELECT Distinct [Forms]![frmReports]![txtFirstBegin] , [Forms]![frmReports]![txtFirstEnd] , IsDate([Forms]![frmReports]![txtFirstBegin]) , IsDate([Forms]![frmReports]![txtFirstEnd]) FROM AdultCommunityEducation That should return the dates and -1 (True) for the IsDate() functions. If it does not returns dates and returns false for either then you need to work on what is happening there. If it does return true then further exploration is needed. Such as are there actually records in the specified date range. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County GovCoder wrote: Sorry, this was intended as a reply to the "Confused and Lost" topic I posted earlier "GovCoder" wrote: What I mean by not working is that the report runs, as does the query, and returns no records, it comes back with nothing but field names and page number, I tried this previously with using parameters declared on the parameter screen, didn't make a difference. Here is the SQL of the query. SELECT AdultCommunityEducation.FVF100Adults, "Adults/General Population" AS [Community Education], Sum(AdultCommunityEducation.FVF100Numberpresentati ons) AS [Total Number of Presentations], Sum(AdultCommunityEducation.FVF100AdultParticipant s) AS [Total Number of Participants] FROM AdultCommunityEducation WHERE (((AdultCommunityEducation.FVF100Presentationdate) Between CDate([Forms]![frmReports]![txtFirstBegin]) And CDate([Forms]![frmReports]![txtFirstEnd]))) GROUP BY AdultCommunityEducation.FVF100Adults, "Adults/General Population"; I see that I didn't use the value property in the query, I only referenced it when setting the value of the text boxes on the form. Thanks for the help! . |
Thread Tools | |
Display Modes | |
|
|