Form error "Type Mismatch"

[Guest]

Hi,

In my time tracking db, I have a hidden form that opens immediately after
the switchboard "fWorkLogHidden". If someone forgot to log out of the db (by
filling in StopTime) the previous day, there is another form
"fWorkLogPreviousDay" that opens from the FormLoad event of the hidden form,
requiring stop time to be filled in the next time the db opens.
Below is code I currently have in place, but get error "13 Type Mismatch".

DoCmd.OpenForm "fWorklogpreviousday", acNormal, , "stoptime isnull" And
"starttime BETWEEN Date() and (Date() - 1)"

I know this is not right, but I've been going round and round with this code
trying to make it work and decided to post for help. What I want to happen
is for the PreviousDay form to open records where the StartTime is the day
before and the StopTime is empty. Originally, I had the query that backs
this form set to StartTime = Date()-1 and StopTime IsNull. I read about form
filters so I removed the criteria from query and set the "Allow Filters" to
Yes.

I hope this makes sense and that someone will have answer to make this work
as I want it to. Any help is greatly appreciated.
Thanks,
Pam

 

[Guest]

I think it is just a syntax problem

DoCmd.OpenForm "fWorklogpreviousday", acNormal, , "stoptime isnull" And
"starttime BETWEEN Date() and (Date() - 1)"

Try
"stoptime Is Null And starttime BETWEEN #" & Date() -1 & "# AND #" &
Date() & "#"

 

[Douglas J. Steele]

On the off chance that the user has his/her Short Date format set to
dd/mm/yyyy, the following would be safer:

"stoptime Is Null And starttime BETWEEN " & Format(Date() -1,
"\#yyyy\-mm\-dd\#") & " AND " &
Format(Date(), "\#yyyy\-mm\-dd\#")