[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

How to show Months and Quarters in a Query

I have a table that lists:

Date Complete

I have a report that includes that Field.   THe user would like a form to allow them to enter in:

Date Range     i.e.  01012012 -    03012012
or
Run the report by Quarter.

How would I do this ?

So I need to know how to specify this on the Form that will be used to run the report, as well what the query that is the record source.
0
seamus9909
Asked:
seamus9909
1 Solution
 
NorieCommented:
You could use a form with 2 textboxes and a group of option buttons.

The user can either type the start/finish dates into the textbox or they can be automatically be entered when they select one of the option buttons each of which represents a quarter.

The query would look something like this.

SELECT [DateCompleted], [OtherField], [TheOtherField]
FROM [Table1]
WHERE [DateCompleted]>=[Forms]![frmDates]![txtStartDate] AND [DateCompleted]>=[Forms]![frmDates]![txtEndDate]
0
 
seamus9909Author Commented:
So the Report is Called Billing Report.  I was going to inlcude a Button that will open the report.  

The record source of the report would be what you included above?
0
 
SheilsCommented:
The is a good article plus sample forms and code on this link:

http://www.databasedev.co.uk/report_date_parameters.html
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
NorieCommented:
Yes, I should have included a command button for opening.

The record source would be a query similar to what I posted.

I don't know what your field names are, if any other tables are involved or there are further criteria so I just posted a simple example.

I think the important part is the criteria for your data range.
0
 
seamus9909Author Commented:
So here is the Query I built


SELECT Tblclaims.InvoiceSentDate, Tblclaims.Patient, Tblclaims.FileNo, Tblclaims.[$billed], Tblclaims.[$paid Doctor (cost)], Tblclaims.[$Rec'd (income)] FROM Tblclaims WHERE (((Tblclaims.InvoiceSentDate)>=[Forms]![form1]![txtStartDate] And (Tblclaims.InvoiceSentDate)<=[Forms]![form1]![txtEndDate]));


I am getting Error saying the expression is typed incorrectly or too complex to be evaluated!
0
 
NorieCommented:
I can't see anything wrong with that.

Perhaps it's the $()' characters in the field names causing the problem.

You really shouldn abvoid using any of those characters in a field/table name.

Does the query work without the criteria?
0
 
seamus9909Author Commented:
without passing the dates in yes it does run
0
 
NorieCommented:
What happens when you use the DateValue function with the values from the form?

(((Tblclaims.InvoiceSentDate)>=DateValue([Forms]![form1]![txtStartDate]) And (Tblclaims.InvoiceSentDate)<=DateValue([Forms]![form1]![txtEndDate])));
0
 
seamus9909Author Commented:
No difference still says its too complex!
0
 
NorieCommented:
I've set up a form and a query with that SQL and it works just fine.

Is there anything else going on in the report?

Does the query work when you open it without the report, but with the form?
0
 
peter57rCommented:
Try declaring the parameters explicitly..

PARAMETERS [Forms]![form1]![txtStartDate] DateTime, [Forms]![form1]![txtEndDate] DateTime;
SELECT Tblclaims.InvoiceSentDate, Tblclaims.Patient, Tblclaims.FileNo, Tblclaims.[$billed], Tblclaims.[$paid Doctor (cost)], Tblclaims.[$Rec'd (income)] FROM Tblclaims WHERE (((Tblclaims.InvoiceSentDate)>=[Forms]![form1]![txtStartDate] And (Tblclaims.InvoiceSentDate)<=[Forms]![form1]![txtEndDate]));
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now