Please help with type mismatch error.

[Al]

I have an access (2003) database with a sql backend. I am having problem with
an error message "Type mismatch". The code break on the following line:

dteLoginDate = DLookup("DateLogged", "tbluserlog", "LogID=" & intLogID & "")
& " " & _
DLookup("TimeLogged", "tbluserlog", "LogID=" & intLogID & "")

dteLoginDate is identified as a date (dim dteLoginDate as Date). However,
when I debug, I noticed that the value returned by the Dlookup to
dteLoginDate is (12:00:00 AM) not a date. I tried the following:

dteLoginDate = Format(DLookup("DateLogged", "tbluserlog", "LogID=" &
intLogID & "") & " " & _
DLookup("TimeLogged", "tbluserlog", "LogID=" & intLogID & ""),
"Short Date")

Still the same result. I have the field in the SQL server set as a
smallDateTime, this makes the date show as a long date format i.e. date and
time are showing. is there a way to make this field accept just date. why the
line I am using above is not working?
any ideas
thanks

 

[Stefan Hoffmann]

hi Al,

dteLoginDate is identified as a date (dim dteLoginDate as Date). However,
when I debug, I noticed that the value returned by the Dlookup to
dteLoginDate is (12:00:00 AM) not a date. I tried the following:

dteLoginDate = Format(DLookup("DateLogged", "tbluserlog", "LogID=" &
intLogID & "") & " " & _
DLookup("TimeLogged", "tbluserlog", "LogID=" & intLogID & ""),
"Short Date")

First of all, you have to ensure that the DLookup() functions does not
return NULL, cause only Variant can store these.
Then you have take care of the different date/time parts in your fields:

Dim DateLogged As Variant
Dim TimeLogged As Variant

DateLogged = DLookup("DateLogged", _
"tbluserlog", "LogID=" & intLogID)
TimeLogged = DLookup("TimeLogged", _
"tbluserlog", "LogID=" & intLogID)

' here you can handle the NULL cases as well
If Not IsNull(DateLogged) And Not IsNull(TimeLogged) Then
dteLoginDate = DateValue(DateLogged) + TimeValue(TimeLogged)
End If

or when you ignore these lines:

dteLoginDate = _
DLookup(
"DateValue([DateLogged]) + TimeValue([TimeLogged])", _
"tbluserlog", _
"NOT IsNull(DateLogged) " & _
"AND NOT IsNull(TimeLogged) " & _
"AND LogID = " & intLogID)

is there a way to make this field accept just date.

Huh? No. SMALEDATETIME is a combined field the same as in Access. It has
always a date and a time part.

btw, why don't you store date and time in one field?


mfG
--> stefan <--

 

[Al]

Thank you Stefan, this helped. The reason why I do not like to combine date
and time, is to make it easy when I try to filter and group by date.
Especially the grouping. do you have a suggestion on that? I would like to
hear it!

hi Al,

dteLoginDate is identified as a date (dim dteLoginDate as Date). However,
when I debug, I noticed that the value returned by the Dlookup to
dteLoginDate is (12:00:00 AM) not a date. I tried the following:

dteLoginDate = Format(DLookup("DateLogged", "tbluserlog", "LogID=" &
intLogID & "") & " " & _
DLookup("TimeLogged", "tbluserlog", "LogID=" & intLogID & ""),
"Short Date")

First of all, you have to ensure that the DLookup() functions does not
return NULL, cause only Variant can store these.
Then you have take care of the different date/time parts in your fields:

Dim DateLogged As Variant
Dim TimeLogged As Variant

DateLogged = DLookup("DateLogged", _
"tbluserlog", "LogID=" & intLogID)
TimeLogged = DLookup("TimeLogged", _
"tbluserlog", "LogID=" & intLogID)

' here you can handle the NULL cases as well
If Not IsNull(DateLogged) And Not IsNull(TimeLogged) Then
dteLoginDate = DateValue(DateLogged) + TimeValue(TimeLogged)
End If

or when you ignore these lines:

dteLoginDate = _
DLookup(
"DateValue([DateLogged]) + TimeValue([TimeLogged])", _
"tbluserlog", _
"NOT IsNull(DateLogged) " & _
"AND NOT IsNull(TimeLogged) " & _
"AND LogID = " & intLogID)

is there a way to make this field accept just date.

Huh? No. SMALEDATETIME is a combined field the same as in Access. It has
always a date and a time part.

btw, why don't you store date and time in one field?


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Al,

Thank you Stefan, this helped. The reason why I do not like to combine date
and time, is to make it easy when I try to filter and group by date.

This may make sense under some circumstances, but normally changing the
data model due to UI issues is not a good idea.

Especially the grouping. do you have a suggestion on that? I would like to
hear it!

As long as it only concerns Accessm then you can use DateValue(Logged)
as group by value.


mfG
--> stefan <--

 

[Al]

Fair enough, thanks
Al

hi Al,

This may make sense under some circumstances, but normally changing the
data model due to UI issues is not a good idea.

As long as it only concerns Accessm then you can use DateValue(Logged)
as group by value.


mfG
--> stefan <--