Criteria help

[jcaptchaos2]

I have this code that moves the 3rd shift start date ahead 1 day because they start at 10pm the previous date, I need to add to it so if they transact on between midnight and 7am it doesn't move the date ahead. The column name for the time is [Time On] any ideas?

IIf([shift]=3,[date]+1,[date])

thanks
JC

[June7]

What is the [Time On] datataype - text or date? Show sample value - 24hr or AM/PM?

Maybe something like:

IIf([shift]=3 And [Time On] > 700 And [Time On] < 2400, [date]+1, [date])

Getting the time values right is the trick.

[jcaptchaos2]

June7,

Thanks for your response, the datatype is Date/Time formatted as Medium Time. I will work with tyour suggestion today.

Thanks JC

[jcaptchaos2]

June7

This appears to be working perfectly thank you for your help. Have a great day.

[jcaptchaos2]

Well spoke to soon, looks like it is not working as I thought, it may be a format thing like you said. If I look in the table the time looks like this "5/3/2011 10:00:21 PM", even if I type 22:00 into the table with a 3 for shift it does not seem to matter. The [Time On] field has a datatype of Date/Time. any other suggestions?

IIf([shift]=3 And [Time On]>7 And [Time On]<24,[date]+1,[date])

[June7]

Extract the time part from datetime and compare with criteria. Test this out:

Format([Time On], "hh") > 7 AND Format([Time On], "hh") < 24

[jcaptchaos2]

June7,

Thanks again for your help, I am not sure how to do what you are suggesting here is what I changed it to but it does not work. I don't think I am doing what you are suggesting correctly.

IIf([shift]=3 And Format([Time On], "hh") > 7 AND Format([Time On], "hh") < 24 )

[June7]

Well, what you show is not the entire expression, which should be:
IIf([shift]=3 And Format([Time On], "hh") > 7 AND Format([Time On], "hh") < 24, date]+1, [date])

Is this what you actually have? If it is and still not working, what is the error - a message, wrong results, nothing?

[jcaptchaos2]

June7,

The error reads "The expression you entered contains invalid syntax" "You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotations marks"

[jcaptchaos2]

Ok I found why I got that error but now I am getting this error,

"The expression IIf([shift]=3 And Format([Time On], "hh") > 7 AND Format([Time On], "hh") < 24, [date]+1, [date]) cannot be used in a calculated column.

IIf([shift]=3 And Format([Time On], "hh") > 7 AND Format([Time On], "hh") < 24, [date]+1, [date])

[June7]

I tested that expression in the Immediate window of VBA editor. So now tested in query designer. Doesn't like the quote marks, had to change to apostrophes. It worked but was a simiple Select query. Suggest you try that first and when you get that working try it in your final query. If still doesn't work for you will need to see your query statement for analysis. Don't think ever encountered that error msg.

[jcaptchaos2]

Ok thanks for your help, I am using this expression in a table using hte calculated option but even after changing the quote marks I still get the error "cannot be used in a calculated column"

[June7]

I was assuming you were building a query and sounds like you will have to. I have never done a 'calculated column' in table. Must be new to Access2010.

Build query and use the calculated field to construct field in query with the expression.

[jcaptchaos2]

Thanks for all your help, will give it a try tomorrow at work