Help with an Update statement

[TechnoWeenie]

I'm having problems updating my database and I can't figure out why.

First, I insert a time into the DB. This is the checkout time. This works, no problem.
when the unit comes back I have a check_in_time. This is not working. I'm using the same statement I used to put the checkout time in.

here is my statment for the checkin

strSQL = "UPDATE tblDetailEvents SET check_in_time='" & FormatDateTime(NOW,4) & "' WHERE ID='" & eventID & "';"

When I run this I get the following:


UPDATE tblDetailEvents SET check_in_time='13:07' WHERE ID='15';
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.



Both checkout and check_in_time and the same type field, Date/Time. Since I can enter a checkout time You would think that I could enter the check_in_time but I can't. I've tried putting [ ] around the names and it still doesn't work. I've even messed with the type of field I'm trying to put this in (changed it to text or memo, etc.) but still nothing.

 

[BattleDroid42]

You're trying to insert the time as text into a date field. Thus 'type mismatch'. This error message is always the wrong data type being inserted into a field.

Try formating the time like this:

CDate(Format([check_in_time],"Long Time"))



Remember, wherever you go...there you are.

 

[TechnoWeenie]

I tried it as you suggested. No go. I'm not sure if this will make a difference, I doing this in ASP.

I did a quick lookup on CDate in the book that I have here but it didn't give me much more than 2 sentences. I tried using it but I still come up with the same problem.

 

[Aerin]

Would it be helpful if instead of " you will put #
"UPDATE tblDetailEvents SET check_in_time=#" & FormatDateTime(NOW,4) & "# WHERE ID='" & eventID & "';"

and probably Time() will faster than FormatDateTime(now,4)?

Also you have eventID, is this numeric or text? if it is numeric and you are trying to update it like a text, could be the problem as well.

 

[TechnoWeenie]

When I tried using the # I got the following error

UPDATE tblDetailEvents SET check_in_time=#12:16:38 AM# WHERE ID='15';

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

The eventID I'm searching for is just the auto counter in Access. That is one way I could keep track of all the events that will be entered into the database.

 

[PHV]

Replace this:
WHERE ID='" & eventID & "';"
By this:
WHERE ID=" & eventID & ";"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TechnoWeenie]

I tried removing the ' from the eventID and I still have the same Data type mismatch error

 

[TechnoWeenie]

Well, I got it to work.

First I had to SELECT the record I was looking for and then change the values and UPDATE the record. Don't know why it works this way and not the other but it works!