Results 1 to 13 of 13

Thread: Optimize Code..

  1. #1
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Unanswered: Optimize Code..

    A regurgitation of my previous problem, slightly modified:
    Has a function that outputs a number based on a date. It converts the date to a number so i can sort by the number.

    Ask why i do this long winded way? I tried outputting the date as string, then formatting it to a date, and it didn't sort before. Tried outputting it as a date and it still ddin't sort. So in exasperation, decided to convert date to a number, and then sort.

    However, the code takes too long to work in a report which has around 200 records displaying, calling the function 200 * around 3 times...
    It takes around 37secs to open.

    Set conn = CurrentProject.Connection
    Set rec = New ADODB.Recordset

    rec.Open "....Some SQL query which finds the date from a field.", conn, adOpenStatic, adLockOptimistic
    Dim dat As String
    If IsNull(rec(1).value) Then
    dat = "01/01/1001"
    Else
    dat = rec(1).value
    End If
    DueDate = Int(Mid(dat, 1, 2)) + Int(Mid(dat, 4, 2)) * 30 + Int(Mid(dat, 7, 4)) * 12 * 30


    i Think its the Int(..) part thats taking too much time...

    EDIT******
    I tried removing the Int(...) part; obviously it didn't sort right but it took significantly less time.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Why don't just storie the date as a date/time in the feild type
    this is a number and it get sorted right

    Plus storing it as a date/time you can do heap more to that value

    sort it by just the day
    or just by the Month
    or just by the Year
    or event by the weekday
    or what evey date thing you want
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    i did. The field that the SQL query is digging up is a date/time field.
    I tried making the function return a date/time, but it didn't sort!

  4. #4
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    can you post your sql query?

    Originally posted by Jerrie
    i did. The field that the SQL query is digging up is a date/time field.
    I tried making the function return a date/time, but it didn't sort!

  5. #5
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    SELECT [IDS LIST].[Identification Number], Critical_Deadline_Table.Due_Date FROM [IDS LIST] INNER JOIN Critical_Deadline_Table ON [IDS LIST].[Identification Number] = Critical_Deadline_Table.IDS_Link_ID where ((([IDS LIST].[Identification Number]) = " & ID & "))ORDER BY Critical_Deadline_Table.Due_Date;

  6. #6
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    since you are using the sql in code you can do it this way too. Try it and see if that works.

    SELECT [Identification Number], Due_Date
    FROM [IDS LIST], Critical_Deadline_Table
    WHERE [Identification Number] = IDS_Link_ID
    AND [Identification Number] = " & ID &
    ORDER BY Due_Date;

    correct any typos if there are any..

    Originally posted by Jerrie
    SELECT [IDS LIST].[Identification Number], Critical_Deadline_Table.Due_Date FROM [IDS LIST] INNER JOIN Critical_Deadline_Table ON [IDS LIST].[Identification Number] = Critical_Deadline_Table.IDS_Link_ID where ((([IDS LIST].[Identification Number]) = " & ID & "))ORDER BY Critical_Deadline_Table.Due_Date;

  7. #7
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    nope same performance...i think its the int()....

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Jerrie
    nope same performance...i think its the int()....
    Why not use the built in date functions?

    The way vb applies these functions should be significantly less taxing

    Dim dat As DateTime
    ...
    DueDate = month(dat)+ day(dat) * 30+ year(dat) * 12 * 30

    Or if you want to keep the date format:
    DueDate = str(month(dat)) &"/"& str(day(dat) * 30) &"/"& str(year(dat) * 12 * 30)
    Last edited by Teddy; 04-08-04 at 12:55.

  9. #9
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    will try that...that look promising!

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    A's dates are doubles.
    are u certain you need conversions?

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    but wat bout the "/"?
    is that just an addition on top of the double?
    how is the date represented as a double? (total days? or just ddmmyy)?

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    A's dates are days.fractionaldays since 1/1/100

    all the "/" fluff is from formatting - it is not stored in the data.

    try

    dim myDate as date
    myDate=now()
    myDate=myDate - 7

    ...myDate will show the date of one week ago (in a box expecting a date) following all the long/short date formats etc ...but it is just a double.

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    coo..will try
    thx ppl!

Posting Permissions

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