Complicated Expression

[xmeb]

Hi:

I have two number fields (pickups and dropoff) for various pick ups and a drop off. There are several pick ups daily in the early morning followed by one drop off. I need a text box on a form that will compare the pick ups total to the drop off total for the past twelve hours so that we are sure that everything picked up was dropped off, in other words, that the two numbers are the same. Ideally, the text box would be red when they do not agree and green when they do which would mean that while they are doing pick ups (they create a record in the database for each pick up) the text box would be red but when they create the record for the drop off the text box would go to green if the two numbers were the same and stay red if not. Can this be done?

Thanks,

xmeb

 

[SkipVought]

Hi,

So what code do you have so far, explaining what is not working in exact terms.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xmeb]

The following is what I am using now.

=([SumOfDO]-[SumOfPU])

conditional

Then I use conditional formatting for the color. Equal to "0" it is green and equal to or greater than "-1" it is red.

It works okay but does all records and not just the last 12 hours.

Thanks.

 

[dhookom]

xmeb,
Please provide table and field names. Can we assume the form's record source contains records beyond the last 12 hours?

It would also help if you took the time to provide some sample records including only the significant fields/columns.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

The table name is "DataTable".

The field names are "Pickups" and "Dropoffs".

Yes, there are lots of records well beyond 12 hours.

The "DataTable" also has a staff name field, date field, two time fields (start and end), an actions field, etc.

Thanks.

 

[dhookom]

xmeb,
Which field(s) are used to calculate "last 12 hours"? Do you really have fields with names of [Date], [Start], and [End]? What are you storing in these fields?

There wouldn't be questions after questions if you simply took the time to "provide some sample records including only the significant fields/columns".

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

last 12 hours"

That depends on a reference point. Can it be ANY point of reference? Could it be with respect to 6:06 AM, so that the prior 12 hours would be from the previous day at 6:06 PM to 6:06 AM?

Or do this refer to a sepcific 12 hour slot each day: for instance from 12:00 AM to 12:00 PM and 12:00 PM to 12:00 AM each day?

"daily in the early morning" is not quantitative.

This is all VERY NEBULOUS.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xmeb]

The field that calculates the last 12 hours is "DDate" and the name of it is "DDateTextBox" and it is a Date/Time Field. "Start Time" is named "StartTimeTextBox" and "End Time" is named "EndTimeTextBox" and both are Date/Time Fields, but have nothing to do with the 12 hour calculation.

Where I come from the "last 12 hours" has always meant just that, the past 12 hours from now. If it was from another reference point around here we would say the 12 hours previous to 4:00 PM yesterday or something like that.

The pickups start at 1:00 AM and the one drop off is at approximately 7:00 AM so I used 12 hours because the supervisors come on duty at 8:00 AM so by 1:00 PM they would have definitely attended to the database and noticed if things were amiss. So yes, it could be a fixed time like 1:00 AM to 1:00 PM or even a bit smaller of a window, like 1:00 AM to 11:00 AM, would work okay. It just needs the last 12 hours or whatever so that it is only dealing with the current (the ones for today) pickups and dropoffs and not all of them which is the way it now works.

Again, the following is what I am using now.

=([SumOfDO]-[SumOfPU])

Then I use conditional formatting for the color. Equal to "0" it is green and equal to or greater than "-1" it is red.

Thanks.

 

[dhookom]

We didn't have a clue what you were comparing the current date/time to until you finally posted the name of the field [DDate].

To SUM the number of Pickups in the last 12 hours, your expression would be:

=Sum( Abs((Now()-[DDate]) < 0.5) * [Pickups])

Based on that expression, you should be able to figure out the number of DropOffs in the previous 12 hours and then be able to calculate the difference.


Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

Thanks, but with applicable records in the last 12 hours in the table the text box with that expression in it is blank.

 

[dhookom]

Please provide the record source SQL view of your form.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

I'm not sure what that is. The record source for the form is a table named "DataTable". Thanks

 

[dhookom]

Create a query using the table and add all the necessary fields plus a new calculated column:

IsRecent: Abs((Now()-[DDate]) < 0.5)

Records with a DDate value within the last 12 hours should have a 1 while all others would have 0.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

That works fine. So use criteria like 'Not "0"' to get just the recent ones and go from there? Thanks.

 

[dhookom]

You should be able to use a text box in your form footer or header with a control source like:

=Sum(IsRecent * [Pickups])

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

Okay, thanks, it's working great. Now the final part is the conditional formatting. I used the "Dropoffs" field and set it to not equal to "[Pickups]" but it does not work. Thanks again.

 

[dhookom]

does not work" does not work for me. I can't see your screen to see your results. I don't know where you set the conditional formatting and I don't know the exact expression you used.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

I put this "Field Value Is not equal to Sum([IsRecent]*[Pickups])" in conditional formatting in the Dropoffs text box and set the text to red but is stays black. Thanks.

 

[dhookom]

What section is the control in that you are attempting to format. I thought we were assuming this was in the header or footer section. If so, you wouldn't be using Pickups.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

Duane:

I have this working. Thank you very much for all your help.

xmeb