Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing Data from Query (2000)

    I have a table that has information stored with a datestamp. I have data from July 1st to July 16th. When I run the following query, it only returns data for datestamps that the day begins with a 1. For example July 2nd through the 9th are excluded. Here is the query:

    SELECT [tblDataLogger].[Unit1], [tblDataLogger].[Unit2], [tblDataLogger].[Month], [tblDataLogger].[Day], [tblDataLogger].[Year], [tblDataLogger].[Hour], [tblDataLogger].[Minute], [month] & "/" & [day] & "/" & [year] AS Datestamp, TimeSerial([Hour],[Minute],0) AS [TimeStamp]
    FROM tblDataLogger
    WHERE ((([month] & "/" & [day] & "/" & [year]) Between [Forms]![frmMain]![txtStartDate] And [Forms]![frmMain]![txtEndDate]));

    Thanks,

  2. #2
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Data from Query (2000)

    For some reason the query sees 10, 11, 12, 13, etc as being less than 2, 3, 4 etc. Is there a way to stop this?
    Thanks,

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing Data from Query (2000)

    Try

    WHERE DateSerial([Year],[Month],[Day]) Between [Forms]![frmMain]![txtStartDate] And [Forms]![frmMain]![txtEndDate]

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Data from Query (2000)

    It looks like your dates are being stored as text fields.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Data from Query (2000)

    Hans,
    Your suggestion worked on one of the queries but not the other. Here is the other query:

    SELECT [tblDataLogger].[Unit1], [tblDataLogger].[Unit2], [tblDataLogger].[Month], [tblDataLogger].[Day], [tblDataLogger].[Year], [tblDataLogger].[Hour], [tblDataLogger].[Minute], [month] & "/" & [day] & "/" & [year] AS Datestamp, TimeSerial([Hour],[Minute],0) AS [TimeStamp]
    FROM tblDataLogger
    WHERE ((([month] & "/" & [day] & "/" & [year])<Date()-365));

    I tried replacing month] & "/" & [day] & "/" & [year] with DateSerial([Year],[Month],[Day]) and the query does not return any data. Any suggestions?

    Also I checked the table and the fields are set to number not text.

    For some reason, now all the data between the date range is displayed in the query, but it will not sort based on the DateSerial. When I sort Ascending the dates show up 7/1/2008, 7/10/2008, 7/11/2008, 7/2/2008, 7/3/2008, 7/4/2008 etc. Shouldn't the 2, 3, and 4 come before the 10th?

    Thanks,

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing Data from Query (2000)

    The WHERE clause

    WHERE DateSerial([Year],[Month],[Day])<Date()-365

    should select all records whose date is more than a year ago.

    If you sort on DateSerial([Year],[Month],[Day]), the records should be sorted as date values, not as strings.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Data from Query (2000)

    thanks hans... that worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •