Results 1 to 4 of 4
  1. #1
    Join Date
    May 2014
    Posts
    16

    Unanswered: SQL Select problem

    I have an Access database that has the following table defined:

    NotesID AutoNumber
    Datex Date/Time (with the format set at Short Date 5/5/2014)
    Timex Date/Time (with the format set at Short Time 17:35)
    Contact Text (field size 50)
    Reference Text (field size 50)
    Type Text (field size 50)
    Notes Memo

    The tables looks like this ...

    1 5/5/2014 12:00 Doe, John SharePoint Note This is a note
    2 5/16/2014 13:50 Slick, Mary SQL Note This is a note also.


    How do I code in VBA a select statement that will read a specific record? I know the Date, Time and Contact. Any help will be appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    Const c_SQL As String = "SELECT * FROM TableName " & _
                            "WHERE Datex = #@D# AND Timex = #@T# AND Contact = '@C';"
    
    Dim rst As DAO.Recordset
    Dim strSQL As string 
    
    strSQL = Replace(Replace(Replace(c_SQL, "@D", Format(SomeDate, "yyyy-mm-dd"), & _
                                            "@T", Format(SomeTime, "hh:nn"), & _
                                            "@C", SomeContact)
    Set rst = Currentdb.OpenRecordset(strSQL, dbOpenSnapshot)
    If rst.EOF = True Then
        ' --> no record found.
    Else
        ' You can retrieve the data in rst!NotesID, rst!Datex, etc.
    End If
    rst.Close
    Set rst = Nothing
    Where SomeDate, SomeTime and SomeContact are the known values for the row you're looking for.
    Have a nice day!

  3. #3
    Join Date
    May 2014
    Posts
    16

    SQL Select Problem

    The

    strSQL = Replace(Replace(Replace(c_SQL, "@D", Format(SomeDate, "yyyy-mm-dd"), & _
    "@T", Format(SomeTime, "hh:nn"), & _
    "@C", SomeContact)

    has a compiler error in it and I cannot figure t out. Any suggestions?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Oops! Sorry, it should be:
    Code:
    strSQL = Replace(Replace(Replace(c_SQL, "@D", Format(SomeDate, "yyyy-mm-dd")), _
                                            "@T", Format(SomeTime, "hh:nn")), _
                                            "@C", SomeContact)
    Have a nice day!

Tags for this Thread

Posting Permissions

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