Data type mismatch in Query

[johnseito]

SELECT timevalue([apt start time]), timevalue([apt finish time]), cint(format(timevalue([apt finish time]) - timevalue([apt start time]), "h")) as difference
FROM Fixing 
where [apt start date] = [apt finish date] and cint(format(timevalue([apt finish time]) - timevalue([apt start time]), "h")) >=6

why is this a date type mismatch?

 

[pr2-eugin]

Try this code..

SELECT TimeValue([apt start time]), TimeValue([apt finish time]), [URL="http://www.techonthenet.com/access/functions/date/datediff.php"]DateDiff[/URL]("h", [apt finish time], [apt start time]) AS Difference
FROM Fixing 
WHERE [apt start date] = [apt finish date] AND DateDiff("h", [apt finish time], [apt start time]) >=6

 

[CJ_London]

are all your fields in the query a date type?

TimeValue requires a string input so

timevalue([apt finish time])

will fail if [apt finish time] is a date

 

[spikepl]

@CJ

Not quite. DateValue can be used to strip the time component of a datetime, and in the same way a TimeValue can be used to strip of the Date component of a datetime. It does accept a DateTime as input.

 

[CJ_London]

@Spikepl

Learn something everyday! Was going by this

The syntax for the TimeValue function is:
TimeValue ( string_time )string_time is a string representation of a time. string_time can be a time ranging from 0:00:00 to 23:59:59. string_time can be entered in either 12-hour or 24-hour clock notation.
[B][SIZE=2]Applies To[/SIZE][/B]

[LIST]
[*]Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000
[/LIST][B][SIZE=2]For Example[/SIZE][/B]

[SIZE=2]TimeValue ("3:12:57 PM")[/SIZE][SIZE=2]would return 3:12:57 PM[/SIZE][SIZE=2]TimeValue ("15:12:57")[/SIZE][SIZE=2]would return 3:12:57 PM[/SIZE]

 

[johnseito]

SELECT timevalue([apt start time]), timevalue([apt finish time]), datediff("h", [apt finish time], [apt start time]) as difference
FROM Fixing 
where [apt start date] = [apt finish date] and datediff("h", [apt finish time], [apt start time]) >= 6

I tried this and is still the same error as my code before.


I think I put timevalue for the starttime and endtime is to make it into a number.

 

[johnseito]

are all your fields in the query a date type?

It was a date and time in one field.
I broke it up into separate date and separate time type.

and I tried to use the finish time minus start time, but I couldn't do it, maybe because is text, so I convert it by using timevalue function.

 

[jdraw]

johnseito,

A recommendation : Do not use field and object names with embedded spaces. Choose a naming convention with alpha, numeric or the underscore (_) only. It will prevent many syntax errors.

Tell us about your fields. Are they Date data type or what?
Can you tell us in plain English what your query is trying to do?

 

[johnseito]

I have two fields (start date/time) and (finish date/time), both of them have date and time values in them (as with the two example below) and they may be either text or dates which I import from excel. I can't really put them as date/time field type in access because it won't allow me, I believe is because some of them are just text but is still date and if I make the field as date/time field type I will lose all the data that is not dates but text.

For example of the two fields

28-02-2013 17:02

sometimes these two field also have it like this

01/03/2013: 16:26

What I did for these two fields is I broke them and separate the date into a specific field and the time into a specific field and now I want to query the start date and the finish date are the same and the time difference from the finish time and the start time is 6 or greater.

I broke them into dates as this

02/28/2013

and time as this

5:02:00 PM

I can't do the difference between the time without converting it into timevalue, that is why I have

TimeValue([apt finish time])

Thanks

 

[johnseito]

where [apt start date] = [apt finish date] and cint(format(timevalue([apt finish time]) - timevalue([apt start time]), "h")) >=6

WHERE [apt start date] = [apt finish date] AND DateDiff("h", [apt finish time], [apt start time]) >=6

the error only occur in the where clause, if I take the where clause away, the code has no error.

 

[johnseito]

A recommendation : Do not use field and object names with embedded spaces. Choose a naming convention with alpha, numeric or the underscore (_) only. It will prevent many syntax errors.

I'll remember that. Thanks.

 

[jdraw]

I don't know if it's relevant but you may wish to change

WHERE [apt start date] = [apt finish date] AND DateDiff("h", [apt finish time], [apt start time]) >=6

to
WHERE [apt start date] = [apt finish date] AND (DateDiff("h", [apt finish time], [apt start time], [apt finish time]) >=6 )

The earlier date comes first in the DateDiff otherwise you'll get a negative number.

 

[johnseito]

WHERE [apt start date] = [apt finish date] AND (DateDiff("h", [apt finish time], [apt start time], [apt finish time]) >=6 )

I tried it and still have the same error, "data type mismatch in criteria expression".

I also noticed you have apt finish time, apt start time and apt finish time again. I didn't know datediff can have three values, even if I take out
[apt finish time] there is still the same error.

 

[jdraw]

The 3 apt things are wrong. I was cutting and pasting ---seems I copied and pasted.
The correct I think is
WHERE [apt start date] = [apt finish date] AND (DateDiff("h", [apt start time], [apt finish time]) >=6 )

Can you post a copy of your database? I don't see a data type issue, but I haven't seen the tables.

 

[CJ_London]

You have an extra field in datediff

try

WHERE [apt start date] = [apt finish date] AND [COLOR=purple](DateDiff("h", [apt finish time], [apt start time) >=6 )[/COLOR]

 

[johnseito]

You have an extra field in datediff

try

WHERE [apt start date] = [apt finish date] AND [COLOR=purple](DateDiff("h", [apt finish time], [apt start time) >=6 )[/COLOR]

I tried that already, same error.
I will post some image of the import data/time type and my conversion of the datas and time and then how I lead to where my error now occurs.

The best I could do is post image and not a database or file right?

 

[CJ_London]

you should be able to post a db or file - give it a try

 

[johnseito]

you should be able to post a db or file - give it a try

only limited in uploading some of these files types and limited by size.

accdb2.00 MB--

bmp100.0 KB--

doc100.0 KB--

gif100.0 KB--

jpe100.0 KB--

jpeg100.0 KB--

jpg100.0 KB--

mdb2.00 MB--

pdf100.0 KB--

I will forward a sample in text file after I put them together.

 

[Brianwarnock]

If you have to use Timevalue to convert your fields to times in the select then surely you have to do that in the Datediff

Brian

 

[johnseito]

If you have to use Timevalue to convert your fields to times in the select then surely you have to do that in the Datediff

Brian

how come it works like this in the select

datediff("h", [apt finish time], [apt start time]) as difference

but not in the where.

I noticed I do see some negative numbers, with my code there isn't any negative numbers.
the first being the start and the second being the finish

with this query

SELECT TimeValue([apt start time]), TimeValue([apt finish time]), DateDiff("h",[apt start time],[apt finish time]) AS Difference
FROM Fixing

3:28:00 PM

3:11:00 AM

difference between these two is negative 12.

and this and this

8:56:00 PM

6:15:00 PM

as negative 2.