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:-

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:-

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?

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:-

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

Sub Refresh_Me()
' Written 18/04/2007 by Lee
' Refresh info every two minutes for duration of business day.


    Application.ScreenUpdating = False
        Sheets("All CS").Select
    Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
        Sheets("SJ Centre").Select
    Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
        Sheets("PM").Select
    Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
        Sheets("OV").Select
    Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
        Sheets("All Mortgage Sales").Select
    Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
        Sheets("All Centre").Select
    Range("I22").Value = "=NOW()"
    Sheets("Totals").Select
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
    'email
Application.ScreenUpdating = True
ActiveWorkbook.Save
    Range("IV1").Value = "120"
Range("A1").Select
    Call Timer
End Sub

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

Dim CountDown As Date
Sub Timer()
Application.StatusBar = "Refreshing Data In " & Range("IV1") & " Seconds"
CountDown = Now + TimeValue("00:00:01")
Application.OnTime CountDown, "Reset"
End Sub
Sub Reset()
Dim count As Range
Set count = [IV1] ' IV1 contains the number of seconds for the countdown.
count.Value = count.Value - 1
If count = 0 Then
Refresh_Me
Exit Sub
End If
Call Timer
End Sub

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)

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

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

=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:

=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

=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.