Re: VBA for a Vlookup formula
Gerald,
You attached this question to another thread which was more than 4 weeks old. This is unlikely to obtain any response. Please start a separate thread when asking a new question.
Can you also read about how to properly format code snippets using BBCODE - it makes code much easier to read.
Thanks.
Re: Excel query to Sql Server
How fast is the query in Management Studio? Is there a noticeable difference?
First thing is to determine where the bottleneck is - it may not be Excel.
Secondly, I'd look at using ADO as opposed to MSQuery - should be much faster, depending on your query.
As ever, more details would be more helpful.
(PS - I moved your post from HAVE-A-CHAT to appropriate forum.... probably why you had no response until now)
Re: If text is entered incorrectly, the cell turns red
Hi
I can't get to that account to download the file, however I figure that data validation might be a useful solution to this.
I have attached a small example.
Regards,
Will
Re: Run SQL Stored Procedure from VBA
Your code is more than a little confusing, so perhaps an example might be best.
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Set conn = New ADODB.Connection
conn.ConnectionString = “your connection string here”
conn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "put stored procedure name here"
cmd.Execute
conn.Close
Set conn = Nothing
Set cmd = NothingRe: A ball park figure on costings (not to hire!)
There's probably a very good reason why this has attracted no replies as yet.
It's very difficult to say when the requirements are so vague.
If you put a more detailed set of requirements together, I'm sure someone would be able to estimate the time it would take. Then it's just a matter of the rates people would charge, which could vary considerably!
Re: Ms query 'select distinct' wont work
Quote from Mark Pajak;567343Following on from this I can repeat what happened - when I remove the fields to be displayed in the results from the query and subquery, but leave the tables joined with the DISTINCT clause still in there onm the 'irn' column, I get the correct number of rows. When I add in the fields I need from that table the number rows increases with duplicates for each extra artist returned. Is that right?
try this
[vb]
SELECT e1.ColMainTitle,
e1.AssAssociationDate ,
IIf (e1.LocLocationCode= 'Storage', '', e1.LocLocationCode & ' - ' & e1.LocLevel1 ),
e1.DimWidth,
e1.DimHeight,
e1.DimLengthUnit,
e1.MatTechnique_tab,
e1.ColObjectNumber,
e1.AcqCreditLine,
e1.Namlast,
e1.Namfirst & ', ' & e1.Nammiddle ,
e1.ColArtistRole_tab,
e1.BioBirthDate,
e1.BioDeathDate,
e1.BiocommencementDate,
e1.BiocompletionDate,
e1.ColCreationNotes,
e1.LocLevel1 & ' - ' & e1.LocLevel2 & ' - ' & e1.LocLevel3 & ' - ' & e1.LocLevel4 & ' - ' & e1.LocLevel5,
e1.LocLevel6,
e1.ecatalogue_key,
e1.Multimedia
FROM
((((ecatalog.csv e
LEFT JOIN
(SELECT M.* FROM Measurem.csv M WHERE M.DimMeasurementType= 'image') M1
ON e.ecatalogue_key=M.ecatalogue_key)
LEFT JOIN
(SELECT Mul.MulMultiMediaRef_key, Mul.ecatalogue_key, Mul.MulIdentifier,Mul.Multimedia FROM MulMulti.csv Mul WHERE Mul.Multimedia <> '' ) Mul1
ON Mul.ecatalogue_key=e.ecatalogue_key)
LEFT JOIN
(SELECT * FROM Associat.csv A WHERE a.AssAssociationType = 'date') A1
ON A.ecatalogue_key=e.ecatalogue_key)
LEFT JOIN
(SELECT distinct art.irn ,
art.Namlast,
art.Namfirst,
art.Nammiddle,
art.BioBirthDate,
art.BioDeathDate,
art.BiocommencementDate,
art.BiocompletionDate,
art.ecatalogue_key
FROM Artist.csv art) art1
ON art.ecatalogue_key=e.ecatalogue_key) e1
order by e1.irn [/vb]
Re: Make 2010 look like 2003
Quotereally can not get on with the format of 2010
A common complaint when 2007 was launched - for Office 2007 the UI was completely rewritten to move from the old menu based UI to what is now commonly known as the Ribbon UI, with further tweaks in 2010.
There is no real way to make the revised products that use the Ribbon UI it look like pre-2007 versions so don't waste your time.
My advice is to do one or both of the following.
1. Keep using the new versions. It takes 3 to 6 months of constant use to get to grips (depending on how much you use them), but once you do you will inevitably "prefer" the new version.
2. Install Office 2003 as well, then when you need to do something desperately fast (or something that needs to work in 2003) use that instead.
Re: Windows 7 or vista..?
Windows 7 is basically Vista that works as Vista should have done. Call it a major service pack 
So, definitely Windows 7 (and if possible, yes go for a 64bit machine)
Re: Sorting Data -macro Support
Just saw your subsequent post.
The amendment I suggested leaves you with 142 records.
Re: Sorting Data -macro Support
First thing I notice is that the branch data in the data sheet is (in some cases) in a different format to the data you are looking up in the Branch List. So you are not comparing apples with apples, so to speak.
Take the example of branch 002001, which remains in the data after running the macro, even though it is not in the Branch List...
It "looks" the same in both sheets, but in the data sheet Excel sees it as 2001 (a number) so this is what your macro looks for in the in the Brach Sheet... it finds it too, as part of the value of brach 142001
You need to ensure the data formats are the same or you will have incorrect results
You might get away with amending this line
'
Set rngFindRange = Sheets("Branch List").Range("A1:A" & lngLastBrnchRow).Find(.Cells(lngLoopRow, 2)[B].Text[/B], LookIn:=xlValues)
'Which would change the looked up value from 2001 to 002001
A man was crossing a road one day when a frog called out to him and said, "If you kiss me, I'll turn into a beautiful princess." He bent over, picked up the frog, and put it in his pocket.
The frog spoke up again and said, "If you kiss me and turn me back into a beautiful princess, I will tell everyone how smart and brave you are and how you are my hero" The man took the frog out of his pocket, smiled at it, and returned it to his pocket.
The frog spoke up again and said, "If you kiss me and turn me back into a beautiful princess, I will be your loving companion for an entire week." The man took the frog out of his pocket, smiled at it, and returned it to his pocket.... See more
The frog then cried out, "If you kiss me and turn me back into a princess, I'll stay with you for a year and do ANYTHING you want." Again the man took the frog out, smiled at it, and put it back into his pocket.
Finally, the frog asked, "What is the matter? I've told you I'm a beautiful princess, that I'll stay with you for a year and do anything you want. Why won't you kiss me?"
The man said, "Look, I'm a computer programmer. I don't have time for a girlfriend, but a talking frog is cool."
Jack wakes up at home with a huge hangover he can't believe. He forces
himself to open his eyes, and the first thing he sees is a couple of aspirin
next to a glass of water on the side table. And, next to them, a single red
rose! Jack sits down and sees his clothing in front of him, all clean and
pressed Jack looks around the room and sees that it is in perfect order,
spotlessly clean.
He takes the aspirins, cringes when he sees a huge black eye staring back at
him in the bathroom mirror, and notices a note on the table:
"Honey, breakfast is on the stove, I left early to go shopping-Love you!"
He stumbles to the kitchen and sure enough, there is hot breakfast and the
morning newspaper. His son is also at the table, eating. Jack asks
"Son...what happened last night?"
"Well, you came home after 3 am, drunk and out of your mind. You broke some
furniture, puked in the hallway, and got that black eye when you ran into
the door."
"So, why is everything in such perfect order, so clean, I have a rose, and
breakfast is on the table waiting for me?"
His son replies, "Oh THAT! Mum dragged you to the bedroom, and when she
tried to take your trousers off, you screamed, "Leave me alone, you tart,
I'm married!
Broken furniture £85.26
Hot Breakfast £4.20
Red Rose bud £3.00
Two Aspirins £0.38
Saying the right thing, at the right time.........Priceless...
Re: What do you think of...
lol
Re: Cell Formula Dependant On List Selection
Does the user not have the ability to drill down within the Pivot Table itself?
Seems like you are trying to recreate pivot table functionality without using a pivot table... which sounds kinda daft... or maybe i have misunderstood the question 
Re: Pick Dates Through Checkboxes
Presumably you are restricting the date selection as only specific dates are available?
Why not use a listbox control on your userform that has its source as a query that returns the available dates from the relevant table(s) ?
Re: Convert Range To Pseudo Table
Create a comma delimited string variable of values (eg varParameter)
'id1','id2','id3','id4' etc
from your values and pass these as a single variable as follows
With rst
stSQL = "SELECT *.* " & _
"FROM TEMPLATES RIGHT JOIN emp ON TEMPLATES.Id = emp.ID " & _
"WHERE (((emp.LOGIN) IN (" & varParameter & ")) AND (emp.UNT > 10));"
.Open stSQL, cnt 'Create the recordset.
Set .ActiveConnection = Nothing 'Disconnect the recordset.
If Not (rst.EOF Or rst.BOF) Then L.Offset(0, 3).CopyFromRecordset rst
.Close
End WithObviously you'll need to change your paste location too
Re: Happy Birthday Dave Hawley 
Happy Birthday Dave
Re: UserForm For SQL Query With Controls As Parameters
QuoteI think the Chr(13) and the Chr (10) stand for a maximum amount 13 (or 10) characters
Nope, that's just a carriage return command in VBA
Do you have a way of finding out the data types of the date field and MMYY field?
Re: UserForm For SQL Query With Controls As Parameters
It all depends on your data types. Supply the correct data type to your query and it will work... neither of the examples you give are correct dates, so I assume they are perhaps integers? But maybe they are text...
You could help by explaining your data source a little more
Re: UserForm For SQL Query With Controls As Parameters
Try this
"gl_trans_closed.ledger_year_month" & Chr(13) & "" & Chr(10) & "FROM qdb.dbo.gl_trans_closed gl_trans_closed" & Chr(13) & "" & Chr(10) & "WHERE (gl_trans_closed.fund_number=" & FNText & ") AND (gl_trans_closed.trans_doc_date_gl>='" & DDText & "') AND (gl_trans_closed.ledger_year_month>='" & YMtext & "')")
.Refresh BackgroundQuery:=False
End WithNote the additional single quotes before and after your date parameters