Search results

  1. C

    WHERE AND OR >= <= dilemma

    Hi Guys I think this has more to do with the thought process of what is going on within the WHERE clause. What I need to do is SELECT WHERE [year] >= 2013 AND [weekno] WHERE [year] = 2013 >= 38 AND [year] <= 2014 AND [weekno] WHERE [year] = 2014 <= 14 What I currently have is (which does not...
  2. C

    Entering default values

    UGH, I thought I had this solved but now I find it doesn't update, I could seriously cry.... If I delete and retype the above code it changes, How do I force it to reflect the change when another combo box on the form is updated? M
  3. C

    Entering default values

    sort of SOLVED.....grrr =DLookUp("[DefaultYear]","[tmpDefaultPeriod_0]") =DLookUp("[DefaultWeek]","[tmpDefaultPeriod_0]") Note to self: Dont forget the quotes!! M
  4. C

    Entering default values

    I have a 1 row table as a result of 2 queries that give me the 'MaxYear' AS 'DefaultYear' and 'MaxWeek' AS 'DefaultWeek' in that year for a selected retailer and stores, from a master table. On my form I want the default Year and Default Week text boxes to display the values of these I have...
  5. C

    the use of FORMAT

    Thanks CJ I looked at Round before but as at the time I was looking for thousand seperators I ignored it. I've gone back to it now. Martin
  6. C

    the use of FORMAT

    I have a query that when run exports itself and opens up in Excel. In the design query I set the format to Fixed,0 decimals or Fixed,2 decimals or Percent,2 decimals. Eventually this has became a pass through query as my data is stored on SQL Server. I'm not sure if its a result of the...
  7. C

    sum if

    My end output needs be numbers in brackets = test data AVERAGE [CM_DATA].[VOLUME] (5.786) ADD SUM [CM_DATA].[VOLUME] for last two weeks (1+3=4) MULTIPLY 7 SUBTRACT [CM_DATA].[STOCK] (2) so the formula will be like this: ReOrder: =(5.786 + 4) * 7 - 2 Reorder: = 67 (fixed at 0dp) AVERAGE...
  8. C

    sum if

    Hi Paul Thanks for that, I will try and make it fit. I assume [Agents] should = [CM_DATA] :D The result I got from your code was Week SumOfVolume 51 40928 52 28678 Hmmm Due to the WHERE clause (last 2 weeks) the average for the whole of the calendar year is now wrong. The Query only...
  9. C

    sum if

    is this what you mean.. SELECT CM_DATA.Week, Sum(CM_DATA.Volume) AS SumOfVolume FROM CM_DATA GROUP BY CM_DATA.Week ORDER BY CM_DATA.Week; excuse my use of reserved words :o
  10. C

    sum if

    Hi Guys Thanks for your input Brianwarnock - LAST is a feature within Access which appears not to have a performance issue. Its getting the previous week thats a problem and then summing the two to get a value. I can get LAST(WeekNo) and even LAST(WeekNo)-2 actually that should probably be...
  11. C

    sum if

    I am trying to find the sum of items sold in the last two weeks something along the lines if this: If week number is greater than the maximum weeknumber then sum the Volumns of the last two weeks. Ive tried this: IIF([CM-DATA].[WeekNo] >= Max[CM_DATA].[WeekNo]-2,SUM([CM_DATA].[Volume]))...
  12. C

    Update query

    OK I was really stupid yesturday and for the last month This code works A: it should have been an append query not an update query B: I had a wrong table in the query Paul Thanks for your time, I would not have seen it if I didn't set my self up as a clown. This is my working query: INSERT...
  13. C

    Update query

    Hi Paul Assuming that you are looking at my db are you talking about Query 0_Current_User_2 UPDATE (User_Data INNER JOIN [Current_User] ON User_Data.lngEmpID = Current_User.User) INNER JOIN tblCountry ON User_Data.strEmpName = tblCountry.Country SET [Current_User].[User] =...
  14. C

    Update query

    Hi Paul I've uploaded a version.
  15. C

    Update query

    hang on me being dim... remove the parameter statement and I now get through but the table is still not populated. Actually thats not entirely true Current_User is being populated its the 3rd and final Query in the Macro that is not working. I've changed this based on your insight UPDATE...
  16. C

    Update query

    I must be doing something really stupid here as I still get the same message box PARAMETERS cboEmployee Short; INSERT INTO [Current_User] ( [User] ) Values(forms!frmLogon.cboEmployee);
  17. C

    Update query

    Yes, I see that was wrong, However this has made no difference PARAMETERS cboEmployee Short; INSERT INTO [Current_User] ( [User] ) SELECT forms!frmLogon.cboEmployee AS Expr1 FROM [Current_User]; I still get a text box asking for me to Enter Parameter Value for cboEmployee Even if I put a...
  18. C

    Update query

    OK, I have changed it to: PARAMETERS cboEmployee Short; INSERT INTO [Current_User] ( [User] ) SELECT frmLogon.cboEmployee AS Expr1 FROM [Current_User]; I still get a message box to enter a value for cboEmployee If I also change PARAMETERS cboEmployee Short; to PARAMETERS...
  19. C

    Update query

    Hi Paul Thanks for your comments. I am at a complete loss though where this full form reference is missing the Empty_User macro works so where/how do I change the other 2 queries and what is the difference? Apologies for my ignorance... Martin
  20. C

    Update query

    I am having a problem, where when a user selects a user name from a dropdown box (cboEmployee) an After Update event occurs DoCmd.RunMacro "user" Me.txtPassword.SetFocus The Macro "user" should do the following: Open Query "Empty_User" DELETE Current_User.User AS Expr1, Current_User.User FROM...
Back
Top Bottom