Missing records using a timestamped field to query.

I'm trying to extract a range of information using a timestamp field through 
a select query.  I'm using Between[Date] and [Date].  It's not caputuring all 
of the records, specifically the records w/ the 1st and the very last days of 
the range.  I tried doing something like this "Date([Timestamp])" but to no 
avail.  Any thoughts as to why?!  Thanks, Michael
0
Utf
2/12/2008 4:39:02 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
785 Views

Similar Articles

[PageSpeed] 22

On Tue, 12 Feb 2008 08:39:02 -0800, Michaelcip
<Michaelcip@discussions.microsoft.com> wrote:

>I'm trying to extract a range of information using a timestamp field through 
>a select query.  I'm using Between[Date] and [Date].  It's not caputuring all 
>of the records, specifically the records w/ the 1st and the very last days of 
>the range.  I tried doing something like this "Date([Timestamp])" but to no 
>avail.  Any thoughts as to why?!  Thanks, Michael

Please be more specific. Between [Date] AND [Date] is not a range.

Please post the SQL view of your query and perhaps a sample of the data you're
missing. My *guess* is that you're using a range of just dates, but that the
table data contains date and time; if so a criterion

>= [Start date] AND < DateAdd("d", 1, [End date])

on the date field should do the job. If the user enters 2/12/2008 in the End
Date parameter, the DateAdd will ensure that all times during that day are
included in the search.
-- 
             John W. Vinson [MVP]
0
John
2/12/2008 6:33:26 PM
IF your timestamp field contains a date and a time then those field that 
occur from 00:00:01 and on on the last date in your range will be excluded. 
You will only see the records that have midnight on the last date.

I can think of no reason for your missing records on the earliest date in 
the range.

Try this as the criteria.
>= [Enter begin date] and < DateAdd("d",1, [Enter End Date])

-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Michaelcip" <Michaelcip@discussions.microsoft.com> wrote in message 
news:86587EB4-CB49-4B33-941E-96941EDC881A@microsoft.com...
> I'm trying to extract a range of information using a timestamp field 
> through
> a select query.  I'm using Between[Date] and [Date].  It's not caputuring 
> all
> of the records, specifically the records w/ the 1st and the very last days 
> of
> the range.  I tried doing something like this "Date([Timestamp])" but to 
> no
> avail.  Any thoughts as to why?!  Thanks, Michael 


0
John
2/12/2008 7:01:28 PM
It's a SQL statement to display a bar chart:
SELECT tblQA.CODE, Count(*) AS [Count]
FROM (qryElectrical INNER JOIN tblQA ON qryElectrical.QAID = tblQA.QAID) 
INNER JOIN tblCode ON qryElectrical.CODEID = tblCode.CODEID
WHERE (((qryElectrical.tblHeader.TimeStamp) Between 
[Forms]![frmReports]![Start] And [Forms]![frmReports]![End]) AND 
((tblCode.CODEID)=[Forms]![frmFailCode]![txtCode]))
GROUP BY tblQA.CODE
ORDER BY Count(*) DESC;

"John W. Vinson" wrote:

> On Tue, 12 Feb 2008 08:39:02 -0800, Michaelcip
> <Michaelcip@discussions.microsoft.com> wrote:
> 
> >I'm trying to extract a range of information using a timestamp field through 
> >a select query.  I'm using Between[Date] and [Date].  It's not caputuring all 
> >of the records, specifically the records w/ the 1st and the very last days of 
> >the range.  I tried doing something like this "Date([Timestamp])" but to no 
> >avail.  Any thoughts as to why?!  Thanks, Michael
> 
> Please be more specific. Between [Date] AND [Date] is not a range.
> 
> Please post the SQL view of your query and perhaps a sample of the data you're
> missing. My *guess* is that you're using a range of just dates, but that the
> table data contains date and time; if so a criterion
> 
> >= [Start date] AND < DateAdd("d", 1, [End date])
> 
> on the date field should do the job. If the user enters 2/12/2008 in the End
> Date parameter, the DateAdd will ensure that all times during that day are
> included in the search.
> -- 
>              John W. Vinson [MVP]
> 
0
Utf
2/12/2008 7:32:02 PM
On Tue, 12 Feb 2008 11:32:02 -0800, Michaelcip
<Michaelcip@discussions.microsoft.com> wrote:

>It's a SQL statement to display a bar chart:

If the field named TimeStamp (which I presume is selected in qryElectrical) is
a date/time field containing a time portion... and if you're missing the
records from the last day of the range... try

