Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Dec 2004
    Posts
    41

    Question Unanswered: date problem (my last problem) :)

    Hey Guys. Listen to this weird problem.

    I have a form, which takes a base date recieved by a user, which is always
    a beginning of a month, let's say : 01/10/2004. That's the format, days, months, years.

    After getting the first date, i'm calculating one month ahead, because this form should display entries from one month only, so I do it like this:

    Code:
        dDate = Format(Me.date.Value, "dd/mm/yyyy")
        Gettin = DateAdd("m", 1, dDate)
        ToDate = DateAdd("y", -1, Gettin)
    
        ToDateFinal = Format(ToDate, "mm/dd/yyyy")
        sDateFinal = Format(Me.date.Value, "mm/d/yyyy")
    Then I get two great dates:
    01/10/2004
    AND
    31/10/2004

    Working Great!

    But, when the query is running, I look in my report and I see it always getting the wrong rows, so I thought i'll check it out by going and having alook at the query.

    I take a look at the design view, and I see great true working stuff:
    Code:
    Between #01/10/2004# And #31/10/2004#
    This is correct, and should get me the rows I want, but if so, why isn't it working?

    I move to SQL view, and I see the problem, in the sql view, it looks like this:
    Code:
    Between #10/1/2004# And #10/31/2004#
    You don't have to be a pro to understand where is the problem.
    I even tried playing with the date format in the operation system (win xp) and everything is just fine, all the formats are correct, plus as you can see, the query and the dates are just fine, but for some reason, he takes the first date and swapps it and ruining my entire code, and I find myself getting rows from january instead of October only.

    I guess this is some kind of a bug or something, but anyway, I need to solve it.

    Any ideas?
    That's my last problem in this database, you helped a lot by now and I owe much to you, THANKS!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    look here

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2004
    Posts
    41
    First thing - Thanks man.

    I followed the instructions and for some reason when i use the function, i get a null value.

    how should it be? something like:

    Code:
        b_ToDateFinal = Format(ToDate, "mm/dd/yyyy")
        b_sDateFinal = Format(Me.date.Value, "mm/d/yyyy")
       
        ToDateFinal = MakeDate(b_ToDateFinal)
        sDateFinal = MakeDate(b_sDateFinal)

  4. #4
    Join Date
    Dec 2004
    Posts
    41
    Oh, I see, it should be used INSIDE the query?

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i'm still delicate after the champagne last night, so i can only offer hints.

    try to get your dates into date datatype

    Dim someDate as Date
    someDate = dateserial(year, month, date)

    someDate is now a Date

    someDate can now display in a textbox etc etc using your windoze regional settings date format:users in another region will see the date displayed as they prefer.

    somedate is now a date (not text) so you can feed it to the www.msdn function MakeUSDate(somedate)
    use MakeUSDate(somedate) in a query.

    alternative approach: persuade USA to adopt the more-logical dd/mm/yyyy date format that the rest of the world manages to survive with

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    crossed posts.

    anyhow - it's a well known prob. so post again if stuck - lots of folk here can help you out.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2004
    Posts
    41
    Jessus, I tried everything and I keep getting 10/1/2004....

    Thank you very much for your help, I have no idea what the hell is wrong here...

  8. #8
    Join Date
    Sep 2004
    Posts
    36

    Maby

    Try this...

    Create a modul...

    Past this code into it....

    Code:
    Function MakeUSDate(x As Variant) 
    If Not IsDate(x) Then
    Exit Function 
    MakeUSDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#" 
    End Function
    Now you just use

    MakeUSDate(DateField) where ever you want it... query function where ever..

    If i misunderstud the question send me a copy of you db (with dummy data)
    and I'll se what I can do..

    EX for query with between:

    Between MakeUSDate([Forms]![frmUsers]![txtFromDate]) and MakeUSDate([Forms]![frmUsers]![txtToDate])
    Last edited by theJedi; 01-01-05 at 14:46.

  9. #9
    Join Date
    Dec 2004
    Posts
    41
    It will all be alright if it wouldn't trim my zero's...

    it's going 10/1/2004

    and it should be 01/10/2004

    I tried everything you said and nothing works..

    by the way, i need days/months/years, not the american way of months/days

  10. #10
    Join Date
    Sep 2004
    Posts
    36

    post?

    Please zip it down and add it.. and I'll have a look at it strait away...

    Where are you using this code? Form Query Module??

  11. #11
    Join Date
    Dec 2004
    Posts
    41
    I bet that could be a great idea but the database is not in english, it's hebrew, and it will be very difficult for me to start removing classified data etc... It has lot's of data which i'm not allowed to reveal.

    but I can show you the code:
    By the way, now I changed to the date formats to mm/dd/yyyy to see if it will solve the problem, and it didn't.

    Code:
        ToDateFinal = Format(ToDate, "mm/dd/yyyy")
        sDateFinal = Format(Me.date.Value, "mm/dd/yyyy")
       
        strSQL = "SELECT Madrichim.hug_one, Madrichim.hug_two, Madrichim.hug_three, * FROM MadrichimEvents INNER JOIN Madrichim ON MadrichimEvents.which_madrich = Madrichim.name"
        strIN = strIN & "'" & which_madrich.Value & "',"
        strWhere = " WHERE MadrichimEvents.date_one Between " & MakeDate(sDateFinal) & " And " & MakeDate(ToDateFinal) & " OR MadrichimEvents.date_two Between " & MakeDate(sDateFinal) & " And " & MakeDate(ToDateFinal) & " OR MadrichimEvents.date_three Between " & MakeDate(sDateFinal) & " And " & MakeDate(ToDateFinal) & ""
    the MakeDate command is this:

    Code:
    Function MakeDate(x As Variant)
      If Not IsDate(x) Then Exit Function
      MakeDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"
    End Function
    And though all that, I'm still getting stuff like
    Between #10/01/2004# And #31/10/2004#

  12. #12
    Join Date
    Sep 2004
    Posts
    36
    Code:
          
    ToDateFinal = Format(ToDate, "mm/dd/yyyy")      
    sDateFinal = Format(Me.date.Value, "mm/dd/yyyy")           
    strSQL = "SELECT Madrichim.hug_one, Madrichim.hug_two, Madrichim.hug_three, * FROM MadrichimEvents INNER JOIN Madrichim ON MadrichimEvents.which_madrich = Madrichim.name"
    strIN = strIN & "'" & which_madrich.Value & "',"
    strWhere = " WHERE MadrichimEvents.date_one Between " & MakeDate(sDateFinal) & " And " & MakeDate(ToDateFinal) & " OR MadrichimEvents.date_two Between " & MakeDate(sDateFinal) & " And " & MakeDate(ToDateFinal) & " OR MadrichimEvents.date_three Between " & MakeDate(sDateFinal) & " And " & MakeDate(ToDateFinal) & ""
    This can not be the intire code.. Is it german?...Madrichim is a new word for me

    have you tried...

    where startdate#" & fromdate & "#
    where fromdate format is 01.mm.yyyy

    guessing the date field is yyyy ....
    and are you using db.execute or?...

    best if you take the complete code and send on pm... easer if I see the whole thing....

  13. #13
    Join Date
    Dec 2004
    Posts
    41
    Do you have any online messenger? msn, icq?

    I'm off to bed soon but send me your info and I shall contact you tomorrow so we can solve this out.

    By the way, it's hebrew

  14. #14
    Join Date
    Sep 2004
    Posts
    36

    msn

    you got it on pm!!

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    don't mess around with format() -- leave me.date.final and ToDate in your own regional settings format and let the function take the strain during the concatenation

    in a "global" module:

    Public Function MakeUSDate(x As Variant) as string
    dim strTemp as string
    If Not IsDate(x) Then
    msgbox "The input value [" & x & "] is not recognised as a date!", vbexclamation, "Error in MakeUSdate()"
    Exit Function
    endif
    strTemp = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"
    msgbox "The input value [" & x & "] has been converted to: [" & strTemp & "]", vbinformation, "Check it!"
    MakeUSdate = strTemp
    End Function


    and in your SQL concatenation:

    strWHERE = " WHERE ((MadrichimEvents.date_one Between " & MakeUSdate(Me.Date.Final) " And " & MakeUSdate(ToDate))
    strWHERE = strWHERE & " OR (MadrichimEvents.date_two Between " & MakeUSdate(Me.Date.Final) & " And " & MakeUSdate(ToDate))"
    strWHERE = strWHERE & " OR (MadrichimEvents.date_three Between " & MakeUSdate(Me.Date.Final) & " And " & MakeUSdate(ToDate)))"


    ...and kill all the extra stuff in makeusdate() after it works for you.



    ??

    izy
    currently using SS 2008R2

Posting Permissions

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