TimeValue

[turnbuk]

I'm using TimeValue («stringexpr») to pull only the time from a Time/Date field (10/1/1999 8:00:00 AM). It works correctly and shows me 8:00:00 AM. But when I try to add a criteria to that TimeValue I keep getting an error that states "Data type mismatch incriteria expression". Does anybody know what is not matching up? I double checked the format of the field and it is Date/Time. My Critieria is written as: Between #10:00:00 AM# And #11:00:00 AM#

[boblarson]

You need to build a separate field for your criteria. Otherwise you have to pass a valid DATE and time value for it. So create the field for your query:

TimeOnly:TimeValue([DateFieldNameHere])

and then apply your criteria as you have to THAT field.

[turnbuk]

When I do that and run the query it asks for me to "Enter a Parameter Value" for [DateFieldNameHere].

Just so i'm clear, I should have two TimeValue Expressions, correct? One to identifty the TimeValue from my Date/Time field. Then another one to identify the TimeValue from the first TimeValue?

[boblarson]

When I do that and run the query it asks for me to "Enter a Parameter Value" for [DateFieldNameHere].

Did you put your date field name in that spot instead of leaving it as it was? I put DateFieldNameHere as a cue to you to put the actual name of your date/time field there.

Just so i'm clear, I should have two TimeValue Expressions, correct? One to identifty the TimeValue from my Date/Time field. Then another one to identify the TimeValue from the first TimeValue?

You don't need two TimeValue expressions. Just one. So, the question is - are you doing it like I said? Are you creating, in your query, a new field by using what I put:

TimeOnly:TimeValue([PutYourDateTimeFieldNameHere])

and then in the criteria you would have

Between #10:00:00 AM# And #11:00:00 AM#

But if you have nulls in that field you may need to use:

TimeOnly:TimeValue(Nz([PutYourDateTimeFieldNameHere],0))

to handle the nulls

[turnbuk]

OK. Thanks for clarifying. I had it correct originally. It is exactly how you recommended. But that is where I get the error: "Data type mismatch in criteria expression"

This is what I have it written as:

FIELD --> TIMEONLY: TimeValue([InjuryIllness]![InjIll_DateTime])

CRITERIA --> Between #10:00:00 AM# And #11:00:00 AM#

I also tried with the Null expression but same result.

[boblarson]

Your field InjIll_DateTime field isn't a multi-value field is it?

[turnbuk]

Looking at the source table the data type is stricktly a Time/Date field. Data when ran resembles: 10/1/1999 8:00:00 AM.

[boblarson]

I've had issues with fields like that before when trying to limit by just time. If you want to post a copy of the database, I can try to get to it and see if I can tweak it to work correctly. But I may not get to it for a while.

It still might have to do with the nulls, so you might want to try this:

TIMEONLY: TimeValue(Nz([InjuryIllness]![InjIll_DateTime], #1/1/1900 12:00:00 AM#))

And then save that query and then use that saved query in another one where you can then put the criteria on the TIMEONLY field.

[turnbuk]

TIMEONLY: TimeValue(Nz([InjuryIllness]![InjIll_DateTime], #1/1/1900 12:00:00 AM#))

Ok, i used this expression, then created another query with the TIMEONLY field and added my criteria. Good news is, no mismatch error! Bad news is, it shows up zero results each time, no matter what time frame i choose.

However, if I choose a simple =#10:00:00 AM# criteria I DO get results. Which is a step in the right direction. There must be something now with my BETWEEN criteria that I need to adjust.

[boblarson]

Try using this instead:

>=#10:00:00 AM# And <=#11:00:00 AM#