Hello! I am using Access 2010 and I have an Unbound Form (FrmFilter) that filters a report by (up to) 7 criteria using the code below, which works beautifully. I would like to add 2 additional unbound date fields to filter by as well to see only the specified date range in the report:
Start & End Date, both filtering the field "dteAuditDa te".
These would be Filter8 & Filter9.
I've tried adding to...
LBinGA
Last Activity: Nov 15 '13, 07:55 PM
Joined: Feb 25 '10
Location: Atlanta, GA
-
MS Access Filter Report by Selection (Add Date Range Fields)
-
I believe I've figured this out.
I had a SetValue Macro running to calcuate totals on fields that I was using VBA to calculate data. Anyway, I removed all the SetValue references, moved all the calculations into VBA and I can use the Unbound form to open the records form and make edits now.
After only 5 or so hours....sheesh .
Thought I'd post the solution to my problem in case anyone else had this.... -
Ok, I asked the underwriter to help me out with getting the calculation across and here is what we've come up with:
In letters:
G=((A/B)xD) x F
In field names:
UmbPrem=((UL Prem / UL Mod) x Rate) x Umb Mod
God, I hope that helps, Pat..... ?Leave a comment:
-
Hi Pat:
Ok, I'm sorry I'm not explaining it properly. The formula would be exactly how you've written it:
...Code:[B]A[/B]*[B]B[/B]=C*D=E*[B]F[/B]=G A * B = C * D = E * F = G U/L Prem1 * U/L Mod1 = U/L Man1*rate1 = Man XS1*Umb Mod1 = Umb Prem 1 U/L Prem2 * U/L Mod2 = U/L Man2*rate2 = Man XS2*Umb Mod2 = Umb Prem 2 U/L Prem3 * U/L Mod3 = U/L Man3*rate3 = Man XS3*Umb Mod3 = Umb Prem 3Last edited by Frinavale; Mar 25 '10, 06:49 PM. Reason: Please post code in [code] ... [/code] tags. Added code tags.Leave a comment:
-
Update or CancelUpdate without AddNew or Edit
I have an Unbound Menu Form that brings up all the records in the db by name after the user types into a Parameter window. The record Form ("Special Acceptance Form Letter") is launched when the User Double Clicks their selection. I've used this form before on TONS of db's, however, on this particular db I'm getting the Update or CancelUpdate without AddNew or Edit error after the record Form is opened and I try to move from one record... -
Ok, Pat, I think I see. I did F8.
The first line it breaks on is:
rate1 = CDec(DLookup("[fldRate]", "tblRates", " [fldDateLower] <= #" & dteEffectiveDat e & "# AND [fldDateUpper] >= #" & dteEffectiveDat e & "# AND [fldLevel] = 1"))
I can't get it to move past that.
Also, my question on the CDate line
dteEffectiveDat e = CDate(Me.[Effective...Leave a comment:
-
Once again, thank you, Pat. Sorry this is taking me so long to test out. They've had me working on another project mean time....sigh.
Ok, I added the code to cmdCalculatePre m's On Click event. I'm getting the error:
Run-time error: 3464: Data type mismatch in criteria expression.
There are a couple of questions I have regarding it, in my effort to understand what it is we're doing:
1. Why do you use...Leave a comment:
-
Hiya: Sorry for the late response. Been snowing like crazy in GA and everyone loses their heads!
I've tried the above and to be honest, it's a bit over my head, but I'm trying.
The fields: [U/L Mod 1] and [Umb Mod 1] are User entered fields that will be used to do further calculations to obtain a final [Prem/Ops Umbrella Prem 1] at the end of the calculation string. Keeping in mind that the "1" designation...Leave a comment:
-
Hmmm...now that's a very good question. Before, the User only need enter information in the first field ([U/L Prem]) of the level, each indicated meerly by a Label named 1, 2 or 3.
Those fields for each level are actually indicated as:
1 [U/L Prem 1] * [U/L Mod 1]=[U/L Man 1] *.09=[Man XS 1]*[Umb Mod 1]=[Umb Prem 1]
and 2 and 3 the same way. Using the If/Then statement, I repeated it 3 times, one time each...Leave a comment:
-
Zepp,
I've copied the code over you generously suggested (changed it to included the = sign), added it to BeforeUpdate on the Form, made a tblRates and inserted the dates and rates for the level 1 (Prem/Ops Man Prem 1).
I'm not sure I understand how the one Rate Table will address each level. Will I need a Table for each level.
The Prem/Ops Form is designed like this:
1 [U/L Prem] * [U/L Mod]=[U/L...Leave a comment:
-
Thank you, Zep! I'm testing it out this morning and I'll let you know what comes of it. Hopefully I can wrap my mind around it with enough coffee.
The If/Then code works great now but as you might guess, I have numerous premiums to apply it too and if I could have just one Tbl per set of rates that need to change annually, that would be ideal.
:D
LB in GALeave a comment:
-
Thanks, Smiley. Don't know why I wouldn't have tried the # signs. Duh.
I would, I'm sure, prefer to store the rates & dates in a Table to call by DLookup, but I don't know how to do that. This was my "baby-step" effort at resolving the issue.
I have an Access Inside Out book, a few other reference books and the internet to search. Any suggestion on where to start or what to google to get an idea of...Leave a comment:
-
OMG! I can't believe it was that simple! I have been working on this ALL DAY, trying quotation marks, 2-digit years, 4-digit years, and standing on my head in the corner for the last hour, none of which worked save to give me a bigger headache.
Didn't realize I would have to mimic a Query.
Thanks, Pat!
Now, would you recommend I continue to use the IIf Statement over the Select Case going forward?...Leave a comment:
-
Nested IIF Statement (and Select Case) with Date Ranges
Hello all:
I'm having trouble with a nested If Statement working properly. I'm working in MS Access 2002 (OS Windows XP, ver 2002). The non-working code is going on the Form (a Single) as follows:
...Code:Dim Effective_Date As Date If Me.Effective_Date >= 1/1/10 And Me.Effective_Date <= 12/31/10 Then [Prem/Ops Manual XS 1] = ([Prem/Ops U/L Manual 1] * 0.09) [Prem/Ops Umbrella Prem 1] = [Prem/Ops Manual XS
No activity results to display
Leave a comment: