Posts by cdu311

    Re: Getdate() Function


    Hi Mate,


    Thanks for coming back to me so quick. i tried your suggestion this morning and its a no go, when i type in what you suggested it comes up stating "Invalid Use Of '.','!', or '()'.


    Please note this is not urgent as i am experimenting with something just now - but it would be nice to know why it dont like me. ;)


    Thanks again for your input.


    Regards


    Lee

    Hi All,


    Im writing a new SQL query in Microsoft Query in Excel so that i pulls yesterdays statistic information. I was helped out a couple of weeks ago from Will Riley about using the getdate function, i eventually got it to go and do what i wanted it to but in this new query i have tried to simply add getdate()-1 to get yesterdays data but it simply does not like this, if i use <=getdate() it works if i use >getdate() it returns nothing which i expect to happen.


    Could anyone possibly expand for me as to why it would not work?


    Many Thanks


    Lee

    Re: Assign Todays Day To Sql


    Hi,


    That is not the case, when i try to add TODAY or NOW it comes up stating that they are not recognised in MSQuery, trust me - if it was that easy i would have used them but due to the nature of the database (which is a Sybex might i add) it is very picky on what i can pull out of it.


    Thanks for commenting though.


    Regards


    Lee:cool:

    Re: Assign Todays Day To Sql


    Hi Will,


    thanks for your help, i finally managed to crack this, it took me ages (and a little help from an SQL god i know ;) )


    I used the following to get the date variable:-


    Code
    convert(char(14),GETDATE(),106)


    so it now cuts off the time and leaves dd/mm/yyyy. I would like to say thanks for the help you provided by pointing me in the right direction with regards to GetDate() function. this is definitely an excellent command to use in MSquery and it has been written down for future.


    :thanx:


    Lee

    Re: Assign Todays Day To Sql


    sorry mate, this is over me now, how is it possible to link an sql query to
    a cell within excel? i have added =today() to excel thats not a problem, the sql is in but i cant find a way within MSQuery to say go get a cell from Excel?


    :confused:

    Re: Assign Todays Day To Sql


    Hi Will


    I have added the code you suggested into SQL itself - see below:-

    SQL
    SELECT iSkillsetStat.Timestamp, iSkillsetStat.Skillset, 
    iSkillsetStat.Application, iSkillsetStat.CallsAnswered, 
    iSkillsetStat.CallsAnsweredAfterThreshold, iSkillsetStat.SkillsetAbandoned,
    iSkillsetStat.SkillsetAbandonedAftThreshold
    FROM blue.dbo.iSkillsetStat iSkillsetStat
    WHERE (iSkillsetStat.Timestamp<getdate()) AND (iSkillsetStat.Skillset Like '%IF_CS%%') 
    AND (iSkillsetStat.Application Not Like '%Application%')


    this seems ok but the <getdate() pulls everything pre todays date, again this seems ok but i would only like todays information ie 02/05/2007 and not everything prior to this i have tried everything (getdate()-1, getdate(-1), getdate(today) etc etc - can you possibly add further info on how to get todays data.
    :crying:
    Many Thanks

    Re: Repeat Application.ontime


    Hi mate,


    i never got an email to say you posted back - so it was only by chance that i checked this. So thanks for answering. I never thought about adding a timer on - i already have one on the code to update the information - by adding another it would not cause issues?


    Cheers bud


    Lee

    Hi All,


    does anyone know how to repeat the following line?

    Code
    Private Sub Workbook_Open()
    Application.OnTime TimeValue("22:02:00"), "Email"


    so that everyday it runs at the same time Everyday?
    At the moment i have to close the workbook at somepoint during the day then relaunch so that it picks this line up, when this eventually "goes live" it will not be monitored as closely as it does now during testing.


    Cheers


    Lee

    Re: Assign Todays Day To Sql


    Hi,


    Thanks for comming back to me. i usually just use the Microsoft Querey to change the date - it comes with the wizard thingy, doesnt take long but it does annoy me. if i go to edit query and go into the section View/SQL this is what i get:-


    SQL
    SELECT iSkillsetStat.Timestamp, iSkillsetStat.Skillset, iSkillsetStat.Application, iSkillsetStat.CallsAnswered, iSkillsetStat.CallsAnsweredAfterThreshold, iSkillsetStat.SkillsetAbandoned, iSkillsetStat.SkillsetAbandonedAftThreshold
    FROM blue.dbo.iSkillsetStat iSkillsetStat
    WHERE (iSkillsetStat.Timestamp>={ts '2007-04-25 00:00:00'}) AND (iSkillsetStat.Skillset Like '%IF_CS%%') AND (iSkillsetStat.Application Not Like '%Application%')


    i should probably note here that the date/time is automatically allocated in the central database using its own program.


    so how would i get previous noted code into this? cheers mate.


    Lee

    Re: Update Links Automatically


    ToddKerr


    Massive apologies for delay - i have been up to my eyes with various projects. :redface:


    I used your method and it works a treat the data pulls perfectly, no more having to sit every 2mins pointing to the file where data is stored. :cool:


    Many Thanks


    Lee

    Re: Assign Todays Day To Sql


    Hi Mate,


    Sorry for delay been really busy (must remember to post reply on another subject i raised :confused: )


    Im sorry but i didnt myself 100% clear. I made the spreadsheet by using data/import external data/import.


    Form here the vba kicks in by using


    this is done over 2 workbooks merged into 1, the "Call Timer" section calls


    in order to have the sheets refresh after two mins. but like i said in my original post i have to come in and refresh twelve sheets. so using your method where would i incorporate this? sorry for confusion.


    Thanks Muchly


    Lee

    Hi Folks,


    Im hoping this is the right forum to post this question on. if not just let me know and i will shift it. :)


    I am running 12 worksheets that each have their own querey to a centralised database. that all runs sweet as, but each morning i have to come in and then change each of the querys to todays date. Does anyone know how i could get it to run itself? the format of the date in the query is dd/mm/yyyy hh:mm the hours and mins are not really an issue because nothing happens between 10pm and 8am so there is no concern there.


    Cheers ;)

    Re: Code To Open Html File


    OR (sorry i thought you were meaning the internet) :rolleyes:


    Code
    Private Sub CommandButton1_Click()
    If Range("A1") > 1 Then
    Shell "F:\Microsoft Office\OFFICE11\excel.exe book1.xls"
    Else
    End If
    End Sub


    if you want to open an excel file. see whats happening? you point it to where the file is stored and it will happily open it.


    :cool:

    Re: Code To Open Html File


    you could try this


    Code
    Private Sub CommandButton1_Click()
    If Range("A1") > 1 Then
    Shell "C:\Program Files\Internet Explorer\Iexplore.exe www.google.com"
    Else
    End If
    End Sub


    this is very basic example - but you get the picture when you click the button it checks to see if cell a1 is greater than 1 - if so launch google.com or else it does nothing.


    Hope this helps.


    Regards


    CDU311

    Re: Update Links Automatically


    Hi Guys,


    First up thanks for the responses - im at home just now but will check these in the morning three points to note:-


    1, sorry bout the code tags, i miss keyed - i was in a hurry to get this online before i left work. ;)
    2, Robert B - the file path is correct i.e
    \\servername\shareonserver\folder\folder\folder\filename.xls - so unfortunately buddy - this is not the answer. (i checked tonnes of times as i, like you thought i had miss typed something)
    3, TodKerr - No i dont care about other sources due to only having one source so I gave your line a quick try at home using a couple of very basic workbooks and it seemed to work, so i will give it a try when i get in tommorrow at the crack of dawn. i will re-post to let you know outcome. thanks mate :cool:

    Hi Folks,


    Can anyone point me in the right direction im using two spreadsheets that run on the timer control but after 30mins (for example after they both update i want to take information from one of the spreadsheets and place it into the other - im using


    [code]
    ActiveWorkbook.UpdateLink Name:= _
    "\\ifdata004\DATA$\CSA\System Support\New Lee\SLSS2.xls", Type:=xlExcelLinks
    [\code]


    to update the links accordingly but the dialog box keeps coming up asking me where to go.


    Any help would be appreciated.


    Many thanks


    CDU311

    Re: Sum From Two Tables


    well based on my code that i dropped in against 1116 cells in excel to refresh it took around 30 seconds, based on urs against the same criteria (1116 cells) it only took 20 seconds! savings of 10 seconds!! :cool:


    dont know if you really needed to know that but i thought i would let you know since you helped me out.

    Re: Sum From Two Tables


    good man, thanks muchly for this im using your way as yours looks cooler than mine - mine looks lame. : D


    Thanks again.


    Regards


    CDU311

    Re: Sum From Two Tables


    Hi Mate,


    Many Thanks for coming back to me - i went home last night before you emailed me so sorry for delay. This was bugging me and here is what i came up with


    Code
    =mysql("Select SUM(FTE) From [Skillsbuilder],[Telephony] WHERE Skillsbuilder.StaffNumber = Telephony.StaffNumber AND (((Skill_1 = '0')))", "\\IFDATA004\Data$\CSA\Resource Team\SkillsBuilder\SkillsBuilder.mdb")


    This works quite happily. I tried your way and it may be just me but i cant get it to work correctly.


    Regards


    CDU311

    Hi All,


    Would it be at all possible to sum two tables in access through excel. For example - i want to select certain fields in one table and other fields in another table and if they match my criteria then output the result as a sum. below is the code ive been trying. :confused:


    Code
    =mysql("Select count(*)From [Skillsbuilder] WHERE SkillsBuilder.StaffNumber = Telephony.StaffNumber AND ((SkillsBuilder.FTE='1') AND (Telephony.Skill_1='1'","\\IFDATA004\Data$\CSA\Resource Team\SkillsBuilder\SkillsBuilder2.mdb")


    or

    Code
    =mysql("Sum (*)Select StaffNumber, FTE, From [Skillsbuilder], Skill_1 From [\\IFDATA004\Data$\CSA\Resource Team\SkillsBuilder\SkillsBuilder2.mdb.Telephony] WHERE Telephony.StaffNumber = SkillsBuilder.StaffNumber AND ((Telephony.Skill_1='1'))")


    thanks for your help.