calculation not working

A

awake2424

Registered User.
Local time
Today, 10:14
Joined
Oct 31, 2007
Messages
479
Code: 
 If Me.Test = "LR" And Me.Time_Received > #12:00:00 PM# Then
   Me.Date_Received = [Date_Received] + 1
End If
If Me.Test = "LR" And Me.Time_Received < #11:59:00 AM# Then
   Me.Date_Received = [Date_Received]
End If

This code seems to change the Date_Received by one day even if the sample is received before 11:59 AM. Thanks.

Desired:
Date_Received = 7/28/2014
Test = LR
Time_Received = 10:00 AM
Due_Date = 7/31/2014 (4 days including date_received)

Date_Received = 7/29/2014
Test = LR
Time_Received 12:45 PM
Due_Date = 8/1/2014 (4 days including date_received)

Currently looks like:
Date_Received = 7/28/2014
Test = LR
Time_Received = 10:00 AM
Due_Date = 7/31/2014 (4 days including date_received)

Changes to:
Date_Received = 7/29/2014
Test = LR
Time_Received = 10:00 AM
Due_Date = 8/1/2014 (4 days including date_received)

Date_Received = 7/29/2014
Test = LR
Time_Received 12:45 PM
Due_Date = 8/1/2014 (4 days including date_received)
 
Hmm, you might need to use Between...

Code: 
Me.Time_Received Between #12:00:00 PM# And #11:59:00 AM#
...because technically 11:59 AM is after 12:00 PM. Way after but still after.
 
Im not sure I understand:

I am trying to code if a sample is received before noon then the Date_Received is unchanged.

If a sample is received after noon then the Date_Received +1 (1 day is added to it). Thanks.
 
awake2424 said:
Code: 
#12:00:00 PM# Then
#11:59:00 AM#
Click to expand...
Basic question but making sure you realise that both of them are only 1 minute apart? I.e. the first is 12 noon (1 minute after 11:59 AM) and the other is 11:59 in the morning.
 
Hmm, any time after Noon is a legitimate time and any time before one minute before Noon (11:59 AM) is a legitimate time. Your argument will stop on the first criteria it can apply. Now, you also have to bare in mind with no stopping point 10:00 AM is AFTER 12:00 PM once the clock circles. Understand? So, you need to say...

After Noon but before ?????
Before 11:00 AM but how far before ?????

Remember Access can't rationalize that you are only referring to today. It only does what you tell it to do. So, you need to say Between Noon and 5:00 PM for one argument and then Between 9:00 AM and 11:59 AM. Is that clearer?
 
Code: 
 If Me.Test = "LR" And Me.Time_Received > #12:00:00 PM# < #7:00:00 PM# Then
   Me.Date_Received = [Date_Received] + 1
End If

If Me.Test = "LR" And Me.Time_Received > #5:00:00 AM# < #11:59:00 AM# Then
   Me.Date_Received = [Date_Received]
End If

Is this what you mean't? The Date_Received seems to be changing for both conditions.

For example,
Date_Received = 7/28/2014
Test = LR
Time_Received = 10:00 AM
changes to Date_Received 7/29/2014

Date_Received = 7/28/2014 (this is correct)
Test = LR
Time_Received 12:45 PM
changes to Date_Received 7/29/2014

Thank you.
 
I was thinking of Between but that will work also. And, yes, that is what I was talking about. Just always rember Access *knows* nothing. You have to tell it *exactly* what you mean.
 
Using the code I posted:

Date_Received = 7/28/2014 (this is not correct)
Test = LR
Time_Received = 10:00 AM
changes to Date_Received 7/29/2014

Date_Received = 7/28/2014 (this is correct)
Test = LR
Time_Received 12:45 PM
changes to Date_Received 7/29/2014

It seems to be applying only 1 condition and then skipping the other and I am not sure how to fix it. Thanks.
 
Code: 
 If Me.Test = "LR" And Me.Time_Received [B]Between[/B] #12:00:00 PM# And #7:00:00 PM# Then
   Me.Date_Received = [Date_Received] + 1
End If

