Results 1 to 7 of 7
  1. #1
    hawzmolly is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    Query doesn't return all data

    I have a strange one I can't seem to solve. I have a query that I use to generate a report which returns each entry of daily sales data. I can either call for this data by the month or all of the months. My trouble is that the last month, March in this case will return only the last entry date. I can run Jan or Feb and the query (report) will return every day's data. I need the data from every date entered for a report for the head of this department.

    My code is below. Please show me the error of my ways.


    SELECT tblDailies.Date, tblDailies.GreetingCards, tblDailies.Giftware, tblDailies.CandyPhoneCardsCameras, tblDailies.MagazinesBooks, tblDailies.Balloons, tblDailies.Jewelry, tblDailies.Flowers, tblDailies.GiftCardSales, tblDailies.Stamps, tblDailies.Month, tblDailies.Year, tblDailies.Tax, [GreetingCards]+[Giftware]+[CandyPhoneCardsCameras]+[MagazinesBooks]+[Balloons]+[Jewelry]+[Flowers]+[GiftCardSales]+[Tax]+[Stamps]+[NonTaxSales] AS TotalSales, [GreetingCards]+[Giftware]+[CandyPhoneCardsCameras]+[MagazinesBooks]+[Balloons]+[Jewelry]+[Flowers]+[GiftCardSales] AS TotalTaxableSales, tblDailies.Over, tblDailies.Short, [Over]+[Short] AS OverShort


    FROM tblDailies
    WHERE (((tblDailies.Month) Like [Forms]![SearchDailyByMonth].[qMonth] & "*"))
    ORDER BY tblDailies.Date;

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Let me get this straight you use this query basically to find the sales details for a specific month and now your problem is that You want the dales details for all the dates.

    You input the month value from a Form (SearchDailyByMonth) Control named (qMonth)


    Now the solution are as follows:
    For this solution I assume that Your Date Field date type is Set to Date/Time

    WHERE (((tblDailies.Date) Between #1/1/2010# And #12/31/2010#));
    This will display all the sales details for the year.

    Now if you want to enter the Dates from your form (SearchDailyByMonth) Create Two TextBox qDate and qDate1.Make sure that the text box Format are set to short Date like the date format in the Date Field in table tblDailies

    WHERE (((tblDailies.Date) Between [Forms]![SearchDailyByMonth]![qDate] And [Forms]![SearchDailyByMonth]![qDate1]));


    I assume you know how to build a query and add criteria.


    If you have saved date in text field this solution will not work. You have to then convert date in the text field which is a string into date using DateSerial e.g. I have date in Form of mm/dd/yyyy 03/10/2010 in a text field to convert it into date in a query:

    on a blank column in a query after selecting all the fields from your table do this:
    Date1ateSerial(Right([Date],4),Left([Date],2),Right(Left([Date],5),2))
    now you have to add the criteria in the newly created field.

    If you want to use query wizard to build the queries the criterias are:
    1) Between #1/1/2010# And #12/31/2010#
    2) Between [Forms]![SearchDailyByMonth]![qDate] And [Forms]![SearchDailyByMonth]![qDate1]

    Mark the Thread solved if this solves your problem.

  3. #3
    hawzmolly is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77
    Thanx for the quick reply.

    Maybe I didn’t state my problem correctly or fully. At the end of each month or even during the current month, I need to produce a “report” that details daily amount of sales for each category, the result of all taxable and non-taxable sales, and the average sales for each category. I use a query (SQL view shown) to get the report data. If I want individual date data I can get that with a separate query. In this case I need to create the report for only a specific month.

    The tblDailies has “Date/Time” for the date field, “Currency” for the dollar amount fields i.e., “GreetingCards”, “Text” for a month and year fields.
    When the operator needs the data i.e., “Print Daily Reports by Month” they are taken to a pop-up that asks for the month.

    Using WHERE (((tblDailies.Month) Like [Forms]![SearchDailyByMonth].[qMonth] & "*"))
    If one enters “Jan” then the January Report is presented including 1/2/2010 through 1/31/2010. February works also. But when we run March the report only comes up with one date. That is the problem. The report should work the same in all cases. If the pop-up is left blank then all months are presented. The Jan and Feb are ok but the March report shows only the last day entered.

    Because the person who normally inputs data and prints reports is not at all familiar with Access I don’t want them to have to enter the inclusive dates (1/1/2010 to 1/31/2010) to get the report.

    I have included screen prints for your perusal.

    [IMG]file:///C:/Users/Haworth/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg[/IMG]
    [IMG]file:///C:/Users/Haworth/AppData/Local/Temp/msohtmlclip1/01/clip_image004.jpg[/IMG]
    [IMG]file:///C:/Users/Haworth/AppData/Local/Temp/msohtmlclip1/01/clip_image006.jpg[/IMG]

  4. #4
    hawzmolly is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77
    Sorry - here are the files

  5. #5
    hawzmolly is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77
    Everything I have tried has not worked. I rebuilt the query from scratch. Now the query works as it is supposed to. Maybe some kind of clitch. Thanx for the help and suggestions.

    If I ever find the cause I will post it here.

  6. #6
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    zip your database and send upload. lets see what is bugging you.

  7. #7
    hawzmolly is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    Red face I found it - How embarrassing!!!

    The problem was in one of the db fields. I had failed to enter the month correctly. It was one of those things that is so simple it was easy to overlook.

    Thanx for all the help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Return all records from Query
    By ysrini in forum Queries
    Replies: 1
    Last Post: 01-15-2010, 09:52 PM
  2. Importing html data - Carriage Return Problem
    By MichelleNZ in forum Import/Export Data
    Replies: 1
    Last Post: 11-26-2009, 05:13 PM
  3. Replies: 0
    Last Post: 08-04-2009, 08:51 PM
  4. Replies: 0
    Last Post: 11-12-2008, 05:18 PM
  5. Return Record # In Query
    By redwinger354 in forum Access
    Replies: 1
    Last Post: 09-15-2007, 01:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums