Search results

  1. A

    Average based on number of data points

    I have table which contains 6 month historical usage of items. I am trying to calcuate an average usage number for each item. The issue is that not all items have 6 months of data. So for an item that has 3 months of data I want to calculate the average such that the denominator is 3 and not 6...
  2. A

    Question Formula for 'Week Starting Date'

    Hi, I want to calculate the week starting based on a given date in an access query. The formula I am currently using is, Wk_Starting_Date: DateAdd("ww",[POA_ETA_Wk]-1,DateSerial(Year([POA ETA]),1,1))-(Weekday((DateAdd("ww",[POA_ETA_Wk]-1,DateSerial(Year([POA ETA]),1,1))),2)-1) For my...
  3. A

    Query parameters defined by variable instead of form

    Also, any idea on I can write a "Sub" so that it runs when the database is opened? For example, in the following code, Private Sub Form_Load() Dim strUser As String Set WshNetwork = VBA.CreateObject("WScript.Network") strUser = WshNetwork.UserName [loginid] = strUser Dim varName As String...
  4. A

    Query parameters defined by variable instead of form

    any idea how I could do that? VBA is not my forte! :)
  5. A

    Query parameters defined by variable instead of form

    yeah, it did not work.....the query result was blank.
  6. A

    Query parameters defined by variable instead of form

    Ok, I made a new module and put the function in there. The function I have is Option Compare Database Public Function GetUser() GetUser = varName End Function Now do I just write GetUser() in the query criteria?
  7. A

    Query parameters defined by variable instead of form

    What is a standard module? Can I just add a new module and put the function there?
  8. A

    Query parameters defined by variable instead of form

    Thanks Pbaldy and boblarson. where should I define the function? I defined it in the form and put criteria as RetUser() in the query, this came up with a undefined function error when I tried to run the query. What am I doing wrong here?
  9. A

    Query parameters defined by variable instead of form

    Hi, I want to design a database that will pickup a windows username and store it to a variable. Later on I want to reference to this variable as the parameter in a parameter query. In other words, in the parameter queries I have built so far I have always had the user enter the parameter in a...
  10. A

    Grabbing the year from a date entered as a parameter

    Does anyone know how to define parameters in a union query? (Like in a normal select query we go into Query>Parameters and then define each parameter and its data type?) I have a crosstab query that is based of this union query and when I run the cross tab query I get error 3070. Thanks!
  11. A

    Grabbing the year from a date entered as a parameter

    YTD is a field and at the moment it only contains 2009 YTD. In the parameters tab I have added StartDate and EndDate as Text -- would this be an issue?
  12. A

    Grabbing the year from a date entered as a parameter

    I changed [Forms].[Form1]![EndDate] to [Forms]![Form1]![EndDate] But it still asks me for the YTD parameter?
  13. A

    Grabbing the year from a date entered as a parameter

    Here is what I have SELECT SUPERVISOR, NAME, SCALENAME, SumOfINVDOLR, SumOfCONSDOLR, METRIC, YTD, SCALEMIN, SCALEMID, SCALEMAX FROM MIOH_MONTH WHERE YTD = Right([Forms].[Form1]![EndDate], 4) & " YTD" UNION ALL SELECT SUPERVISOR, NAME, SCALENAME, SumOfSALES, SumOfBOS, METRIC, YTD, SCALEMIN...
  14. A

    Grabbing the year from a date entered as a parameter

    I have a form in which the user enters a StartDate and EndDate which determines the range of data he wants to view. I want to grab just the year (YYYY) part of the EndDate and use it as a parameter result for the YTD. i.e. if StartDate=01/2009 EndDate=08/2009 I want my query to read the EndDate...
  15. A

    Parameters in a union query

    Yeah that's kind of what it is. I use it when I have many queries to unify or when I want all columns. Saves the time of selecting each column. It worked btw - I took out the "=" Thanks!
  16. A

    Parameters in a union query

    This is what I have, SELECT SUPERVISOR, NAME, SCALENAME, SumOfINVDOLR, SumOfCONSDOLR, METRIC, PERIOD1, SCALEMIN, SCALEMID, SCALEMAX FROM MIOH_MONTH WHERE PERIOD1 = Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate] UNION ALL SELECT SUPERVISOR, NAME, SCALENAME, SumOfSALES...
  17. A

    Parameters in a union query

    I will try it out this way. It does work with the format I have posted - I use the posted format because it is easier when working with more than two queries. Like in this case I have 4 queries that would be unified into 1 query.
  18. A

    Parameters in a union query

    I have a union query the SQL for which is as given below: TABLE MIOH_MONTH UNION TABLE SERVICE_MONTH UNION TABLE SURPLUS_MONTH UNION TABLE TRFALL_MONTH; One of the columns of the union query is a month column, I want to add a parameter to this query which will prompt the user to enter the month...
  19. A

    Error 3265 - Item not found in collection

    Pbaldy, do you know how this code could be modified if I wanted it to ignore the first two columns of the query and start from the third column? That is, the third column of the crosstab query will be the first column of the report. Also the first two columns of the query are supervisor and a...
  20. A

    Criteria to specify range of months and YTD

    That is helpful, particularly the year-to-date formula - but I will need to merge the year-to-date formula with the date range formula to use on the same column. Is there a way to do that?
Top Bottom