If Me.Test = "LR" And Me.Time_Received [B]Between[/B] #5:00:00 AM# And #11:59:00 AM# Then
   Me.Date_Received = [Date_Received]
End If

I get an expected then or goto with the Between in bold highlighted. Thanks.
 
I think Between works with only Select...Case statements or queries, so you need >= and <=

Plus you really don't need the AM/PM part if you're going to work with the 24hr format.
 
Code: 
 If Me.Test = "LR" And Me.Time_Received >= #12:00:00 PM# <= #7:00:00 PM# Then
   Me.Date_Received = [Date_Received] + 1
End If

If Me.Test = "LR" And Me.Time_Received >= #5:00:00 AM# >= #11:59:00 AM# Then
   Me.Date_Received = [Date_Received]
End If

Date_Received = 7/28/2014 (this is not correct)
Test = LR
Time_Received = 10:00 AM
changes to Date_Received 7/29/2014

Date_Received = 7/28/2014 (this is correct)
Test = LR
Time_Received 12:45 PM
changes to Date_Received 7/29/2014

It seems to be applying only 1 condition and then skipping the other and I am not sure how to fix it. Thanks.
 
Code: 
Me.Time_Received >= #12:00:00 PM# [COLOR="Blue"]AND Me.Time_Received[/COLOR] <= #7:00:00 PM#
 
@vbaInet

I told you the coffee sometimes gets the best of me. Nice catch!
 
Code: 
 If Me.Test = "LR" And Me.Time_Received >= #12:00:00 PM# AND Me.Time_Received <= #7:00:00 PM# Then
   Me.Date_Received = [Date_Received] + 1
End If

If Me.Test = "LR" And Me.Time_Received >= #5:00:00 AM# AND me.Time_Received >= #11:59:00 AM# Then
   Me.Date_Received = [Date_Received]
End If

should prevent the date from changing if the time is not meet and only change the date if the time is met? Thanks :).

Code: 
 Date_Received = 7/28/2014 (this is correct)
Test = LR
Time_Received = 10:00 AM

Date_Received = 7/28/2014 (this is correct)
Test = LR
Time_Received 12:45 PM
changes to Date_Received 7/29/2014
 
Is the Date_Received field not bound to Me.Date_Received? If it is you only need one If:
Code: 
    If Me.test = "LR" And Me.Time_Received >= #12:00:00 PM# And Me.Time_Received <= #7:00:00 PM# Then
        Me.Date_Received = [Date_Received] + 1
    End If
Or
Code: 
    If Me.test = "LR" And Me.Time_Received >= #12:00:00# And Me.Time_Received <= #19:00:00# Then
        Me.Date_Received = [Date_Received] + 1
    End If
By the way, are you sure Time_Received is in the right format and only stores Time?
 
I have attached the database and changed the code to:
Code: 
 If Me.test = "LR" And Me.Time_Received >= #12:00:00 PM# And Me.Time_Received <= #7:00:00 PM# Then
        Me.Date_Received = [Date_Received] + 1
    End If

The date_received is good if the time_received is before after noon.
The date_received doesn't seem to change if the time_received in after noon.

Me.Date_Received is bound to Date_Received and the format is set to medium time though when I click on the Time_Received field even though 11:37 AM shows (1/1/1900 11:37:15 AM) is what is actually in the field.

Thank you.
 

Attachments

Last edited:
Is this linked to a Sharepoint List?

There is no data to test. I don't want to enter something and have it show up somewhere messing something up.
 
Yes, it is linked to a sharepoint list, but there is no data in there yet so feel free to enter data in it. Thank you.
 
You must log in or register to reply here.

Similar threads

T
Baffled by Time
Replies
9
Views
527
DHookom
DHookom
R
Merge two tables in query by date/time, preserving hourly time in one of the tables
Replies
12
Views
945
Ken Sheridan
Ken Sheridan
D
Using VBA to find perfect numbers, and accurately calculate pi to 100,000 decimal places.
Replies
11
Views
922
Steve R.
Steve R.
MattBaldry
Start and End Times with Overlap
Replies
11
Views
1,226
arnelgp
arnelgp
P
Reading a long text file to insert rows in a table
Replies
6
Views
1,061
pacctono
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom