Search results

  1. C

    substring extraction

    Hi Paul Sorry my bad, I forgot to mention its T-SQL VAL is not a valid instruction So far I've got to cast(replace(cast(SUBSTRING([CONTENTS],PATINDEX('%'+'x'+'%',[CONTENTS])+2,PATINDEX('%'+'ml'+'%',[CONTENTS])-3) Martin
  2. C

    substring extraction

    Hi Guys Does anyone know how I can retrieve the following information from a field [CONTENTS] 1 x 17.7ml 2 x 17ml 1 x 181.1ml I need 17.7 17 181.1 I was using a combination of CAST(LEFT(RIGHT...)) AS Float but that obviously won't work due to too many variations Martin
  3. C

    Creating my first stored procedure

    Hi Guys Up until now I have be through the exersize of writing an Access database then moving on to an Access front end and SQL Server back end. So I have now written all my queries as Views, well most of my queries as views. I have been advised that some of these would benefit as stored...
  4. C

    textBox refresh

    I used forms afterupdate
  5. C

    textBox refresh

    lol yes it did help, but it didnt work anyway I added me.requery in an [Event procedure] to After Update Martin
  6. C

    textBox refresh

    Im assuming you mean in the [Event Procedure] but no that made no difference :( and I cant see an after update in the forms properties...
  7. C

    textBox refresh

    I am using a DLookup to default a value in my TextBox This text box has an event attached to it that updates a table when a different value is entered. I am having difficulty in getting the default value to reappear when I change my critera. For instance Manufacturer is Ford Colour is...
  8. C

    query where there is no match

    Hi namliam I have the latter ie "one price at a given week and not have any prices until it changes" I was grasping at straws when I only used a change date, but it looks like I may need to use the two dates as in your wonderful example. Many thanks for the time you put in to read and...
  9. C

    query where there is no match

    OK I have beendoing som emajor work on this now and come up with the following. I think I need to insert a select query into my main query and this is where my knowledge stops :banghead: This is quite long so please bear with me... I have been running queries without taking into...
  10. C

    Refresh a textbox contents

    Private Sub cboChain_AfterUpdate() Country1 = Me.cboChain.Column(1) Chain1 = Me.cboChain.Column(0) DoCmd.OpenQuery "0_Current_user_3", , acEdit DoCmd.OpenQuery "qryDefaultPeriod001" DoCmd.OpenQuery "qryDefaultPeriod002" DoCmd.OpenQuery "qryUpdateYearWeek_0" Form.Requery End Sub
  11. C

    Refresh a textbox contents

    I have a Tab Control on a Form On this Tab Control is a Text Box [Text476] that is linked to a 1 row Table [Period] in my database. I also have on the Form but outside of the Tab Control a drop down box that allows the user to select a customer. On selecting the customer an After Update...
  12. C

    query where there is no match

    Thanks namliam I will certainly look into this idea.I can only use dates if I do some sly work with the year and weekno which I have had to do in this project anyway in places. Likewise the concatenation of year and week no, which again I have done in places. So your advice is have a start and...
  13. C

    query where there is no match

    Hi guys I am looking for advice here as I am not sure how to get this scenario to work. I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo I am about to create a historical table of RRP. What is the best way to set this out so that I can query the two tables to that...
  14. C

    count DISTINCT [field1] where [field2]=True

    Sorry my bad This is in a report footer
  15. C

    count DISTINCT [field1] where [field2]=True

    I am trying to get a distinct count of [FAMILY] where [ACTIVE] = True I am using SQL server back end with pass through query to create my report. This is I guess important as DISTINCT becomes a valid operator...I think.
  16. C

    WHERE AND OR >= <= dilemma

    Yes, Access front end SQL Server back end. Its a learning curve all the way :cool:
  17. C

    WHERE AND OR >= <= dilemma

    Monardo Thanks for the help I think we've cracked it, I'm sure there are better ways but it looks like the correct result WHERE CAST(CAST(CM_DATA.YearASnvarchar(4))+right('0'+cast(CM_DATA.WeekASnvarchar(2))...
  18. C

    WHERE AND OR >= <= dilemma

    Hi Monardo Not quite I am now getting 2013 2 2013 32 2013 34 2014 1 where I would have expected 2013 38 to 2013 52 2014 1 to 2014 14 based on Start_Year = 2013 Start_Weekno = 38 End_Year = 2014 End Weekno =14 As I am using a pass-through to SQL Server i changed it slightly to...
  19. C

    WHERE AND OR >= <= dilemma

    Ugggh This is still is wrong using the above code I now get 2013 wk 1 - 14 AND 2014 wk 1 -14 Is anyone dining out on this yet
  20. C

    WHERE AND OR >= <= dilemma

    Try WHERE CM_DATA.Year = [WorkPeriod28].[start_year] AND CM_DATA.Year <= [WorkPeriod28].[end_year] OR CM_DATA.Week = [WorkPeriod28].[start_weekno] AND CM_DATA.Week <= [WorkPeriod28].[end_weekno] Why could I not see this before :banghead:
Back
Top Bottom