Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Driving a query in a specifc month (Access 2000)

    I have created a database that I wish to have more automated. At the beginning of each month I run different queries and such for the previous month. All my data is being driven from a payroll database (not Access). I have several particular queries derived that I have to go into the design view and change the between criteria to pull for the month before. Example: between 10/1/02 and 10/31/02. Is it possible to have this automated somehow which would eliminate me from continually going into the design view and change the criteria every month. My ultimate goal is to get all the queries to run without going into the design view and changing criteria each month. Often times I miss one fields criteria which botchs the whole query. Any recommendations or suggestions.

    Thank you,
    Kari Perreault

  2. #2
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving a query in a specifc month (Access 2000)

    In the query design view place the following in the 'Criteria' line under the appropriate field(s):
    Between [Enter begin date] and [Enter end date]

    This will produce a prompt when you run the query to enter appropriate dates.

    HTH
    Michael

  3. #3
    Lounger
    Join Date
    Feb 2001
    Location
    Torn between Kent & Essex, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving a query in a specifc month (Access 2000)

    If you build a simple form with a couple of text boxes for the between dates and a button to run the query.
    In the On Click Event for the button use something like

    Private Sub cmdRunQry_Click()

    Dim rst1 As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT tblTest1.*" & Chr(13) & _
    "FROM tblTest1" & Chr(13) & _
    "WHERE (((tblTest1.fldDate1) > " & Me![txtDate1] & ") And " & _
    "((tblTest1.fldDate1) < " & Me![txtDate2] & "))" & Chr(13) & _
    "ORDER BY tblTest1.fldID;"

    Set rst1 = CurrentDb.CreateQueryDef("qryTemp", strSQL)


    End Sub


    HTH
    Gavin

  4. #4
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving a query in a specifc month (Access 2000)

    Thanks for the tip but this is the error I received: The Microsoft Jet database engine does not recognize "[Enter begin date] as a valid field name or expression.

    I'm stumped. Any other ideas??

    Thanks, Kari Perreault

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Driving a query in a specifc month (Access 2000)

    Why don't you post the SQL of the query.
    This way we can often offer help a little faster if we can see what you are doing.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving a query in a specifc month (Access 2000)

    TRANSFORM Sum(REPORTS_V_CHK_VW_HOURS.CHECKVIEWHOURSAMT) AS SumOfCHECKVIEWHOURSAMT
    SELECT REPORTS_V_CHK_VW_HOURS.[FILE#], REPORTS_V_CHK_VW_HOURS.NAME, Sum(REPORTS_V_CHK_VW_HOURS.CHECKVIEWHOURSAMT) AS TOTALS
    FROM REPORTS_V_CHK_VW_HOURS
    WHERE (((REPORTS_V_CHK_VW_HOURS.CHECKVIEWPAYDATE) Between Now()-31 And Date()) AND ((REPORTS_V_CHK_VW_HOURS.CHECKVIEWHOURSCODE)="S"))
    GROUP BY REPORTS_V_CHK_VW_HOURS.[FILE#], REPORTS_V_CHK_VW_HOURS.NAME
    ORDER BY REPORTS_V_CHK_VW_HOURS.NAME, REPORTS_V_CHK_VW_HOURS.CHECKVIEWPAYDATE
    PIVOT REPORTS_V_CHK_VW_HOURS.CHECKVIEWPAYDATE;

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Driving a query in a specifc month (Access 2000)

    Does this query work? It looks as though it should although I would change the Now()-31 to Date()-31.
    Try defining Parameters for the [Enter Start Date] and [Enter End Date] and see if that works.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving a query in a specifc month (Access 2000)

    Can you please elaborate on defining parameters? This query does work with the criteria that I currently have set but want to put criteria that you specified.

    HELP???

    Thanks, Kari p.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Driving a query in a specifc month (Access 2000)

    You define parameters by the following line as the first line of the query:
    PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
    Then I believe that you can use the [Enter Start Date] and [Enter End Date] as your required criteria.

    So your query becomes:
    PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
    TRANSFORM Sum(REPORTS_V_CHK_VW_HOURS.CHECKVIEWHOURSAMT) AS SumOfCHECKVIEWHOURSAMT
    SELECT REPORTS_V_CHK_VW_HOURS.[FILE#], REPORTS_V_CHK_VW_HOURS.NAME, Sum(REPORTS_V_CHK_VW_HOURS.CHECKVIEWHOURSAMT) AS TOTALS
    FROM REPORTS_V_CHK_VW_HOURS
    WHERE (((REPORTS_V_CHK_VW_HOURS.CHECKVIEWPAYDATE) Between [Enter Start Date] And [Enter End Date]) AND ((REPORTS_V_CHK_VW_HOURS.CHECKVIEWHOURSCODE)="S"))
    GROUP BY REPORTS_V_CHK_VW_HOURS.[FILE#], REPORTS_V_CHK_VW_HOURS.NAME
    ORDER BY REPORTS_V_CHK_VW_HOURS.NAME, REPORTS_V_CHK_VW_HOURS.CHECKVIEWPAYDATE
    PIVOT REPORTS_V_CHK_VW_HOURS.CHECKVIEWPAYDATE;

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving a query in a specifc month (Access 2000)

    Oh, Thank you, thank you! This worked perfectly. I have another problem with a query in this database. As you will notice in the SQL, I have a calculated field called P-BALANCE (stands for previous balance). I always have to go into the calculated field and change the date it refers to. How would you suggest to automate it? THANK YOU!

    SQL as follows:
    PARAMETERS [Enter month to view (numeric)] Value;
    SELECT [Non Exempt Employees].[FILE#], [Non Exempt Employees].[NAME] AS EMPLOYEE, [Non Exempt Employees].[SENIORITYDATE] AS [S-DATE], IIf([TERMINATIONDATE]>90 And [STATUS]="T",[terminationdate]) AS [T-DATE], [Non Exempt Employees].[STATUS], IIf(DatePart("m",[Sick AdjQuery].[date])=[Enter month to view (numeric)],[sumofhours]) AS ACCRUED, IIf([balance query].[9/20/2002] Is Null,0,[balance query].[9/20/2002]) AS [P-BALANCE], IIf([BenType]=4,nz([BenUsed Sick].[Hours])+nz([Totals]),[Totals]) AS USED, nz([P-BALANCE],0)+nz([ACCRUED],0)-nz([USED],0) AS [E-BALANCE], [E-BALANCE]*[RATE1AMT] AS TOTAL
    FROM ((([Non Exempt Employees] LEFT JOIN [Balance Query] ON [Non Exempt Employees].[FILE#]=[Balance Query].[FILE#]) LEFT JOIN [Sick Leave Used] ON [Non Exempt Employees].[FILE#]=[Sick Leave Used].[FILE#]) LEFT JOIN [Sick AdjQuery] ON [Non Exempt Employees].[FILE#]=[Sick AdjQuery].[FILE#]) LEFT JOIN [BenUsed Sick] ON [Non Exempt Employees].[FILE#]=[BenUsed Sick].[FILE#]
    WHERE ((([Non Exempt Employees].[FILE#]) Not Like 95587) And (([Balance Query].[BENACRPOSNUM])="4"));

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Driving a query in a specifc month (Access 2000)

    Why don't you post the query called [Balance Query], and we can see what you are trying to do. You would need some kind of label substitution.
    Where do you use the query you have shown, in VBA code somewhere?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving a query in a specifc month (Access 2000)

    This calculated field is pulling the balance from the prior month. Is this what you needed to be able to steer me even further in the right direction? What do you mean by a label substitution?

    Thanks,
    Kari Perreault <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    TRANSFORM Sum([BENEFIT BALANCES].BALANCE) AS SumOfBALANCE
    SELECT [BENEFIT BALANCES].[FILE#], [BENEFIT BALANCES].NAME, [BENEFIT BALANCES].BENACRPOSNUM
    FROM [BENEFIT BALANCES]
    WHERE ((([BENEFIT BALANCES].STATUS)="A"))
    GROUP BY [BENEFIT BALANCES].[FILE#], [BENEFIT BALANCES].NAME, [BENEFIT BALANCES].BENACRPOSNUM
    ORDER BY [BENEFIT BALANCES].NAME
    PIVOT [BENEFIT BALANCES].DATE;

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Driving a query in a specifc month (Access 2000)

    How many date columns does this query return?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  14. #14
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving a query in a specifc month (Access 2000)

    It has 11 columns. It started with the end of December 2001 and has a balance in a separate column for each month thereafter, ending with October 2002

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Driving a query in a specifc month (Access 2000)

    Is the P-Balance field always the second last column in the query?
    Where do you use these queries? Are they used as part of a report?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Page 1 of 3 123 LastLast

Posting Permissions

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