Select Where Field Is Empty ?
Hi everyone ...
Using VB6, Access2000 and ADO, I need to do what should be a simple select statement.
In plain english I need to Select everyting from table1 where field3 is NOT empty.
The field data type is Text.
When I use the SQL below, I get a syntax error.
Here is the SQL ... any help?
Code:
strSQL = "Select * From Table1 Where Field3 <> """
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
SQL - Select All Records From Table Where Field Is Empty / Not Empty ?
I have a problem here in that I dont know how to execute this SQL query as I am not sure of the correct syntax...
I am trying to select all records from a recordset (populated by a table), where a particular field is empty...
So I guessed...
db.Execute("SELECT * FROM CUSTOMER ORDERBY CustomerID WHERE CustTelephone = "" ")
but its not working...
Similiarly, I need a query that returns all records where a certain field is filled, I have no idea how to do this one...
Guessed at...
db.Execute("SELECT * FROM CUSTOMER ORDERBY CustomerID WHERE CustOrder = "'(value)'" ")
(where '(value)' would mean any value at all)
funny enough its not working either
Can anyone please help ?
Thanks
SQL - Select All Records From Table Where Field Is Empty / Not Empty ?
I have a problem here in that I dont know how to execute this SQL query as I am not sure of the correct syntax...
I am trying to select all records from a recordset (populated by a table), where a particular field is empty...
So I guessed...
db.Execute("SELECT * FROM CUSTOMER ORDERBY CustomerID WHERE CustTelephone = "" ")
but its not working...
Similiarly, I need a query that returns all records where a certain field is filled, I have no idea how to do this one...
Guessed at...
db.Execute("SELECT * FROM CUSTOMER ORDERBY CustomerID WHERE CustOrder = "'(value)'" ")
(where '(value)' would mean any value at all)
funny enough its not working either
Can anyone please help ?
Thanks
Access Select Qry - Join Number Field To Text Field
Hey
I have a select query with 2 tables which I want to inner join table1 field1 data type is number with table2 field1 data type text.
I've tried CInt(table2.field1) in SQL but doesn't like this:
SELECT table1.field1, table2.field1,
FROM table1 LEFT JOIN table2 ON table1.field1 = CInt([table2].[field1])
Any ideas folks?
Cheers,
Mudz
Empty Field?
Hi
I have a Data Grid Control that is connected via ADODC to a Microsoft Access DB. I want the user to be able to add, modify, or delete records on the grid. This all works fine, except when the form opens up, the first field of the first record is blank with the cursor blinking there. For some reason it sticks the cursor there and erases what was previously in that field.
Does anyone know how to remedy this?
Thanks!
Empty Field
What is the value of an empty field? I tried testing a recordset for empty fields like this...
Code:
if rsTabSearch.Fields("FileProperties") = "" then
'code here
end if
if rsTabSearch.Fields("FileProperties") = Null then
'code here
end if
but none are working... Any thoughts on this?
How To Tell A Field In A Recordset Is Empty
Ok I am implementing a database application. I am working with MaskEdBox this is how i have it working, one form comes up and the user types in the name they want to search for, then another form pops up with the matches in a listbox, then u click on the name u want and it jumps back to the orignal form, but the problem is i need to know how to tell in code if a particular field in recordset is empty. Like for instance
I am searching for Jimmy I find his name on the list and i click on his name, how can i tell before populating the form with his info if the recordset has nothing for his address or so on. Any help would be greatly appreciated
here is what i have
If rs!Workphone = "" Then
mskphone.Mask = mskphone.Format
Else
mskphone.Text = rs!Workphone
End If
How To Figure Out The Empty Field?
I have following code to read one text filed(atafromemail.txt), and change the pattern to another text file(SwitchPattern.tex). finally, convert to excel
datafroEmail.txt:
Total Amount: $8.95 USD
Currency: U.S. Dollars
Transaction ID: 32238683KU050193G
Item Number: 30524117856
Buyer: Betsi Coleman
Buyer's User ID: betsitwo
Message: bla ...
betsitwo's CONFIRMED Address: 1234 St.
.
.
SwitchPattern.txt:
"$8.95 USD","U.S. Dollars","32238683KU050193G","30524117856","Betsi Coleman","betsitwo", "bla ...", "1234 St."
My problem is sometimes data from email no the Message line at all.
This will cost problem in switchPattern file like the address line will move foward to Message field and so on. If there were more than one record, the whole thing will mess up. Anybody help?
<vbt>
Private Sub TextSwitch()
Dim txtAmount, txtCurr, txtTranID, _
txtNum, txtBuyer, txtUserID, txtmessage, txtAddress, xR As String
Dim i, j
Open App.Path & "SwitchPattern.txt" For Output As #1
Open App.Path & "DataFromEmail.txt" For Input Access Read As #6
Do While Not EOF(6)
Line Input #6, xR 'Read a line
If Left(LTrim(xR), 14) = "Total Amount: " Then
txtAmount = Right(xR, Len(xR) - 14)
Write #1, txtAmount,
End If
If Left(LTrim(xR), 10) = "Currency: " Then
txtCurr = Right(xR, Len(xR) - 10)
Write #1, txtCurr,
End If
If Left(LTrim(xR), 16) = "Transaction ID: " Then
txtTranID = Right(xR, Len(xR) - 16)
Write #1, txtTranID,
End If
If Left(LTrim(xR), 13) = "Item Number: " Then
txtNum = Right(xR, Len(xR) - 13)
Write #1, txtNum,
End If
If Left(LTrim(xR), 7) = "Buyer: " Then
txtBuyer = Right(xR, Len(xR) - 7)
Write #1, txtBuyer,
End If
If Left(LTrim(xR), 17) = "Buyer's User ID: " Then
txtUserID = Right(xR, Len(xR) - 17)
Write #1, txtUserID,
End If
If Left(LTrim(xR), 9) = "Message: " Then
txtmessage = Right(xR, Len(xR) - 9)
Write #1, txtmessage,
End If
j = InStr(1, xR, ":", 1)
i = InStr(1, xR, "CONFIRMED Address:", 1)
If i <> 0 Then
If Mid(LTrim(xR), i, 18) = "CONFIRMED Address:" Then
txtAddress = Right(xR, Len(xR) - j)
Write #1, txtAddress
End If
End If
Loop
Close #6 'Close
Close #1
End Sub</vbt>
Thank you!
Fang
Filter For Empty Field In ADO
I want to filter a Recordset for two cases of a date field: either empty or not.
On other fields, I am using:
rs.Filter = "FieldName = Whatever" which works fine.
How do I specify FieldName = "" or Fieldname <> ""?
As you can probably tell, I am new at this :-)
Thanks!
How To Proof If A Field Is Empty ??
Hellllooo,
i want to proof if a table field is empty. I tried this :
If !State = "" Then
Text3(2).Text = "No Content !"
Else
Text3(2).Text = !State
End If
Does anybody know a working solution ??
Mine causes error: illegal use of null. Means that !State is empty and Text3(2).Text doesn´t like empty fields or content.
Please help. Thanks
Problem With Empty Field ..
Im trying to update a table in my database.. and im using ".Find" to locate the column to be updated..
but it must only update if txt_name = fld_name and lbl_date = fld_date and fld_time_out is empty ..
my problem is , the fld_time_out = ' " + empty + "'" doesnt work..
any ideas or alternative i can use ? .. thanks..
please check my code:
Adodc1.Recordset.Find "fld_name = '" + txt_name.Text + "'"
Adodc1.Recordset.Find " fld_date = '" + lbl_date.Caption + "'"
Adodc1.Recordset.Find " fld_time_out = '" + empty + "' "
If Adodc1.Recordset.EOF Then
MsgBox ("..")
Else
With Adodc1.Recordset
.Fields!Fld_time_out = lbl_time
.Update
End With
MsgBox ("record saved")
Adodc1.Refresh
End If
Updating An Empty Field
Im trying to update all my fields in Access where the fld claimnumber is blank
Set mrstDWCActivityLog = GdbDWC.OpenRecordset("tblactivitylog")
GdbDWC.Execute "UPDATE tblactivitylog " & _
"SET fldClaimNumber='" & dbcClaimNumber.BoundText & "' " & _
"WHERE fldClaimNumber='" & "" & "'"
Im Just not sure how to find an empty field.
can anyone help
Refering To An Empty Field?
I can't figure this one out, how would you refer to an empty field in the database? Closest I can come with is something like:
Code:
If datLeague.Recordset!Name="" then
Something along those lines... except databases obviously can't be refered to as "".
What's the code you need to make this reference?
SQL For Access - Where Field Is/is Not EMPTY
How can I query where a field is either empty or not empty?
I would normally do...
VB Code:
recordset.Source = "SELECT * FROM table WHERE [FIELD] = 'xyz';"
but obviously in this case i want to do it so it'll bring back the records if the field is zero length and another for if it is not zero length. Anyone know how to do this?
thanks.
How Do I Evaluate An EMPTY RS Field?
I have a SELECT that brings back records but sometimes one of the Fields in the RS will be empty. So, whenever I use the code below on an empty RS field value I get the error, "can not be found in the ordinal", message. How can I just check if the RS field value has something in it?
Code:
if len(rs("ResponseTest")) = 0 then
thanks
Populate Empty Field
Using code- Is there anyway to enter a value into an empty field in a recordset? Maybe by using an If...Then statement???
Thanks for help
paul
Characterization Field Empty
This probably isn't the correct forum, but I'm a VB guy so....
I'm using the Indexing Service for searching through the contents of PDF documents. A couple of the fields I want to get data from, Characterization and DocSubject, are returned as Null. I set the cached = true for these properties, made sure the Generate Abstracts was set to True for the Indexing Service (catalog inherits this value), restarted the service, and initiated a full rescan of all directories of the catalog.
According to articles I've read I understand it may take some time before the word lists are updated, but it's been several hours and I only have a small amount of test documents in the directories (about 30 files, most are about 50KB, the biggest is about 500KB).
I did find one web article that mentioned a GenerateCharacterization attribute in the Registry that should be set to 1. Of the three attributes I found in the Registry with that name, one was set to zero. So I set that to 1, but it didn't make a differnce.
Here's a sample of my code:
CODE Dim rs As ADODB.Recordset
Dim strConn As String
Dim strSearch As String
On Error GoTo ErrHandler
Set rs = New ADODB.Recordset
strConn = "Provider=MSIDXS;Data Source=MyCatalogName"
strSearch = "SELECT Size, DocTitle, DocSubject, FileName, Characterization from SCOPE()"
rs.Open strSearch, strConn
'rs("Characterization") and rs("DocSubject")
'are both Null!
To Select An Empty Cell
In order to select a value before a blank cell, I get this command from other topic:
myvariable = Range("C5").End(xlDown).Row
unfortunately if the cells contain a formula like this:
=IF($C55="";"";........)
the above command not recognize no empty cell until that do not end the formulas introduced
In few word I need to modificate the command, inserting a control if cell is empty, or something like this, and not if is blank.
Someone could write to me this simply (I hope) solution ?
Thanks to everybody.
Empty Datagrid Select
Hello All
I have two Datagrids on a form, One filled one not filled
I have a mouse_up on both Datagrids so if datagrid not filled is mouse_up then error.
Question: how to check not data at datagrids Me still poor with datagrids but getting better..
maybe something like this below
I need the statement for datagrid = nothing like Textbox.text = ""
Code:
If (datagridC = Nothing) = True Then
MsgBox(" Moron no data here to select" ETC....
Exit Sub
End If
Thz
MBCMDR
Edited by - MBCMDR on 9/27/2004 2:58:28 PM
Data Field (Empty) Not Found
I am trying to create a limited dynamic data report - I scan the field names in from the recordset and then fill the text controls - data field properties with the names scanned in etc.
Unfortunatelly some times there are not enough field names to fill all the text controls and so I get the data field (empty) not found message.
Does anyone know how to assign a null value to a data field, or some way round it ??
On error resume next does not work
Cheers
Paul
Saving A Empty Textbox To A Db Field
im getting the zero-length string error, this isn't a problem when im saving a new record, i just dont update those fields that are blank,, but when im trying to edit a field i need to remove the old data and replace it with the new data... even if the new data is "".... how can i take an empty string and save it to a field?
Data Field (Empty) Not Found
I am trying to create a limited dynamic data report - I scan the field names in from the recordset and then fill the text controls - data field properties with the names scanned in etc.
Unfortunatelly some times there are not enough field names to fill all the text controls and so I get the data field (empty) not found message.
Does anyone know how to assign a null value to a data field, or some way round it ??
On error resume next does not work
Cheers
Paul
Empty Field Causing A Problem
Just as one problem gets sorted up crops another
I've got a small database with 4 records in it (see attachment) but on the fourth record the person didn't have a company name so I left the field blank
I expect to have many records with blank fields in them but my program doesn't like them being blank and comes up with some 'invalid use of null' error
Instead of running through and putting the data into the textboxes for each record and just that record I think its trying to put into each textbox the data that comes next in the database - if that makes sense
Any Advise?
How To Check An Empty Field On A Form?
Here's the code I have for a text field on my form.
If Len(.Text1(5).Text) > 0 Then
If Not IsDate(.Text1(5).Text) Then
MsgBox "The received date must be a valid date."
Exit Sub
End If
End If
I always gives me the msgbox error even when I don't put anything in that field. I can't figure out why. The field's default text is empty, so I don't understand how it's getting inside that first if statement. Anyone have an idea or a better way to do this? thanks.
Data Report - Empty Field
I created a data report with ADO and the record set is prepared on the fly. Some of the columns in the record set will be empty. But the data report does not allow me to have empty fields. How do I have it ?
Thanks in advance
James
Recordset Field Value Empty When Using CursorLocation = AdUseServer
Greetings!
Here's my problem:
I have a recordset that ABSOLUTELY needs to have server side cursor.
If I do something like:
Code:
Dim oConn as ADODB.Connection
Dim oRs as ADODB.Recordset
Dim sSql as String
Dim sValue as String
sSql = "SELECT * FROM Item WHERE pkIdItem = 1"
Set oConn = New ADODB.Connection
oConn.CursorLocation = adUseServer
Set oRs = oConn.Execute(SQL)
While Not oRs.EOF
sValue = oRs("The75thField")
'// Here, I grab all the 75 fields from the recordset, or do whatever I need
'// to do with the values.
Wend
Set oRs = Nothing
Set oConn = Nothing
I have lots of fields that are "Empty" (not null or "").
Does anybody have any idea how to fix this problem? This particular behavior always happens when I am using tables that have lots of fields inside. Usually, I just fix it by setting the Cursor Location to "adUseClient", but for a reason of "memory managing" and speed of my application (kind of...), I absolutely need to use a server side cursor.
Thanks in advance!
Validate The Empty Field Based On Tab Index
Code:
How can i validate the empty field based on tab index ?? The following code only can found out the empty field randomly.
Public Function ValidateEmptyField(formName As Form) As Boolean
ValidateEmptyField = False
Dim ctrl As Control
For Each ctrl In formName.Controls
If TypeOf ctrl Is TextBox Or TypeOf ctrl Is ComboBox Or TypeOf ctrl Is RichTextBox Or TypeOf ctrl Is ImageCombo Then
If Not ctrl.Visible = False Then
If ctrl.Text = vbNullString Then
MsgBox "Please Fill up the " & ctrl.Tag, vbCritical, "Save Failed"
If ctrl.Enabled = True Then
ctrl.SetFocus
End If
ValidateEmptyField = True
Exit Function
End If
End If
End If
Next
End Function
Thanks for any reply !!!
Empty Date Field Entry Problem
I have several text boxes in a VB6 entry form that take short dates and saves them to an Access 2002 db. If they all get filled the save works fine. If any one is left empty I get the following error message: Syntax error in date in query expression '##'.
The code used to Enter and Save them is:
VB Code:
Con.Execute "INSERT INTO Contract (Date_Filed [and 17 other fields]) VALUES (#" & TxtDate_Filed.Text & "#, [and 17 other field values])"
I have read about a "Null" problem but don't know if this is an instance of that since I don't get the "Invalid Use of NULL (94)" error message. Any help appreciated. --Ed
How To Insert Empty String In Numeric Field?
Hi,
I'm connecting to a database via ADO. The table contains several fields that are of the Number data type.
If I go in the table itself, I can clear out the values in these fields but how can this be done in VB?
I try and use the following peices of code witout any luck:
oRs!Field1 = ""
oRs!Field1 = vbNull
oRs!Field1 = vbNullString
It keeps returning the type mismatch error. Please keep in mind that I do not want to put 0 into the field, I want to make them blank, or null. This works if the field is a Text type, but not with Numeric type.
Any help or examples would be greatly appreciated..
Dan
Showing Datareport Empty Field Borders
I have a datareport that works perfectly in every way, except that my empty fields do not show borders. I have some fields that get their data from a database, which work fine. The problem is the empty fields, which need to show just empty cells, but instead have no border and are just empty space. (The user will print out the form and fill in these last parts by hand, so I need to make the form idiot resistant and clearly show the spaces to write in. The borders for all the fields are set as "1-rtBSSolid". The empty fields are labels. Any help is appreciated
(btw, I posted this to another forum and have gotten no help, so I'm hoping DevShed will come through for me )
How To Display Filled And Empty Field At A Time
hi all,
a) I want to display the value of fields in which some of have data and some are empty from an access file.
b) I am using login ID and Password for the user in my project but I want to know being an administrator who and when is logging in.
pl. help me
surnasiv
How To Still Show Query Results When 1 Field Is Empty?
I've installed MSDE SQL Database and put a database in it by importing an access database into it.
It is a relational database. I've written a sql statement that uses INNER JOIN to get data from a table and display it.
The problem is that every record that has even one empty field does not show up in the results.
Example... in the example below all records have a Stocknumber yet if a record doesnt have anything in the "model" field it wont display the record at all. It Will display all records that have All fields filled.
How can I make the records still show even if one field is empty?
Thanks for any info.
Code:
'my SQL statement
SELECT dbo.VehicleMaster.StockNumber
FROM dbo.VehicleMake
INNER JOIN dbo.VehicleMaster ON dbo.VehicleMake.VehicleMakeID = dbo.VehicleMaster.Make
INNER JOIN dbo.VehicleModel ON dbo.VehicleMaster.Model = dbo.VehicleModel.VehicleModelID
Edited by - Matrix1000 on 2/29/2004 9:29:46 PM
How To Show Fields By Relation When The Field Can Be Empty
Hi all,
in my uploaded file you can see a part of my ERD. I also uploaded the TSQL for easily recreating the tables.
I want to have a dataset containing:
Role,Page, View(y/n), Insert(y/n),Update(y/n),Delete(y/n)
I found a close solution with following query:
SELECT dbo.PagePermission.IntranetRoleId, dbo.PagePermission.PageId, dbo.PagePermission.PermissionId AS V, PagePermission_1.PermissionId AS I, PagePermission_2.PermissionId AS U,
PagePermission_3.PermissionId AS D
FROM dbo.PagePermission FULL OUTER JOIN
dbo.PagePermission PagePermission_3 ON dbo.PagePermission.IntranetRoleId = PagePermission_3.IntranetRoleId FULL OUTER JOIN
dbo.PagePermission PagePermission_1 ON dbo.PagePermission.IntranetRoleId = PagePermission_1.IntranetRoleId FULL OUTER JOIN
dbo.Page ON dbo.PagePermission.PageId = dbo.Page.Id FULL OUTER JOIN
dbo.PagePermission PagePermission_2 ON dbo.PagePermission.IntranetRoleId = PagePermission_2.IntranetRoleId
WHERE (dbo.PagePermission.PermissionId = 1) AND (PagePermission_1.PermissionId = 2) AND (PagePermission_2.PermissionId = 3) AND (PagePermission_3.PermissionId = 4)
GROUP BY dbo.PagePermission.PageId, dbo.PagePermission.PermissionId, PagePermission_1.PermissionId, PagePermission_2.PermissionId,
PagePermission_3.PermissionId, dbo.Page.PageName, dbo.PagePermission.IntranetRoleId
yet when one of the permissions doesn't exist for a certain role in a certain page; the record is not shown at all. I'd like to see the Null's as well. What am I doing wrong?
Edited by - Sensytje on 1/26/2006 4:35:55 AM
Returning A Date/Time Field To Empty
I am accessing a database in the following manner:
set db = OpenDatabase("c:Databasename.mbd", false)
set rec = db.openrecordset("tblName",dbOpenDynaset)
rec.MoveFirst
Do Until rec.eof
With rec
.Field("date")=vbNull
.MoveNext
End With
Loop
It continues to fill the date with "12/31/1899"
I can't figure out how to just delete the information in the field so that it is empty.
Jason Hatt
Select Statement Returns An Empty Recordset
I am using VB6 and connect to an SQL 2000 database.
The following select statement only works the first time it is executed. On subsequent invocations it returns an empty recordset:
Select * From Customer WHERE Category = 'A' ORDER BY Name
If I remove the ORDER BY clause, the select works every time.
Any suggestions?
Write To File Produces #NULL# From Empty Field
I am trying to write the contents of some of the fields of my form to a text file. For use with an other application.
I would like the results of empty fields to be written to the text file with just another comma. However the empty fields always get written to the text file as #NULL#
How can I get arounds this?
BTW I'm new to VB6
Much thanks in advance
Two Select Statements Using A Field From Select 1?
Ok I'm having a bit of a problem. Problem 1 is I'm working with someone elses tables and nothing was setup right but too many things uses these tables and they can't be changed.
Problem 2. I had data in 6 different tables that I need to extract (not every record from each table). So I made a table that looks like this.
ID - AutoNumber (Key)
Type - Text
RecordNum - Number
TableName - Text
My Table would look like this [Table name = LISTS].
1 US 5 IPBs
2 US 7 IPBs
3 US 1 OrderForms
4 US 3 OrderForms
5 US 1 TNB
6 US 2 TNB
7 US 3 TNB
8 CA 1 IPBs
9 CA 2 IPBs
10 CA 1 OrderForms
And so on..
What I need is and SQL statement like this "select * from LISTS where type = US"
BUT return all fields from the "tableName" field table where ID = the "RecordNum" field.
I could just say
SELECT * FROM LISTS WHERE Type = 'US'
Do While Not rs.EOF
SQL = "SELECT * FROM " & rs("TableName") & " WHERE ID=" & rs("RecordNum")
rs.movenext
Loop
but that seems like way too much work (for my program).. Is there a way to build a select statement with two SELECTS in it based off a field returned?
VB 6.0 And MSSQL: Select Returns Empty Recordset In Error
This is my first post and am grateful for any help. I am a newbie and have a problem. I am writing a simple program to store medical data from PubMed locally for retrieval and later review. I have a MSSQL database (MSDE latest service pack) with 20 columns and one primary key. The values in the primary key are unique.
I am concerned because something is wrong from the get-go. I run my search, extract the unique UID that match my search. The local MSSQL DB is then queried to see if the record already exists. If not it is INSERTed.
The first search I run returns 340 results. They are all then INSERTed. Now when I run the same search, 14 or more of the Recordsets are reported to be missing. Each are then INSERTed but the MSSQL DB gives me the DUPLICATE error, because the records are already in the local DB. Why then when I check to see if they are local, MSSQL returns "no record found", even though the record exists?
Running a query for one of these UIDs outside of VB "SELECT * FROM PubMed where PubMed_ID = '123456'" works fine (123456 is an example of a UID that is in the DB but VB/SQL/ADO says it is not.
Code:
Code:
Dim SecondSearch As String
SecondSearch = "http://www.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?" & _
"db=" & NLMDatabases & _
"&retmax=1000&usehistory=n" & _
"&term=" & NLMsearchterm
SecondSearch = Inet1.OpenURL(SecondSearch)
' this returns an XML file with a total count and a list of UIDs matching this search
Ss = SecondSearch
Dim re As New VBScript_RegExp_55.RegExp
re.Pattern = "<Count>([d]+)</Count>"
re.Global = True
re.IgnoreCase = True
re.MultiLine = True
Set expressionmatch = re.Execute(Ss)
If expressionmatch.Count > 0 Then
'there are two places/results that match this expression - just take the first
For Each expressionmatched In expressionmatch
Count1 = re.Replace(expressionmatched.Value, "$1")
Exit For
Next
Else
MsgBox "There are no results that match your Search terms"
Count1 = re.Pattern & "</B> was not found in the string: <B>" & StringToSearch & "</B>."
Exit Sub
End If
' so there are Count1 results.
' let us look at them each to see if they are new
Set re = Nothing
' now extract the Ids returned
' check to see if they are in the local DB
' if so do not add them in to the pubmed query
' o/w get pubmed data and add it to the local DB
' then show all results as desired - citation, flexgrid, abstract, etc
re.Pattern = "<Id>([d]+)</Id>"
re.Global = True
re.IgnoreCase = True
re.MultiLine = True
Set expressionmatch = re.Execute(Ss)
If expressionmatch.Count > 0 Then
ReDim UIDs(expressionmatch.Count - 1)
Dim Count As Integer
Dim NewUID As String
Count = 0
NewCount = 0
For Each expressionmatched In expressionmatch
NewUID = re.Replace(expressionmatched.Value, "$1")
HowManyData = 0
sSQLtoCount = "select * from PubMed where PubMed_ID = '" & NewUID & "' AND PubMed_PMID = '" & NewUID & "'"
'PubMed_ID is the primary key. PubMed_ID and PubMed_PMID are separate datafields but for our purposes
' they are always the same.
Dim RS As ADODB.Recordset
'Create a recordset object
Set RS = New ADODB.Recordset
RS.CursorType = adOpenStatic
'Debug.Print sSQLtoCount
'Open the recordset based on the sSQLtoCount
cnn = "Provider=MSDASQL.1;" & _
"Persist Security Info=FALSE;" & _
"Data Source=" & dbName
RS.Open sSQLtoCount, cnn
'Assign the number of recordsets found
HowManyData = RS.RecordCount
'Close the recordset
RS.Close
'Destroy the recordset object
Set RS = Nothing
If HowManyData < 1 Then ' this is a new UID not found in the local DB
NewUIDs.Add NewUID
NewCount = NewCount + 1
Call PubMed2(NewUID, NewUIDs) ' PubMed2 is the sub to add the unique NewUID to the local DB
Else
localCount = localCount + 1
End If
Count = Count + 1
Next ' look at the next UID returned to see if it is new or not and INSERT it if new
End If ' there are no/ no more UIDs to process - end the sub
Set re = Nothing
End Sub
Sorry for the long post. Thanks for your sage advice.
Ray
Select 0 Records/Open A Empty Recordset Object
hi,
I am creating a VB Form which in used only to insert a new Customer record using ADO / Recordset in the database.
I want to open the recordset object with zero records.
so i am using a code
Dim cn as Connection
Dim rs as Recordset
Dim lsSQL as string
'....
'....
lsSQL = "select * from Customer_Master where 1=2 "
rs.open = lsSQL,cn,adOpenKeyset, adLockOptimistic
'open the recordset with 0 records , since 1=2 will fail for all records in database.
'...
'...
rs.Addnew
'... set the recordset fields
rs.Update
rs.Close
Is there some better way than this, other than using the SQL Insert statement. !!!
Thanks
Sameer
Multiple-Step Operation Generated Errors When Setting Date Field To Empty Or Null
I have a form with a bunch of bound controls and a few are date fields. When I try to clear out a date that has been previously set in the record I get the Multiple-Step Operation Error -2147217887(8004e21) The database allows nulls for these date values, so it is OK at the DB level - looks like the ado is causing the problem.
DB is SQL Server 2000
I found the following on Microsoft's site that is directly related to my problem - looks like there is no workaround according to them.
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q247/2/02.asp&NoWebContent=1
Anyone else have any ideas? There is to much invested in using the bound controls at this point - I cannot abandon this method.
Select A Field On A Webpage
Heey,
I am Nathalie from the Netherlands,
Who knows the visual basic code to select a particular field on an website, and fill it with a value?
Thanks for your answer.
Nathalie
How Can I SELECT On Field Numbers?
Does anyone know how to select the field number, or order instead of using the field name in SQL Server?
Instead of SELECT Books from Library
substitute "Books" for a number...
Thanks
diver
Problem With Select Last(field)
I have a database of my dvds which I am working into an online website. I have a select statement:
Code:
sql = "select Count(dvdid) as dvdCount, last(dvdTitle) as lastDVD from dvddb_dvds"
Which is supposed to return a) the number of dvds (which it does correctly) and b) the title of the most recently added dvd. That bit it doesn't do correctly. Can anyone give me any hints why?
It does return a dvd title, but one that's at position 236 of 251, and not alphabetically the last either.
Select Using Date Field - No Matches
Hi. I'm trying to use vb code to select data from a table based on a date field. I'm not sure why its not working. If the Date field in the table is of type date, does it matter what format my date variable is in when I SELECT?
i.e. Date in table is in the format : 05 November, 2002
myDate is 11/05/02
SELECT * FROM myTbl WHERE myTbl.Date = myDate
returns no records.
If necessary, how do I format myDate to match myTbl.Date?
p.s. there's a date for every day of 2002 so I know theres data that should match.
Thanks in advance for your help.
Mike I.
Wreally having trouble
To Select Last Recordset From The Table Field
in my application i have a counter which i put in the database table..so whenever the application is launched it has to start from the next value of the counter
for example, if the application is runing now and i m entering data..so counter will keep increasing like 1,2,3,4,...if the application is stopped at counter = 15 then when next time it is lauched it has to start from 16...
to do this i m using following simple way...
While Not adorecordset_recevoi.EOF
strnum= adorecordset_recevoi.Fields("numero")
adorecordset_recevoi.MoveNext
Wend
m = strnum
...
....
so in above code m will always have the latest value of the strnum....
this works good without errors....but i know this is not a good way to do this.....this is just a simple logic....
so i want to know whether this can crash at anytime and i should use any other method..(pls suggest one)....or i can go along with this...
this can be a silly question as my other questions on this board...but believe me i dont hve any experiance of vb and i m totally new person to this programming field as well...
thanx for any and all support...
Select A Recordset Field With A Variable?
I'm writing a small DAO class for a project I'm working on and now I want to add a function which would be used like:
Code:
MySring = DbObject.GetField("FieldNameHere")
The problem is that when using the recordset the code used is:
Code:
AString = RecordSetObject!ActualFieldName
I want to use a variable in the place of the ActualFieldName which contains the field to look at. Is it possible? I tried but it didn't work :/
Thanks
|
TRACKER
