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

    Unanswered: Getting dates problem

    I am Getting dates from access 2003 table called TblEmpAttendance using a sql string. I havent the slightest clue as to where to get started.

    Basically in my VB program(vb6 not vb.net),I have a custom made calendar control...set up just like microsoft calendar control. I also have an Access 2003 DB where one of my tables(TblEmpAttendance). This tbl has 3 Fields.
    EmployeeID(Foreign key), Value, And Date. I need to loop though a dte for ANY given month and load the values into my custom calendar.

    I set up my calendar so that even though, Like Microsofts calendar, I have 42 boxes to hold dates--My calendar only had a max of 31 visible at any time--31 cuz thats the maximum days in a month. Now for each Box I have 1 label, and one textbox. I refer to them as LblDate(i) And TxtDate(i) as theyre both part of an array.

    So the LblDate(i).caption is the same as the VALID day numbers for the days of whatever month the user is working in. The first Visible LblDate(i) is "1" or LblDate(1) ..and the same for the text boxes. FOR example if theyre working in the current month and year-July 04--I would have 31 txtDate(i).text ANd 31 LblDate(i).text..where "i" is the day of the month.

    So I am (From Inside VB) using:
    Private Sub LoadValues()
    'Exit Sub 'not yet tested
    Dim SQLString, SomeDay As String
    Dim I As Integer

    For I = 1 To 31 Step 1
    SomeDay = MyMonth & "/" & I & "/" & CboYear.Text
    If IsDate(SomeDay) Then
    SQLString = "Select * From TblEmpAttendance where Date =" & SomeDay
    TxtDate(I).Text = SQLString
    End If
    Next I

    End Sub

    But all it does is fill each textbox in my calendar with my SqlString--
    "Select * From TblEmpAttendance where Date =7/18/2004"

    -NOTE I COPIED THAT OUT OF TXTDATE(18).TEXT..the 18th day of the month!

    I need to know how I execute a SQL statement form VB that will work(As it usually does) with an Access 200 or better Database!

    Thanks in advance!

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Your in the wrong forum for this question, you should check outthe vb one in the data mannipulation area,... however.... something like this in your loop

    Code:
    Set myConn = ADODB.Connection
    myConn.Open "myConnectionString"
    Set myDates as myConn.Execute(SQLString)
    if not(myDates.eof) then
      DateText = ""
      do while not myDates.eof
        DateText = DateText & vbCrLf & myDates("Value") 
      loop
      TxtDate(I).Text = DateText
    end if
    Set myDates = nothing
    Set myConn = nothing
    Hope that helps....

  3. #3
    Join Date
    May 2004
    Posts
    27
    Set myDates = myConn.Execute(SQLString) is an invalid command
    Im using vb6 not vb.net..is that .net code-it seemd to be the recurring answer i get?

    Thanks anyhow

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    rokslide is correct. If you are looking for advice about getting dates, you have come to the wrong place. We mostly spend our weekends looking through online manuals or writing obscure program code.

    Try here instead, and good luck:

    http://www.matchmaker.com/
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    it's vb code. it should work (I type it wrong in my example though as should be =

    probably need MDAC 2.8 for the execute method.....

    Anyhow, try this...

    Code:
    Set myConn = ADODB.Connection
    Set myDates = ADODB.Recordset
    myConn.Open "myConnectionString"
    myDates.Connection = myConn
    myDates.Open SQLString
    if not(myDates.eof) then
      DateText = ""
      do while not myDates.eof
        DateText = DateText & vbCrLf & myDates("Value") 
      loop
      TxtDate(I).Text = DateText
    end if
    Set myDates = nothing
    Set myConn = nothing

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    Dim con As Object, rs As Object, SQLString, MyMonth As String
    Dim DStart As String, DEnd As String, i As Integer
    MyMonth = "01"
    DStart = MyMonth & "/01/" & CboYear.Text
    DEnd = CStr(DateAdd("d", -1, DateAdd("m", 1, CDate(DStart))))
    SQLString = "Select [Date] From TblEmpAttendance where [Date] between '" & DStart & "' and '" & DEnd & "' order by [Date]"
    Set con = CreateObject("adodb.connection")
    con.open "provider=sqloledb;data source=YOUR_SERVER_NAME;initial catalog=YOUR_DATABASE_NAME;integrated security=sspi"
    Set rs = con.execute(SQLString)
    i = 1
    While Not rs.EOF
    	 TxtDate(i).Text = rs!Date
    	 rs.movenext
    	 i = i + 1
    Wend
    rs.Close
    Set rs = Nothing
    con.Close		 'DO THIS ONLY IF YOU DON"T WANT TO REUSE THE CONNECTION OBJECT
    Set con = Nothing '---------||-----------||------------||-----------------------
    "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
  •