Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: SQL Help (for use in MS Access FE DB)...

    Ok, so I'm not really using SQL to do this, rather it's an MS Access front-end on a database that will soon be having an SQL back-end but this is my issue...

    On the MS Access front-end, I have a form where the user can select a country to run statistics for. The user selects a country from a combo-box which is populated by tblCountry. THEN the user can select a pre-determined number of days from a combo-box populated by tblDays (30,60,90,120,180 calendar days) OR they can select certain dates between using two unbound text boxes for "StartDate" and "EndDate" BUT if the user DOES NOT select either of these, it will need to default to 1-year PRIOR to the current date.

    The results will be viewed in a Pivot Chart / Bar Graph.

    I don't know what type of statement would be needed for this NOR do I know how to go about doing a criteria statement of this magnitude.

    All this would search back through the tblPassport and count the PassportID (PK) issued from the country during the dates criteria which is selected.

    Any ideas?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am not sure what the question is but I would try to ask it in the Access forum.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by Thrasymachus
    I am not sure what the question is but I would try to ask it in the Access forum.
    I tried and so far haven't had anyone that could help with the SQL statement that I need so I figured I'd post it in the SQL forum to see if anyone knew what I needed to do in order to get it started.

    I think it's either an "If, Then" or "Case" SELECT statement but can't figure out how to develop it or even where to start.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Grafixx01
    need to default to 1-year PRIOR to the current date
    Look at the DateAdd() function.
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Grafixx01
    Ok, so I'm not really using SQL to do this, rather it's an MS Access front-end on a database that will soon be having an SQL back-end but this is my issue...

    On the MS Access front-end, I have a form where the user can select a country to run statistics for. The user selects a country from a combo-box which is populated by tblCountry. THEN the user can select a pre-determined number of days from a combo-box populated by tblDays (30,60,90,120,180 calendar days) OR they can select certain dates between using two unbound text boxes for "StartDate" and "EndDate" BUT if the user DOES NOT select either of these, it will need to default to 1-year PRIOR to the current date.
    All the working out up to here should be done in Access (i.e. work out the start and end date in the front end based on your rules).

    After that you need to decide if you are accessing the table directly or procedures, and if you are connecting using ADO, a pass through query or linked tables (the answer to the first question affects what options are available for the second question). This also affects whether or not you write Access SQL or T-SQL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pootle flump
    All the working out up to here should be done in Access (i.e. work out the start and end date in the front end based on your rules).

    After that you need to decide if you are accessing the table directly or procedures, and if you are connecting using ADO, a pass through query or linked tables (the answer to the first question affects what options are available for the second question). This also affects whether or not you write Access SQL or T-SQL.
    Thanks pootle, I guess I have to think about this one huh? Its not something that I want in the DB but the user wants it so I guess I'll have to do it anyways.

    I did the table design, form design and whatnot already in MS Access. I added a command button to the form for "Get Results" which is what I'm going to have the query run and then display the results in a subform - Pivot Table.

    The tables will be linked because it'll be split fe-be DB, as well as being linked through relationships to one another.



    georgev,
    I thought of that too but I figure with the conditions that it'd be nearly impossible.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Grafixx01
    I thought of that too but I figure with the conditions that it'd be nearly impossible.
    Care to explain the conditions in full then please?
    George
    Home | Blog

  8. #8
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by georgev
    Care to explain the conditions in full then please?
    Sorry, I thought that you meant to use the DateAdd() function on ALL of my possibilities in this situation, cause I figured it'd be something like:

    DateAdd( "d", -xxx, Now()) *or something like that as I'm not too familiar with the DateAdd function, for ALL of the select X-number of days prior to today.
    DateAdd( "d", -365, Now()) *for 1 year prior as default
    And then the between dates XYZ and ZYX

    Oh well, I'm just lost on even where to start cause I don't know if this should be like an "if, then, else" or a "case statement" query...

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You've actually got it all sorted in English.

    Use your dateadd code in VBA. Assign the two values to hidden text boxes on a form. Then execute:
    Code:
    SELECT col_1, col_2 FROM myTable WHERE myDate BETWEEN Forms!myForm!start_date AND Forms!myForm!end_date
    Also, look up the "y" parameter for dataadd and use DATE() not NOW(). If you aren't familiar with the function - look it up
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - if you are using a linked SQL Server then you interact with the tables exactly as you would if they were Access\ JET tables. So - you don't need to over think stuff.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pootle flump
    You've actually got it all sorted in English.

    Use your dateadd code in VBA. Assign the two values to hidden text boxes on a form. Then execute:
    Code:
    SELECT col_1, col_2 FROM myTable WHERE myDate BETWEEN Forms!myForm!start_date AND Forms!myForm!end_date
    Also, look up the "y" parameter for dataadd and use DATE() not NOW(). If you aren't familiar with the function - look it up
    pootle,

    I thank you for your help! I'm going to look further into it.

    I've designed the form already with a combobox that holds the "Country" the user selects to search by...

    Then it's got another combobox where they can select the 30,60,90,120,180 days OR the BETWEEN dates OR default to 1-year prior to Now().

    So I guess I'll try an If, Then, Else since that makes the most sense to me because it would be like

    SELECT tblPassport.PassportID, tblPassport.IndID, tblCountry.CountryID, tblCountryNM FROM tblPassport WHERE ...
    =countryname from combobox ON
    CONDITION=
    1. = number of days from preselected combobox days OR
    2. = Date BETWEEN OR
    3. = DateAdd ( "d", -365, Now())
    ORDER BY IndID

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No no no!

    That's the point we keep trying to make. The SQL does not need to do all this work. It just needs a start date and an end date. The where clause of your query should vary very little from mine.
    Work out all the complicated stuff in VBA, keep your SQL nice and clean.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, I've moved to Access since this has nowt to do with SQL Server.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •