Posts by lasw10

lasw10 · Jun 24th 2005

Re: Loop does not work well

Code

Sub Macro1()
Dim c As Range


Dim r As Integer
r = 5
Do Until Cells(r, 1) = ""
    If IsNumeric(Cells(r, 1)) Then
        Cells(r, 1).Delete shift:=xlUp
    Else
        r = r + 1
    End If
Loop


End Sub

Display More

lasw10 · Jun 24th 2005

Re: Multiple Returns with VLOOKUP

why not create a pivot table off your second sheet that has multiple entries?

lasw10 · Jun 24th 2005

Re: Notify new data has been added

i am sure there will be an application or even table based event you could use to trigger a macro to launch an email from MS Access but in reality I would say it's more likely that this would be simpler from the web page

What is the web page coded in and does the web server has access to an smtp mailserver?

lasw10 · Jun 24th 2005

Re: IF expressions in select query

i don't have MS access on my desktop but you would something along the lines of

select iif(assigned_to="",'Unassigned',iif(completion_date="","Open","Closed")) as result from table group by 1

obviously you may need to amend the syntax depending on whether or not you're using NULLs etc

NOTE the IIF is not a typo - MS have kindly avoided allowing you to use IF in MS Access.

lasw10 · May 10th 2005

Re: import outlook contact objects from gloabal address list to spreadsheet

post of the code you're using to retrieve the username etc...

lasw10 · Mar 23rd 2005

Re: Search all mail item folders and subfolders

well it's limited to subfolders of inbox here:

Set MyFolder = myNamespace.GetDefaultFolder(olFolderInbox)

it will only loop subfolders of MyFolder so you want to change MyFolder to the default Personal Folders

in Win2K I think it's something like Mailbox - Name of User so let's presume that name of user is Joe Bloggs.. so I would use something like

Code

Set ol = CreateObject("outlook.application")
Set ns = ol.GetNamespace("MAPI")
Set fl = ns.Folders


Dim ind as integer
ind = 1
Do Until InStr(fl.Item(ind),"Joe Bloggs") > 0
     ind = ind + 1
Loop


Set fl = fl.Item(ind)

Display More

Above uses fl instead of MyFolders and would just set default folder as mailbox - so then just loop subfolders of fl.

should work.

lasw10 · Mar 23rd 2005

Re: JOIN & Max query

I tested this and it worked for me...

SQL

SELECT a.site, a.complete as lastofcomplete, a.[submitted by] as lastofsubmittedby, a.[date submitted] as maxofdate_submitted, b.region as region
FROM tblstatus AS a, tblsites AS b
WHERE 1=1 
and a.site=b.site
and a.[date submitted] = (select max(c.[date submitted]) from tblstatus c where c.site = a.site)
ORDER BY 5,1;

lasw10 · Mar 23rd 2005

Re: TOP statement and order by

where are you using the resulting recordset? ie is it possible for you to merge the recordsets together (ie run as separate queries) - easy is .net etc...

lasw10 · Mar 23rd 2005

Re: Roundup in sql

if you're trying to do this on the basis of the row index of the record this will also be dependent upon which db you are using (MySQL, Access etc...)

And are you saying you want this to be a valid field in your db or a value in a retrieved recordset?

lasw10 · Feb 8th 2005

Re: Save attachment as

Hmm, interesting ... one way would be to distribute some OL VBA to the recipients that would force the save on the attachment (of specific name).

http://www.outlookcode.com/d/comaddins.htm

For an add-in why is the path so important? Can they not just browse to wherever they saved it and load it that way?

(and am presuming that recipients do not have access to the same file directory...)

lasw10 · Feb 4th 2005

Re: New Use to SQL Server / ASP

Hi Barry - sorry to hear you've been dropped in the deep end but we all start somewhere eh?

Hard to explain the whole asp thing in a brief synopsis but here goes:

ASP does not have error handlers... and I don't use the product you mention so have nae idea on that front I am afraid... what I tended to do in the past was use response.write all the way down for each bit - that way I could tell what had worked and what had not ... when it bugged out

As for server/client side - most ASP will be client side

You can determine when you're using server side as you will probably find references to ADO - recordsets and connections ...

If you wanted you could post a small sample of one of your pages and we could have a look and add commentary to try to explain what it's doing - obviously change any sensitive connection stuff....

lasw10 · Feb 3rd 2005

Re: Calculating Between Variable Dates

I think you mean so that if I entered 01/12/01 in A1 then it would be years between 01/04/02 and today - correct?

Try something like

I think... this is what you mean

=DATEDIF("01/04/"&IF(MONTH(A1)<4,YEAR(A1),IF(MONTH(A1)>4,YEAR(A1)+1,IF(DAY(A1)>1,YEAR(A1)+1,YEAR(A1)))),TODAY(),"Y")

My guess is there's a better formula than this...

lasw10 · Feb 3rd 2005

Re: select cell event

use the selection change event in the Worksheet code

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Row = 2 And Target.Column = 13 Then
    MsgBox "hello"
End If


End Sub

lasw10 · Feb 3rd 2005

Re: Calculating Between Variable Dates

go to Addins and ensure Analysis Toolpak is checked (and VBA one too if there... never remember which one EDATE is used in...)

lasw10 · Feb 3rd 2005

Re: Calculating Between Variable Dates

have a look at the DATEDIF function in XL Help.

eg

=DATEDIF(EDATE(A1,12),TODAY(),"Y")

assuming A1 holds 01/04/00

lasw10 · Feb 3rd 2005

Re: Save attachment as

2 Qs

1. Mail being received or mail being sent - guessing with the button it's the latter...
2. What type of file (.xls etc)

lasw10 · Feb 3rd 2005

Re: crosstab

can you give some more pointers? ie table names, fields etc etc

not much to go on...

lasw10 · Feb 3rd 2005

Re: MS Query or ADO

Luke's opinion...

MS Query sucks, isn't great for variables and is Slooooooooooooooow.

ADO way to go.

(It rhymes so it must be true).

lasw10 · Feb 3rd 2005

Re: New Use to SQL Server / ASP

Aye - I would be willing to offer a hand here and there.

I am a confused hybrid of open source... that is to say I too use MySQL db but code in asp.net or though did a lot of stuff in asp 3.0 too...

My company is too tight to use SQL Svr but asp.net is free if you're happy to use the free tools....

On aside Will - have you tested out the new 4.1 Server? Not bad on the old sub query thing - v5 will do the stored procedures apparently... fingers crossed.

lasw10 · Dec 16th 2004

Re: Double Lookup

This is assuming your values in Column A are unique.. if not you will need a bit more coding to go through each possible combo of A & B to your textbox values

eg of simple method assuming A is unique and in order... (if not switch from MATCH to VLOOKUP)

Code

Sub .... ()


Dim r as integer
OnError GoTo NaeMatchPal
r = application.worksheetfunction.match(textbox1.value,Sheets("Sheet1").Range("A:A"),0)
if Sheets("Sheet1").Cells(r,2) = Textbox2.value then
     textbox3.value = Sheets("Sheet1").Cells(r,2).Offset(0,10)
else
     'to display a message for no match for Textbox2 and value in B where A = textbox value
     msgbox "Column B Does Not Match " & TextBox2.value & " where A" & r & " = " & Textbox1.value
End if


Exit Sub


NaeMatchPal:
MsgBox "No Match in Column A for " & Textbox1.value & " "
Exit Sub


End Sub

Display More

Note - untested... and if syntax errors apologies - have been immersed in aspx for the last 100 years (or at least that's how it feels)