Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




VB6 0 / SQL / FROM-WHERE-ORDER BY Conditions


In my customer program I am trying to build frmFind. What I want to do is: Search records from tblCustomers. The 4 cboFields are: fldFirst, fldLast, fldCourse, fldCourseDate. The 2 choices for cboSign are: "=" and LIKE. (and of course I have txtValue where user inputs text to search by). I want to be able to order ascending or descending by the fields: fldFirst, fldLast fldCourse, fldCourseDate. Here is my coding so far, I have not started the ORDER BY because I do not understand how to do it. I've looked at many different sources by things just aren't clicking in my head. PLEASE HELP!!

Private Sub cmdBuild_Click()
If cboSign = "=" Then
lblSQL.Caption = "Select * FROM tblCustomers where fld" & cboFields.Text & cboSign.Text & Chr(34) & txtValue & Chr(34)
Else
lblSQL.Caption = "Select * FROM tblCustomers where fld" & cboFields.Text & " " & cboSign.Text & " " & Chr(34) & "%" & txtValue & "%" & Chr(34)

End If
End Sub

Private Sub cmdFind_Click()
cmdBuild_Click
frmRegistration.adoCustomers.RecordSource = lblSQL
frmRegistration.adoCustomers.Refresh
frmRegistration.Height = 7515
frmRegistration.Width = 13560
Unload frmFind
End Sub




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Order By Date(text Data Type In Access Mdb) Is Not In Proper Order
Friends,

I am using crystal reports 9 with vb6 and the db is ms-access 2002.
I am just adding the "tbltime" table to display all the fields in the crystal reports.
In the "tbltime" table a column called logdate which is of text datatype in the database.
when i view it in the report the result is not in the proper order but in the database table it is in the ascending order.

logdate in TblTime in dd/mm/yyyy format:

01/09/2005
02/09/2005
03/09/2005
01/10/2005
02/10/2005
01/11/2005

when i view in the reports the logdate field is displaying like this.....

01/09/2005
01/10/2005
01/11/2005
01/11/2005
02/09/2005
02/10/2005
03/09/2005

what to do now?

Thanks
Sathyguy

Change Order Of Xml Nodes Nased On Order Of List Bx
i have the following xml
<a>
<b url="abc1" />
<b url="abc2" />
<b url="abc3" />
</a>

this is loaded into a listbox
abc1
abc2
abc3

I can move items in the list up or down and i want the xml to save in the order the list box is in

any ideas?

Order File1 In True Order
Right now the File box orders like this:

x1.bmp
x10.bmp
x11.bmp
....
x19.bmp
x2.bmp
x20.bmp
x21.bmp
...

is there a way to make it order in TRUE order? Without renaming all the files with numaric placeholders?

Data Order Vs Combo Box Order....
Hi everybody !

I easy question !!!

How can I be sure that the items in a combobox will be in the same order in my database ??? Because I use for example ''If combo1.listindex = 3'' and the value in the 4th position(index 3) do not change...

Recordset Order Is Not Same As Table Order
We have a VB5 program that has been running for a couple of years.  Recently we have encountered an issue that I know
how to fix, but i don't know the cause.

The program pulls a recordset from an Access Database Table
(Select * from Table where Print_Date is Null), then prints
a form from each record, adds a Print_Date & updates the table record.  This program has worked fine for years.
The order of the printed records has always been the same as the order in the table UNTIL RECENTLY...
A couple of months ago, the program began printing the
forms in MOSTLY the same order as the table. It starts printing about 2/3 of the way down, goes to the end, then comes back to the beginning & prints the first 2/3. Any ideas what may have caused this quirk?

Possible To Specify Order By Order? [Comparing Two DB Tables]
Hi everyone...got a little problem here and i'm not quite sure what to do to fix it...here's what i'm doing.
I'm using VB6 to create a program that analyzes residuals. i'm pulling values from two excel documents then populating two access 2000 DB tables. Once i get them into the tables, i'm pulling all the records from each and putting them into their own UDT's. one for Last month's information, and one for this month's information. The purpose of this program is to determine which accounts have been activated or cancelled between last month and this month.

