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

    SQL Statement in VBA to select based on 2 matching values AND a date range?


    Hi, all!

    I'm still struggling with SQL statements, and I'm having particular trouble with a series of them that I have to write for a module now. The basic gist:

    I have a set of date variables; one is a user-entered date, and the other three are 12, 24, and 36 months back from that date. What I'm trying to do is to write the SQL statements into this module to go back and pull financial data where 2 identifying numbers on the form match the identifiers in the table, but only for the time period between the user-entered date and Xmonth variables.

    Can anyone tell me or point me in the direction of what the correct syntax would be for making sure those 2 identifiers match AND pulling only the data in the correct date range?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,025
    A little more detail please, regarding

    financial data where 2 identifying numbers on the form match the identifiers in the table, but only for the time period between the user-entered date and Xmonth variables.
    seems the sql would be along these lines:

    SELECT fld1, fld2, DateFieldInTable, otherFields from yourTable
    WHERE
    yourTable.fld1 = me.Number1OnForm AND
    yourTable.fld2 = me.Number2OnForm AND
    yourTable.DateFieldInTable BETWEEN Me.DateEntered AND (Me.DateEntered - 12)

  3. #3
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Sorry! The two identifying numbers would be a policy number, and an account number; it has to be both, too, because there can be multiple accounts per policy, and multiple policies per account. The "Xmonth" there was just a stand-in for the 12, 24, and 36 month intervals. I've got it set up so that the user enters the date they want to go back FROM, and then the module will take that, subtract 12, 24, and 36 months from it, and store each result in a variable. From there, the SQL is intended to compare the policy number and account number on the form to the policy and account numbers in the relevant table, and then only bring back the records that exist from the start date back to the 12, 24, and 36 month prior dates (I realize that will be 3 separate SQL statements).

    Thanks, Orange! I'll try that.

  4. #4
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Crap, I totally forgot to factor in that I need that to return the SUM of numbers that it pulls.

    That's what I get for trying to do this before sufficient coffee...

  5. #5
    fluppe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    48
    ... 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.

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

    Yeah, I mean to run 3 separate queries for the different time ranges. In reality, there's going to end up being a lot of them, because I have to do 12/24/36 months for 3 different figures.

    I haven't marked solved yet, since I am still writing all of these, and then have to test them, but I'll mark it when I know I haven't bungled it. LOL

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,025
    You could add a Sum(...) and Group By etc to the SQL as appropriate. The key is to know exactly WHAT it is you're trying to accomplish.

    see: Aggregate queries SQL

  8. #8
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Still trying to get this just right. I have my SQL statements like this, at the moment:
    Code:
         L12W = "SELECT Sum(tblActPrem.APWrit) AS SumOfAPWrit FROM tblActPrem" & _
         "WHERE tblActPrem.EntID = Me.ctlEntID AND tblActPrem.PolNum = Me.ctltblRnwlTrack_PolNum" & _
         "AND tblActPrem.APDate" & BETWEEN startDate AND L12M"
    I'm reading that I need to be concatenating these variables, but again, I find myself struggling with the proper syntax. Every result I get in Google seems to have a different way of doing it, and none of them are working on mine. "startDate" and "L12M" are the date variables, with "startDate" being the date the user enters, and "L12M" being that date - 12 months.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,025
    Well your SQL will have invalid syntax when rendered. You need a space before the WHERE and the AND.

    You should do a debug.print L12W, which will print the rendered sql in the immediate window. If you don't see the error there,
    then copy the printed sql and paste it into the sql of the query wizard and try to execute the query.

    You still haven't given the full and latest description of WHAT you're trying to do in plain English.

    But I'll take a guess (totally untested)

    Code:
    L12W = "SELECT Sum(tblActPrem.APWrit) AS SumOfAPWrit FROM tblActPrem " & _
         " WHERE tblActPrem.EntID = " & Me.ctlEntID & " AND tblActPrem.PolNum = " &  Me.ctltblRnwlTrack_PolNum  & _
         " AND tblActPrem.APDate BETWEEN " & Me.startDate & " AND " & Me.L12M & ";"""
    Note the Me. values refer to controls on form or report, so they can not be with the quotes. If they are within quotes , they are just text. Outside of quotes they have a value that "Access" will calculate/fill.

    Good luck.

  10. #10
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Oh, sorry about that! I'll try:

    The form I'm working on is meant to gather data on an account and its insurance policy, and present it for renewal analysis. I want the user to enter a date (MM/YYYY) that the analysis will run backwards from, click a button, and have it fill in "written premium", "earned premium", and "net loss" for 12, 24, and 36 months back from that date, which it should do via the SQL statements; for each one, it needs to go back to the relevant table, total up all $$ figures for this particular account&policy between the user entered date, and the 12/24/36month variable date, and fill the appropriate fields on the form (9, in all). From there, there are calculations and comparisons I need to write code for too, but I think I'll be able to handle that, if I can just pry the data out of the tables.

    Does that help?

    And thank you! I will give this a try.

  11. #11
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    I'm hesitant to write this, but...I can't get a debug.print to work on this. Nothing will print to the Immediate Window; I just get a type mismatch error.

    Please keep in mind I have NULL in the way of formal training for any of this. I'm learning as I go, on a project I sort of got Rick-rolled into. I'm sorry I'm terrible.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,025
    Can you post a copy of the database? Remove anything confidential or private. You should do a compact and repair first, then create a zip.

    Show us code where you get the error.

  13. #13
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    It's a little embarassing to have pros look at it, because I know it's a mess.

    The form I'm struggling with at the moment is frmRnwAnalysis.

    Please keep in mind that the extent of my training is books and the internet since just this last Summer, and this is literally the first database I've ever built.

    Database Copy.zip

  14. #14
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    I'd also like it known that over 95% of the data is just goofy dummy data that I'm using to test things; we won't be putting the real data in until closer to completion. So if it looks like it's full of nonsense...it is.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,025
    How do I get a StartDate into your form. seems it always wants to be 1899??
    Also your fields
    tblActPrem.EntID and tblActPrem.PolNum are text data type so you need to surround such values with quotes.

    Why is APDate only MM/YYYY???? It will affect Date processing, in my view. Can these be first day of month? or LastDay of Month?
    A Date/Time datatype is designed for dates, not part dates.

    I'm still failing on L12W match

Page 1 of 2 12 LastLast
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 - Senior Forums