How do I get nearest start date and nearest end date?

[Rebel]

I have a table that archives hour meter readings for my equipment. You sharp folks here helped me set it up and it works great. Thanks! I am at the point now where I want to do some studies and calculations on this data. The first project I’m working on is equipment utilization over a period of time. I have all my calculations figured out, all I need is to pull the proper data out and run it through. And that’s where I’m a little stuck.

My table is just three fields: tblHourMeterArchive = eqnum, readingdate, meterreading. I want to be able to input a StartDate and EndDate and have the query choose the archive date that is nearest to the input date and the meter reading on those dates. For example, I have meterreading dates of 1/9/05, 1/30/05, 6/5/05, and 6/19/05. I input 1/18/05 as the StartDate and 6/16/05 for the EndDate, the query should return the archive dates of 1/9/05 and 6/19/05. How would I go about doing this?

I’ve done some research on “nearest date”, but most of the information has been about doing it in VB and SQL. Can this be done without going into coding? I’m still very much a novice when it comes to VB code. Ultimately, I would like to have this work with a form and calendar controls. I’ve been studying some of the examples and suggestions for the calendars, but I’m not really sure how I would go about setting up a query in code and linking it all together.

As always, any help and/or links to information you could provide would be greatly appreciated.

 

[Rebel]

Is this something that can't be done? Am I wording my question incorrectly?

 

[namliam]

Dont expect an answer within a couple of hours come on its not like your paying us....

You could try doing something with Abs(meterreading -Startdate) and Abs(meterreading -EndDate) and take the lowest values as beeing "valid"

Things like this are "hard" to do in SQL only but doable

Hope this helps....

 

[Rebel]

Dont expect an answer within a couple of hours come on its not like your paying us....

Oh, I realize I am not paying anyone here and I am therefor extremely grateful for the help I receive, and especially for links to other sites with good information to further my Access education. If you take a second look at the dates of my two posts, you will see that I waited the requested 24+ hours. The only reason I posted the second time was because, 1) my question had dropped to the second page and I feared it would become lost and forgotten, and 2) after seeing 50-odd reads and no replies I began to fear I was asking for the impossible.

You could try doing something with Abs(meterreading -Startdate) and Abs(meterreading -EndDate) and take the lowest values as beeing "valid"

I need you to expound on this a bit, if you could please. After doing a search I see that abs() essentially drops the negative from any value, but I'm not sure how you mean to apply it here. meterreading is a general number format (like 1548.8), whereas readingdate, StartDate, and EndDate are short dates.

Maybe I'm going about this all the wrong way. Perhaps it would be better if I just made a drop-down list of all the available Archive Dates and simply picked the dates that I want to start and end that way?

I'm sorry if I seem dense in all of this. I'm new to doing advanced stuff with Access and almost completely clueless to SQL and VB, so I still don't grasp a lot of the terms and functions and such that the experienced users take for granted.

 

[stepone]

Hi there,

I think the original reply should have asked you to do Abs(readingdate -Startdate). If you do a query of your table, with the addition of a calculated field for Abs(readingdate -Startdate), then taking the minimum value of the calculated field will give you the record you are looking for (where the reading date is closest to start date.

You will need to do the same for end date.

You are going to end up with nested queries (one to find the date you are after, and another to select the reading based on that date).

And then the same again for end date.

It's possible, but maybe simpler to use your second idea, of dropping down valid dates.

StepOne

 

[Rebel]

I think the original reply should have asked you to do Abs(readingdate -Startdate).

Ahhh, ok. I understand now.

If you do a query of your table, with the addition of a calculated field for Abs(readingdate -Startdate), then taking the minimum value of the calculated field will give you the record you are looking for (where the reading date is closest to start date... You are going to end up with nested queries (one to find the date you are after, and another to select the reading based on that date).

I have an inkling as to what you're talking about here; a sort of fuzzy understanding. I would definitely need to see some examples to get a better handle on it. Unfortunately, I think that solution might be far out of skill range at the moment, so I'll have to note it and do some further study on it.

It's possible, but maybe simpler to use your second idea, of dropping down valid dates.

I figured as much, so I started working on a solution in this direction. Unfortunately, I've run into a whole new set of problems, namely that I can't seem to make a report from the crosstab query I am using to fish out the data. Whenever I try, Access is telling me there are no fields in that query to choose from.

Thanks a lot for all of your advice so far, namliam and StepOne. Boy, this little project of mine is sure turning out to be one heck of an education.

 

[namliam]

First, sorry for missing the date....

Can you put together a sample? The form (or part of it) and some little data....
I like puzzles like this one, I might just be inclined to have a look see....

 

[Ricky21]

>[Filter discharge date]![StartDischargeDate] And <[Filter discharge date]![EndDischargeDate]

Could this help just change the fields to suit yourself. Add in the query under the date field