SELECT tblQA.CODE, Count(*) AS [Count]
FROM (qryElectrical INNER JOIN tblQA ON qryElectrical.QAID = tblQA.QAID) 
INNER JOIN tblCode ON qryElectrical.CODEID = tblCode.CODEID
WHERE (((qryElectrical.TimeStamp) >=[Forms]![frmReports]![Start] 
And (qryElectrical.TimeStamp) < DateAdd("d", 1, [Forms]![frmReports]![End]))
AND ((tblCode.CODEID)=[Forms]![frmFailCode]![txtCode]))
GROUP BY tblQA.CODE
ORDER BY Count(*) DESC;
-- 
             John W. Vinson [MVP]
0
John
2/12/2008 8:08:25 PM
This did the trick!  I appreciate your time in educating me!!

"John W. Vinson" wrote:

> On Tue, 12 Feb 2008 11:32:02 -0800, Michaelcip
> <Michaelcip@discussions.microsoft.com> wrote:
> 
> >It's a SQL statement to display a bar chart:
> 
> If the field named TimeStamp (which I presume is selected in qryElectrical) is
> a date/time field containing a time portion... and if you're missing the
> records from the last day of the range... try
> 
> SELECT tblQA.CODE, Count(*) AS [Count]
> FROM (qryElectrical INNER JOIN tblQA ON qryElectrical.QAID = tblQA.QAID) 
> INNER JOIN tblCode ON qryElectrical.CODEID = tblCode.CODEID
> WHERE (((qryElectrical.TimeStamp) >=[Forms]![frmReports]![Start] 
> And (qryElectrical.TimeStamp) < DateAdd("d", 1, [Forms]![frmReports]![End]))
> AND ((tblCode.CODEID)=[Forms]![frmFailCode]![txtCode]))
> GROUP BY tblQA.CODE
> ORDER BY Count(*) DESC;
> -- 
>              John W. Vinson [MVP]
> 
0
Utf
2/12/2008 8:34:03 PM
Reply:

Similar Artilces:

How to get TASK_ID field for summary tasks without using Project.a
I know for tasks which are not summary tasks we can get TASK_ID field using statusing web service. But i could nto find any other options than Project web service to get TASK_ID field for summary tasks and the top level project task. Problem of using Project web service is that in my custom sharedpoint web part where we are using PSI web services we get all the data required using Resource and Statusing web service for the logged in resource. But Statusing web service retrieves TASK_ID only for actual tasks and not for summary tasks. Now just to get TASK_ID of summary tas...

Can't get the proper display of a field in my report.
I have 2 tables, both using autonumbers for their primary key. The first table is for contacts (i.e. last name, first name, etc.). The second table is for businesses (business name, etc.) I have a field in my contacts table that has a number format so it can be used as a foreign key for the business table. I then set up the relationship between them & enforced referential integrity. When I run a query, I see the name of the business (after setting up a combo box) - no problem. When I run a report based on that query, a number is displayed (not the business name). Suggestions, pleas...

Controlling printed records when report bound to multiple tables
I created a report that uses the control from a form to generate a report based on that record's primary key. This form also has a subform which has relationships tied to the primary key for record identification and is linked to the main table. When preview the report the data from the subform either does not show up in the preview when using the filter [control]=[form]![control].[value] or makes multiple copies of the report equal to the number of entries in the subform's table. Is there any way around this? I have tried queries but have not found a way to use a f...

2007
Project desktop expert, new to Project Server 2007 and working with a sandbox implementation currently. When I use the PWA Build Team...Replace to replace a generic resource on a project with an actual named resource after first publishing a plan, my updates don't appear to be reflected in Project Server. If I check out and open the Project Plan in MSP Pro, however, the updates were made, and then if I publish the schedule, the updates appear in server. I thought when you made resource updates using PWA that they should be reflected immediately (or as soon as the request...

Permissions is not allowing me to use my print preview.
Help! Don't understand My Permission in Excel. Even though I unrestrict, it still doesn't allow me to Preview my documents. How do I reset? Please Help, ...

With a Query in Access 2007, How can I Create This Query
I need a query that will list all records in table 1 for which there are no auditor records (Table 3). Somehow, I need to use the relationship between tables 2 and 3 to find what's not in table 1. The following query gives me a list of all records that do have auditor records. I'm at a dead end on this one. Query SELECT PTOTNamesTbl.PTOTAuditingTherapist, PTOTAuditingTherapist.PTOTFirstName, PTOTAuditingTherapist.PTOTLastName, AuditDetailInitialEval.TherapistLastName, PTOTNamesTbl.PTOTFirstName, PTOTNamesTbl.PTOTLastName, AuditDetailInitialEval.Medicare, AuditDet...

