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




How To Sort Alphanumeric Field In Access Database?


Hi! everybody

I'm facing a very typical problem? In one of my projects I have taken a field titleCode in my access table. The data type is text and it contains alphanumeric values like:

abc1
abc3
abc2
..
..
..
abc10
abc20
abc11

Now, I need to show the records list sorted in ascending order of titleCode like this:

abc1
abc2
abc3
abc10
abc11
abc20

Whereas the result shows like this:

abc1
abc10
abc11
abc2
abc20
abc3

I have used the following query::::::::

"SELECT * FROM Library_Master order by TitleCode"


Is there any way to get the desired result by SQL query, PLEASE!!!! help me.

thanks

Macmillan




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Alphanumeric Sort Issue
Converting to VB 6 Applications connecting to Access to .NET connecting to SQL Server. I'm comparing reports and noticed an inconsistancy in the order some records were displayed.

The Short Story
In Access DAO recordsets, the character "-" doesn't appear to be affecting the sort of a field. I have records with the first 13 characters exactly the same (including case). The next letter in 1 is a "." (period). The next letter in another is a dash ("-"). The ASCII value of the period is 45. The ASCII value of the dash is 46. Here are two strings that I'm sorting:

My Company Co.
My Company Co-Ames

In VisData connecting to the Access database, this shows in the correct order:

SortMail
-----------------------------------
My Company Co-Ames
My Company Co.

In the Access recordset in VB 6, they sort like this:

SortMail
-----------------------------------
My Company Co.
My Company Co-Ames

In Query Analyzer and .NET datareader, they sort like this:
SortMail
-----------------------------------
My Company Co-Ames
My Company Co.

I attached an Access Database (Office XP) with an example of this. I have a table with 3 fields and a query showing the results that I'm talking about. I entered about 14 records with a period or a dash.

This is not the version of Access we use with our VB 6 app. We use '97 or 2000.

Is there a know problem or a fix to this?

AlphaNumeric Sort Revision
I am trying to compare 2 strings to see where strone will go in a list of alphanumeric characters.

Here is how the list looks. I want to be able to input a number and use the code i have attached to write it to the file in the appropriate position.

Attached is my code please assist in anyway possible. Time is of the essence.
-Ia ppreciate it. I am sendin zip archive


161500
162000
163100
163200
163400
164010
164020
164030
170W10
170001
170002
170003
170004
170005
170006
170007
170008
170009
170010
170011
170012
170013
170014
170015
170016
170017
170018
170019
170020
170021
170022
170023
170024
170025
170026
170027
170028
170029
170030
170031
170032
170033
170034
170035
170036
170037
170038
170039
170040
170041
170042
170043
170044
170045
170046
170047
170049
170050
170051
170052
170053
170054
170055
170056
170057
170058
170059
170060
170061
170062
170063
170064
170065
170066
170067
170068
170069
170070
170071
170072
170073
170074
170075
170076
170077
170078
170107
170108
170109
170111
170113
170115
170117
170120
170121
170122
170123
170125
170126
170135
170140
170141
170166
170167
170168
170361
170363
170391
170470
170471
170472
170473
170474
170475
1705A0
1705A1
1705A2
1705A3
1705A4
1705A5
1705A6
1705A7
1705A8
1705A9
1705B0
1705C1
1705C5
170500
170501
170502
170503
170504
170506
170507
170508
170509
170511
170513
170520
170521
170522
170523
170526
170527
170528




Private Sub Evaluate_AlphaNumeric(StrOne As String, StrTwo As String)

Dim ArrOne() As String, ArrTwo() As String
Dim counter As Integer
Dim StrOne_Len As Integer, StrTwo_Len As Integer
Dim IsStrOne_Number As Boolean
Dim IsStrTwo_Number As Boolean

'the first character read is Greater than the other due to the
'char codes A-Z (65-90), 1-9 (48-57)

StrOne_Len = Len(StrOne)
StrTwo_Len = Len(StrTwo)

ReDim ArrOne(StrOne_Len - 1)
ReDim ArrTwo(StrTwo_Len - 1)

For counter = 0 To UBound(ArrOne)
ArrOne(counter) = Mid$(StrOne, counter + 1, 1)
Next

For counter = 0 To UBound(ArrTwo)
ArrTwo(counter) = Mid$(StrTwo, counter + 1, 1)
Next


If StrOne <> StrTwo Then
For counter = 0 To UBound(ArrTwo)
IsStrOne_Number = IsNumeric(ArrOne(counter))
IsStrTwo_Number = IsNumeric(ArrTwo(counter))

Select Case True

Case IsStrOne_Number And Not IsStrTwo_Number
'character in StrOne is a number but isn't in the StrTwo
'StrTwo is Greater
AlphaNum_CompPass = False
Exit For

Case Not IsStrOne_Number And IsStrTwo_Number
'character in StrTwo is a number but isn't in the StrOne
'strOne is Greater
AlphaNum_CompPass = False
Exit For

Case Not IsStrOne_Number And Not IsStrTwo_Number
'both are not numbers

If ArrOne(counter) < ArrTwo(counter) Then

AlphaNum_CompPass = True
IsAlphaNum_Less = True
ElseIf ArrOne(counter) = ArrTwo(counter) Then


ElseIf ArrOne(counter) > ArrTwo(counter) Then

End If
End Select

Next
Else

End If

End Sub

Alphanumeric Sort Problem
Hi guys,

Im having a slight problem with my code. I have a section of code which pulls the records from the database, selects the last record and adds 1 to it to create the new reference ID. However, Im having issues with records that reach 100. Basically it wont allow me to go over 100. After digging into the code a little I realised it's ordering the results alphanumerically rather than numerically due to the debtorID being in the format MT10001100 for example. Does anyone know how I can set it so the last digits will be placed in the correct order with 100 appearing after 99 instead of after 10?

Thanks for your help. Section of code is below.


Code:


Data3.RecordSource = "SELECT * FROM debtor WHERE clientID = '" & resultsID & "' ORDER BY debtorID"

Data3.Refresh

' If no previous debtors have been added for client
If Data3.Recordset.RecordCount = 0 Then

debtorID = resultsID & "01"

Else

Data3.Recordset.MoveLast

debtorID = Data3.Recordset!debtorID

debtorID = Replace(debtorID, resultsID, "")

' If debtors for client are less than 10
If debtorID < 9 Then

debtorID = debtorID + 1

debtorID = resultsID & "0" & debtorID

Else

debtorID = debtorID + 1

debtorID = resultsID & debtorID

End If

Alphanumeric Sort Problem
Hi guys,

Im having a slight problem with my code. I have a section of code which pulls the records from the database, selects the last record and adds 1 to it to create the new reference ID. However, Im having issues with records that reach 100. Basically it wont allow me to go over 100. After digging into the code a little I realised it's ordering the results alphanumerically rather than numerically due to the debtorID being in the format MT10001100 for example. Does anyone know how I can set it so the last digits will be placed in the correct order with 100 appearing after 99 instead of after 10?

Thanks for your help. Section of code is below.


Code:


Data3.RecordSource = "SELECT * FROM debtor WHERE clientID = '" & resultsID & "' ORDER BY debtorID"

Data3.Refresh

' If no previous debtors have been added for client
If Data3.Recordset.RecordCount = 0 Then

debtorID = resultsID & "01"

Else

Data3.Recordset.MoveLast

debtorID = Data3.Recordset!debtorID

debtorID = Replace(debtorID, resultsID, "")

' If debtors for client are less than 10
If debtorID < 9 Then

debtorID = debtorID + 1

debtorID = resultsID & "0" & debtorID

Else

debtorID = debtorID + 1

debtorID = resultsID & debtorID

End If

How To Sort A Database By Field
Isthere a way to sort out a database recordset using VB?

Numerically Sort A Text Field In Database
Since I have a field that is text, the ORDER BY option is doing it in abc order.

The value can be text but is mostly numeric.

So most of the rows returned in a query have numbers in them.

Is there a way to have these sorted numerically in the SQL statement?


NOTE:
The recordset returned is the data source for a listview box.
I could also solve the problem if I could figure out how to numerically sort a column in a listview box.

Right now I use the following code to sort on a column click, but it is also doing it abc and not 123.

