Page 1 of 2 12 LastLast
Results 1 to 15 of 27

Thread: Date range

  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Exclamation Unanswered: Date range / Still no good ELP!

    Could some help me out on this one;
    Yes I have tried msdn / google etc.
    I even had this working at one point saved it for a ciggy came back it no longer worked.

    This is what I was using it did work now it dose'nt..

    WHERE (((Int_Fault.Job_Done)=True)) AND ((Int_Fault.Date_Resolved) >= #03/10/2004#) AND ((Int_Fault.Date_Resolved)< #03/18/2004#)

    But what I could really do with is this;

    WHERE (((Int_Fault.Job_Done)=True)) AND ((Int_Fault.Date_Resolved) >= Date less one month) AND ((Int_Fault.Date_Resolved)< Todays date

    Thanks in advance

    s33ka
    Last edited by s33ka; 03-29-04 at 07:13.

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Is it possible that null data or garbage data in the fields that you are looking in caused your where statement to no longer work?

    Regarding the change desired, can you use the Date() function? This returns the date only portion (see the related function Now() that returns date and time stamp). For the other search criteria, perhaps you could use the DateAdd function.

    DateAdd("m", -1, Date())
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Date range

    Originally posted by s33ka
    Could some help me out on this one;
    Yes I have tried msdn / google etc.
    I even had this working at one point saved it for a ciggy came back it no longer worked.

    This is what I was using it did work now it dose'nt..

    WHERE (((Int_Fault.Job_Done)=True)) AND ((Int_Fault.Date_Resolved) >= #03/10/2004#) AND ((Int_Fault.Date_Resolved)< #03/18/2004#)

    But what I could really do with is this;

    WHERE (((Int_Fault.Job_Done)=True)) AND ((Int_Fault.Date_Resolved) >= Date less one month) AND ((Int_Fault.Date_Resolved)< Todays date

    Thanks in advance

    s33ka

    This?:

    ... AND ( Int_Fault.Date_Resolved BETWEEN #" & DateAdd("m",-1,Date) & "# AND #" & Date & "#)

  4. #4
    Join Date
    Mar 2004
    Posts
    15

    Talking

    You see thats where I was going wrong in the first place...
    I was dropping a string like;

    AND (Int_Faul.Date_Resolved)>= =Date() AND < =DateAdd "m", -1 Date()

    Which I probably stand corrected looks as if it should have worked, I can say with hand on heart that it didi not


    AND ( Int_Fault.Date_Resolved BETWEEN #" & DateAdd("m",-1,Date) & "# AND #" & Date & "#)

    This also reads to work, but I dont understand the & and # wait maybe!

    ("this reads similar to my .asp page scripts")

    Which would mean that "#" desinates a number suach as a date and "&" would in effect concat the vars together, let me know if I am on the right track.

    Guys thanks for help on this one


    s33ka

  5. #5
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    To true. The "#" (or single quote) desinates a number such as a date and "&" (or "+") is the means of concatenation.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  6. #6
    Join Date
    Mar 2004
    Posts
    15

    Still dont get it

    AND (Int_Fault.Date_Resolved) >= (#" & DateAdd("m", -1, Date) & "#) AND (Int_Fault.Date_Resolved) < (#" & Date & "#)

    Ok so this is what I am trying now but I'm getting further syntax errors which would mean that I have the bracketts in the wrong place, could someone let me know if this is right, and if so which ones..

    thanks again
    s33ka
    Last edited by s33ka; 03-29-04 at 07:18.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Still dont get it

    Originally posted by s33ka
    AND (Int_Fault.Date_Resolved) >= (#" & DateAdd("m", -1, Date) & "#) AND (Int_Fault.Date_Resolved) < (#" & Date & "#)

    Ok so this is what I am trying now but I'm getting further syntax errors which would mean that I have the bracketts in the wrong place, could someone let me know if this is right, and if so which ones..

    thanks again
    s33ka
    Check the PM I sent you ... Also post your complete SQL statement ... That is most likely where the error is ...

  8. #8
    Join Date
    Mar 2004
    Posts
    15
    Its not brining up my PM's its telling me its blank.

    Full statement;

    Code:
    SELECT [Int_Fault].[HandyMan], [Int_Fault].[Hours_spent], [Int_Fault].[Fault_Id], [Int_Fault].[Property_Add], [Int_Fault].[Date_Resolved], [Int_Fault].[Job_Done]
    FROM Int_Fault
    WHERE ((([Int_Fault].[Job_Done])=True)) AND (Int_Fault.Date_Resolved) >= (#" & DateAdd("m", -1, Date) & "#) AND (Int_Fault.Date_Resolved) < (#" & Date & "#)
    ORDER BY [Int_Fault].[HandyMan];
    Well thats it, this has really got me stomped.
    s33ka

  9. #9
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    try using less parenthesis and more " as the parenthesis's only confuse the criteria

    SELECT [Int_Fault].[HandyMan], [Int_Fault].[Hours_spent], [Int_Fault].[Fault_Id], [Int_Fault].[Property_Add], [Int_Fault].[Date_Resolved], [Int_Fault].[Job_Done]
    FROM Int_Fault
    WHERE [Int_Fault].[Job_Done]=True AND Int_Fault.Date_Resolved >= ("#" & DateAdd("m", -1, Date) & "#") AND Int_Fault.Date_Resolved < ("#" & Date & "#")
    ORDER BY [Int_Fault].[HandyMan];

  10. #10
    Join Date
    Mar 2004
    Posts
    15

    Complie error

    Tried that and it returns a complie error.

    Could it be that I am trying to call a function within a function so to speak.?

  11. #11
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    what's the description of the compile error

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    HOW are you invoking this SQL statement? Are you running this as a query? Using RunSQL?

  13. #13
    Join Date
    Mar 2004
    Posts
    15

    Wired

    Ok just re-booted me sys.

    Tried again, this time there is no compile error, but what I do get now is a message box open asking me for a Parameter Value of Date so I stick in todays date hit OK and get a reported query of zero returns ie theres nothing in the report, and yet when I started with the preset dates I had a returned value of 6 records.

    Hows this for wired.

    So it asks me for the date (Which I can live with) but then returns nada?
    Go figure.
    any ideas??????


    ADDED A LITTLE LATER
    I have just added () at the end of Date (Date()) I now gwt the same results but no message box........

    M Owen

    This is being run from a button on an ACCESS form , basically I am actually opeing a report that gets it data from a Query this query
    Last edited by s33ka; 03-29-04 at 09:31.

  14. #14
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    would you post the buttons' code

  15. #15
    Join Date
    Mar 2004
    Posts
    15

    OK

    Code:
    Private Sub Command85_Click()
    On Error GoTo Err_Command85_Click
    
        Dim stDocName As String
    
        stDocName = "Time Query"
        DoCmd.OpenReport stDocName, acPreview
    
    Exit_Command85_Click:
        Exit Sub
    
    Err_Command85_Click:
        MsgBox Err.Description
        Resume Exit_Command85_Click
        
    End Sub
    Well thats it just a straight forward put a button here jobby

Posting Permissions

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