Transferring Field from Existing Table/limitations and change of d
Thank you in advance for your help! I have two Excel spreadsheets that I successfully imported into Access 2003 and created tables for. I need to add the field from one table to the other, but there is not a direct match in the relationship. The large table uses the Employee ID as the primary key. The smaller table contains one field that lists a subset of these Employee ID numbers (a selection of certain employees). I need to transfer this field to the larger table, but I do not know how to tell Access to match up the corresponding numbers (i.e., the large table lists all employees, bu...

How do I TRIM a field in existing fields....?
I didn't realize when I imported some data into my database that there were a bunch of spaces after all the data. I know that I can do a RTRIM the data in a query, but I don't want to have to remember every time I create a query to TRIM it. Do I have to use a query and make a temp table and then delete the data from my original table and then put the data back in from my temp table. I can do this, but wanted to know if there is a better way. Thanks Kelvin Kelvin A standard approach to importing (and "cleaning") data is to import/append to a temporary table, then ...

Query cross two table
Hi, I have two tables, tbAdmission and tbCode. In my tbAdmission, I have Code1, Code2 and Code3. In my tbCode, I have Code, Description1 and Description2. In my Form, frmAdmission, I have txtCode1, txtCode2 and txtCode3 that are all bounded to tbAdmission. And txtDescription1Code1, txtDescription2Code1, txtDescription1Code2, txtDescription2Code2, txtDescription1Code3 and txtDescription2Code3 that are unbounded and only for displaying the descriptions. txtCode1, txtCode2 and txtCode3 all refer to Code in tbCode to retrieve Description1 and Description2 for displaying in the unbou...

lotus approach queries VS access queries.
Hi, We are migrationg from approach to access. My basic underastanding of the procedure is that the data has to be migrated and all the other features like forms and reports have to be recreated. Is 'Approach query' different from MS Access query? Can this be assumed to be replaced by Access query? cheers, Nuti ...

Question on multiple NICs used by Exchange 2003
I have a requirement to bind four virtual SMTP servers to unique IP addresses. The server has four NICs and each IP address is uniquely bound to a virtual SMTP server. I used telnet to confirm that the appropriate virtual SMTP server responds to each assigned IP address. In case you are wondering why I did not use virtual IP addresses- Per Microsoft, the virtual IP address schema will not satisfy our requirement that the same IP address be used for sending (relaying) the e-mail that received it. Their recommendation was to have a NIC for each virtual SMTP server. The problem that I am ex...

Developing a robust database at one co. then using it at another c
Are there any legal implications of using the type of database listed above to perform similar functions for another company? The 2 company's are in 2 totally different industries and the new database will have to be modified to fit the requirements for the industry it is in. The database was developed at the old company. there is no such thing as a robust Jet database. Move to SQL Server if you want to build a solution that will work for the next decade. Jet is and always has been depecrated On Apr 7, 12:14=A0pm, BoaMan10 <BoaMa...@discussions.microsoft.com> w...

How to use AjaxToolKit in asp.net for DHTML Editor
Dear Sir/Madam Please tell me how to use AjaxToolKit dll for DHTML purpose using C#. Thanks in Advance "Deep" <vinodkus@gmail.com> wrote in message news:7ac633e9-2cbd-4417-9b4e-363a9b5b3e74@d27g2000yqf.googlegroups.com... > Please tell me how to use AjaxToolKit dll for DHTML purpose using C#. http://tinyurl.com/ybfuqo8 ...

Using the classes created with xsd.exe
I have created classes from several xsd files. These files create about 150 classes and spot checking them they do represent types in the xsd files. the question is how do I use these files. How do I load data into them and create xml from them. Is there some articles about this subject. Thank you, -- Jerry Hi Jerry, As for the classes you've generated, are they normal classes or dataset classes? As for the normal classes you generated through xsd.exe, you can use XML serialization to convert those class instances into XML content or deserialize the XML content back into objec...

Report repeats a field
Example: John Smith 2,14 5,27 3,18 John Smith 3,17 4,27 7,34 John Smith 1,22 6,57 8,92 I want that the report shows a name(John Smith) only one time like this: John Smith 2,14 5,27 3,18 3,17 4,27 7,34 1,22 6,57 8,92 The report get the informations from a Query that get the information from a table. One way to do this would be to set the "name" control's Hide Duplicates property to Yes. Another way to do this would be to use Sorting & Grouping, then Gro...

Date Query 12-07-07
Hello I'm trying to build a query that shows me all records where a field is older than 90 days from today or are null. I've tried using <Now()-"90" Or Is Null but this doesn't give the desired results. What am I getting wrong? Thanks Assumption: Your field is a date field Field: YourDateField Criteria: Is Null Or <DateAdd("d",-90,Date()) Or try dropping the quotes in your expression so it reads Field: YourDateField Criteria: <Now()-90 Or Is Null -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management Un...

Reminders coming back after offline use
I have a user who uses Outlook on his laptop, but when he is out of the office he accesses mail through OWA. Every time he comes back in the office he says he gets inundated with old reminders popping up. And when you click on "Dismiss" you get an error that says, "conflicting edits have been made to the same item..." and there are duplicates of the event. I looked at this today and noticed that there is an event with the original date and the duplicate was showing a last-edited date of last Thursday. I asked if anything unusual happened last Thursday and he said he pro...

How to merge columns and rows into one cell besides using Merge and Center Icon?
I had posted this question before, but I couldn�t find this thread in any of the pages up till page 17. So I�m posting again. I want to merge all rows and columns starting from A1 to J2, with no lines in between into one cell. My text data value are in D1 and D2 respectively. Using Merge and Center Icon will only retain the upper-left most data, resulting the data in D2 to be deleted. So how to merge all rows and columns into one cell and yet prevent the data in D2 to be deleted? Any help will be greatly appreciated. ------------------------------------------------ ~~ Message posted from h...

Virus Outbreak by using SMTP virtual server
Hi, The client was accidentally open approved-password.zip file then the nightmare began to spread out. It will use "system administrator" and "webmaster" as sender to inform the user that their password is grant or deny. Today, it tried to send out as NDR style. I assumed the virus on the workstation is using our smtp server. Will there a log that SMTP virtual server may generate so I can keep cross reference such as workstation or username so I can pinpoint? Any suggestion or recommendation will be appreciated. I am currently using Trend Micro Offican or Scan...

Using MFC dll from non-MFC application
I have been struggling several weeks with this problem. I need to call an MFC dll from a non-MFC application. The MFC dll was originally a working MFC program that I conveted to a dll. The dll works fine except that it crashes when I exit. Can anyone provide me with some snippets of working code showing how you did this? An example of the calling code, the CWinApp (or at least the InitInstance) and the hook function would be great. I have read several times that this should be easy to do, so I am probably just missing a simple line somewhere. The way I created my dll is to use to ...

Missing pictures when converting to pdf
When I convert a file to pdf, some of the pictures in the doc file do not make it into the pdf file. I tried several times. Tried creating a new Word file. Compressed pictures. Help, I have to deliver this document to my customer soon. Make sure that the pictures are embedded and not linked int he word file. Are u linking them?? "Martiart" wrote: > When I convert a file to pdf, some of the pictures in the doc file do not > make it into the pdf file. > I tried several times. Tried creating a new Word file. Compressed pictures. > Help, I have to deliver th...

SQL Query in VBScript
I am using the following code to perform a SQL query and return a recordset I am getting the error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" This error occurs when I open the record set. What am I doing wrong? Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3 Set objConnection = CreateObject("ADODB.Connection") Set objRecordset = CreateObject("ADODB.Recordset") objConnection.Open "DSN=ChartMES;" objRecordset.CursorLocation = adUseClient strQuery = "Use...

FORM Calculated Field
I am trying to attempt a simple percentage calculation to provide our sales team for the Opportunity Entity. I have 3 fields 1. estimatedvalue (Est. Revenue)- money 2. closeprobability (Probability) - int 3. dsi_weightedrevenue (Weighted Revenue) - int I need the following calculation to populate my Weighted Revenue (int) field as a percent estimatedvalue X closeprobability = dsi_weightedrevenue I have tried the following script adding it to the OnLoad event of the form being sure to select the "Event is enabled" check box there as well. But after publishing I get nothing but a...

Deleting data from a table through a query
I have a database that is designed to update a list of credit union members from a master list so that vehicle insurance coverage can be tracked. I have been able to run an unmatched query to achieve a list of old members who have either paid off their vehicles or moved their loans to other locations. What I need to do now is delete these people from the main table. I have the cascade update and delete funtion in place to delete the vehicle information once the member is deleted, but I don't know how to take the information found in the unmatched query and delete those members fr...

Auditing form on record change
HI, I have a form with multiple fields that I want to track changes to. I was able to create a auditing trail when users click on particular buttons however if they dont click on the buttons the auditing does not occur. What I am wanting to do is to run the audit when a user changes to a new record. I have tried the "on current" and "before update" but they didnt work. Any suggestions? Take a look at what Allen Browne has at http://www.allenbrowne.com/AppAudit.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "...