data type mismatch - help!

[johnlatona123]

im just about ready to shoot myself....

ive got this string of queries based on a table that use this function correctly. and i have a second string of queries based on a DIFFERENT table that will not work when i have the criteria entered. what am i missing? i think ive checked all the formatting areas i can find, but something is eluding me...

field - COstart: TimeValue([start time])

criteria - Between #5:00:01 AM# And #4:00:00 PM#

id upload my DB, but i am getting a file attachment error that wont allow me to do so. thoughts?

please help me with this issue!

where else can i check to resolve this issue?

please advise.

 

[DJkarl]

I would guess the field [start time] is not a date field OR if it is you may have a NULL value in it, most function will throw an error when a NULL is encountered. If it isn't a date field change the datatype or use the Cdate() function. If it already is a date field use the NZ function to account for NULL values.

 

[johnlatona123]

awsome great CDATE did it!

now, how do i convert a medium or short time, to a general whole number like for example 3:20 = 200 minutes

my purpose now is to be able to divide time by a quantity and then multiply the result to calculate time utilized

please advise

 

[johnlatona123]

also, could you elaborrate on the usage and implementation of the "NZ function" as you call it.

thanks!

 

[DJkarl]

To convert time such as 3:20 (3 hours 20 minutes) to 200 minutes I think you'd need to write a VBA function to do that, I don't believe there are any built-in functions to convert between time formats.

The NZ function can be used like

NZ([FieldOrValue], ValueIfNull)

The field or value will be checked, optionally you can provide a default value if it is null, if no value is provided then I believe it puts whatever the default value is for the datatype being used. If you're using a numeric type this is likely a zero, if it is string data it will be an Empty or zero length string.

 

[johnlatona123]

alrighty, how can i get to the end result using a vba function?

 

[johnlatona123]

hi!

ive attached a paint image of my next issue in this problem, how can i convert the "#num!" items to a usable number such as zero?

this error or whatever it is, is causing me issues in the next query i need to write.

please help!

 

[DJkarl]

Based on your picture these are divide by zero errors, if you want zero to be displayed instead of an error then you need to use an IIF statement in the query to check for a zero value of your divisor.