Create a Query that Prompts for Date and Time
in [Queries]
|
From: SSi308 on 5 May 2010 16:43 I am in the beginning stages of setting up a call database that tracks calls made by sales people. The table includes separate fields for date and time. A query is needed that will prompt for a date range and time range. For example a user may want a report that shows all calls for April 1 through April 7 between 11:00 AM and 12:00 PM. I created the query for a time range, which worked. I then added the expression, for date range: >=[Start Date] And <=[End Date] I have also tried: BETWEEN [Start Date] AND [End Date] Both expressions get the same error when trying to run the query: "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." For your reference the time expression is: Between [Start Time] And [End Time] How do I set up the query to allow choosing both date range and time range? There are other criteria that will also need to be added, but thought I should get this working first. Thanks for any help you can give. Lori
From: Dorian on 5 May 2010 18:00 You can do it in a query but it's much better to create a form for the user to enter the dates and times and then to run the query from a command button. The query will refer to the form controls to get the dates and times. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "SSi308" wrote: > I am in the beginning stages of setting up a call database that tracks calls > made by sales people. The table includes separate fields for date and time. > > A query is needed that will prompt for a date range and time range. > For example a user may want a report that shows all calls for April 1 > through April 7 between 11:00 AM and 12:00 PM. > > I created the query for a time range, which worked. I then added the > expression, for date range: >=[Start Date] And <=[End Date] I have also > tried: BETWEEN [Start Date] AND [End Date] > > Both expressions get the same error when trying to run the query: > "This expression is typed incorrectly, or it is too complex to be evaluated. > For example, a numeric expression may contain too many complicated elements. > Try simplifying the expression by assigning parts of the expression to > variables." > > For your reference the time expression is: Between [Start Time] And [End Time] > > How do I set up the query to allow choosing both date range and time range? > > There are other criteria that will also need to be added, but thought I > should get this working first. Thanks for any help you can give. > > Lori
From: Dorian on 5 May 2010 18:17 Post the full SQL text of your query. Use BETWEEN not >= and <= Here is sample: SELECT CaseNumber, Unit, CMPFiledDate, CMPRcvdDate, CMPPlus30, CMPPlus60 FROM [MyTable] WHERE CMPFiled = True AND CMPAcpt = False AND CMPRcvdDate IS NOT NULL AND CMPRcvdDate BETWEEN NZ([CMP Rcvd From],#1/1/1900#) AND NZ([CMP Rcvd To],#12/31/2999#) ORDER BY CMPRcvdDate DESC This also shows how to set up default if user enters nothing. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "SSi308" wrote: > I am in the beginning stages of setting up a call database that tracks calls > made by sales people. The table includes separate fields for date and time. > > A query is needed that will prompt for a date range and time range. > For example a user may want a report that shows all calls for April 1 > through April 7 between 11:00 AM and 12:00 PM. > > I created the query for a time range, which worked. I then added the > expression, for date range: >=[Start Date] And <=[End Date] I have also > tried: BETWEEN [Start Date] AND [End Date] > > Both expressions get the same error when trying to run the query: > "This expression is typed incorrectly, or it is too complex to be evaluated. > For example, a numeric expression may contain too many complicated elements. > Try simplifying the expression by assigning parts of the expression to > variables." > > For your reference the time expression is: Between [Start Time] And [End Time] > > How do I set up the query to allow choosing both date range and time range? > > There are other criteria that will also need to be added, but thought I > should get this working first. Thanks for any help you can give. > > Lori
From: SSi308 on 6 May 2010 11:00 Dorian, Thanks for the reply, I finally did get this to work. My error was that I had added a line in the query for totals and was trying to total the date field. After changing back to group the query ran fine. I am interested in how to do this with a form versus a query though. I thought the query needed to be set up first then a form could be created from that. Lori "Dorian" wrote: > You can do it in a query but it's much better to create a form for the user > to enter the dates and times and then to run the query from a command button. > The query will refer to the form controls to get the dates and times. > -- Dorian > "Give someone a fish and they eat for a day; teach someone to fish and they > eat for a lifetime". > > > "SSi308" wrote: > > > I am in the beginning stages of setting up a call database that tracks calls > > made by sales people. The table includes separate fields for date and time. > > > > A query is needed that will prompt for a date range and time range. > > For example a user may want a report that shows all calls for April 1 > > through April 7 between 11:00 AM and 12:00 PM. > > > > I created the query for a time range, which worked. I then added the > > expression, for date range: >=[Start Date] And <=[End Date] I have also > > tried: BETWEEN [Start Date] AND [End Date] > > > > Both expressions get the same error when trying to run the query: > > "This expression is typed incorrectly, or it is too complex to be evaluated. > > For example, a numeric expression may contain too many complicated elements. > > Try simplifying the expression by assigning parts of the expression to > > variables." > > > > For your reference the time expression is: Between [Start Time] And [End Time] > > > > How do I set up the query to allow choosing both date range and time range? > > > > There are other criteria that will also need to be added, but thought I > > should get this working first. Thanks for any help you can give. > > > > Lori
From: SSi308 on 1 Jun 2010 10:19 Dorian, Wanted to follow up on this post. I took your suggestion and with the help of John Spencer and Karl Dewey was able to get the parameter to work. Here is the sql view, thanks.. PARAMETERS [Forms]!frmWeeklyReport![txtStartDate] DateTime, [Forms]!frmWeeklyReport![txtEndDate] DateTime; SELECT DailyCalls.EmpID, Employees.Department, Employees.Initials, Count(DailyCalls.EmpID) AS [Total Calls] , Abs(Sum(LengthOfCall>=#12/30/1899 0:3:0#)) AS [Calls 3+] , Abs(Sum(CallDirection="OUT")) AS [Out Calls] , Abs(Sum(CallDirection Like "IN*")) AS [In Calls] , Abs(Sum(CallDirection="OUT"))/Count([DailyCalls.EmpID]) AS [Pt Calls Out] , Abs(Sum(CallDirection Like "IN*"))/Count([DailyCalls.EmpID]) AS [Pt Calls In] , Abs(Sum(LengthOfCall>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls 3+] , Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls Out 3+] , Abs(Sum(CallDirection Like "IN*" And (DailyCalls.LengthOfCall)>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls In 3+] FROM Employees INNER JOIN DailyCalls ON Employees.EmpID = DailyCalls.EmpID WHERE (((DailyCalls.CallDate)>=[Forms]![frmWeeklyReport]![txtStartDate] And (DailyCalls.CallDate)<DateAdd("d",1,[Forms]![frmWeeklyReport]![txtEndDate]))) GROUP BY DailyCalls.EmpID, Employees.Department, Employees.Initials; Lori "Dorian" wrote: > Post the full SQL text of your query. > Use BETWEEN not >= and <= > > Here is sample: > SELECT CaseNumber, Unit, CMPFiledDate, CMPRcvdDate, CMPPlus30, CMPPlus60 > FROM [MyTable] > WHERE CMPFiled = True AND CMPAcpt = False AND CMPRcvdDate IS NOT NULL AND > CMPRcvdDate BETWEEN NZ([CMP Rcvd From],#1/1/1900#) AND NZ([CMP Rcvd > To],#12/31/2999#) > ORDER BY CMPRcvdDate DESC > > This also shows how to set up default if user enters nothing. > -- Dorian > "Give someone a fish and they eat for a day; teach someone to fish and they > eat for a lifetime". > > > "SSi308" wrote: > > > I am in the beginning stages of setting up a call database that tracks calls > > made by sales people. The table includes separate fields for date and time. > > > > A query is needed that will prompt for a date range and time range. > > For example a user may want a report that shows all calls for April 1 > > through April 7 between 11:00 AM and 12:00 PM. > > > > I created the query for a time range, which worked. I then added the > > expression, for date range: >=[Start Date] And <=[End Date] I have also > > tried: BETWEEN [Start Date] AND [End Date] > > > > Both expressions get the same error when trying to run the query: > > "This expression is typed incorrectly, or it is too complex to be evaluated. > > For example, a numeric expression may contain too many complicated elements. > > Try simplifying the expression by assigning parts of the expression to > > variables." > > > > For your reference the time expression is: Between [Start Time] And [End Time] > > > > How do I set up the query to allow choosing both date range and time range? > > > > There are other criteria that will also need to be added, but thought I > > should get this working first. Thanks for any help you can give. > > > > Lori
|
Pages: 1 Prev: How to calculate overdue? Next: Help with InStr in a query |