Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    27

    Unanswered: newbie need help

    I need to know how to write a sql statement for VB to Microsoft Access for the following criteria.



    TblEmployees only Field to be concerned with is EmployeeID -Its PK
    TblEmpAttendane has these fields:
    EntryID - Pk
    EmployeeID - Fk
    Date- A text form date validated through vb to avoid hassle
    Value-A single digit value that can be alpha OR numeric

    To get the Date range for the current month I use variables to store the First and last day of the month(in a CUSTOM calendar control).

    I have been trying to use this Sql statement(that wont work):
    SqlString = "SELECT * FROM TblEmpAttendance WHERE Date BETWEEN '" & FirstDay & "' AND '" & LastDay & "'"


    I need to:
    1.)using the current EmployeeID (from TblEmployees)
    2.) find the same EmployeeID in TblEmpAttendance
    3.)Get the date range for the current month and year(valid days currnt mo.)
    -these date are supplied by the variables "FirstDay" AND "LastDay" as seen in above SqlString

    4.) Find the Values associated with the dates and EmployeeID

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you using Jet or MS-SQL (aka MSDE) as your database engine? They have differences in how they handle dates, and the example you gave would not fly very well in Jet.

    -PatP

  3. #3
    Join Date
    May 2004
    Posts
    27
    Im using jet to connect(through code) to my db.
    Can u assist me in how to improve my table format..or whatever I would have to do in order to be able to do this correctly?

    All that I did was set up a string format in vb to make sure that the correct amount of characters and the proper syntax was used:
    eg.... 00/00/0000
    In my code I nvr explicity refer to them as a date. Only the user would think that it was a date!


    Thanks-Greg S

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd try using:
    Code:
    SqlString = "SELECT * FROM TblEmpAttendance WHERE #" _
    & FirstDay & "# <= Date AND Date <= #" & LastDay & "#"
    This is only a swag, but I think that it should work.

    -PatP

  5. #5
    Join Date
    May 2004
    Posts
    27
    Thanks I will try that and get back to you. Much appreciated!

  6. #6
    Join Date
    May 2004
    Posts
    27
    I have a lot more to do than I thought. I did do a date conversion function from within access so it is only displayed as dd/mm/yy format.
    Then from vb I used:
    SqlString = "SELECT * FROM TblEmpAttendance WHERE Date Bewteen '" & FirstDay & "' AND '" & LastDay & "'"

    It seemd to work fine as I can refer to a field but for some reason when I refer to the recordcount property,I always get -1...Seems weird how I can read the info but not get the recordcount!!

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Check the BOL. If you use ADOpenStatic you won't have -1 as a recordcount.

    -PatP

  8. #8
    Join Date
    May 2004
    Posts
    27
    I dont know what you mean by "BOL" but as I am using ADO as a connection it does support bookmarks(not that I believe I need on with the move.bof statement) If this is not what you were getting at could you be more specific?

    OK WAIT..I added adOpenStatic to my statement!
    now it seems to be working
    But I had to put in in the form of:
    AdoRecordset.Open SqlString, AdoConnection, adOpenStatic

    instead of AdoRecordset.Open (SqlString, AdoConnection), adOpenStatic
    like the link you posted...Im still not sure why the perverbial correct way wouldnt work but my way worked anyhow..
    THANKS MUCH!
    Last edited by greg85374; 07-25-04 at 13:52.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    adOpenStatic, adOpenDynamic, adOpenKeyset, and adOpenForwardOnly are qualifications of the cursor that is being open either on the server or client side (depends on the CursorLocation property). The default (adOpenForwardOnly) will have -1 for RecordCount property.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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