find shift value based on time for report

[baldrex]

I am running a report based on a table where the time has been entered in this format HHMMSS and what I want to do is have access look at the time then instead of placing the time on the report I would like it to replace the time with a string name i.e. graveyard, day shift etc. is there a way to have it compare and then replace with the appropriate string ?

[imnewtoaccess]

You have to create a table with the Time String you want to display corresponding to each time of the day, and use that table in your report.

[baldrex]

Imnew,

hmmm, you mean I have to create a table with every sec of every min of every hour and assign a shift to that second, min, hr ?

i was hoping for something like this - column in my table has 093025 and when I run my report I would like the number to be replaced with Day Shift and not the number - but i want to be able to look at the query and still see the actual number - i just want it to be replaced on the report

ie under the column heading it is "Time Created" and the column contains the numbers as above - i need to replace the number with the corresponding shift based on the number in the query

does this make sense ?

[FishVal]

Imnew,

hmmm, you mean I have to create a table with every sec of every min of every hour and assign a shift to that second, min, hr ?

i was hoping for something like this - column in my table has 093025 and when I run my report I would like the number to be replaced with Day Shift and not the number - but i want to be able to look at the query and still see the actual number - i just want it to be replaced on the report

ie under the column heading it is "Time Created" and the column contains the numbers as above - i need to replace the number with the corresponding shift based on the number in the query

does this make sense ?

Plz, clarify the logic used in replace.
If it can be implemented by several comparissons you may use Switch function or nested IIf function in report query or in report field.

[OldBirdman]

Why isn't this a very simple problem?

In the Properties Box, Data Tab, Record Source, use the following:
=IIf(strHHMMSS<"08","Graveyard Shift",IIf(strHHMMSS<"16","Day Shift","Swing Shift"))

Replace strHHMMSS with the correct field name. This works if the HHMMSS field is a string. If it is a Time field, replace with Hour(timeField).

Above assumes Midnight/0:00:00 to 7:59:59 as Graveyard, 8:00:00 to 15:59:59 as Day, and 16:00;00 to 23:59:59 as Swing Shift. Of course, your day shift may not be 8 to 4 and you must decide whether 8:00:00 is Graveyard or Day. Coding will be easier if shift starts on x:00:00 and ends on x:59:59.

I will be in an location without internet connection for the next week, so will not respond to questions about this response. But I just could not resist answering it.

Old Birdman

[OldBirdman]

Addendem:
The field name may have to be enclosed in square brackets [ ], therefore:
=IIf([strHHMMSS]<"08","Graveyard Shift",IIf([strHHMMSS]<"16","Day Shift","Swing Shift"))

OldBirdman

[NeoPa]

Why isn't this a very simple problem?

The simple answer to that is that the OP didn't provide enough information.
Not everyone would feel comfortable thinking they knew which times were associated with which shifts, without that information being specified (as it should have been) in the question.
Not everyone who knows Access knows everything about everyone's question. In this case you knew enough and provided a straightforward answer.

An alternative answer (not perhaps quite as straightforward as yours, but with other benefits in some situations) would be to have a table of shifts with start and end times and a title associated. This would fit more neatly into SQL code and be easier to maintain (without touching the code). It also extends more easily if the number of shifts increases, not requiring a rewrite.

Remember, there's VERY rarely only one way to crack an egg.

[OldBirdman]

I'm sorry if I ruffled some feathers. I read this problem, and it seemed to me to be easy.

Of course, I did not address the fact that the shifts might not be the seme for each employee. A general case solution requires another table, and some code to support it.

However, as stated, the problem was to substitute a shift name for a shift time. Was I out of line to present a different solution? If someone has answered a question, should nobody suggest an alternate solution? I really do not know the ediquite here, please tell me if I am out of line here, in responding to a problem where someone has already responded.

Personally, if I present a problem to this forum, I would like as many solutions as possible. More is better. I do understand that if someone is answering, others may not, as there are too many problems for everyone to answer everything.

Bottom line is that baldrex has not responded as to whether his problem was solved or not. If not, what did all who responded miss. If his problem is solved, which solution gave him the CLUE necessary to proceed?

Old Birdman

[NeoPa]

I would hate to think that I was discouraging you from answering questions here at all. In truth there is nothing wrong with your answer.
What I was responding to was your question (This is the bit I quoted in my reply). I was merely trying to explain to you (as you'd posed the question in the first place) why other, perhaps less simple, answers may quite validly be suggested.
If my feathers were ruffled a little, it was simply that I took the question as a criticism of other responses. I see it as my job to support all our members, particularly those who offer their time to help others.
I didn't mean to sound critical as, in truth, I wasn't sure you had meant it that way, so I answered the question as directly and straightforwardly as I could.
I would never try to criticise any member for offering a solution. Believe me there have been a few who suggested stuff that was just basically wrong. In that case I would try to explain why I thought another solution would be preferable, or explain the problem with their idea. Your answer certainly didn't fall within that category.

[OldBirdman]

OK, I cannot answer any question, as I am not one of your "Experts".

If I don't try to help other members, am I still entitled to help.

I have been slow to respond to this critizism because I did not want to over-react, but I must respond. I offered a solution to a problem AS I INTEPRETED IT. Perhaps my intepretion was incorrect, perhaps not. As we still haven't heard from "baldrex" we don't know if the real question they needed to know has been answered or not.

Have you ever heard the joke about the child who asks "Where did I come from?". After a long explanation from the mother to the asking child, the child responds "Joey is from Chicago, where am I from?". A simple question can be either simple or extremely complex. It seemed to me that the original response was too complex, and so I responded. If "baldrex" was happy with any solution, then this forum has succeeded again. If not, it failed. We don't know, unless the response with to the individual responding, and not to the general forum. It was not to me.

I really don't know how to monitor new questions, I don't know how to close a thread, and I don't know to monitor old threads submitted by me. Again, Is there a "thescripts for Dummies"? I don't find the answer promised when I first submitted that question.

OldBirdman

[NeoPa]

My friend,
I'm not sure why you think I'm being critical.
I don't find anything to criticise. I like that you offer whatever help you can.

[NeoPa]

I really don't know how to monitor new questions, I don't know how to close a thread, and I don't know to monitor old threads submitted by me. Again, Is there a "thescripts for Dummies"? I don't find the answer promised when I first submitted that question.

OldBirdman

There is only the FAQ, but I'm not sure that is directed at members as a User Guide.
If you have any specific "How To" questions I'll be happy to answer them for you (as, I suspect, would any of the other "staff"). Feel free to PM me if you have any questions.

-NeoPa.