Query for a specific date range & calculation

[hthg21]

I am trying to calculate the total hobbs time (Ending Hobbs - Starting Hobbs = Total Hobbs) based on a user inputed date range. The query that I created (see attachment) doesn't seem to give me what I'm wanting. I thought that this would be as simple as I made it, but I'm obviously missing something.

Thanks in advance for your help.

 

[pbaldy]

Is it on purpose to use times from 2 different tables? The time from the "one" side wouldn't change.

 

[plog]

What is it giving you and what are you wanting? Please provide sample data to show this.

 

[hthg21]

I should have explained that FlightHobbsStart & MaintenanceHobbsStart are fixed times and not used in the calculation I'm trying to do.

 

[pbaldy]

FlightHobbsStart is in your calculation.

 

[hthg21]

I posted the wrong pic! I'm all screwed up.

 

[pbaldy]

What are the data types of those 2 fields? If date/time, you're probably getting an accurate answer but not interpreting it correctly. You'd get a double where the integer was number of days and the decimal the remainder, where for instance .25 would be 6 hours.

 

[hthg21]

HobbStart & HobbsEnding are both Numbers-Decimal.

 

[pbaldy]

That should be straightforward then. How is the answer different than you'd expect? Can you post a sample db?

 

[hthg21]

I attached a pic of the flight table with some test data in it. I was running the query using the following N71KP=AircraftID 3 01/01/14 thru 03/01/14.

It should be 22-1.3=4.7 total hobbs & it is returning with 1.7 as the total.

Thanks for your help!

 

[pbaldy]

First off, since you don't use the second table in the query, try dropping it. Second, where are you geographically? Where I'm headed is international date settings. By 3/1 do you mean January 3rd or March 1st? I'm wondering if Access is confused.

 

[hthg21]

I need the tblAircraft so I can get the time just for a particular aircraft.

In the US, Seattle area.

 

[pbaldy]

You're not using in that query though. Is it possible to post the db or a representative sample to play with?

 

[hthg21]

Here it is and I'm up for any and all suggestions. I had to delete a bunch of the Reports & Forms, but this should do.

 

[hthg21]

As I sat here and :banghead: I realized that in my query I had the Total in the DateOfFlight set as Group By instead of Where.

Thanks pbaldy for your help!

 

[pbaldy]

Glad you got it sorted out. You should have been seeing multiple lines in your query result.

 

[hthg21]

I was only seeing one line in the query return, sometimes the most simple problems cause the most headache.