Please help with type mismatch error.

  • Thread starter Thread starter Al
  • Start date Start date
A

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
 
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")
Click to expand...
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.
Click to expand...
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 <--
 
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!

Stefan Hoffmann said:
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")
Click to expand...
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.
Click to expand...
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 <--
Click to expand...
 
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.
Click to expand...
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!
Click to expand...
As long as it only concerns Accessm then you can use DateValue(Logged)
as group by value.


mfG
--> stefan <--
 
Fair enough, thanks
Al

Stefan Hoffmann said:
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 <--
Click to expand...
 
Back
Top