Format Date/Time

Hi all, I am using MS Access with VB. In my db table, there is a Date field, and data type is Date/Time (there is no other choice in Access). So the date in the Date field will be
'10/7/2003 1:25:26' ... and so on. Now, I need to retrieve the date with just the Date and not time --> '10/7/2003' and display it in my Excel. I need to categorized according to Month. How can I select the Date ONLY from the Date field where the data type is Date/Time?? Any idea? I did use split function to split the date and time. But, when SELECT DATE from table ... the DATE still take it as Date/Time ... HELP!!!! ;(

what about using format$ (rsrecordset("FieldName"), "mm" & "/" & "dd" & "/" & "yyyy")

Hi FM ... the statement you gave ... where should I put it?

getReport = "SELECT format$(rs.fields("date"), "mm" & "/" & "dd" & "/" & "yyyy") FROM REPORT"

it doens't work this way

My code:
==============
....

' The first query is to get the Date/Time value from db, I actually want to get the Month value, then using each Month value to loop in rs2.

getReport = "SELECT DATE FROM REPORT"
Set rs = cn.Execute(getReport)

' Then, I try to use the value to split it to find the Date only. And it success

Dim fStr() As String

fStr = Split(rs.Fields("date"), " ")

msgbox "Date is " & fstr(0)


Do While Not rs.EOF

Dim rs2 As New ADODB.Recordset
Dim getReport2 As String

'in this part, I realized the DATE value still Date/Time, not just Date

getReport2 = "SELECT * FROM REPORT WHERE DATE='" & fstr(0) &'" "
Set rs2 = cn.Execute(getReport2)

......
Loop

So... let me get this straight. You're pulling the date value from the database, then pulling the date apart and storing the individual pieces in an array using the split function, then using the first value in that array to try and find records within a particular month? I don't know if this would work, but you might try changing the "=" to a "LIKE '" & fstr(0) & "%'"

Hi FM ... Yes, that's basically what I am trying to achieve. And ... 'LIKE' doesn't work too Is there any other way round to get the Date then compare in other query?

well, if you could determine what month you were going to need, then you could use the BETWEEN keyword in SQL and pass the month start and end?...

Select * from REPORT Where DATE BETWEEN '" & dtStartDate & "' AND '" & dtEndDate & "'"

Hi FM ... I have tried so many ways ... but it dun seems to work. I am try to get data which within 6 months starting current month. (eg. get data from May to October) ... However, if I use the query "getData = "SELECT * FROM REPORT WHERE (date() BETWEEN '" & Format(dtStartDate, "short date") & "' AND '" & Format(dtEndDate, "short date") & "')" ... it will then print out ALL the data from the db and not within 6 months The following is my code, kindly take a look. Hope to hear any suggestion from you. Thank you very much.

============================
Public Sub getExcelByMonth()

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

Dim ctm As Date

Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=" & App.Path & "\account_db.mdb"

ctm = Date

MsgBox "Date now is " & ctm

Dim getS() As String

getS = Split(ctm, "/")

MsgBox "Current month is " & getS(0)

Dim findFirstMonth As Integer

findFirstMonth = getS(0) - 6 + 1

Dim findYear As Integer

If findFirstMonth = 0 Then
findFirstMonth = 12
findYear = getS(2) - 1

ElseIf findFirstMonth < 0 Then

findFirstMonth = 12 + findFirstMonth
findYear = getS(2) - 1

End If

Dim getData As String
Dim dtStartDate As Date
Dim dtEndDate As Date

If findFirstMonth < 0 Or findFirstMonth = 0 Then

MsgBox " First date is " & findFirstMonth & "/" & "1" & "/" & findYear
dtStartDate = " '" & findFirstMonth & "' & " / " & '" & firstDay & "' & " / " & '" & findYear & "'"

Else

MsgBox " First date is " & findFirstMonth & "/" & "1" & "/" & getS(2)
dtStartDate = findFirstMonth & "/" & "1" & "/" & getS(2)
End If

dtEndDate = getS(0) & "/" & "1" & "/" & getS(2)

getData = "SELECT * FROM REPORT WHERE (date() BETWEEN '" & Format(dtStartDate, "short date") & "' AND '" & Format(dtEndDate, "short date") & "')"
Set rs = cn.Execute(getData)

Dim getDate As String

Do While Not rs.EOF
getDate = Format(rs.Fields("date"), "Short Date")
'MsgBox rs.Fields("date")
MsgBox " Selected date in 6 months = " & getDate
rs.MoveNext
Loop

cn.Close
Set cn = Nothing

End Sub

well, I have never used the format function like that. I would try dtstartdate = FormatDateTime(DateAdd("m", -6, Now), vbShortDate)
dtenddate = formatdatetime (Now, vbshortdate)


sorry it took me so long.. I've been on a bit of a personal hiatus to get some coding done for my own benefit

Thanks FM ... it works from your suggestion Really appreciate that

si Senor!