Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    8

    Unanswered: Extra Character in Field - How to Compare? (LIKE Function)

    OK, so I've been at this for about 2 hours now and I have no idea how to get it working, so I beg here for help...

    I've got two tables of phone call details (TableA, TableB).

    TableA has a field called CONNECT_TIME (which indicates the time of a call). It is a numeric field with the following format "hhmmsst" (with the hour field out of 24, so 11pm is 23 and the leading hour is not shown if zero, so 8am would start "8" and not "08").

    TableB has a field called Call_Time (which indicates the time of a call). It is a numeric field with the following format "hhmmss" (with the same hour characteristics as described before).

    I would like to match records between the two tables, but the CONNECT_TIME field has an additional character (for the tenths of a second) so I can't figure out how to compare them. I've tried using the LIKE function but don't know what I'm doing wrong. This is what I've tried:

    Code:
    SELECT TableB.*
    FROM TableB, TableA
    WHERE ((TableB.Call_Time) Like [TableA].[CONNECT_TIME] & "*");
    I've also tried a few variants of it, but just have no idea how to get the query to work.

    Example:
    TableA.CONNECT_TIME = "904089" (9:04:08.9 AM)
    TableB.Call_Time = "90408" (9:04:08 AM)
    I need the two values to match, but the tenths is not allowing me to do a direct match.

    Thanks in advance for any help!!

    ~ Ketan aka DaCurryman

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Code:
    Public Function CONNECT_TIME(ThisValue As String) As String
    Dim TempValue As String
    TempValue = Mid(ThisValue, 1, Len(ThisValue) - 1)
    If Len(TempValue) <> 6 Then
    TempValue = "0" & TempValue
    End If
    CONNECT_TIME = TempValue
    End Function
    
    
    Public Function Call_Time(ThisValue As String) As String
    Dim TempValue As String
    If Len(ThisValue) <> 6 Then
    TempValue = "0" & TempValue
    End If
    Call_Time = TempValue
    End Function
    I would Use the above function

    in 2 querys

    Join:CONNECT_TIME([CONNECT_TIME])

    then other query

    Join: Call_Time([Call_Time])


    Create a new querys With the Two above querys and Join then Job Done

    Only This That I can see happing is it could slowwwwwwwwwwwwwwww down

    so you could turn the 1st 2 qurey into make tables then the 3rd query would read the new tables

    Just put them into macro running them in the right order
    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
    Sep 2006
    Posts
    8
    Thanks myle,

    But I need some more help. I added the functions you indicated using the VB Editor. But I don't know how to call the procedure for a specific table. I'm not a total n00b, but enough as to where I don't use VB when using Access. Thanks again.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Your criteria are the wrong way round in your where clause (CONNECT_TIME would be like Call_Time & "*").

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Quote Originally Posted by DaCurryman
    Thanks myle,

    But I need some more help. I added the functions you indicated using the VB Editor. But I don't know how to call the procedure for a specific table. I'm not a total n00b, but enough as to where I don't use VB when using Access. Thanks again.

    Copy and paste code in module then create the querys and put this in a new coloum

    Join:CONNECT_TIME([CONNECT_TIME])

    Join = the feild name

    CONNECT_TIME msaccess will find this function
    and put the feildname value as give a reply
    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.

Posting Permissions

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