Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Date problem (2003)

    I have a query which runs a report, giving jobs performed. I've got the report to run giving me either all jobs, or just the ones I choose by part id. I want to pick them by Date. I put in the criteria Between [From ] And [To ], and although when the query is run I am asked for the From and To, I get all the results from the table, and not the date range I entered. Could any suggest what the problem could be. Thanks

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

    Re: Date problem (2003)

    I assume the query is the Record Source of the report. Is that correct? If so, could you post the SQL of the query?

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date problem (2003)

    Yes it is. Here's the SQL

    SELECT tblAward.[Quote No], tblAward.Date, tblAward.[Part No], tblAward.Description, tblAward.[Materials Cost], tblAward.[Ancilleries Cost], tblAward.[Design Hrs], [Design Hrs]*25 AS [Design Cost], tblAward.[Programming Hrs], [Programming Hrs]*25 AS [Programming Cost], tblAward.[Blocking Up Hrs], [Blocking Up Hrs]*25 AS [Blocking up Cost], tblAward.[Gen Mill/Drill Hrs], [Gen Mill/Drill Hrs]*25 AS [Gen Mill/Drill Cost], tblAward.[Turning Hrs], [Turning Hrs]*25 AS [Turning Cost], tblAward.[Grinding Hrs], [Grinding Hrs]*25 AS [Grinding Cost], tblAward.[CNC Milling Hrs], [CNC Milling Hrs]*25 AS [CNC Milling Cost], tblAward.[Spark Erosion Hrs], [Spark Erosion Hrs]*25 AS [Spark Erosion Cost], tblAward.[Polishing Hrs], [Polishing Hrs]*25 AS [Polishing Cost], tblAward.[Fitting Hrs], [Fitting Hrs]*25 AS [Fitting Cost], tblAward.[Check Hrs], [Check Hrs]*25 AS [Check Cost], [Materials Cost]+[Ancilleries Cost]+[Design Cost]+[Programming Cost]+[Blocking up Cost]+[Gen Mill/Drill Cost]+[Turning Cost]+[Grinding Cost]+[CNC Milling Cost]+[Spark Erosion Cost]+[Polishing Cost]+[Fitting Cost]+[Check Cost] AS TOTAL, tblAward.Details, tblAward.[Requested By], tblAward.[Manufacturing Time], tblAward.[Purchase Order No], [Enter Code ] AS Expr1
    FROM tblAward
    WHERE (((tblAward.Date) Between [From ] And [To ]) AND ((tblAward.[Part No]) Like "*" & [Enter Code ] & "*")) OR (((tblAward.Description) Like "*" & [Enter Code ] & "*"));

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

    Re: Date problem (2003)

    You must put the Between ... And ... condition on both criteria lines in the query design grid, or (equivalently) change the way the parentheses are placed in the SQL statement:

    ...
    WHERE tblAward.Date Between [From ] And [To ] AND (tblAward.[Part No] Like "*" & [Enter Code ] & "*" OR tblAward.Description Like "*" & [Enter Code ] & "*");

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date problem (2003)

    Done exactly what you said, not getting any results now

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

    Re: Date problem (2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date problem (2003)

    Compact and repair failed. I'll need to do it again.

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

    Re: Date problem (2003)

    You don't have date criteria at all in your query <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    What I proposed works flawlessly - see attached version.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date problem (2003)

    I typed in From 30/04/2005 To 04/05/2005, and got nothing

  10. #10
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date problem (2003)

    Sorry, typed in wrong date

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date problem (2003)

    It's working, but if I only type in the same date in the From and To, I don't get anything. Thanks Hans.

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

    Re: Date problem (2003)

    Since you have set the default value of the Date field to =Now(), the values contain a time component. You don't see this component, since the format has been set to Short Date. If you set it to General Date, you should see the time component too.

    You can get around it by changing both occurrences of the date criterion to

    Between [From ] And [To ]+1

    You will also have to declare the parameters explicitly:
    - Open the query in design view.
    - Select Query | Parameters...
    - Enter [Enter Code ] in the Parameter column and set the data type to Text.
    - Enter [From ] in the next row and set the data type to Date.
    - Enter [To ] in the next row and set the data type to Date.
    - It is important that you enter the parameters <big>exactly</big> the same as they are in the Criteria part of the query.
    - Click OK.

Posting Permissions

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