DateTime conversion

[megatron7]

I am getting a data type conversion error. WHY?

strFld is already of data type DateTime.

strFld = "MEMBERCALLEDDTE"

Set rst = db2.OpenRecordset("tblUNION_UserCallResults")

Do While Not rst.EOF
rst.Edit
rst(strFld) = FormatDateTime(rst(strFld), vbShortDate)
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

 

[Eupher]

megatron7,

Looks like you are trying to assign a string value to a date variable:

[COLOR=blue]strFld[/color] = [COLOR=red]"MEMBERCALLEDDTE"[/color]

HTH,

Ken S.

 

[megatron7]

Well, I guess I'm doing this incorrectly then. strFld was suppose to be the field name in my table. I wanted to convert everything in this field to short date. How would I go about doing that?

Thanks,

 

[adalger]

FormatDateTime returns a string, not a date. Try Format() instead.

 

[megatron7]

i get the same data type error message.

 

[Eupher]

Oops, my mistake, I misunderstood how you were using the variable. So in your table, you have a field called "MEMBERCALLEDDTE" and it's already defined as a date/time data type, and already holds date/time data? If so, there's no conversion necessary - or possible, in terms of converting the data to short date. The date/time data type is a 64-bit number, with the digits to the left of the decimal representing the date, and those to the right are the time - but I think it's uncommon to see this number in its native format. How the number is displayed is governed by the format property of the field, which can be set in the table's design view - or in the format of any form or report control that displays the data.

HTH,

Ken S.

 

[Eupher]

p.s. The CDate() function can be used to coerce an expression to the date/time data type. However, this has no effect on how the data is displayed, which is still controlled by the field's format property.

Ken S.

 

[megatron7]

Now I'm getting a mismatch error. Well, the reason for me converting the field to ShortDate was because my query didn't return what I was asking. I am grabbing dates from multiple tables each table's datetime format is different. When I query for today's date, it only returns the dates with the short date format. All the other dates with the time are left out. That's the reason I want to format the field. If there is another way, please let me know.

this is my code for the query

Function BuildSQLStatement(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strORDERBY As String
Dim strGROUPBY As String

txtReportName = "Out Bound Calls"
strReportName = "Calls"

strSELECT = "A.Call_Status_Desc, Sum(A.CountOfCall_Status_Desc) AS SumOfCountOfCall_Status_Desc, A.USER_ID, " & _
"A.USER_FN, A.USER_LN, A.DB_TABLE "

strFROM = "qdfCountUserCallStatus AS A "


strFROM = strFROM & "GROUP BY A.Call_Status_Desc, " & _
"A.USER_ID, A.USER_FN, " & _
"A.USER_LN, A.DB_TABLE, A.MEMBERCALLEDDTE, " & _
"HAVING (((A.MEMBERCALLEDDTE)>=#" & cboStartDate & _
"# And (A.MEMBERCALLEDDTE)<=#" & cboEndDate & "#));"

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM

If strWHERE <> "" Then
strSQL = strSQL & "WHERE " & strWHERE

BuildSQLStatement = True

End Function

 

[Eupher]

megatron7,

Instead of this:

"HAVING (((A.MEMBERCALLEDDTE)>=#" & cboStartDate & _
"# And (A.MEMBERCALLEDDTE)<=#" & cboEndDate & "#));"

How about this?

"HAVING ((([COLOR=red]Format(A.MEMBERCALLEDDTE, 'Short Date')[/color])>=#" & cboStartDate & _
"# And ([COLOR=red]Format(A.MEMBERCALLEDDTE, 'Short Date')[/color])<=#" & cboEndDate & "#));"

HTH,

Ken S.