How to solve date in openrecordset

[LeNenne]

Hi

I am going to solve a problem and have now idea of how,

I will that in the list1 only those people that correspond to
the date of the date is showing.

No one else than that one of the day

the word Prn is standing for birthday in format 50-04-21
in the database.

Lenny

Private Sub Birthday()

Dim myDay As String

myDay = Format$("yy-mm-dd")

Set rs = db.OpenRecordset("Phone", dbOpenTable)



Set rs = db.OpenRecordset("SELECT * FROM Phone WHERE prn LIKE '" & "'" & "& '*'") '& myDay)

 List1

End Sub

Private Function List1()
Dim myDay As String

myDay = Format("now, yy-mm-dd")

If rs.RecordCount = 0 Then

       Exit Function

End If
     rs.MoveLast
     rs.MoveFirst
     max = rs.RecordCount
     rs.MoveFirst
     LstData.Clear
    For i = 1 To max
       'LstData1.AddItem rs("prn") & " " & rs("prn")
    LstData1.AddItem rs("eftn") & " " & rs("prn") '& myDay

    rs.MoveNext
    Next i

    CmdOk.Enabled = True
    Label2.Visible = True
End Function

[rishif2]

a few things i wanna make clear from you

why using the following :-
myDay = Format$("yy-mm-dd")

why using Format function in following manner(it will not produce what you think)

myDay = Format("now, yy-mm-dd")
here you should write As myDay = Format(Now,"yy-mm-dd")

why opening recordset which is alredy opend (check you line 7 and line 11)

i really dont understand what you are gonna do with following query(which does not produce what you think).

"SELECT * FROM Phone WHERE prn LIKE '" & "'" & "& '*'"
if you are determined about the date then there is no need to use Like %

at last why changing the position of recordset

rs.MoveLast
rs.MoveFirst
max = rs.RecordCount
rs.MoveFirst

at last dont forget to close your connection and recordsets.
for getting perfect solution , you need to put your problem in more clear way

[LeNenne]

Thanks for the words.

I try to tell it more.

I have a phone book with serveral people in it
and there is a txtbox(txtprn) with stands for the birthday.
There is name, adress, zipcode, email and phonenumber txtboxes
I will in the list1 have just those peoples that correspond to the day
of the pc day in it, So I can write some cards for there birthday and a reminder.

What I am trying to say is that the date of the year is correspond to the birthday of a person in list1

Lenny

[LeNenne]

Hi I think the problem is myDay expresion.

how to extract from the database in the background and then
let myday get the right birthday in the database it stands year - month- day (50-04-21)
and that is not the day of the pc the computers day is yy - mm - dd ( 13-04-21 )
I will extract just mm-dd ( 04-21 from the database and show it in the list1box.

how to solve that please help

Lenny

[tinstaafl]

To extract just the month and day, with the month in long format you could use something like this:

Dim mydate As Date
mydate = Now
Dim datestring As String
datestring = Format(mydate, "mmmm d")

Today, datestring would be "April 21".

To keep the month short use,datestring = Format(mydate, "mm d"). To get "04 21".

[LeNenne]

and how shall implent it in the code above

   * Private Sub Birthday()*
    Dim myDay As String
    myDay = Format$("mm-dd")
    Set rs = db.OpenRecordset("Phone", dbOpenTable)
    Set rs = db.OpenRecordset("SELECT * FROM Phone WHERE prn LIKE '" & "'" & "& '*'") '& myDay)
    List1
    End Sub


    *Private Function List1()*
    Dim myDay As String
    myDay = Format("now, mm-dd")
    If rs.RecordCount = 0 Then
    Exit Function
    End If
    rs.MoveLast
    rs.MoveFirst
    max = rs.RecordCount
    rs.MoveFirst
    LstData.Clear
    For i = 1 To max
    'LstData1.AddItem rs("prn") & " " & rs("prn")
    LstData1.AddItem rs("eftn") & " " & rs("prn") '& myDay
    rs.MoveNext
    Next i
    CmdOk.Enabled = True
    Label2.Visible = True
    End Function

[tinstaafl]

 myDay = Format(Now, "mm-dd")

[rishif2]

Well i dont know what database connectivity you are using .but this may work as a hint for you
i used here ADODB connectivity

rs.Open "select * from date2 where date2", cn, 2, 3
While Not rs.EOF
dt = Format(rs.Fields(0), "mm-dd")
sysdt = Format(Now, "mm-dd")
If dt = sysdt Then
List1.AddItem rs.Fields(0) & " ---" & rs.Fields(1)
End If
rs.MoveNext
Wend
rs.Close

Now i assume that you can adjust above code with your database connectivity
hope this helps you to solve the issue . . .

[LeNenne]

Thanks that solve it