Currently, i'm working on the Cancelled Accounts section and it's mostly working correctly. It's a little bit hard to explain what's happening, but i'll do my best
Code:
Public Sub FillTypes()
Dim rs As ADODB.Recordset
Dim i As Integer
'Fill First Month Information
strSQL = "SELECT * FROM Month1 Order By PurchaseDate ASC"
OpenRecordset rs, conn, strSQL
rs.MoveFirst
i = 0
Do While Not rs.EOF And Not rs.BOF
    lstMnthAnalyze.CompanyName(i) = Trim$("" & rs!BusinessName) 'Fill Last Month UDT
    lstMnthAnalyze.PurchaseDate(i) = Trim$("" & rs!PurchaseDate)
    lstMnthAnalyze.Revenue(i) = Trim$("" & rs!Revenue)
    lstMnthAnalyze.Commission(i) = Trim$("" & rs!Commission)
    lstMnthAnalyze.ContractStatus(i) = Trim$("" & rs!ContractStatus)
    lstMnthAnalyze.MerchantStatus(i) = Trim$("" & rs!MerchantStatus)
    lstMnthAnalyze.State(i) = Trim$("" & rs!MerchantState)
    i = i + intOne
    rs.MoveNext
Loop
CloseRecordset rs

'Fill 2nd Month Information
strSQL = "SELECT * FROM Month2 Order By PurchaseDate ASC"
OpenRecordset rs, conn, strSQL
rs.MoveFirst
i = 0
Do While Not rs.EOF And Not rs.BOF
    currMnthAnalyze.CompanyName(i) = Trim$("" & rs!BusinessName) 'Fill Current Month UDT
    currMnthAnalyze.PurchaseDate(i) = Trim$("" & rs!PurchaseDate)
    currMnthAnalyze.Revenue(i) = Trim$("" & rs!Revenue)
    currMnthAnalyze.Commission(i) = Trim$("" & rs!Commission)
    currMnthAnalyze.ContractStatus(i) = Trim$("" & rs!ContractStatus)
    currMnthAnalyze.MerchantStatus(i) = Trim$("" & rs!MerchantStatus)
    currMnthAnalyze.State(i) = Trim$("" & rs!MerchantState)
    i = i + intOne
    rs.MoveNext
Loop
CloseRecordset rs

End Sub

Now, to compare lstMnthAnalyze with currMnthAnalyze, the Company names MUST be in the same order to get accurate results.
For Example:
lstMnthAnalyze = Company1 Date1 currMnthAnalyze = Company1 Date1
lstMnthAnalyze = Company2 Date1 currMnthAnalyze = Company2 Date1
lstMnthAnalyze = Company3 Date1 currMnthAnalyze = Company4 Date2
lstMnthAnalyze = Company4 Date2
This would return that Company 3 has been Cancelled this month and this would be working properly.

However, this is what's happening:
lstMnthAnalyze = Company1 Date1 currMnthAnalyze = Company2 Date1
lstMnthAnalyze = Company2 Date1 currMnthAnalyze = Company1 Date1
lstMnthAnalyze = Company3 Date1 currMnthAnalyze = Company4 Date2
lstMnthAnalyze = Company4 Date2
This returns that Company 2 is cancelled, company 1 is cancelled and company 4 are cancelled..because they're not matching up the way they're supposed to. Here's the Comparison module if it'll help clear anything up.
*EDIT: Company 3 also comes up cancelled...just thought i'd clarify a little
Code:
Public Sub TallyRemoved()
Dim x As Integer
Dim space As String
Dim bRemoved As Boolean
RemovedCount = 0
x = 0
frmExcelAnalyze.lstDataDisplay.Clear
For i = 0 To UBound(currMnthAnalyze.CompanyName())
    If Len(lstMnthAnalyze.CompanyName(i)) = 0 Then
        i = UBound(currMnthAnalyze.CompanyName())
        GoTo FinishedLastMonth:
    End If
        Select Case currMnthAnalyze.CompanyName(x)
            Case lstMnthAnalyze.CompanyName(i)
                lstMnthAnalyze.bRemoved(i) = False
                bRemoved = False
            Case Else
                lstMnthAnalyze.bRemoved(i) = True
                frmExcelAnalyze.lstDataDisplay.AddItem "Company: " & lstMnthAnalyze.CompanyName(i) & " Total Revenue: $" & lstMnthAnalyze.Revenue(i) & " First Purchasing: " & lstMnthAnalyze.PurchaseDate(i)
                RemovedCount = RemovedCount + intOne
                bRemoved = True
        End Select
        If bRemoved = True Then
            x = x
        Else
            x = x + 1
        End If
' bRemoved = False
FinishedLastMonth:
Next i
End Sub

Anyone have any ideas? Questions? Need clarification? Let me know, it's working except that i can't get it to order by date and keep the company order also.
Thanks!
*EDIT2* made subject line more accurate

 -Ryan

Edited by - mrsmiley0221 on 1/13/2005 12:00:39 PM

Conditions
I'm trying to make a form come up when the total number of a text box is less then 0, i've tried using this code but it hasn't been successful, can you suggest any other ways to do this?

Private Sub Form_Load()
If Form6.Text1 <= 0 Or Form6.Text3 <= 0 Then
Form6.Timer1.Enabled = False
Form6.Timer2.Enabled = False
Timer1.Enabled = True
Form26.Show
End If
End Sub

[SQL]Conditions
I have a table with the next collumns in it:

Hours | Factor
200 | w
100 | w
200 | d
300 | m

When the factor is "W" I have to divide the hours by 7, when the factor is "D" the hours remain the same and when the factor is "M" I have to divide the hours by 20.

I tried the if else statement, but I can't make it work. Does anybody have any ideas?

Using Conditions In VB
I was wondering if there was anything resembling an 'unless or until' condition in VB6. I know theres 'If, Else, Elseif', but anything that resembles 'unless'?

The text file gets written to in the following way.

09:47:18 Purchase order for domain names.
09:47:18 Sold 1 domain name www.domain1.com @ $9.90 for account A155.
09:47:18 Sold 1 domain name www.domain2.com @ $9.90 for account A155.
09:47:18 Sold 1 domain name www.domain3.com @ $9.90 for account A155.
09:47:18 Sold 1 domain name www.domain4.com @ $9.90 for account A155.
09:47:18 End of Order.

The first line is not required by me. I just need to add the total quantity of domains sold, starting from line 2.
The last line, 'End of Order', tells me that that is the end of the file.

The file gets written in anywhere from a few milliseconds to a couple of seconds. I could put in a long sleep interval, and then read the file, and be sure to get all the data....but I want to be able to read the file as soon as it is written in its entirety.

I add the quantity one line at a time. So I stop reading when i reach the 'End of Order' line. But that is only after the entire file has been written. I want to do it as fast as possible, so that my program continues to check the file, and as soon as it receives an 'End of Order' line, stops reading, and calculates the total.

Is there a condition in VB6, that I could use in such a situation? that says,
'keep on reading until/unless the line is 'End of Order'?

Conditions
Hi Everyone, I hope you all had a good labor day weekend. Some of you might have read some post on getting data and setting conditions to alert via email if a value has reached its set point. I ran into an issue where the GetValue returned a No Sample and Calculation failed result which resulted in an automated email being genreated. In my code I added that:


Code:
If con2.GetValue(vTime, retstatus) < 2 Then


If the value is < 2 an automated email was generated, but I am stumped on how to set conditions that if a No Sample or Calculation Failed value appears it will not send an email out. Any and all help is appreciated.

How To Use Conditions
I am trying to use the If/Elseif condition as part of my VB6 code. However, im having some problems with it.

Basically, my application calculates the value of a text box, reads the value, and then exports the value to another event.


Code:

If Text1.Text = "" Then
Call Calculate
Elseif Text1.Text = value1 Then
Call LogSheet
Elseif Text2.Text = 0 Then
Call Restart
End if


Seemingly very simple, but the above code does not execute properly. There are times when the 3rd condition is true, i-e Text2.Text = 0, but instead of "'Call Restart', it goes to 'Call Logsheet'.
Most of the time, it executes ok, but there are times, specially when I start the application, when it mixes up the conditions. Am i using any incorrect statement in the syntax?

The 'Calculate' event is calculating the value of Text1.Text.
The value of Text1.Text will either be 0, or value1. However, it takes about half a second to calculate....so if it runs the above code before half a second has elapsed, it could return a null value, which is why I have included a condition for a null value as well.
Basically, i want the 'Calculate' event to check the value of Text1.Text, and as soon as the value is either 'value1' or '0', I want it to trigger the above code.

Any help guys? I need this to be very accurate. Thanks

Conditions
Write a condition to express the following:

X is strictly between -10 and 10.
Exactly one of X and Y is greater than 6.
Both X and Y are positive or both X and Y are negative.

Help With Installation Conditions
I have made an application that will only work on windows XP. I would like to ask how this can be achieved using VSI. I think you must use the conditions or somethings but i'm not sure. Anyone has any idea on how this can be achieved?

VSI Launch Conditions...
I need to set a launch condition in Visual Studio Installer that will verify the version of IE (must be 5.5 or higher) and vbscript.dll (must be 5.6 or higher) on the user's machine, and not allow installation if those conditions are not met.

The only example I found in any documentation that is close is:
Code:
VersionNT>=500
which only checks to see that the OS is Windows 2000 or higher.

Does anyone know of any resources that provide examples of this?

Thank you.

VB Excel Conditions
I need to write code in excel VB to see if a certain cell contains text or numbers how?

Please Help
I have been looking through the visual basic bible for an hour

Multiple Conditions
How do you have mulitple conditions, i'm trying to make a command button enable but i want more then one condition to be made before it enables.

also i have made a program that has a couple of items, when you click on the items it plays the song through media player, how do i keep the form on top of the media player when it comes up? This is probably difficult to imagine i know but try to bear with it.

Thanks, any help is much apprecitated......

COUNTIF With More Conditions?
Hey

I want to count rows of a range where there are multiple conditions on each line, say 6 or 7. Right now i am using a custom function but this is very slow and not very flexable.. Is there a better way?

Need Help With Multiple Conditions
I want to have a web page open if certain conditons are met. I want it to run if "chkEmployment.Value = 1" and if the variable "min" is equal to 0, 10, 20, 30, 40, or 50. Any help would be apreciated I am a newby so bear with me.

Hyperlink Conditions
Hi all,

I am a german student and making an internship in the usa, so sorry for my bad english. I have one problem in visual basic with Excel 2000.

I want to declare a hyperlink with several conditions

Sample:

Cell contains: 1,0
I don`t need any hyperlink

cell contains: 2,0
I need a hyperlink to document1

cell contains: 2,3
I need a hyperlink to document2

I think there is a possiblity to solve this problem.

Could anyone send me the syntax of this problem

thanks a lot

joe

Datasource Conditions...
can u set conditions if we set an access table as the datasource of an MSHFlexGrid...

I have...

Dim rs As New ADODB.Recordset, sql As String
sql = "SELECT ID, Batch_ID, Vessel, Start_Time, Vessel_Tag FROM tblStage3Batches ORDER BY ID"
rs.Open sql, sConn, adOpenKeyset, adLockReadOnly, adCmdText

Set Stage3Display.MSHFlexGrid1.DataSource = rs
Stage3Display.Refresh

rs.Close
Set rs = Nothing

I wanna set a condition that all records in the table with Vessel_Tag = -1 should not be displayed...i tried placing the .datsource line in:

if rs!Vessel_Tag <> -1 then

end if

but that doesn't work...any ideas on how i can set this condition?

Regards,

Farooq

Going On To Next Loop If Conditions Met
Hi i'm a vba beginner and I was just wondering if any one could help me

I have a For loop and I'd like skip onto the next loop if a particular condition is met, otherwise continue on with the code within the loop
Currently the code is as follows:


Code:
For i = 1 To pFLayer.FeatureClass.FeatureCount(Nothing)
Set prow = pcursor.NextRow


Fieldnum1 = pcursor.FindField("Window")

If prow.value(Fieldnum1) = 0 Then 'I'd like to go to the next loop if this is true

End If

..............'Otherwise go onto the rest of the code in the loop here

..............
Next i



Any help would be much appreciated!

List Of Conditions
Does anyone know where I can get a list of conditions (=, <>, Like is all I know)?

Thanks

A Bulk Of If Conditions !!
Hai

i have a Timer set to 50 interval

The timer has 400 single line If conditions
i.e

if a>b and b<c then a=c
if d>b and c>a then c=d

My question is will this be an overload to the timer?

Equivelant Conditions
For the following problem, determine whether or not the two conditions are equivalent – that is, whether they will be true or false for exactly the same values of the variables appearing in them.

(a = b) And (( b = c) Or ( a = c ));
( a = b ) Or (( b = c ) And ( a = c ))

Execution Of IF Conditions
I stuck with if statement's behavior, if you combine more conditions with 'and' operator for 'IF' statement, why it executes all the conditions at once to check the true/false state? Instead it should execute conditions one by one.

for example

bVal=False

If bVal=True And MsgBox("Do you want more?",vbYesNo)=vbYes Then
...
End If

Now we know bVal is False and the statements within if..endif are not going to execute, its worthless to check msgbox return value. Moreever if you respond either yes or no to msgbox, the lines between if...endif are not going to execute at all.
I want to say if the first conditions turns to 'True' then and only then it should check the second condition else quit checking more conditions and continue at the line below 'End If'

Any way I can write another 'if' statement withith first 'If', but why should I?
And what if I have five or more conditions to check?

Help! Multiple If Conditions?
ok... I want my program to check all "Checkboxes" - which it does... but I want it to check throughout the program (i used a timer) to see if ALL the checkboxes are checked. However, I am not too sure about how I would basically say:

If check1, check2 AND check3.value = true then
msgbox ....
end if...

please help

Help! Multiple If Conditions?
ok... I want my program to check all "Checkboxes" - which it can do... but I want it to check throughout the program (i used a timer) to see if ALL the checkboxes are checked. However, I am not too sure about how I would basically say:

If check1, check2 AND check3.value = true then
msgbox ....
end if...

please help

Help With Adding With Conditions
I have numeric values in numeric order in cells A1:A500. These values range from 0-1000. What I need to do is count down past all the zeros until I come to the first number above 0. then I need to take away the first 5 numbers and the last 5 and add them all up. Please can anyone help with some VB to do this.

any help is appreciated.

thanks

Check A Conditions
can anyone help me..

how to check a conditions in dataenvironment. like my database field reflect 0 or 1. the value 0 represent "male" and 1 represent "female". so i h've to print in my datareport instead of 0 and 1.

very urgent..
Regards
RajeshKumar.M

Conditions For Messages Box ?
I want to have a condition for when a message box is clicked. Like if i have ok and cancel buttons on the message box how do i determine which one is clicked ?

Msgbox Conditions?
i want to display a msgbox with an okay and a cancel button

if the user clicks okay i want to fire some event
if the user clicks cancel i want to exit sub.

my question is how do i catch the presses of the different buttons on the msgbox?

If Statements - AND And OR Conditions?
I'm searching a database and adding the contents of a field to a listbox if the conditions are met. I'm not getting records that I know are there and getting some records that I shouldn't get.

I am wondering how the AND and OR conditions work in an If statement.

Such as:
VB Code:
If rs.Fields("WONumber") = MyVar1 Or _ rs.Fields("WONumber") = MyVar2 Or _ rs.Fields("WONumber") = MyVar3 And rs.Fields("ConstDate") >= DateVar1 And rs.Fields("ConstDate") <= MyDateVar2 Or _ rs.Fields("WONumber") = MyVar4 And rs.Fields("ConstDate")>= MyDateVar3 And rs.Fields("ConstDate") <= MyDateVar4 Then  List2.AddItem rs.Fields("WONumber")
Now, is each OR it's own condition, and all the ANDs one condition or what.

The way I have it written is the way I want each condition.

VB Code:
If rs.Fields("WONumber") = MyVar1 Or _    'One conditionrs.Fields("WONumber") = MyVar2 Or _    'One Conditionrs.Fields("WONumber") = MyVar3 And rs.Fields("ConstDate") >= DateVar1 And rs.Fields("ConstDate") <= MyDateVar2 Or _  'One Conditionrs.Fields("WONumber") = MyVar4 And rs.Fields("ConstDate")>= MyDateVar3 And rs.Fields("ConstDate") <= MyDateVar4 Then 'One Condition   List2.AddItem rs.Fields("WONumber")


I just don't know how the OR and ANDs are handled.

Any help is appreciated.

JO

Do While -Multiple Conditions-
Is there a way that you can be executing a do loop statement while multiple conditions are true and then have the loop stop as soon as one of the two becomes false?

i.e:

Do While (MyVar1<5 or MyVar2<10) (Which ever comes first)
...
...
...
loop


-thanx-

Acting On Conditions
Hi,

When certain conditions are met in my program, I want form1 to open form2. I now need form1 to wait until form2 is closed, but then carry on along the program.

Any idea's

Ta
Nick

Inserting Into DB With Conditions
I am trying to write code for inserting data from one imported table into several exisiting tables. I need help with my conditions for my SQL for inserting the data into these new tables. I have a primary key, AssetID, and want to check the new tables for this AssetID before inserting the imported data into the tables. If AssetID already exists, I need to UPDATE the field in the table. If AssetID does not exist, I need to INSERT the new field into the table. Can I incorporate this condition into my SQL somehow, or do I need to write a function that checks then call the function before running my SQL for INSERT or UPDATE. Any and all help will be greatly appreciated. Thanks

Conditions Evaluation
This is really dumb, but it's giving me problems.
Can't get this ElseIf to get to the next image control to make it visible.
Dim strRedSox As String
Dim strYankee As String
Dim strFull As String

strRedSox = lstRedSox.Text
strYankee = lstYankee.Text
strFull = strRedSox$ & strYankee$

If strFull$ = PedroJeter Then
imgPedro.Visible = True
MsgBox "________", vbInformation, "___________!"
ElseIf strFull$ = SchillingJeter Then
' imgPedro.Visible = False
imgSchilling.Visible = True

Tried w/o the comment and about six other ways, imgPedro always displays.
Would a Select Case statement be better, and if so how?
Thanx.

Random Conditions
Hi, i am trying to write a program for an experiment. Ok i have got an image and i can draw a circle, cross or triangle on it. I can set it up manully so that the image will just randonly position say a triangle or a circle, 10 times. What i need is for the program to randomly position a triangle 10 times, circle 10 times and cross ten times. The order also has to be random as well say:
1) triangle
2)triangle
3) cross
4) circle
5) cross
6) etc

until there have been 10 presentations of cross, triangle and circle.
I have 3 functions, one for drawing a circle, one for the cross and another for the triangle, so i am guessing i need something to call these functions randomly and aslo not know when it has called one function 10 times so it doesn't call it again.

can anyone tell me how to do this, because its been bugging me for the last 3 hours!!

IF &lt;2 Conditions&gt; THEN IF &lt;1 Condition&gt; THEN....
I've always wondered if there was a more efficient / elegant way to write this type of statement:
Code:
        If strNewType = SearchGrid Or SearchGrid = "" Then
            If strNewType = SearchGrid Then Check4Dupes = True
            Exit For
        End If

Basically, we want to act on two conditions, but one condition needs an extra step if true.

|
+--JDMils
|
+--VB6
+--VB Dot Net
|
+-- Navman GPS Forums @ http://forum.jdmils.com
|

While Loops Conditions Ignored?!
Code:
Do While (.AbsolutePosition - 1) < .RecordCount & .AbsolutePosition > 0

When I debug this, AbsolutePosition is 1 and RecordCount is 4 yet it skips past the loop!

Race Conditions
hi guys.....

i'm recently developing a software that will somehow solve race conditions (and i named it CMa4 Server) in a network and iv been trying to implement the ideas of andrew tanenbaum by using a semaphore to protect my shared resource but somehow my program doesn't do what is expected to. the problem iv encountered is that there is an instance wherein a program is not allowed to complete it's expected operation because the server cannot respond to the request, this usually happens when a program has a "competitor" requesting simultaneously to access the shared resource. my questions are; (1) if programs simultaneously send a message to a server program, can server receive all these messages? and (2) where can i find lost messages?

your advice is a great help for me and codes are very much appreciated...

more power.

VBA Recompile Conditions
Let's say I have an xla in Excel. I've added a reference to a COM object which I've written. And I've compiled it and shut down.

Now let's say I upgrade the COM object with new functionality, enums, interfaces etc.

Is there a definitive list of changes to the type library which will cause the xla to recompile when I next try to run any functions in it ?

The actual situation I have is this : I have 2 xlas, both compiled against the same COM dll and xla 1 uses functions in xla 2 which use and return types in the COM dll.

The COM dll then gets upgraded. Its type library gets enums and interfaces added to it. The next time I run a function in Xla 1 it appears to get recompiled because of some change in the type library and throws an error on any functions it tries to access in xla 2, which appears not to be being recompiled.

Since I'm only making additions to the type library of the COM dll, no changes or removals of interfaces and enums, I don't know why xla 1 seems to think it needs to recompile.

Can anyone help ?

Darwen.

Need Help In Selecting Conditions.TQ.
Hi,

I wrote this code as below:-


Code:

Private Sub cmdSearchMC_Click()

uVK = (txtValKey)
uDetID = UCase(txtDetID)

If (txtDetID) = "" Then
MsgBox "Please type the Details ID and press the Search MC button.", vbCritical, "Error"
txtDetID.SetFocus
Exit Sub
End If

rs.Open "SELECT * FROM tblMC Where DetID='" & uDetID & "'", conn, adOpenKeyset, adLockOptimistic, adCmdText

If Not rs.EOF Then
rs.MoveLast
Select Case (DetID = uDetID)

Case ValKey = uVK
txtUnitNumber = rs!UnitNumber
txtApartID = rs!ApartID
txtOutstandingBF = rs!BalanceCO
txtCurrentI = rs!InterestB
cmdSearchMC.Enabled = False
cmdSearchPurchaser.Enabled = False
rs.Close

Case ValKey <> uVK
txtUnitNumber = rs!UnitNumber
txtApartID = rs!ApartID
txtOutstandingBF = rs!BalanceCO
txtCurrentI = rs!InterestB
cmdSearchMC.Enabled = False
cmdSearchPurchaser.Enabled = False
txtDetID.Enabled = False
Frame1.Visible = False
cmdCalculateInterest.Visible = True
cmdCalculateBalance.Visible = False
rs.Close

End Select

Else
MsgBox "The Details ID entered is not registered in the MC database." & vbCrLf & _
"Please press OK and search using the Search Purchaser button.", vbExclamation, "Validation"
cmdSearchMC.Enabled = False
cmdSearchPurchaser.Enabled = True
cmdSearchPurchaser.SetFocus
rs.Close
End If

End Sub


What I want to do is:-
search in the database from its last record where the DetID matches.
then, to check whether the ValKey as in the form matches the record in the database.
if the ValKey matches, code as below is executed.

Code:

txtUnitNumber = rs!UnitNumber
txtApartID = rs!ApartID
txtOutstandingBF = rs!BalanceCO
txtCurrentI = rs!InterestB
cmdSearchMC.Enabled = False
cmdSearchPurchaser.Enabled = False
rs.Close

if ValKey doesn't match then, code as below is executed.

Code:

txtUnitNumber = rs!UnitNumber
txtApartID = rs!ApartID
txtOutstandingBF = rs!BalanceCO
txtCurrentI = rs!InterestB
cmdSearchMC.Enabled = False
cmdSearchPurchaser.Enabled = False
txtDetID.Enabled = False
Frame1.Visible = False
cmdCalculateInterest.Visible = True
cmdCalculateBalance.Visible = False
rs.Close

and lastly, if the DetID doesn't match as any of the records in the database then, code as below is executed.

Code:

MsgBox "The Details ID entered is not registered in the MC database." & vbCrLf & _
"Please press OK and search using the Search Purchaser button.", vbExclamation, "Validation"
cmdSearchMC.Enabled = False
cmdSearchPurchaser.Enabled = True
cmdSearchPurchaser.SetFocus
rs.Close


I am not too sure whether I am using the correct type of code( select ). I am using this as I tried using If/Else statement previously but failed to work too.

Hope someone could please point out my mistake and show me how it could be done.

TQ very much.

Conditional Formatting For More Than 3 Conditions
Could someone shed some light on this subject for me please.
What I have is data is Column I & J from rows 4 thru 45 (I4:J45). I need the cell to be colored in by the data that is entered in the box.

THis is kind of the bare bones of the arguement:
Condition 1
If Cell Value > 0.1 then Green cell
Condition 2
If cell Value < -0.1 then Red cell
Condition 3
If cell Value between 0.1 & -0.1 then Yellow cell
Condition 4
If cell Value = "mid range" then white (no color)

I have been doing some research and found select case statements to probably fit the best from what im asking but i havent been able to create the proper syntax.
Please help anyone that may have an idea how to do this. thanks and very much appreciate it.

Test For Multiple Conditions And Then Sum
Hi guys, i've already posted a similar question, but i just need to expand on it slightly. My excel formulae is nowhere near as good as my VB is so please treat me like a n00b.

i need a formula to test for conditions within two seperate ranges, and then if said conditions are met, then to return and Sum() a value from another field

eg:
if i had a sheet with three fields, "month", "day" and "value" i want the formula to sum() all results from the "value" field where the corresponding fields month="jan" and day="sat"

the vaule of "value" is a real number

Thanks in advance

Deleting Dups With Conditions
Hello,

I am trying to run through a routine that will look down a column (A) and find those that are duplicate names and delete them (Shifting the data up). The duplicate names are always on top of each other so searching is not needed. However, there could be multiple duplicates and the number varies.

The conditional portion of this task is that IF the cell value cells(activecell.row,3) is less than the first duplicate then that value must be kept to compare it to the rest of the dups. The same goes for cell value cells(activecell.row, 4). At the end of the loop, values kept are placed with the name.

To be more specific, these values are employee name, start time and finish time. The start and finish times are what creates the dups because the way that our system is setup. I need the earliest start time and the latest finish time.

I have attached an example of what I am trying to accomplish. Further, the second sheet is the complete data in case you need to refresh sheet 1 with new data.

Here is the code that I am trying to run:

Code:
Sub DeleteDups()
Dim intW As Integer
Dim intX As Integer
Dim intY As Integer
Dim intZ As Integer
Dim intStartRow As Integer
Dim intFinishRow As Integer
Dim strStartName As String
Dim strStartTime As String
Dim strFinishTime As String

On Error Resume Next
intW = Application.WorksheetFunction.CountA(Sheets(1).Range("A:A"))
For intX = 1 To intW
strStartName = Sheets(1).Cells(intX, 1).Value
strStartTime = Sheets(1).Cells(intX, 3).Value
strFinishTime = Sheets(1).Cells(intX, 4).Value
intY = intX + 1
If Sheets(1).Cells(intY, 1).Value = strStartName Then
Do Until Sheets(1).Cells(intY, 1).Value <> strStartName
If Sheets(1).Cells(intY, 3).Value < strStartTime Then
strStartTime = Sheets(1).Cells(intY, 3).Value
End If
If Sheets(1).Cells(intY, 4).Value > strFinishTime Then
strFinishTime = Sheets(1).Cells(intY, 4).Value
End If
intY = intY + 1
Loop
Range(Cells(intX + 1, 1), Cells(intY, 5)).Select
Selection.Delete Shift:=xlUp
Sheets(1).Cells(intX, 3).Value = strStartTime
Sheets(1).Cells(intX, 4).Value = strFinishTime
strStartName = Empty
strStartTime = Empty
strFinishTime = Empty
intStartRow = Empty
intY = Empty
End If
Next intX
End Sub

The problems that I am having is that the routine runs into an error because intY tries to continue looping past the data Cell(35600).

The other problem is that the conditions are not working properly. It might grab the appropriate times with one employee, but the next might be wrong. I have done some basic logic tests and they seem to be wrong as well. For example, before I run the macro I see if cells(7,4) = "9:49PM" is greater than cells(6,4)= "10:02PM" and I get a TRUE. So maybe excel is doing its job correctly based on the data that it has, but logically, it is not correct. The current format of the cells is General. If I change it to time, it does not matter.


Any ideas would be great!

Thanks again!
Mike

Copy Row Based On Two Conditions
Hi

I have a database that I want to copy the entire row to another worksheet if the values in two rows match the values in a sheet called “switch in” cells B2 & B3. The first sheet is called “datalist” and the rows that I’m trying to match are rows 1 and 3

The data needs to be copied to the sheet called “Switch In” starting at row 5

The value in row 1 is a date, so if the date land within the month in Cell B2 and the value in Row 3 matched the value in cell B3 then the entire row needs to be copied.
Thank you

Conditional Formatting 3 Conditions Not Enough
I'm trying to change the background colour of a cell based on various parameters however the conditional formating menu point doesn't offer enough conditions. So I'm having to write a VB function to do the following and would be very grateful for any advice on the situation.

On the worksheet, in column A I have a list of modules and in column K something called the "severity of problem".

Only the "severity of problem" is changed manually and I would like my function to check for changes in this column. Then I would like my function to return
1. the name of the module in column A and
2. the value of the "severity of Problem" cell in column K.

I'm a novice at coding VB and would be grateful for any starters/pointers/code.

Thankyou in advance

Excel VBA: Create New Conditions
Hi all,

I have this program with a userform With a click of a command button in the userform, i want program to prompt user using input box for no. of criteria tables to create. (eg. in attchment>> LUTS)
After prompting for no. of LUTS to create, program would prompt for each LUTS no. of criteria in each LUT.

Eg: command button click:
inputbox: "Enter no. of LUT to create: " Eg. 3 in the attachment
then next:
inputbox: "Enter no. of criteria in LUT no. 1: " Eg. 5 in the attachment
inputbox: "Enter criteria no. 1: " Eg. Tan
inputbox: "Enter criteria no. 2: " Eg. Joo
.....
.....
inputbox: "Enter criteria no. 5: " Eg. Lee

Then this will continue for the 2nd LUT:
inputbox: "Enter no. of criteria in LUT no. 2: " Eg. 4 in the attachment
inputbox: "Enter criteria no. 1: " Eg. Alexander
.....
.....
inputbox: "Enter criteria no. 4: " Eg. Hoh

this will continue until all LUT and their criterias are created.
Anyone knows how to do this using vba coding??
This is really complicated for me for someone of my lvl. :/
pls see attachment for details. Thanks in advanced.

ps: cant attach excel file so i have copy and paste it to notepad


Abyss of the goat

Countif Stacked Conditions
hey guys, call me a n00b but i have a small problem with the COUNTIF function

i just need it to be able to test two seperate conditions on two seperate columns but i cant work out how its done

for example, i have two columns on a sheet, one called type and one area, both are text, i need to count how many items of one type are in a particular area.

how do i stack them?

Thanks in advance

Copyright © 2005-08 www.BigResource.com, All rights reserved