Recent content by lydonw

That does make sense, and I appreciate the help quite a lot Pat.

So this is what I have. I have a field that finds the previous authorization's end date PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart]) And a field that uses that end date to find the previous auth's BSTunits PrevBST...

I'm not sure why I hadn't considered doing any of this on the form itself. I had originally thought it best to do get these results in the query and use that data in the form and reports, it seems easier to just do it on the form and report separately. I'll be out of town the remainder of the...

While I do intend to use this information in a report, I primarily wanted to display the results of this comparison in a form that shows Consumer records. My company works with consumers receiving mental health services, and we need to request authorization to continue services for them every...

If it is going to result in slowness I guess I'll try finding a VBA solution, although like I said I don't know much there. While the dataset isn't huge yet, it will get there, and it is used to support a form that is itself a bit clunky (there is nothing to be done about this). So I'm back to...

"The specified field '[ConsumerID]' could refer to more than one table listed in the FROM clause of your SQL statement." I would assume this is because my query's data is sourced from three tables, tblAuthorizations, tblAuthorizationNumbers, and tblConsumers. ConsumerID is a primary key in...

I think I'll give plog's suggestion a go first, if only because I'm not very familiar with VBA.

While I've not done much with VBA you've at least given me something to search for that can hopefully lead to a solution. Appreciated. Any specific advise or direction you might be able to provide pat? You've been a huge help everywhere in this forum and I have to assume you can, in the very...

This seems to require the AuthStart days of the paired records to actually be one after another. IE: AuthStart of 1st record 8/13/13, thus AuthStart of 2nd is 8/12/13. This isn't the case, however. These AuthStart dates are generally 2-3 months apart, where the number of days varies. Further...

Is there nobody that can help with this? I can't imagine it is all that difficult but I still haven't got anything to work properly.

I need to identify pairs of records by their ID and date fields, then compare their number values to get a text result. I have a query that has the following fields AuthInstanceID, ConsumerID, AuthNumberID, AuthStart, AuthEnd, PSRUnits, BSTUnits. Sample data is below (PSRChange and BSTChange...

Another attempt to clarify my question in the hopes someone can help. Imagine there are four fields A, B, C, D I need to compare records based on the following where two records have values for A that are equal, 1st Record is most recent date B, the 2nd is the previous date B. Compare number...

Looking around I haven't found a solution, but I think I can better define what I need. I need to match each consumer's most recent authorization with their previous one(defined by same consumerID and a max AuthEnd that is less than most recent AuthStart). BSTUnits must be compared for these...

I have a table called tblAuthorizations. It appears as follows AuthInstanceID....ConsumerID....AuthNumberID....AuthStart....AuthEnd....PSRUnits....BSTUnits 1374006036.........356679..........20255102..........4/22/13......7/21/13.....0..............416...

For the form that pops up (with the on open event) the source is a table. It may be that our issues appear similar but the causes differ? DLookup is slow, especially as you get more data, but in this case I'm only using it for a singular event that is infrequent, and so it was an acceptable...