I'm sorry, it's a MM/YYYY format. When I try to run it, it always changes to 1899 too.
I will try adding the quotes!
Thank you so much for taking a look at it. And for not telling me how awful it is.
I'm sorry, it's a MM/YYYY format. When I try to run it, it always changes to 1899 too.
I will try adding the quotes!
Thank you so much for taking a look at it. And for not telling me how awful it is.
No, don't change anything at the moment.
I just found something I was not aware of ( or have forgotten over time).
Both of these sql statements work and give the same result. So, if you don't include a Day in Date, seems it is assuming First of month.
So what I've done after getting the mismatch error and an 1899 date is to take the sql and make a query with values I control.'Code:SELECT Sum(tblActPrem.APWrit) AS SumOfAPWrit FROM tblActPrem WHERE tblActPrem.EntID = '1235' AND tblActPrem.PolNum = 'Policy1' AND #01/09/2013# BETWEEN #01/01/2013# AND #01/01/2014# 2091777 SELECT Sum(tblActPrem.APWrit) AS SumOfAPWrit FROM tblActPrem WHERE tblActPrem.EntID = '1235' AND tblActPrem.PolNum = 'Policy1' AND #09/2013# BETWEEN #01/01/2013# AND #01/01/2014# 2091777
Now the question is, How to make sure we get the correct number via the form.
I don't know your form or insurance etc, so any specifics you can provide would be great.
I think I'm following you, but please forgive me if I'm off.
"ctltblRnwlTrack_PolNum" would be a good place to match PolNum to.
"ctlActEntID" would have the EntID.
Would I do those as variables?
I am behind on the 3 dates, though. Are the last 2 set in stone? If it helps, the way we analyze this data is:
Smallest portions are months. When we look at an upcoming renewal, we go back and analyze the last 12 months of premium/loss data as one, the last 24 months of premium/loss as another, and finally the last 36 months premium/loss. What I'm trying to achieve in the form is to have us give the start date to go back from, and then get those 12 month, 24 month, and 36 month chunks of each kind of data, and do some calculations with them. It's all real estate, so the numbers can range into the millions.
Again, thank you SO much for all your help and patience.
I wouldn't change anything just yet.
I haven't been able to identify the match problem. That's what I'm focusing on at the moment.
I select a Policy, I used 01/01/2014 at the startdate -- I'd like to get past the mismatch before any changes.
I have reduced the sql statement to the followiing:
L12W = "SELECT Sum(tblActPrem.APWrit) as SumOfAPWrit from tblActPrem;" and I'm still getting a mismatch error.
The only field is tblActPrem.APWrit which is defined as a Long Integer. It shows a format $#,##0.00;($#,##0.00)
and Dim L12W As Currency 'Last 12 months written premium
in the code may be the issue.
Long Integers don't have decimal places, but Currency has 2 digits. ?????
How carved in stone is the datatype?
UPDATE:
I changed to Variant here ( trying to let Access decide what the match should be)
Dim L12W As Variant 'Currency 'Last 12 months written premium
and got rid of the mismatch.
Here is the code and sql related to L12W
Here is the result of Debug.Print L12WCode:L12W = "SELECT Sum(tblActPrem.APWrit) AS SumOfAPWrit FROM tblActPrem " _ & " WHERE tblActPrem.EntID = '" & Me.ctlActEntID & "' " _ & " AND tblActPrem.PolNum = '" & Me.ctltblRnwlTrack_PolNum & "'" _ & " AND tblActPrem.APDate BETWEEN #" & L12M & "# AND #" & Me.ctlRnwAnalysisDt & "#;"""
Code:SELECT Sum(tblActPrem.APWrit) AS SumOfAPWrit FROM tblActPrem WHERE tblActPrem.EntID = '354651' AND tblActPrem.PolNum = 'Policy2' AND tblActPrem.APDate BETWEEN #01/01/2013# AND #01/01/2014#;"
Thank you, Orange!!! You are a genius!
I feel like such a newb; I totally forgot that I could select "Currency" as a field type. I just start to type in the field types as I'm making tables so that they fill on their own, and I guess I forgot that one existed. I've changed them now, because that's what they really do need to be.
And I'm getting a Debug.Print that mataches yours!
The only problem now though, is that I'm getting "Run-Time error '-2147352567 (80020009): The value you entered isn't valid for this field." when I run the analysis.
SQL makes me want to set things on fire...
You should become familiar with these debugging tips.
Good luck.
Thanks, Orange!
Fluppe, I think I'm starting to understand what you meant here. The only value I can get it to return is the SQL statement itself, so I'm thinking this is how I get the actual value out of it, instead?... solved ??
otherwise:
define a query:
... [SQL] is your SQL statement, just as posted by OrangeCode:Set qry = CurrentDb.CreateQueryDef("", [SQL])
run the query
loop through tbl to add your nnumbers ...Code:Set tbl = qry.OpenRecordset(dbOpenDynaset)
You might do it three times to get three numbers, as "sum from date - 12month up to date", "sum from date - 24 to date" , ... i think it is what you intended.Code:Do Until tbl.EOF ... sum ... however tbl.MoveNext Loop
I think it is not possible, to make a SQL statement return three integers directly.
Are "qry" and "tbl" meant to be variables, or the names of particular objects? And in that third section, how would that fit in?
Sorry to ask so many questions, but on top of still being pretty new to Access and programming, this is the first I'm doing anything with SQL statements of this complexity.