With Me.lsvProjects
If .Sorted And ColumnHeader.Index - 1 = .SortKey Then
.SortOrder = 1 - .SortOrder
Else
.SortOrder = lvwAscending
.SortKey = ColumnHeader.Index - 1
End If
lsvProjects.Sorted = True
End With

Any help would be greatly appreciated.

Thanks

Apply A Bubble Sort To An Access Field
Hi,
Ive done the code for a bubble sort, but was wondering how to apply it to an Access Field linked with a data control link.

Bubble sort code:

Dim vArray As Variant, L As Integer, R As Integer
Dim I As Integer
Dim NoExchange As Boolean
Dim Temp

Do
NoExchange = True
R = R - 1
For I = L To R
If vArray(I) > vArray(I + 1) Then
Temp = vArray(I)
vArray(I) = vArray(I + 1)
vArray(I + 1) = Temp
NoExchange = False
End If
Next
Loop Until NoExchange

Where do I put in the field's name I want to be sorted, data1.recordset.movefist, data1.recordset.movenext etc ?

Open A Access DB, Sort A Field In A Table Then Close The DB?
How can I open a Access DB, sort a field in a table, then save the table and close the DB with DAO?

Sort Access Table Ascending, By Specific Field
Hi,

Just a quick question and it should be fairly simple for the experts.

What I want to do is have a button on a form and when this button is clicked, it sorts an Access table in ascending order by the "Customer Number" field.

The database table is linked to the form through a data control link.

Any help would be much appreciated.

Access 2003 Query Sort Field Non Ascending Or Descending
Hi everyone.

I have a select query to pull a field [Lockbox] from a table and sort it in a specific order (non ascending or descending).

The problem is, when there are no records with Lockbox it errors out, but when the are (unprocessed) Lockboxes it runs fine.

The SQL code is as follows:

SELECT tblGC100.Lockbox
FROM tblGC100
GROUP BY tblGC100.Lockbox, tblGC100.[User Name], tblGC100.[Date Stamp], tblGC100.[Time Stamp]
HAVING (((tblGC100.Lockbox)<>"0002691") AND ((tblGC100.[User Name]) Is Null Or (tblGC100.[User Name])=GetCurrentUserName()) AND ((tblGC100.[Date Stamp]) Is Null) AND ((tblGC100.[Time Stamp]) Is Null) AND (([Forms]![GC100 Data].[txtDate])=[tbl_GC1002].[Cash Deposit Date]))
ORDER BY IIf([Lockbox]="0406919",1,IIf([Lockbox]="0406946",2,IIf([Lockbox]="0406955",3,IIf([Lockbox]="0406968",4,IIf([Lockbox]="0406927",5,IIf([Lockbox]="0406979",6,IIf([Lockbox]="0406933",7,IIf([Lockbox]="0406990",8,IIf([Lockbox]="0409548",9)))))))));

My theory is that I didn't account for the Null value in the IIf statements which is returned when the criteria is not met.

What would I add to the IIf statements to account for this Null value?

Thank you and have a great weekend, Wheels






Edited by - wheels on 5/23/2008 12:50:19 PM

Sort Access Database Through Control Array
Hi,

Ive got a control array set up, that is linked to an Access database with a data control.

Its primary function is to update the yes/no field called "Collected" within Access.

However, I also want it to assign a value to the field "Order Code" and then sort the database.
Ive tried putting in an SQL sort after the database is updated, but it doesn't seem to work.

The code is below, and the sort code is coloured in red:


Private Sub cmdStock_Click(Index As Integer)
If vacant = True Then
Data1.Recordset.MoveFirst
For i = 2 To Index
Data1.Recordset.MoveNext
Next i
Data1.Recordset.Edit
Data1.Recordset("Collected") = -1
Data1.Recordset("Order Code") = Null
Data1.Recordset.Update
Data1.RecordSource = "SELECT * From Stock Table ORDER BY Shelf Number;"


ElseIf occupied = True Then
Data1.Recordset.MoveFirst
For i = 2 To Index
Data1.Recordset.MoveNext
Next i
Data1.Recordset.Edit
Data1.Recordset("Collected") = 0
Data1.Recordset("Order Code") = txtOrderCode.Text
Data1.Recordset.Update
Data1.RecordSource = "SELECT * From Stock Table ORDER BY Shelf Number;"

