Dlookup Date Problem

[Darrenc]

I'm sure I’m being very dense can someone point out my obvious error?

I'm trying to do a dlookup for a date in a table where the form ID matches the ID in the same table.

Dim dteTimeLower As Date
Dim dteTimeUpper As Date
Dim intCurrentID As Integer

    intCurrentID = Forms!frmNewBusinessQuoteForm!QuoteID
    dteTimeLower = Nz(DLookup("TraStartDate", "tblStopWatch", "TraQuoteID " = intCurrentID), Now())
    dteTimeUpper = Nz(DLookup("TraEndDate", "tblStopWatch", "TraQuoteID " = intCurrentID), Now())

Every time i run the code i get "Type Mismatch". I've tried changing variable type, I’ve checked that TraEndDate is actually a date. It all looks like it should work.

The TraStartDate and TraEndDate are in a General Date format because i need the time stamp as well as the date. Is this the source of my problems?

This is really frustrating, i thought this would be very simple to do, as always its the 'simple' things that catch you out.

Regards

Darren

 

[Brianwarnock]

Nz returns a string.

Try

dteTimeLower = Cdate(Nz(DLookup("TraStartDate", "tblStopWatch", "TraQuoteID " = intCurrentID), Now()))
dteTimeUpper = cdate(Nz(DLookup("TraEndDate", "tblStopWatch", "TraQuoteID " = intCurrentID), Now()))

Brian

 

[namliam]

If your table tblStopWatch is not very small, doing 2 dlookups on the same table is slow.
Better to open a recordset (search for it if you dont know it) and use the recordset to get the values.

 

[Darrenc]

Thanks for the tip about CDate, i'd never heard of that until now.

Alas that hasn't solved my problem. I've even made sure i have valid records in the table so that don't have to rely on catching a null.

I guess there's a difference but I'm not getting a "data type mismatch" just a "type mismatch" error??

I'm tempted just to create a couple of invisible text fields and populate the data into those but i'd rather understand why i can't get this dlookup to work though.

 

[Darrenc]

If your table tblStopWatch is not very small, doing 2 dlookups on the same table is slow.
Better to open a recordset (search for it if you dont know it) and use the recordset to get the values.

Ok, i'm going to go the recordset route. I have used them before successfully, it was a while a go though.

Just one thing, can i base the recordset on a query that has a parameter on it? I need to match a record with the current form ID?

    Dim myDB As DAO.Database
    Dim myRS As DAO.Recordset

    Set myDB = CurrentDb
    Set myRS = myDB.OpenRecordset("qryStopWatch", dbOpenDynaset)

    With myRS
        dteTimeLower = CDate(Nz(!TraStartDate, Now))
        dteTimeUpper = CDate(Nz(!TraEndDate, Now))
    End With

    myRS.Close

    Set myRS = Nothing
    Set myDB = Nothing

I have this Forms!frmNewBusinessQuoteForm!QuoteID as a parameter in the query, and i'm getting "too few parameters" error.

Thanks

Darren.

 

[namliam]

"Select * from tblStopWatch where ID = " & Your variable.

Your problem in the DLookup is the = needs to be inside the string like it is in above query...

 

[Darrenc]

All sorted, thanks namliam. Sometimes you can't see the wood for the trees...