How do pull records based on input and input-1

[Russellh]

I need to be able to query the service dates for the bill. So I can query 1509 for the .billYYMM and .readdate and it will give me the TO Date. How do I in the same query tell the query to subtract 1 from the BillYYMM that I just entered and give me the read date for that field in that query?
So Query 1 MBRHISTDETL.BILLMOYR and in the criteria box use [Enter the Bill Month Year YYMM you want to search for] will allow me to run the query at any time for any bill month year that I enter. So another field in that query is MBRHISTDETL.READDATE.


So to get the FROM date and the TO date i need the following MRBHISTDETL.BILLMOYR (whatever I enter as input) and MBRHISTDETL.READDATE that corresponds and then I need [MRBHISTDETL.BILLMOYR]-1 and MBRHISTDETL.READDATE. The first gives me the TO date and the 2nd gives me the FROM date?

Any idea on how to begin that?

[June7]

Is this what you want http://allenbrowne.com/subquery-01.html#AnotherRecord

[Russellh]

Is this what you want http://allenbrowne.com/subquery-01.html#AnotherRecord

The get another value for a field in a different record on the same table is the right way to go. I'm not familiar enough with SQL statements to translate that into what I need. But I need 2 read dates. One would be the read date with BILLMOYR of 1509 (entered from user input) and the READ DATE field that would show on the record and then the read date field that would show if I entered the BILLMOYR -1 or 1508 for the same account number.

[June7]

Is it a certainty that the BILLMOYR value will be sequential, no gaps, so that -1 will always work (exception would be the very first record for each account).

If all you want is the value to show up on a form for the current record, a DLookup() in textbox ControlSource could accomplish that.

DLookup("ReadDate", "MBRHISTDETL", "BILLMOYR=" & Me.tbxBILLMOYR - 1)

But domain aggregate functions can be slow performers in textbox and in queries. So if you want this output in a report, the query method is best.

Another query approach is to build query that calculates a field for the user input value.

SELECT *, BILLMOYR + 1 AS MatchMOYR FROM MBRHISTDETL;

Now build another query that joins the original dataset to this query by linking on BILLMOYR and MatchMOYR fields. Could try this second query as the form's RecordSource. I am wondering if the query will be not editable for the fields from the original dataset.

[Russellh]

Is it a certainty that the BILLMOYR value will be sequential, no gaps, so that -1 will always work (exception would be the very first record for each account).

If all you want is the value to show up on a form for the current record, a DLookup() in textbox ControlSource could accomplish that.

DLookup("ReadDate", "MBRHISTDETL", "BILLMOYR=" & Me.tbxBILLMOYR - 1)

I believe this is similar to the query inside of a query. 2 queries have already been built to just produce the last payment amount and date. I am worried that by the time I am through getting the 12 fields I need from only 2 tables I will have 10 querries stacked or linked together. That would make for a troubleshooting nightmare would it not?

But domain aggregate functions can be slow performers in textbox and in queries. So if you want this output in a report, the query method is best.

Another query approach is to build query that calculates a field for the user input value.

SELECT *, BILLMOYR + 1 AS MatchMOYR FROM MBRHISTDETL;

Now build another query that joins the original dataset to this query by linking on BILLMOYR and MatchMOYR fields. Could try this second query as the form's RecordSource. I am wondering if the query will be not editable for the fields from the original dataset.

I believe this is similar to the query inside of a query. 2 queries have already been built to just produce the last payment amount and date. I am worried that by the time I am through getting the 12 fields I need from only 2 tables I will have 10 querries stacked or linked together. That would make for a troubleshooting nightmare would it not?

[June7]

Have I misunderstood requirement? I thought you just want to get a single value from another record. Are you wanting to filter on a range?

I don't use dynamic parameterized queries. I prefer to use VBA to build filter criteria and apply to form or report either when the object opens or by setting Filter and FilterOn properties.

http://www.allenbrowne.com/ser-62.html