End If
End Sub

Any ideas how I can get it to sort the database by the field "Shelf Number" after it has been updated?

Thanks in advance.

Sort An Access Database In Inverse Order
My Database is already created with data already typed in. The first column is a numerique column which represents a certain Company ID.
I want to sort that column in order (20 to 1 not 1 to 20). Once that is done, there is a button in my program that adds data to this database, I want to check the last number created in the column and create the number after that.

Example
------------
If my highest number is 20 then I want the button to create 21

David

Alphanumeric/Numeric Database Search Problem
Hello guys,

I've run into a bit of a problem with my VB program and the way it interacts with the primary key on the database. I had a series of unique reference numbers in the database which were made up of 2 letters followed by a series of numbers, therefore making the fields text based. This worked fine for a while but I later needed to sort the references numerically so I looked into the option of removing the letters off the front and making all the primary key fields numberic rather than text based.

This was actually successful but I ran into a further problem. When the fields were alphanumeric I offered an option in my program that allowed you to search by particial reference and then used the like comparer to search the database. This allowed people to type in "101" and hit search to find all references containing "101". However since the references in the database are now numeric this no longer works.

Is there a similiar method I can use to compare a partial search entry with a numeric primary key field? If not, how else could I go about this? Would it be an issue of keeping the field in the database as text and somehow converting it in the program to numeric or is there another solution?

Thanks for your help guys, this has been bugging me for ages. :P

HOw To Add A New Field Into An Access Database ??
Im working on a program that needs to give the user the ability to ADD and extra database field inot an existing Access database.

IE. i got the ushall fields for personal info, firstname, lastname, address, telno etc

But the user needs to be able to add extra fields dynamicly.

Im useing DataEnviroments to access edit and delete to records etc so it will need to be done sue the existing dataenviroments i have made.

Can someone point me too or show me an example of adding extra fields??

thank you

(vb6) Get The Sum Of A Field From 3 Access Database ?
Hon'ble gurus,
I use VB6, Access2002 database, ADODB. Is it possible to find the SUM of a common field in 3 Access databases. Say for example: there is a common field like TotalCollection in all three databases where daywise collections are stored. Is it possible to open all 3 databases get the sum of all the fields(totalcollection) and get the result? If possible, can anybody give me some tips. I'll be grateful. With thanks and regards,
pkb_pkb

Text Field In Access Database
I have a text field in my VB form which stores a value like
00001

When I try to write this value to my Access db, it comes up as 1 in the database?

Also, when I try to write 01, it comes up as 1.

I would like Access to store the entire string.

Any ideas? thank you!

Date Field In Access Database
Hi guys,

I am trying to insert date field into the access database and this is what iam trying to do : I have 3 textbox values which i want to insert to the database along with the date when it was entered and i want the date to change to next days date at 2:00am. I was able to load the textbox values into the database. I need ur inputs on this... thanks!!!!

sql = "INSERT INTO table(field1,field2,field3,date) VALUES("
sql = sql + "'"
sql = sql + TextBox1
sql = sql + "'"
sql = sql + ","
sql = sql + "'"
sql = sql + TextBox2
sql = sql + "'"
sql = sql + ","
sql = sql + "'"
sql = sql + TextBox3
sql = sql + "'"
sql = sql + ")"

How To Connect To Access Database With OLE Field
Hi all

i am new to programming and am trying to use visual basic 6 to connect to a access database with a OLE field, I can get the visual basic to connect and retrieve field information to all other fields bar the OLE field which I hold different pictures to each record, any help would be much appreciated

Peter.

Add/Subtract From Access Database Field
Hi,

I am just learning Visual Basic and have a pretty simple question, I'm sure. How do you add and subtract numbers from an Access database field, sort of like a running total. I currently have a form that has an MS FlexGrid control on it. Inside of the flexgrid I have a field labeled amount. If the amount is positive I need it added to the amount in Access, and if it is negative I need it subtracted from the amount in Access. How do I get this to work?

Thanks in advance.

Text Field In Access Database.
I am creating an user interface where the user wants to store some information in a text field.I have to use an access database where the text field cannot be more than 255 characters.Is there any other way to store the information.If not ,how do I restrict the user for not exceeding the limit of 255??When he is esceeding the limit it is causing a run-time error.Please reply.

Text Field In Access Database.
I am creating an user interface where the user wants to store some information in a text field.I have to use an access database where the text field cannot be more than 255 characters.Is there any other way to store the information.If not ,how do I restrict the user for not exceeding the limit of 255??When he is esceeding the limit it is causing a run-time error.Please reply.

Date Field With Access Database
hi gys,

when am storing date to my database(dd/mm/yy) format. sometime it is taking correctly. sometime it is taking mm/dd/yy format. am using calendar control to pick the date and it will go to text box control. after when am clikcing save it is storing into databse. using ADO control + .execute("insert....) for storing into it. can any body can help in this regard

sajan

Deleting The Field Of An Access Database
What is the command to delete a field in an Access Db using VB6 DataEnvironment . So far i've tried this :

DataEnv.rscomRecordset.fields.delete("name of the field")

and got the error 3219 saying i cannot do this operation in the context ...

is there any Declaration i have to make or other command i must use , plz help me so i can get on more serious things ...

I really like this forum , hope my replies will help anyone using VB6 ...

Updating The Value Of A Field In An Access Database...
I have a combo box, in a subform whose value list is based on a query. How can I make these values reflect the true result of the query without closing and reopening the main form.

Thanks

Max Field Size In Access MSDE Database
I know that the maximum field length is 255 in a normal Jet Access Database. I also know that Access 2002 can built databases in the MSDE format. Is the field limitation still 255 characters in these MSDE databases?

Adding A Vb Text Field To A MS Access Database
I have a vb program where I have a text field that has about 20 lines of text and I would like to store this text in a field in an access database. This is my first time working with vb and access together. Any help would be appreciated. Thanks.

Verify If A Field Exists In Access Database???
I need to add a field to an access table. However... the field might already exist and in this case if I try to add the field, I get an error. On Error resume next does not work.

My code to add the field looks like this:


Private Sub AddField(ByVal strTable As String, _
ByVal strFieldName As String, _
ByVal vStyle As Variant, _
Optional ByVal intFieldLength As Integer)

Dim catDB As New ADOX.Catalog
Dim tblNew As New ADOX.Table
Dim Col As New ADOX.Column

On Error GoTo Err_Handler

'find out if field exists, if so..exit sub

catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & objMainform.DBPath

Col.Name = strFieldName
Col.Attributes = adColNullable
If intFieldLength > 0 Then
Col.DefinedSize = intFieldLength
End If
Col.Type = vStyle
catDB.Tables.Item(strTable).Columns.Append Col
catDB.Tables.Item(strTable).Columns.Item(strFieldName).Properties("Jet OLEDB:Allow Zero Length") = True

Set catDB = Nothing
Exit Sub

Err_Handler:
Resume Next

End Sub


How do I first check to see if the field exists. This way I can just skip my addfield code if it does


Thanks,
M

Maximun Field Length In Access Database
Hi,
can anybody tell me what's the maximun field length that an Access database can import?
I'm trying to import an XML file using Access database's import functionality and it's giving error message for a field which was defined as a text field with a length of 1022.

Thanks in advance.

How I Can Save An OleObject In A Access Database Field ...
Hi all,

How I can save the containing object from an OleObject into an accessdatabase field type OleObject ???

Thanks in advance
Abilio

How To Access And Amend A Field Property In A Database
Hi all

I would like to set up a database through VB, Ive managed to create the table and fields (table = ReceivedDbs, fields = Date_Created (Date) and Branch_Number(Long) but I want to do more like:

1. Set the primary key and indexes.  I want to use both Date_Created & Branch_Number as the primary key.

2. Set more properties of the fields i.e:
Date_Created - format = dd/mm/yyyy
             - caption = "Date Created"
             - default value = Now()
             - Indexed = Yes(Duplicates OK)
Branch_Number - decimal places = 0
              - caption = "Branch No."
              - Indexed = Yes(Duplicates OK)

However, I have been tearing my hair out on this one.  Any one have a clue ?

Missy Ed
msedbbw@hotmail.com
 
Looking to exchange ideas and tips on VB and MS Access. Drop me a line: msedbbw@hotmail.com

Check Existence Of Field In Access Database
Hi - I've got a VB6 program which runs with a database back-end.

I would like to implement a module that checks for the existence of fields in the database, and if they're not there, create them using a simple SQL query.

The creation of the fields is easy, however I'm struggling on how to check whether they already exist or not?

If there's anyway that someone can tell me how to do this I'd be greatful!

Many thanks

Chris

Access 2000 Database Field Lookup
I am a novice user of Access and am creating a simple contacts database. Is there a way to lookup contacts within the database by simply typing in the selected field?  

For example: let's say i am in a database named Cities.mdb.  Once i open the database the first city listed is Anaheim, but i need to get to the city of Los Angeles' info.  Is there a way to type an "L" for Los Angeles in the field where the city's name is listed and automatically be sent to the "L" section of my database?

I know that was a mouthful.  But any help would be greatly appreciated.  Thank you all in advance.

How To Use An OLE Field From Access Database In Rich Text Box ??
Hi,
I have a access database "db1.mdb" with "Table1" table and "field1" data field as an OLE object type data.
I've embedded "myfile.rtf" into the first datafield.
Now, I want to open this database and show the contents of myfile.rtf in a rich text box from vb.net application.
I really appreciate any help/idea..
thanks.

Is The Correct Way To Increase The ID Field In Access Database !
Hai,

   When i am Saving new records to the table i insert the Id filled as below, so that the id number will be in a order,

    strSQL = "select max(Id) from TaskAssigned"
    rs.Open strSQL, cn, adOpenStatic, adLockReadOnly, adCmdText

    If rs.EOF = True Or IsNull(rs(0)) Then
    Id = 1
    else
                Id = rs.max(Id)
    End if

  it works correct when i Save

 what i am doing when i update the existing records is, i am deleting the existing records with the below query

  strSql = "delete * from TaskAssigned where Tsk_EmpPos='" & EmployeeIn & "' And Tsk_BN=" & txtBadgeNo.Text & " And
                Tsk_Name='" & TaskName & "'"

and save the records as new with the below query
incVal = max(id)


strSql = "insert into TaskAssigned(Tsk_Id,Tsk_BN,Tsk_EmpPos,Tsk_Name,Tsk_TaskId,Tsk_Tasks) values "
                strSql = strSql & "(" & incVal & "," & txtBadgeNo.Text & ",'" & EmployeeIn & "','" & TaskName & "'," & i & ",'" & VntSpreadCellValue & "')"
Cnn.Execute strSql

what happens at this time is, the incVal is increased often,


Will there be any porblem in future because of this or what. Up to how many digit i can increase the Id and save
I am using Office XP and Access is my database and front end is VB6.

Thank you,
Chock.

How To Add A Field Programmatically To Access Database Using ADODB?
Hi,

I have been trying different things with no luck. I always get an error when trying to append the field.
The last thing I tryed was this:
Code:
  Public adoConn As New ADODB.Connection
  Public adoRS As New ADODB.Recordset

Private Sub cmdContinue_Click()
On Error GoTo ErrorTrap
        DoEvents
        ConnectExternal
        DoEvents

        'Get data from external db
        Dim sqlQuery As String

        adoRS.CursorLocation = adUseClient
    
        adoConn.OpenSchema adSchemaTables
        
        adoRS.Open "Despatched", adoConn
        
        Dim i As Integer
        Dim found As Boolean
        For i = 0 To adoRS.Fields.Count - 1
            If adoRS(i).Name = "ToFollow" Then
                found = True
                Exit For
            End If
        Next i
        If Not found Then

'Next line gives an error
            adoRS.Fields.Append "ToFollow", adVarChar

            adoRS.Fields.Update
            MsgBox "Database updated correctly", vbInformation, "Success!"
            
        Else
            MsgBox "There was no need to update your database as the required field already existed", vbInformation, "Check completed"
        End If
        DisconnectExternal
Exit Sub

ErrorTrap:
MsgBox "Error Number: " & Err.Number & vbCrLf & Err.Description & vbCrLf & vbCrLf & "Debug Information:" & vbCrLf & _
"Project1.Form1.cmdContinue_Click" & IIf(Erl > 0, "." & Erl, ""), vbCritical, "Error Occurred"
DisconnectExternal
End Sub
Public Sub DisconnectExternal()
    adoConn.Close
    'Conn.Close
End Sub

Public Sub ConnectExternal()
        ' Matching a single character use underscore '_' eg: 'SMI_H'
    
        adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & App.Path & "Mydatabase.mdb;"
    

End Sub


Any ideas? What can I use to add a field if it does not exist?
I get the error 3219: operation is not allowed in this context... What context does it need?

Help please!!!!

Field Types For VB/Access Database Coding
Hi,

I'm trying to figure out the SQL syntax for changing the data type of a field during run-time.
I'm using the following SQL statement:

 sSQL = "ALTER TABLE " & aTableName & " ALTER COLUMN " & aFieldName & " " & aFieldType & "(" & aWidth & ")"

where the words prefixed with 'a' are parameters passed in.

My question is:
What are the possible values that I can use for aFieldType?
I know that aFieldType = "TEXT" works, but "NUMBER" doesn't. What other types are available?

Also, I've read that a Precision value may also be required by other data types. Is this true?

Thanks in advance.

How To Edit A Hyperlink Field In Access Database With A VB6 Application
Hi,

how do I edit a hyperlink field in an MS Access database from within a Visual Basic 6 application?
The Data Type of the column in the database table is already Hyperlink.
(I can edit, save, load an Access database with my VB6 application, but now I want to add hyperlinks. Can't figure this one out.)

Thanks in advance.

Multiple Conditions To Pass A Field Through In SQL/access Database
Hi Guys,
Can anyone help me with my SQL here,
the recordset pulls a field called [field_name] but it must not = the var "progenyname" or the text values 'Unknown2' and 'Unknown'
The code below runs but still picks out the 'Unknown2' for some reason.
Any thoughts are very welcome.
Thanks
C

("SELECT * FROM table1 WHERE ([Field_name] <> 'Unknown2')And ([field_name] not like '*" & Replace$(progenyname, "'", "''") & "*')And ([Field_name] <> 'Unknown') And ([Field_sire] = " & progenysire & ") Or ([Field_dam] = " & progenydam & ")")

Cannot Populate A Combo Box With Field Values In My Access Database
I want to populate a combo box on my form with a field in my database. I have about 20 records in my database and I want to populate this combo box with the primary key field in that database. There should be 20 entries in the combo box. 1 for each record in my database. I want to populate it on form load and display the items in alphabetical order. As the user changes the selection in this combo box, I want the data on the rest of the form to reflect the record selected in the combo box. I am having trouble getting the combo box to populate from the Access Database. Can anyone tell me what I am doing wrong? My code is below. Thanks in advance for any help you can give me.

CSS_Oncall is my table name.
cboGroup is the name of my combo box.
dat1 is my data control.

Group is the name of the field in the CSS_Oncall table (I want these values in the combo box.)

Code:
Private Sub Form_Load()
Set objRecSet = dat1.Recordset

Dim pstrSql As String

pstrSql = "Select * From CSS_Oncall"

objRecSet.OpenRecordset pstrSql, Conn, adOpenKeyset
cboGroup.Clear
Do While Not objRecSet.EOF
cboGroup.AddItem objRecSet("Group")
objRecSet.MoveNext
Loop
objRecSet.Close

Insert Values From Text Field Into Access Database
I know this is proably real simple stuff but please bare with me as Im very new to programming and it's termanology.

I just want the form to grab a value from a text input field and add it to a database along with the date. I have got it working with hardcoding the values into the sql statment, but I dont know how to read in values from the VB Form into the sql statment. Below is what I have done so far.


Code:

Private Sub btnRUN_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRUN.Click
Dim cn As ADODB.Connection
Dim currentDate As String = Date.Today
Dim someValue as String = lblNumberInput.Text
cn = New ADODB.Connection
Try
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E: estApp est.mdb;"
cn.Open()
Dim rs As ADODB.Recordset
rs = cn.Execute("INSERT INTO checkIN VALUES (0000001, 01/01/04)")
cn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
On a side not, Reading up about ADO, ADO.NET and a heap of other connection and database methods has got me really confused. Is the above code ADO.NET format?

Cheers.

URGENT : Format Of Password Field In Access Database
Hi all,

i have a problem in vb6.i m using access database as backend and storeing the new user in the table. table name is Set_pass.if i m store password "vijay" then it store in database as it is like vijay.But i want to store password in to access database any other format like * or binery formet or any unread format. I hope all of u guys understend my problem.plzzzz help me.

i want to store password into access database like
ʙ~dW'RnA
**********
or any other format

plzzzzzzzzz provied me solution which decode the value from database and encode the value from text box



thankx in advance

Obtain The Picture From An OLE Field Type Of ACCESS Database
Obtain the picture from an OLE field type of ACCESS database

I need to set a picture property of a commandbutton dinamically (at run-time) according to an OLE type field of ACCESS database.

commandbutton1.Picture = recordset !Olefieldname 'ERROR TYPE MISMATCH

plz help

Adding A Field To An Existing Access 2000 Database Using VB6
Can anyone enlighten me on how to add a field to an existing Access 2000 database using VB6

Obtain The Picture From An OLE Field Type Of ACCESS Database
Obtain the picture from an OLE field type of ACCESS database

I need to set a picture property of a commandbutton dinamically (at run-time) according to an OLE type field of ACCESS database.

commandbutton1.Picture = recordset !Olefieldname 'ERROR TYPE MISMATCH

plz help


How To Use Combo Box With Adodc To Retrive Field In Access Database.
I have access database which I willuse form to retrieve a field to
show in List of combo box.

the method that I use now is adodb code when form load is show below.

Private Sub Form_Load()
Dim sql As String
Dim i As Integer

StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path &
"master.mdb;Persist Security Info=False"

With Cn
If .State = adStateOpen Then .Close
.ConnectionString = StrConn
.ConnectionTimeout = 90
.CursorLocation = adUseClient
.Open
End With

sql = "SELECT * FROM Room"
Rs.Open sql, Cn, adOpenDynamic, adLockOptimistic, adCmdText

' in this section, I will load the CustomerID to show in this form in
combo box.
cboCustomerID.Clear
For i = 1 To Rs.EOF
cboCustomerID.AddItem !rs.("customerid")

Next
' but it's still not work yet.
' the code below is not work anyway.. I'm still confuse the exactly
way to coding..
' With Rs
' Do Until .EOF
' cboCustomerID.AddItem !roomid
' .MoveNext
' Loop
' End With

Call DisplayData 'this is an sub routine that will retrieve data from
field in database to show in textbox in my form. the most coding in
this section is txtroomid = rs("roomid")
.....
End Sub

please help me, I really want to show data in combo box

How To Get The Field Data Type Of Ms Access Database Table Using VB6.0
Hi to all

  I have to give a list of all fields with their data types using vb6.0 coding is it possible? please help me

thanks in advance

Entering Null Into An Access Database Date Field
Hi,

I've recently changed the format of a field in my development database from text to date as i need to order the output by descending date. Unfortunately now I can't update my db with "" in the date field, it says there is a data mismatch in vb when I run a data environment command to update the db.

Any suggestions? I've tried changing the parameter type to date but this doesnt' solve it. Occasionally the field will be null and thus shouldn't have any value, so I need to get round this.

Thanks



Edited by - GraemeW on 1/20/2005 3:22:38 AM

Insert New Field In Access Database On Form Load?
I am trying to insert a field named "QuantityBought" into an access 2000 database when my UPDATED program runs. I check for the old database using the Dir function and , if successful, I want to insert the new field in the old database and then rename it to the new database. I can do everything but insert the new field. Any help would be greatly appreciated.

How To Set The Defalut Value For Access Database Table Field Thru Querry
Database: MS Access 2000
Front End: Visual Bassic 6.0
Using ADO for It.

My application requires runtime deletion of ond of the column of Table which is Access database. I'm able to add new column with "Integer" data type. But i cudn't find the way to set the default value for that column to 0(Zero). If anybody can help me out i'll be highlky obliged to him.

Thanx
Amish

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