Create a Query that Prompts for Date and Time

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
0
Utf
5/5/2010 8:43:02 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
930 Views

Similar Articles

[PageSpeed] 34

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
0
Utf
5/5/2010 10:00:01 PM
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
0
Utf
5/5/2010 10:17:00 PM
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
0
Utf
5/6/2010 3:00:01 PM
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
0
Utf
6/1/2010 2:19:01 PM
Reply:

Similar Artilces:

Problems with creating a newsletter
I chose the form "Newsletter - email". I have created a 3 page newsletter. Now, I have NO IDEA how to send it out as an email. I do not want to send it as an attachment. I cannot figure out which "save as" format or what I need to do so that i can email this newsletter. Is there a website that goes through how to do this? Am I correct when I say that it has to be html in order for me to send it as email? That is not one of the options. Any help you give me would be greatly appreciated. Thank you, Markis www.adreamforabetterworld.com ...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

Opening pub files created with older Publisher versions #2
I have just upgraded to Publisher 2003 from 2000 and am having trouble with pub files sent to me for our chuch newsletter which is using Publisher 97 The text is not wrapping around graphics boxes. Can I fix this? I really don't want to go back to Pub 200 Thank Richard this is caused by Publisher 97 not been printer independent. Even if you went back to Publisher 2000, unless you have the identical font versions and printer driver you would have issues with formatting. It sounds like the person sending you the file has a garbage HP inkjet printer. Get them to install the HP5P laser p...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

running reports from MS CRM prompt for username and password
Senario: When running a report from Microsoft CRM client it prompts for username and password. When given it's runs the report correct question: if MS CRM 3 is configured correctly is it normal that when trying to generate a report in MS CRM prompt for username and password? No, ideally it should pass the users AD credentials to the reporting IIS server (single sign on). If you were to close the browser and run a different report does it still prompt for credentials? I've noticed this does also depend on the hostname. For instance if the reporting server uses a differe...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

Time Clock Systems
Does anyone have a recommendation for a time clock system that integrates well with GP? On Oct 5, 10:20 am, kcd <k...@discussions.microsoft.com> wrote: > Does anyone have a recommendation for a time clock system that integrates > well with GP? We just implemented Time Matrix by Business Computers (www.business- computers.com) and are very happy with it. We implemented quickly the hardware wasn't propietary or complicated so we were able to source our own stuff. Troy I can speak highly of Unitime's time and attendance system. They are a relatively low cost solution t...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Creating Text Box in Publisher 2007 Crashes the Application
Hello, we have a clean install of Publisher 2007 under Windows XP SP2, and when we try to create a text box in a document, (both an existing document and a blank document), publisher crashes. Office is fully patched. I ran Office Diagnostics from the help menu and no problems were found and the issue persists. Any thoughts on how to resolve the issue? Thanks, Syd See if selecting a different printer as default helps. How to view error signatures if an Office program experiences a serious error and quits http://support.microsoft.com/kb/289508/en-us -- Mary Sauer MSFT MVP http://of...

Creating a Macro to Delete Commas #2
I have an excel file that the size will varry. I need a macro that will check all the fields for a comma. If there is one I would like to get rid of it. Does anyone have any idea how to do this? I have no idea and I have been assigned this task. Help --- Message posted from http://www.ExcelForum.com/ No macro required. ctrl-H for find/replace. find , replace nothing (leave the replace field blank). You can of course record that within a macro if you wish. Drabbacs >-----Original Message----- >I have an excel file that the size will varry. I need a macro that will >check ...

Create Exchange mailbox from command line
I'm writing a script using dsadd and I was wondering if it's possible to create an exchange mailbox from the command line. Donovan Maybe not exactly what you want but it may help: http://www.joeware.net/win/free/tools/exchmbx.htm -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "Donovan Linton" <DonovanLinton@discussions.microsoft.com> wrote in message news:D9C839EF-883D-4E2E-8BE9-57782582F043@microsoft.com... > I'm writing a script using dsadd and I was wondering if it's possible to > create an ...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

creating a backup on 2002 for Money 98
I am helping a friend who has 98. I need to make a backup of info on my 2002 for him to use on 98. Any suggestions as to how to do this? M98 can't read any file written by M02 besides .QIF import. M02 can't write any file readable by M98 except for .QIF export. Sounds like QIF export/import is your only choice. I suspect you will find this doesn't do what you want. "Carlotte" <Carlotta41@discussions.microsoft.com> wrote in message news:015b01c3d2fa$bbf8fd60$a101280a@phx.gbl... > I am helping a friend who has 98. I need to make a > backup of info on...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

Adding a word to the end of other words at the same time
I was wondering if there was a way to add a word to the end or beginning of multiple other words in Excel. Example; say I have these 3 words.... Alpha Beta Tera Now I want to add LLC to the end of each word but I want to change them all at the same time. Like Alpha LLC Beta LLC Tera LLC Is there a way to do that? Phil Its Excel 2003 try Sub addtexttoend() For Each c In Selection c.Value = c & " xxx" Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "phil" <ptukey@charter.net> wrote in message news:1125340358.873337.4240@g44g2000cwa.googlegroup...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

Start macro creating a mail with contact data and autotext
Hallo, I am working with an user form. The developing of that form started with Outlook XP with a lot of code inside for different buttons. I changed to Outlook 2007 and unfortunately the code of the form was not longer displayed. What I learned about this is that MS does not support to much code in the form (or maybe a bug). They also do not support any longer. I was sending this form to MS support but they told it is do much code inside and they do not know, why the code is not displayed. In Outlook 2003 the code is displayed as in Outlook XP. Because I do not know real...

How to Identify Records with Overlapping Dates
I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was < Term date TableA_1 and TableA_1 Begin Date < TableA End Date. Which worked great for me finding overlapping records where the product was different, bu...