Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156

    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.

  2. #17
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.
    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
    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.'
    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.

  3. #18
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    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.

  4. #19
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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

    Code:
    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 & "#;"""
    Here is the result of Debug.Print L12W

    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#;"

  5. #20
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    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...

  6. #21
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You should become familiar with these debugging tips.

    Good luck.

  7. #22
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Thanks, Orange!

  8. #23
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Quote Originally Posted by fluppe View Post
    ... solved ??

    otherwise:

    define a query:

    Code:
    Set qry = CurrentDb.CreateQueryDef("", [SQL])
    ... [SQL] is your SQL statement, just as posted by Orange
    run the query
    Code:
    Set tbl = qry.OpenRecordset(dbOpenDynaset)
    loop through tbl to add your nnumbers ...

    Code:
    Do Until tbl.EOF
    
    ... sum ... however
    tbl.MoveNext
    Loop
    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.
    I think it is not possible, to make a SQL statement return three integers directly.
    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?

    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.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 07-29-2014, 11:20 AM
  2. Matching date values .. in query
    By edmscan in forum Queries
    Replies: 3
    Last Post: 07-22-2014, 09:45 AM
  3. Replies: 3
    Last Post: 07-19-2012, 06:51 AM
  4. Select Date Range
    By dr4ke in forum Queries
    Replies: 8
    Last Post: 06-25-2012, 07:04 AM
  5. SELECT only this DATE RANGE (Pic Attached)
    By taimysho0 in forum Programming
    Replies: 1
    Last Post: 05-30-2012, 01:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums