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




Excel - Return Column Alphabet From Column Number


Is there some function in Excel (VBA) that will get me the column alphabet if I provide the column number, or do I have to write one. I ask because I know that there is, yet I recall I wrote this functionality for the previous application, and I don't want to:

(1) Reuse it, if there's something in-built
(2) Re-invent the wheel

Help!

PS: I'm looking for something like:


Code:

Function GetAlphabet(ByVal ColumnNumber as Long) as String


which if I call must say:


Code:

GetAlphabet(1)


Output: "A"




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
How To Convert Scientific Value Of Excel Sheet Column To Number Column In Vb?
Hai Experts,
I have two columns in excel sheet with scientific value (Eg:5E+11).I want to convert this value into number --like 500000(not specifically this no this is an example) using visual basic

Can anyone of u tell me how to do this conversion process?

Thanks...

Logic For Excel Column Alphabet Mapping To Numbers
I need a logic for writing a Excel Column Alphabet to number conversion. Like 'A' translates to 1, 'B' translates to 2, etc. This should include columns like 'AA', 'AB', 'AC', etc.

SQL Return If For The Column Row There Is Only One Number Or Many -- Matching
okay I have a column where for example say I have the number

100423 well I can have another 100423 it is not unique. How can I write code in SQL working in SQL that can do that.

What I am basically trying to do is if there is only one instance of a particular number. I want to get a count of all those, any ideas?

Excel Userform - Search Column For Field And Return Rows Of Data
Hello all!

I am very new to VBA, so I apologize in advance if I’m not formatting my questions appropriately.

I’m trying to create a “reporting tool” using a UserForm in Excel. There is a large master sheet full of data and the users will select which data they would like to view from the userform, click a command button, and then a smaller set of data will be viewable on the sheet. (Basically hiding rows and columns of data.)

Column A is Manager Name
Column B is Order Status

On the UserForm, users can select by Manager name to choose which records they would like to see. (Example, John Doe, Susy Q, Brad Brown)

They can also select Order Status (open, filled, cancelled).

I need some sort of code so that when the user selects “John Doe”, it searches through Column A and only returns the rows of data corresponding to John Doe. I need the same thing to happen if they select “open” for Order Status. (Searching Column B and returning corresponding rows for “open”.)

Is this possible? What type of function should be used for this?

Thanks in advance!

Excel: Converting Column Number To Name
I have a problem I just can get figured out (maybe because it's so late).

I need to find a way to convert a column number to the character form.
Ok, I will give an example, 1 -> A, 2 -> B, 52 -> AZ, ... you get the point. So I came up with this brilliant idea (not really) to treat it in about the same way you would treat conversion from binary to hex, but using a 26-based-like system in stead of a 16-based. At first, all look good, but after some testing, I got some errors with multiples of 26 (not including 26 itself). I think the problem is that my system does not has a zero equvalent. After AZ must come BA, not B0 <- B-ZERO.
Any suggestions

public Function XLCol(byval iCol as Long) as string

Dim lvl as Integer
Dim r as Long
Dim cpos as string
Dim strABC as string

strABC = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

lvl = 0

Do Until 26 ^ (lvl + 1) > iCol
r = iCol Mod (26 ^ (lvl + 1))
cpos = mid(strABC, r, 1) & cpos
iCol = iCol - r
lvl = lvl + 1
Loop

cpos = mid(strABC, iCol / (26 ^ lvl), 1) & cpos

XLCol = cpos

End Function




Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

How Do I Get The Number Of Rows In An Excel Worksheet Column
Folks,

I've have crete a app to read Excel worksheets into an array but I can't find a function/property to return a rowcount of a column any ideas?????

The msdn help says the following...

Tip: Use the CurrentRegion property to return the number of rows in an Excel column. You can then use this number to specify the upper bound of the array's first dimension.


But I cant get it to work
Heres my code.........

'''''''''''''''''''''''''''''''''''''
Dim xlsWorkBook As Excel.Workbook
Dim xlsWorkSheet As Excel.Worksheet
Dim xlsRange as Excel.Range
Dim iCount As Long

Set xlsWorkBook = GetObject("C:vbtest.xls")
Set xlsWorkSheet = xlsWorkBook.Worksheets(1)

Set rCol = xlsWorkSheet.Columns(1)
xlsRange = rCol.CurrentRegion

Debug.Print xlsRange.???? 'what has the row count it

xlsWorkBook.Close
'''''''''''''''''''''''''''''''''''''''''''

Please help!
ft

Dynamically Selecting Column By Excel Column Header (B, C... AD, AE, Etc...)
Hi Guys,

I am trying to automate a spreadsheet which would copy values from one worksheet, and insert it into another after manipulating the values slightly. I have managed most of the code (as shown below), however, I am struggling with the following:

The spreadsheet contains pricing information per supplier, and my aim is to retrieve the part number, size, and cost per part from the original into a new worksheet. As the cost column may be in a different location (based on multiple suppliers in the same worksheet), i need to prompt the user for the worksheet that contains the information and the column that contains the costs. I have already written the code to parse the worksheets and populate the combo box with worksheet names. Once a worksheet is selected, I need to parse all used columns and populate a combo box with the Excel column headers (e.g. B, C... AX, BE, etc...) so that the user can choose the correct column. Once the column is selected, I need to convert the column header to a column number (e.g. Col A = 1, Col F=6, Col BC = 46) so that I can copy the values via the GetRows() subroutine in the code.

Any help would be appreciated. Included below is the code that I have thusfar...

Thanks

-------------------------------------------------------------------------------------------------


Code:
Private Sub Worksheet_Activate()

' Get number of worksheets in workbook
cboWorksheet.Clear

' Get names of worksheets in workbook
For iCnt = 1 To ActiveWorkbook.Worksheets.Count
    If ActiveWorkbook.Sheets(iCnt).Name <> "Data Unload" Then
        cboWorksheet.AddItem ActiveWorkbook.Sheets(iCnt).Name
    End If
Next

End Sub

Private Sub GetRows()

' Setup variables to be used
Dim strNewPart, strNewSize, strSheetName As String
Dim iRowData, sRowData, x, y As Integer


strSheetName = cboWorksheet.Text
    
For i = 1 To ActiveWorkbook.Worksheets.Count
    If ActiveWorkbook.Sheets(i).Name = strSheetName Then
        x = i
    End If
    
    If ActiveWorkbook.Sheets(i).Name = "Data Unload" Then
        y = i
    End If
Next
    
    
'Setup Variables in Use
iLastRow = ActiveWorkbook.Sheets(x).Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row
sRowData = 6
Debug.Print "iLastRow = " & iLastRow
Debug.Print "sRowData = " & sRowData

       ' Loop through the data table and compare each record with data in the lookup table
       ' If a match is found, change the data table with what is in the lookup table
       For iRowData = 8 To iLastRow
           If Worksheets(x).Cells(iRowData, 1).Value <> "" Then
              
              If Worksheets(x).Name = "Product X" Then
                  strNewPart = Worksheets(x).Cells(iRowData, 1).Value & "X"
                  strNewSize = "0.0"
              Else
                  strNewPart = Worksheets(x).Cells(iRowData, 1).Value & "Z"
                  strNewSize = "0.0"
              End If
              
              Worksheets(y).Cells(sRowData, 1).Value = strNewPart
              Worksheets(y).Cells(sRowData, 2).Value = strNewSize
              
           End If
       
       'increment Unload Counter
       sRowData = sRowData + 1

       Next

       Worksheets(y).Select

End Sub

Excel Is Chaznging Number Format To Time When Using VBA To Populate Column
Hi There,

Newbie here.
I have written a sub that goes through and strips the curly brackets from and seperates the two integers into separate columns for {123:456} formated data.

When I run the VBA Excel assumes I want time format and changes the format of the column receiving the two integers to Time from General.
I tried turning off auto fil... but could not change this behavior.

I also tried to find out how to change the format of the cells back to "General" in code but could not figure it out.

Any help would be greatly appreciated.

Thanks,
Marc

How To Fill A Column Serial Number Wise In Excel With Code Done In Vb
i have created a project in vb where i am displaying text in a flexgrid and am then exporting it to an excel spreadsheet where it is also sorted out.I have left an empty column named sr.No. in the grid.What i would lke 2 do now is that after the dat is filled in the spreadsheet,the sr.no column should be filled number wise starting from 1 till the end i.e till data is displayed in the rows.

[ASK] How To Return Value From MSHFlexGrid Column?
Help me please!

How to Return Value From MSHFlexGrid Column?
SQL Statement?

sq1 = "Update tb_stock set Out = '" & MSHFlexGrid.???? & "'" ????

how to take the value/data from the MSHFlexgrid Column

Thanks.

How Can I Get Return Value That Is A Table With 2 Column From Oracle SP
The Code look like that:

Oracle side:
PACKAGE Simple_Package AS

TYPE t_pk1Type1 IS TABLE OF VARCHAR2(100)
INDEX BY BINARY_INTEGER;

TYPE Errordetails IS RECORD
( Code VARCHAR2(50),
Description VARCHAR2(1000)
);

TYPE Terrordetails IS TABLE OF Errordetails INDEX BY
BINARY_INTEGER;

PROCEDURE proc2
(
i_Arg1 IN NUMBER,
ao_Arg2 OUT t_pk1Type1,
ao_Arg3 OUT Simple_Package.Terrordetails
);

END Simple_Package;

PACKAGE BODY Simple_Package AS

PROCEDURE proc2
(
i_Arg1 IN NUMBER,
ao_Arg2 OUT t_pk1Type1,
ao_Arg3 OUT Simple_Package.Terrordetails
)
AS
i NUMBER;
BEGIN

FOR i IN 1 .. i_Arg1 LOOP
ao_Arg2(i) := 'Row Number ' || to_char(i);
END LOOP;

FOR i IN 1 .. i_Arg1 LOOP
ao_Arg3(i).Code := i;
END LOOP;

END proc2;

END Simple_Package;

I try to use this VB code but i get Access vaiolation or syntacx error:

Dim QSQL As String
Dim CPw1 As rdoQuery
Dim Iusername, Ipassword As String

sUserName = "'CUSTOMER'"
sPassword = "'CUSTOMER'"
QSQL = "{call Simple_Package.Proc2(?,{resultset 3, ao_Arg2}," _
& "{resultset 3, ao_Arg3},{resultset 3, ao_Arg3})}"
Set CPw1 = Cn.CreateQuery("", QSQL)
CPw1(0) = 2
' in this point i get the Error
Set Rs = CPw1.OpenResultset(rdOpenStatic, rdConcurReadOnly)

is vb support this data structure as in (ao_Arg3)

How Can I Get Return Value That Is A Table With 2 Column From Oracle SP
The Code look like that:

Oracle side:
PACKAGE Simple_Package AS

TYPE t_pk1Type1 IS TABLE OF VARCHAR2(100)
INDEX BY BINARY_INTEGER;

TYPE Errordetails IS RECORD
( Code VARCHAR2(50),
Description VARCHAR2(1000)
);

TYPE Terrordetails IS TABLE OF Errordetails INDEX BY
BINARY_INTEGER;

PROCEDURE proc2
(
i_Arg1 IN NUMBER,
ao_Arg2 OUT t_pk1Type1,
ao_Arg3 OUT Simple_Package.Terrordetails
);

END Simple_Package;

PACKAGE BODY Simple_Package AS

PROCEDURE proc2
(
i_Arg1 IN NUMBER,
ao_Arg2 OUT t_pk1Type1,
ao_Arg3 OUT Simple_Package.Terrordetails
)
AS
i NUMBER;
BEGIN

FOR i IN 1 .. i_Arg1 LOOP
ao_Arg2(i) := 'Row Number ' || to_char(i);
END LOOP;

FOR i IN 1 .. i_Arg1 LOOP
ao_Arg3(i).Code := i;
END LOOP;

END proc2;

END Simple_Package;

I try to use this VB code but i get Access vaiolation or syntacx error:

Dim QSQL As String
Dim CPw1 As rdoQuery
Dim Iusername, Ipassword As String

sUserName = "'CUSTOMER'"
sPassword = "'CUSTOMER'"
QSQL = "{call Simple_Package.Proc2(?,{resultset 3, ao_Arg2}," _
& "{resultset 3, ao_Arg3},{resultset 3, ao_Arg3})}"
Set CPw1 = Cn.CreateQuery("", QSQL)
CPw1(0) = 2
' in this point i get the Error
Set Rs = CPw1.OpenResultset(rdOpenStatic, rdConcurReadOnly)

is vb support this data structure as in (ao_Arg3)?

Weird MySQL Column Return
Hey guys,

My question is about MySQL and using the Connector/ODBC driver through VB 6. Anyways, I have a table setup with a TIME-type field. I insert it using an SQL statement like:

INSERT INTO Reservations (ResID,PUTime) VALUES(12,'14:13:00')

And when I use ADO to get at the just inserted data, a call like rs!PUTime returns a null value. When I access the database through a separate client (like MySQL's own command-line client or Aqua DataStudio), the appropriate time is returned.

Anybody have any ideas?

How To Return Column Header Name In Datagrid?
I want that, when I double click on a datagrid cell, it will return a msgbox with Column header name (i.e. field name). Like if I write


Code:
msgbox datagrid.text


Then it returns the value of that cell. But I need the column name. Is that possible?

Return Value From A Multi-Column Listbox
How can I return the value in the 3rd column of a multi-column listbox, when the user chooses a row?

Return Column Names From SQL Table
I want to add all the column names to a list box from a SQL Server 2000 table, ie. for each columnName, list1.addItem columnName
Please can someone let me know how?
Any help would be greatly appreciated,
Thanks,
Ryan

Changing The Column Width To The Longest Text In A Column With The MSFlexGrid Control
hi all how do i change the width of a column to fit the text inside that column i am using the MSFlexGrid to show my data, with an adodb conenction
thanks from
mark

Concat Column Values From Multiple Rows Into A Single Column USING Mysql4.1
I"M USING MYSQL 4.1
I'm having two table like:
Doctor Product
1) Doc_ID Name 2) Doc_ID Product_Name
    Doc_1 X Doc_1 p1
    Doc_2 Y Doc_1 p2
         Doc_1 p3
                                           Doc_2 p4
                                           Doc_2 p5
I'm joining these two table via query
Select Doc.Doctor_Id, Doc.Name, Prod.Product_Name from Doctor as Doc inner join Product as Prod on Doc.Doc_ID = PRod.Doc_ID
 
Result is:
Doc_1 X p1
Doc_1 X p2
Doc_1 X p3
Doc_2 Y p4
Doc_2 Y p5

BUT I WANT IT IN THIS WAY: WITHOUT USING THIRD TABLE
Doc_1 X p1,p2,p3
Doc_2 y p4,p5

CAN ANY ONE HELP ME OUT







Edited by - Macmillan on 6/9/2005 5:19:20 AM

Last Number In Column
I have a workbook with a number of worksheets. In column d of each worksheet will be the balance of each account. I want to get a sum of all of these balances. The balances could be on any row in column d. Is there a function I can use to get the last number in column d, or do I need to use code?

Look Up A Number In Another Column
I am trying to find out a way to look up values between columns.

If numbers in Column A can be found in Column B, return 3 or N/A. I don't think Vlookup will help me in this case. Can anyone help me?

Thanks.

Column Number
Is there a way to detect the column number that the cursor is on in a rich text box?

How To Copy And Modify Listview Column Items And Put It In Diffrent Column?
Hi all i got a listview with first column has songIDs i want to for each item in songId column to take its value and add http://localhost/new/play.php?d=04&song= to it and to make it http://localhost/new/play.php?d=04&song=1234 and fill url column. But i do not know . could any one help me achive this .thanks

Save Single Column Array Into Variable Column Text?
So, I have an array with a couple thousand numbers that that represent values from a number of channels in one column. I'd like to put the numbers from one channel in one column. I have a listview that does that but now I'm trying to save the values into a text file. Right now to write them I have:

Open dlgSA.FileName For Output As #1 'dlgSA is the common dialog
On Error GoTo 0 ' turn off error handler

For K = 0 To arraySizeInSamps
Write #1, data(K)
Next K
Close #1 ' close the file
Exit Sub

The number of columns will be the same as the number of channels which is declared as: Dim numChannels As Long

Like I said before, my listView does have them separated in the correct columns but I can't figure out how to save that AS IS in a text or dat file.

Anybody have any ideas?

Thanks for your help.

Get Column Number Od Cell
Hi, for a macro I'm making in Excel, I need to get the column number of a specified cell. I was wondering if excel has any built in functions to accomplish this. I would want something that would return 5 as the column number if I entered "E2" as the cell. I only need the column number and not the row number...

Finding The Column Number Where The Max Value Is In A Row
I would like to use a standard function but, so far, without success. Maybe I need a VB script?

I have rows each with many columns and different Values (numbers). I need to find the column number in every row where the maximum number is.
Thanks for help

Finding Column Number
please help
I would like to find a number in a table in excel and then i want to obtain the value of the column number of the founded cell as a digit to be placed in a new worksheet. i.e if the value is in C43 i would like to obtain the value 3 (representing the column C) into a new worksheet.
the last line its just my attempt to do it that doesn't work


Code:
Cells.Find(What:="484879", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
Selection.Cells.Column = Worksheets("New").Cells(1, 1)
End Sub

thanks in advance

Row Number Column In A Report.
Hi,
I'm not sure if this is a newbie question, but anyway I'm going to ask it.
My program deals with a database (SQL Server) and it's supposed to provide some reports. In order to provide the reports, I'm using Data Report to print the query results.
I query a SELECT statement from the database, and then put the result in a recordset, then bind the recordset to the Data Report's DataSource property.
Actually I do not have a bit of a problem so far. My problem starts when I try to put a Row Number column in my report. This row number, simply starts from 1 and shows the row number of that particular row in the report. More explanations needed?
The query may have any number of rows, with any order, but the row number should simply start with 1 and so on. I could not find any way to have that column.
Your help will be appreciated.
K.Karimi

Search Column For Number Between 6 &amp; 12
Hi guys ive been working on a new way of searching for my criteria using select case instead of the filter option, this allows me to do things easier later in my project.

This is the code im using


Code: Sub FindWOUNDER12DAYS()
'Selects cells containing "1" in column J
Dim Col As Integer
Dim cel As Range
Dim SelRange As Range
    For Col = 11 To 11 'columns a through j
    For Each cel In Range(Cells(1, Col), Cells(65536, Col).End(xlUp))
        Select Case cel
        Case 6 To 12
            Set SelRange = cel
        Case Else
            MsgBox "There are no records in backlog for 6 to 12 days", vbInformation, "No Backlog Reported"
            End
    End Select

    Next cel
    Next Col
SelRange.Select
End Sub


The only problem is the celvalue does not seem to be the correct way of getting the current cell value in the loop.

Any help would be much obliged as im a bit stuck on this. If its something silly im doing, which it pobably is, dont be afraid to call me an idiot. I should know better hehe.

AL



Edited by - Big Al Inc on 8/14/2007 3:43:30 AM

Add Each Number To Different Column In Database
Hello!

I have a one dimension array where a store values that i get from the picture.

In the database i have a table with 10 columns that i named Picture0-Picture10. How can i take the first 10 numbers from the array and put each one to another column??? I mean horizontal and not all 10 numbers in one column like Picture0.What kind of loop do i need to do!

I work with adodc datagrid and sql server!

Any ideas???

Thank's!

Count No Blank Cell In Column AC Based Var In Column Y
With acode in VBA please....

How to count all non blank cells in column AC based a Var filled ="PUGLIA" and return the result of count in var My_count...
In thsi case the coun = 6
I use this formula but difficult to translate in VBA....

=MATR.SOMMA.PRODOTTO((Y3:Y1000="PUGLIA")*NON(VAL.VUOTO(AC3:AC1000)))

Simple Problem... 4-column CSV File -&gt; VB 4-column Array
Hi,
I've got a simple question to most... but I've never used multiple-column arrays before (never could learn how to, mind-boggling!), and not really sure how to progress with this... not even sure if I should be using a multi-column array!

The CSV is like this (even with linebreaks):
392,3290,109
310,1900,490
390,1903,300
321,1033,610

and the CSV has 365 lines in it (one for every day of the year).

I am trying to get the data into an array so it also has an index, like this:

Code:
Index, Cost, Units, Time
1, 392, 3290, 109
2, 310, 1900, 490
3, 390, 1903, 300
4, 321, 1033, 610


This is the cost I use for importing the data into the program, and process into the array:

In the FOR loop, I want to 'index' the CSV file..

Code:
Public Sub GetCostTimeRatio()

Dim hFile As Long
Dim LocalFileName As String

Dim CostTimeRatioIndex(0 To 365, 0 To 365, 0 To 365, 0 To 365) As Long
Dim CostTimeRatioString As String
Dim CostTimeRatioLines() As String
Dim n As Long

LocalFileName = "Q: imeandcost.csv"
hFile = FreeFile
Open LocalFileName For Input As #hFile
CostTimeRatioString = Input$(LOF(hFile), hFile)
Close #hFile

CostTimeRatioLines = Split(CostTimeRatioString, vbCrLf)

For n = 0 To UBound(CostTimeRatioLines)
CostTimeRatioIndex(n,,,) = Split(CostTimeRatioLines(n), ",")
Next

Debug.Print CostTimeRatioLines(UBound(CostTimeRatioLines))
End Sub


But it says 'Can't assign to array' when I call the function, and it itemises the CostTimeRatioIndex as the culprit...

Help.... please...

Many thanks,

Gabba

Getting The Column Header Of The Selected Column In True DBGrid(5.0)
Hi Everyone,
I am new to VB and having a few problems with the code.
I have a true DBGrid(5.0) on a form.When i load the form,i bind the Grid to display values from a table in the Database onto the grid.What I am required to do is to be able to click on any column in the Grid and be able to sort them
(ascending or descending).
I am able to select the whole column by clicking on it.But How do I get the column header or the column number of the column what I select.I will be selecting only one column at a time. I tried finding the values of the column selected by using
colnumber =TestGrid1.Col in TestGrid1_Click event but it is not giving me the correct answer.
Could anyone help me with this...
Thanks,
--Veda

Alter Column NAME In ACCESS,without Dropping The Existing Column?
Hi,

is there a way in MS Access to alter the name of a column through SQL without the need to drop the original column?

Until now, I only saw solutions that drop the original column.

Thnx alot!

Column Number Instead Of Letters In Ranges
Hi i'm struggling on selecting a range using column number. I can identify last Column name (e.g. "F") or column number (e.g. "5") by using code

Selection.End(xlToRight).Select

Let colname = Mid(cell, 2, 1) 'column name (this assumes the excel table is not larger than "Z"!!!)

Let colnum = Selection.Column 'column number

however if I need to then use a cell from second to last column (e.g. Range(E21).Select) I get stuck as you cannot use "colnum-1" to locate second to last column.

Is there anyway to use column numbers in ranges or to select a cell, or alternative a way to find the alphabetical letter before the one selected?

Can anyone help?

Thanks in advance

Derek

How Do Icount The Number Of Variables In A Column
I have a column in an excel spreadsheet with various 9 digit numbers in it. I want to be able to count at the bottom how many different numbers there are. So, for a simplified example, if my list contained:

a,a,b,b,g,a,b - i need to be able to calculate that there a 3 different variables (a,b,g)

I thought there would be a simple formula to do this in excel but i am struggling to find it. Will i need to use vb to do this or have i missed something obvious?

Thanks in advance!

Convert Column Number To A Letter
Hi all,

Say you have the formula

Code:
.cells(rownum,colnum)
rownum and colnum are variables.

How would you get vb to convert the rownum into its letter equivalent. I just want the letter value for that range, not the row number aswell.
e.g colnum=5, therefor its letter equiv is E

Also, I want to extend this so it goes further than IV, the furthest cell to the right on an excel spread sheet.
So if I typed in 300, it will say what Column letter it would be, even though the maximum amount of columns is 256.

Thanks for any help given

How To Have A Ordinal Number Column In A Datagrid?
Sorry for my English.

Thank you for your pay attention!

There is a table in my database.
I want to show all records of the table on DataGrid, and I also want to have a column that show the ordinal number of the record on the grid. What should I do?

Please help me.
Regards,
CuongHa.
P/s: I'm using windows98,VB6.0,MSAccessXP,MADO2.7

Union Between 2 Set Of Values With Different Number Of Column
basically, what i want to do is

select A,B,C from Table1
union
Select A,B from Table2

i know therE's some way to asign a value to the 3rd column, but i dont remember, and i seriously have no clue how to search this...

Input From Files Column Number
I would like to do like this, If the files have four column from files, then it read the the line header from column 2 untill four.. How to do that? I have files some time have 3 column and four column. By default I want the program read the X,Y,Z value only.


Code:

Open txtDir For Input As #12

n = 0

'Read the file header first
Line Input #12, XYZ
' Print #13, "X", "Y", "Z"
While Not EOF(12)

Input #12, Point.x_out, Point.y_out, Point.z_out
n = n + 1 'Record counter

Wend

How To Know The Listview Column Number That I Have Clicked?
Hi, everyone.

How can i know the listview column number that i have clicked?

Thanks.

Flexgrid - Find Column Number
i am trying to find the column position of a column where the heading is the selected value in a combo box

my code at the moment is this...


VB Code:
For i = 1 To number_of_columns                If Results.MSFlexGrid1.TextMatrix(0, i) = Combo1.Text Then            selected_column = Results.MSFlexGrid1.Col        End If    Next

i'm probably not using the right property to get the column number

can someone help me out

thanks

Count Number Of Fields In A Column
How could i make an output parameter that will count the number of rows that return a null value.

I have rows and one of the columns vendor_id can return a null value. I need to count how many have a null value in the query i perform as an output parameter?


Help!!!

VBA Code For Searching Column From First Row And Formating The Column
I am coding a Excel macro in VB for searching a particular column (treat as header) from a number of worksheets in the workbook. That particular column Eg. Accessories, can be in more than one worksheet of the workbook, but it is always in the 3rd column of that particular worksheet (if there) of the workbook. The reason being I want to format the column size from 10 to 50 for that particular column.

Any help will be very much appreciated.

Thanks

Could go this way :


VB:
--------------------------------------------------------------------------------
Dim i As Integer
'loop through all of the workbook sheets
For i = 1 To ActiveWorkbook.Sheets.Count
'assumes a header in the first row of colunm 3
If Sheets(i).Cells(1, 3).Value = "Accessories" Then
Sheets(i).Columns(3).ColumnWidth = 50
End If
Next i


I received this reply. It works well.

But now can this be possible.

Can I do the same thing by searching any cell (Not just Accessories in 3rd column, but say accessories in 5th column and other attributes in other column and different worksheets) from the first row.

Any help is very much appreciated.

Thanks

VBA Code For Searching Particular Column And Formating The Column
I am coding a Excel macro in VB for searching a particular column (treat as header) from a number of worksheets in the workbook. That particular column Eg. Accessories, can be in more than one worksheet of the workbook, but it is always in the 3rd column of that particular worksheet (if there) of the workbook. The reason being I want to format the column size from 10 to 50 for that particular column.

Any help will be very much appreciated.

Thanks

Move Cell In Column B Based On The Value In Column B
I have an Excel spreadsheet that is updated daily. I need a macro that can

1) create a new column between column B and C
2) if the value in column B is 1, then move the value of the cell in column C to the newly created column
3) find the next instance of column B = 1...again move then move the value of column C to the newly created column...repeat until the end of column b

How To Display Flexgrid Values From Column 1 In Column 3
Hi,

how do I get my flexgrid to copy the cell values from the rows in my column 1, to the equivelant rows in my column 3? The number of rows will vary each time the flexgrid is loaded (set by a number I select).

At the moment, I am using the following code to do this, it works fine, but only for the first row, the rows that follow remain empty. I tried .Cols and .Rows but it doesn't seem to like this


With flexgrid
.TextMatrix(.Col, 3) = .TextMatrix(.Col, 1)

How To Make Most Right Column Hided Column Show?
Hello, Everyone:
I tried to most right column hided column show. But I can only choose one side: left side. For example, I hide all the columns which is right side of O column. So, I don't how to make all of them show up again? Would someone be kind to help me out?
Thank you very much!
Charlie

SQL Server + Checking Value Column Against Column In Another Table
Can anyone help?
I would like an employee to enter a sale into an orders table. but I would like to be able to limit them by requiring that the quantity field in the orders table can not exceed the quantity field in the stock table.
I am not sure but I heard that the Check constraint is limited to the table and fields that the check constraint resides in. If so How can I do this?

I would also like to be able to automatically reduce the stock Quantity by the amount given in the Orders Quantity field.


Code:
CREATE TABLE Employees
(
EmployeeID VARCHAR(10) NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Title VARCHAR(30) NOT NULL,
Salary DECIMAL(10, 2) NOT NULL Check (Salary >= 0.00 AND Salary < 300000.00),
ComRate DECIMAL(10, 2) NOT NULL Check (ComRate >=0.00 AND ComRate < 0.20)
)

GO

CREATE TABLE Customers
(
CustomerID VARCHAR(10) NOT NULL,
Company VARCHAR(20) NOT NULL,
Address VARCHAR(50) NOT NULL,
City VARCHAR(20) NOT NULL,
State VARCHAR(2) NOT NULL,
ZipCode VARCHAR(5) NOT NULL,
LocationID VARCHAR(2) NOT NULL,
ContactName VARCHAR(20) NOT NULL,
ContactPhone VARCHAR(20) NOT NULL,
ContactFax VARCHAR(20) NOT NULL
)

GO

CREATE TABLE Products
(
ProductID VARCHAR(10) NOT NULL,
ProductName VARCHAR(30) NOT NULL,
Description VARCHAR(50) NOT NULL,
QtyPerUnit INT NOT NULL,
ReorderLevel INT NOT NULL, Check (ReorderLevel >= 1000 And ReorderLevel <= 2000),
Discontinued VARCHAR(12) NOT NULL,
MaxStockQTY INT NOT NULL Check (MaxStockQTY >= 3000 And MaxStockQTY <= 5000)
)

GO

CREATE TABLE Orders
(
OrderID INT NOT NULL,
EmployeeID VARCHAR(10) NOT NULL,
CustomerID VARCHAR(10) NOT NULL,
SupplierID VARCHAR(10) NOT NULL,
OrderDate VARCHAR(20) NOT NULL,
RequiredDate VARCHAR(20) NOT NULL,
ShippedDate VARCHAR(20) NOT NULL
)

GO

CREATE TABLE OrderDetails
(
OrderDetailID INT NOT NULL,
OrderID INT NOT NULL,
Line INT NOT NULL,
ProductID VARCHAR(10) NOT NULL,
UnitPrice DECIMAL(10, 2) NOT NULL Check (UnitPrice >= 39.99),
Quantity INT NOT NULL,
StockID INT NOT NULL
)

GO

CREATE TABLE Stock
(
StockID INT NOT NULL,
ProductID VARCHAR(10) NOT NULL,
Country VARCHAR(20) NOT NULL,
Quantity INT NOT NULL check (Quantity >= 0 And Quantity <= 5000)
)

GO

CREATE TABLE Location
(
LocationID VARCHAR(2) NOT NULL,
Country VARCHAR(20) NOT NULL,
)

GO

INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('AG00000001', 'Allen', 'Garner', 'President', '250000.00', '0.10');
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('BB00000002', 'Bobby', 'Brown', 'Vice President', '200000.00', '0.10');
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('CC00000003', 'Chester', 'Colby', 'Sales manager', '80000.00', '0.08');
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('DD00000004', 'David', 'Doyle', 'Salesman', '60000.00', '0.06');
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('EE00000005', 'Evan', 'Evers', 'Salesman', '60000.00', '0.06');
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('BF00000006', 'Ben', 'Franklin', 'Salesman', '60000.00', '0.06');
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('GG00000007', 'George', 'Gershwin', 'Salesman', '60000.00', '0.06');
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('MG00000008', 'Mel', 'Gibson', 'Salesman', '60000.00', '0.06');
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('PG00000009', 'Paul', 'McCartney', 'Salesman', '60000.00', '0.06');
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('MP00000010', 'Matt', 'Perry', 'Salesman', '60000.00', '0.06');
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('LS00000011', 'Luke', 'Skywalker', 'Salesman', '60000.00', '0.06');

GO

INSERT INTO Customers (CustomerID, Company, Address, City, State, ZipCode, LocationID, ContactName, ContactPhone, ContactFax) VALUES ('AS00000001', 'American Spices', '1231 Main St.', 'Vancouver', 'WA', '98664', 'US', 'John Smith', '360-604-3211', '360-604-3212');
INSERT INTO Customers (CustomerID, Company, Address, City, State, ZipCode, LocationID, ContactName, ContactPhone, ContactFax) VALUES ('FS00000002', 'French Spice Connect', '1232 Main St.', 'Camas', 'WA', '98607', 'US', 'Mike Smith', '360-834-3213', '360-834-3214');
INSERT INTO Customers (CustomerID, Company, Address, City, State, ZipCode, LocationID, ContactName, ContactPhone, ContactFax) VALUES ('AU00000003', 'Australian Spice Co.', '1233 Main St.', 'Battleground', 'WA', '98667', 'US', 'Bob Smith', '360-696-3215', '360-696-3216');
INSERT INTO Customers (CustomerID, Company, Address, City, State, ZipCode, LocationID, ContactName, ContactPhone, ContactFax) VALUES ('CA00000004', 'Canadian Spice Co.', '1234 Main St.', 'Washougal', 'WA', '98607', 'US', 'Randy Smith', '360-834-3217', '360-834-3218');

GO

INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBCinamin', 'Cinamin', '12 oz bottles', '24', '1000', 'Active', '5000');
INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBGarlic', 'Garlic', '12 oz bottles', '24', '1000', 'Active', '5000');
INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBOregano', 'Oregano', '12 oz bottles', '24', '1000', 'Active', '5000');
INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBThyme', 'Thyme', '12 oz bottles', '24', '1000', 'Active', '5000');
INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBBay', 'Bay leaves', '12 oz bottles', '24', '1000', 'Active', '5000');
INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBChili', 'Chili Powder', '12 oz bottles', '24', '1000', 'Active', '5000');

GO

INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('1', 'SBCinamin', 'US', '2000');
INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('2', 'SBGarlic', 'US', '3000');
INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('3', 'SBOregano', 'US', '2000');
INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('4', 'SBThyme', 'US', '3000');
INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('5', 'SBBay', 'US', '2000');
INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('6', 'SBChili', 'US', '3000');

GO

INSERT INTO Orders (OrderID, EmployeeID, CustomerID, SupplierID, OrderDate, RequiredDate, ShippedDate) VALUES ('1', 'AG00000001', 'AS00000001', 'GS00000001', '12/06/2003', '12/30/2003', '12/15/2003');

GO

INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('1', '1', '1', 'SBCinamin', '49.99', '150', '1');
INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('2', '1', '2', 'SBGarlic', '49.99', '150', '2');
INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('3', '1', '3', 'SBOregano', '49.99', '150', '3');
INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('4', '1', '4', 'SBThyme', '49.99', '150', '4');
INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('5', '1', '5', 'SBBay', '49.99', '150', '5');
INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('6', '1', '6', 'SBChili', '49.99', '150', '6');

Go

INSERT INTO Location (LocationID, Country) VALUES ('US', 'UnitedStates');
INSERT INTO Location (LocationID, Country) VALUES ('AF', 'Africa');
INSERT INTO Location (LocationID, Country) VALUES ('GM', 'Germany');
INSERT INTO Location (LocationID, Country) VALUES ('JP', 'Japan');
INSERT INTO Location (LocationID, Country) VALUES ('CA', 'Canada');
INSERT INTO Location (LocationID, Country) VALUES ('SP', 'Spain');

GO

ALTER TABLE Employees
ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID)


GO

ALTER TABLE Customers
ADD CONSTRAINT PK_CustomerID PRIMARY KEY (CustomerID)

GO

ALTER TABLE Products
ADD CONSTRAINT PK_ProductID PRIMARY KEY (ProductID)

GO

ALTER TABLE Orders
ADD CONSTRAINT PK_OrderID PRIMARY KEY (OrderID)


GO

ALTER TABLE OrderDetails
ADD CONSTRAINT PK_OrderDetailsID PRIMARY KEY (OrderDetailID)

GO

ALTER TABLE Stock
ADD CONSTRAINT PK_StockID PRIMARY KEY (StockID)

GO

ALTER TABLE Location
ADD CONSTRAINT PK_LocationID PRIMARY KEY (locationID)

GO

ALTER TABLE Orders
ADD CONSTRAINT fk_Orders_Employees
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)

GO

ALTER TABLE Orders
ADD CONSTRAINT fk_Orders_Customer
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

GO

ALTER TABLE OrderDetails
ADD CONSTRAINT fk_Orders_OrderDetails
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)

GO

ALTER TABLE OrderDetails
ADD CONSTRAINT fk_Orders_Stock
FOREIGN KEY (StockID) REFERENCES Stock(StockID)

GO

ALTER TABLE Stock
ADD CONSTRAINT fk_Stock_Products
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)

GO

ALTER TABLE Customers
ADD CONSTRAINT fk_Customers_Location
FOREIGN KEY (LocationID) REFERENCES Location(LocationID)

Sorting Date Column And Time Column
Hi,

Can any help help me with SQL query for sorting date and time...

The following sql query does not work

VB Code:
Select * from Reminder where user_id=2 AND done=False order by reminder_Date,reminder_time desc


reminder_date is date/time field in MSAccess
reminder_time is also date/time field in MSAccess
the Result of the above Query is diaplayed in the Attached Image...

Thanks

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