Update Textbox Problem (Not as simple as it sounds)

[Steve_T]

Hello,
I am still wet behind the ears in regard to Access so your help and support is much needed.

I have created a form in Access where i am having trouble with a textbox. In short this box is called [Shift] i want it to be populated by the result the system returns in another Textbox which is called [Time], this box is set up as Time().
So below is what i need it to do when the Form is used.

Textbox: [Time] = (07:00 - 14:59) would return "Earlies" in Textbox: [Shift]
Textbox: [Time] = (15:00 - 22:59) would return "Lates" in Textbox: [Shift]
Textbox: [Time] = (23:00 - 06:59) would return "Nights" in Textbox: [Shift]

I have tried the VBA below without success in this task so i can only assume it is due to the fact that the Time field is system generated or the fact it is in time format:

Private Sub Time_AfterUpdate()

If Me.Time >= "23:00 And < 06:59" Then

Me. Shift = "Nights"

End If
End Sub


Any Help you give is much needed, Thanks

 

[pbaldy]

What did you do, delete the other thread?

Try

If Me.Time >= #23:00# OR Me.Time < #06:59# Then

Note the change to OR, as it could never be both. And you should change the field name, as Time is a reserved word. Access could get confused between your field name and the Time function.

 

[Steve_T]

Paul,
Thankyou for your help, the reason i deleted the thread is because i titled it incorrectly and i was not able to edit the title.
I have used what you suggested but it does not fully work, it does return a value but where it should show "Nights" it states "Lates".
I have set up the format of the Textbox which i have now renamed "Textbox3" to : hh:mm as soon as i added the text to the VBS it changed #23:00# to show #11:00:00 PM# even before i altered the time format???
I have tried altering the time Format to hh:mm:ss PM but this makes no difference plus if i try to alter the code back it alters it again????????????????

 

[pbaldy]

Sorry, I was a bit quick. Try this:

  If Me.Textbox3 >= #3:00:00 PM# And Me.Textbox3 < #11:00:00 PM# Then
    Me.Shift = "lates"
  ElseIf Me.Textbox3 >= #7:00:00 AM# And Me.Textbox3 < #3:00:00 PM# Then
    Me.Shift = "earlies"
  Else
    Me.Shift = "nights"
  End If

 

[Steve_T]

Paul,
Still no joy, Firstly i noticed you have used "And" instead of "Or" which you told me i was not to use.
I tried it with both and the method of using "And" only works when you input the time manualy and where if you use the system time it returns the wrong result. The same is said for when you use the "Or" option.
Like i said mate this one is not as simple as it sounds, perhapes Access has met its match (LOL)
Either way your help is much appriciated as you are the only person to of replied.

 

[slade.justin]

I used this code and it work so try it like this
Private Sub Form_Load()

If Text0.Value > "10:39:33 AM" And Text0.Value < "12:39:33 AM" Then

Text2.Value = "early"

End If
End Sub

 

[Steve_T]

Slade Justin,
All i can say is that i must be stupid. Now it does not even work when i manualy input the time.
Thanks anyway, i have attached the db incase you can spot something i have done

 

[slade.justin]

the problem is in your time and date field of your table you selected that the difult value be =now() which is the time and date so what is happening is when you say between 12:00 and 13:00 its not working because of the date side of it what I would suggest is that you change the time and date to a time field and then another date field.

Regards
Justin

 

[Steve_T]

Thanks for reply,
Your suggestion i have already tried. I have created 1 box for date using a default value of Date() and a seperate box for time with the default value of Time().
I have also tried it with no date box and only a box for time.
Plus also using 12hr format and 24hr format with and without AM/PM, i have been struggling with this inigma for sometime now trying all types of things from either all the above plus also looking at how the VBA is wrote eg ("12:00" , #12:00#) etc.
Hence the title of the thread "This is not as simple as it sounds"
I do feel that the problem is that we are refering to the time field as a textbox and perhapes Access does not see it as one which is why when you create a Table at the start and add a field for either/or Date and Time it has its own option (Date/Time) from the drop down selects available.
I have used the "If me." or "If Text" VBA meny times and it has worked fine when using manual inputted values or even Combo box values but for some reason it does not work when it has to use the system generated time to trigger it.

 

[pbaldy]

I tested my code and it works fine (I mentioned that my first post was too quick; the AND vs OR comment was only valid for the time spanning midnight).

You managed to change the # to " which will cause it to fail. As Justin already noted, you have a date component in your value, which will also cause it to fail. If you want to keep the date, you could add the TimeValue function to your code to peel out the time.

 

[Steve_T]

It Works!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Paul,
I changed the time to " instead of # because that is what user Slade Justin suggested above.
Anyway i have changed the time default value as stated in your email and it works fine, i have checked all time frames by altering my system clock within the control panel and they work.
So can i take his opertunity to thank you and Slade Justin for your much needed help.

THANKS!

 

[pbaldy]

No problem. In general, text values are surrounded by quotes and date/time values by #.