Access: Query With 1 Field No Duplicates
Morning everyone,
Question is this....I need to query my Access Database. 1 table with tracking (Everything that has happened to a specific record (Edits) and date time) How do I query this database so it pulls up only the most current record.
So if there are 4 edits to record 1....it will only pull up the newest edit.
if anyone needs more info, please let me know.
Thanks
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Ms Access Query Getting Duplicates
i am trying to develop a query with 4 tables as follows
Table "JC" has the job codes (i.e. 100, 200, 300, etc)
Table "96" has the full time equivalents FTE for each job code in 1996
Table "97" has the full time equivalents FTE for each job code in 1997
Table "98" has the full time equivalents FTE for each job code in 1998
each table for 96-98 has the following fields
Job code (as above), Location (a, b, c, d) and the FTE (1, .25, .99)
within each of these tables there can be 1 job code with 2 locations each having a different FTE.
the query i am trying to develop will allow me to pull every job code for each location and the FTE for each year. (i.e. i would have the following fields at the top (job code , location, 96FTE, 97FTE, 98FTE) and data could be as follows (621, a, 1, 0, .5) then if location b had any FTE in any of the years for that job it would show (621, b, 1, 2, .8).
now i tried to do the query i pulled up the 4 tables & i selected JC with a left join by job code to each of the years i ended up getting duplicates.
could you please give me some assistance with this one.
Ian.
MS Access VBA: SQL Query -> ADD Autonumber Field
Hi,
i'm new to coding in VBA and Microsoft Access, so the problem probably is easy to solve
I do the following query and get the table you see underneath:
Hi,
ich mach mittels folgendem SQL Befehl eine query in meinem visual Basic zusatz Programm zu Microsoft acces:
Code:
strSQL = "SELECT ModifiedTables.ModifiedTableID, Tabelle1.Act_Author, Tabelle1.ModificationNumber, ModificationLog.Type " & _
"INTO UserInteraktionen" & i & " " & _
"FROM ModifiedTables RIGHT JOIN (ModificationLog RIGHT JOIN Tabelle1 ON ModificationLog.ModificationNumber = Tabelle1.ModificationNumber) ON ModifiedTables.ModifiedTableID = ModificationLog.ModifiedTableID " & _
"WHERE (((Tabelle1.Act_Author)=[Benutzername]));"
http://home.pages.at/gxesch/UserInteraktionen1.Tab.jpg
my problem is:
the ModifiedTableID field is on "autowert" (="autonumber" in english). Does this mean that MS Access uses this field as Primary Key?
I need a new field which adds autonumbers to each record and it can be used as primary key.
When i try to add such a field, i get an error telling me, there already is a "autonumber" (autowert) field.
How can i solve this?
thanks a lot in advance!
PS: Sorry for my not perfect enlish ;-)
Access 2000 Run Query For Nothing In The Field?
Is it possible to run a query and bring up all the records that have nothing in that field. I tried using this: = "" but it still doesnt bring up those records that have that field blank.
Thanks Nick
Add A Field To Query Via Code (vb6 & Access)
Hello
I need to make a recovery program to add a field to a query in users databasae.
This query made from 2 tabels.
Can I do it with code?
for example a madule that check query for this field and if there wasn't , add it to query automaticaly?
Thanks
Access: Update Query Sets My Field To -1
Good evening,
I'm running a VBA code on Access form, i have a weird result from an Update query, instead of the needed results, it sets the filed to -1.
the query is
Code:
UPDATE ps_Transaction
SET StrategyID= 'F03293_20070802' AND Checkbox=No WHERE TF_TransactionID = '1001646' and ID='1658850-CSALCOCOEUR'
this query sets the field StrategyID to -1 instead of 'F03293_20070802'
My field is a string, and the size of the field is big enough to hold the new data. what could be the problem?
thanks,
Mimi
Running An Access Query Using A Form Field For Criteria
Scenario:
Access 2000 DB with several Link tables pointing to a SQL DB. Link via ODBC. Access Form gets input for query criteria.
Need to use VB to output the results of a query to MS Word. The Access report and associated query work great. However, my VB code will not open the query without an error.
My sequence of events is:
User sees Access switchboard, navigates to the button for the desired report. That button opens the form, frmRMABill. the OK command button on this form calls the following module:
Sub RMABilling()
Dim objWord As Word.Application
Dim doc As Word.Document
Dim rstPartBilling As ADODB.Recordset
Dim rstLaborBilling As ADODB.Recordset
Dim rstHeadBilling As ADODB.Recordset
Dim cn As ADODB.Connection
Dim qryPart As String
Dim qryHead As String
Dim qryLabor As String
Dim partTotal As Single
Dim hourTot As Single
' Open Word Document
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set doc = objWord.Documents.Add("h:RMAbilling.dot")
' HEADER QUERY
qryHead = "SELECT dbo_RMA.ID, dbo_RMA.NEW_CUST_ORDER_ID, dbo_DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID, dbo_RMA.IS_WARRANTY, dbo_RMA.LAST_RECEIVED_DATE " & _
"FROM dbo_RMA LEFT JOIN dbo_DEMAND_SUPPLY_LINK ON dbo_RMA.NEW_CUST_ORDER_ID = dbo_DEMAND_SUPPLY_LINK.DEMAND_BASE_ID " & _
"WHERE (((dbo_RMA.ID)=[forms]![RMA_NO]![RMA]));"
Set cn = CurrentProject.Connection
Set rstHeadBilling = New ADODB.Recordset
With rstHeadBilling
.Source = qryHead
.ActiveConnection = cn
.CursorType = adOpenStatic
.LockType = adLockPessimistic
.Open Options:=adCmdText
End With
I am wondering if I need to address the criteria field in my input form witihin the code for this module or if my order of events is wrong. Compiler gives me an error:
No value given for one or more required parameters.
Any suggestions are greatly appreciated.
Thanks,
Sorting An Access Query Based On A Form Field
Hello everyone!
I have an access database for which i built a search form where you enter your criteria and it then runs a query and displays the results. Everything works great except I am trying to add a "sort by" option onto the form, but I cannot get it to work. I am trying to have the ORDER BY clause of the query's SELECT statement reference a control (which contains the names of the columns on which you can sort by) on the form. However, it doesn't seem to be working, and i am almost certain i have something wrong with the ORDER BY clause. Here is what i started with:
ORDER BY [Forms]![Search]![cboSORT_BY];
The form is named "Search" and the control I am trying to reference is a combo box named "cboSORT_BY." I have the form set up to requery the results when the value is changed.
I am figuring that im just missing some stupid syntax error.
Update Table In M.Access,code Field,query
Hi
I have made an application to connect Microsoft Access with Visual Basic 6 to save some records!!!! My table in Microsoft Access has these fields:
code ->>>>>>>>>integer
Name ->>>>>>>>>>char
LastName ->>>>>>>>>>>>>char
Age ->>>>>>>>>>>>>>>>integer
Now i save the records correct to my table!!!!In code field count my records!!!
for examle: 1 Jim Dean 29
2 Leite Koontz 40
3 Melina Barker 20
4 Elina Smith 50
Now when i delete a record for example 3 row( Melina Barker) and save new record in code field will take number 5 but in table i have 4 records saved!!! How i can correct that????? With query??? But how??? Any code ideas????
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
[access Vba]Problem With An Insert Query In A Memo Field: Run-time Error 3075
Hi,
I've a problem with a simple insert query when the number of characters inserted into a memo field is hight.
this is my code:
Code:
...
Dim fso As New FileSystemObject, ts As TextStream
Dim s As String, sql As String
Dim db as Database
Set db as CurrentDb()
Set fso = CreateObject("Scripting.FileSystemObject")
For i = 1 To selFiles
Set ts = fso.OpenTextFile(.SelectedItems.Item(i), ForReading)
s = ts.ReadAll
ts.Close
sql = "INSERT INTO documenti (lid, dtitle, dtext, ddata) VALUES (" & _
Me.lang.Column(0) & ", '" & _
Replace(nof(.SelectedItems.Item(i)), "'", "''") & "', '" & _
Replace(s, "'", "''") & "', '" & _
Now() & "')"
db.Execute (sql)
Next i
I read a text file and I insert its contents into my database, but when the length of variable 's' is more than 18/20,000 chr I got runtime error 3075, but a memo field could contain up to 65,000 chr ca.
Anyone could suggest me where is the problem?
Duplicates Query...
I have an Access 2000 database that imports a number of identical .csv files which are then appended into an existing master table. The problem I've run into is trying to get the duplicate values deleted before the merge.
As it is right now I have managed to:
1) automatically import the *.csv files to a temp table
2) run a query to check for duplicates on the temp table
3) place the results of the query into a second temp table to append onto the master table
What I need now (i think) is a query to compare the temp table to the master and delete the duplicates before appending. The tables have 8 fields(F1,F2...F8) including the PK. Could anyone help out with this, or point me in the right direction?
SQL Query: Return Duplicates
Hi,
could you please assist me with a SQL query. I need to analyse some data. I have two fields..call them fld1 and fld2.
The database should never have the same combination of fld1 and fld2.
so if record 1 has fld1=1 and fld2 = 4 then I shouldnt have this combination in any other record...
but these duplicates unfortunately exist and I have to find them all. I have over 30 000 records in the table.
Can you assist me with a query to find all records where the combination of fld1 and fld2 appears in more than 1 record?
Thanks in advance
Oracle Duplicates Entry Query
Hi guyz,
I need to construct a query but im not sure how do i do this..
I need to check for duplicates..
basically, there are two fields, part number and vendor...
sample data are as following..all this data are located in the same table
PartNumber Vendor
PN1 V1
PN2 V2
PN2 V3
PN4 V4
PN5 V1
So, PartNumber (PN1) IS MAPPED TO Vendor(V1)
As you all can see, there might be duplicates in PartNumber field..But this duplicates only exists if the Vendor is not the same..So, same Vendor cannot have duplicates in PartNumber Field..But different vendors can have two same PartNumber...
So the query that i want to construct goes like that...
How do i checK whether is there any duplicates in PartNumber field for the SAME Vendor...IF there is, give me the list..
thankx in advancee for any helpp..
*RESOLVED* Removing Duplicates From SQL Query
With the following code I am pulling records from a table using a recordset:
VB Code:
Dim rs as Recordset, strSelect as StringSet rs = New RecordsetstrSelect = "SELECT * FROM Login WHERE MgrRep = 'Mgr' OR MgrRep = 'Rep'"rs.Open strSelect, MyDataEnvironment.MyConnectionDo Until rs.EOF = TrueWith cmbNewMgr.AddItem rs.Fields("Manager")rs.MoveNextEnd WithLoop
What this provides is all Manager Names within this field, and they are associated with a Rep...so I am getting duplicate Manager names because more than one rep has the same Manager...
I need to tweak this SQL so that it will remove the duplicate Manager Names.
Could someone point me in the right direction?
Thank you!
SQL Query Assistance Needed - Finding Duplicates
This might be a foolishly simple question so enjoy a chuckle if so! (It has been a long day and I'm just slow right now).
I have a database that is somewhat de-normalized (I inherited it and can't change the structure at the moment) - mostly this isn't too much of a problem, but there are some data entry errors and I'm trying to generate a list automatically.
I am doing a query of this sort:
Code:
SELECT DISTINCT ModelNumber, Cost FROM UnitInfo ORDER BY ModelNumber
The results of this query periodically find multiple entries for the same ModelNumber with different values for Cost - exactly what I intended it to find (the differing cost values are the problem I'm searching for).
What I am wondering is how I could construct a query based on the above that would be something like 'WHERE ModelNumber occurs more than once om the already distinct set of records'.
I have been scanning through the resulting dataset manually but I'm hoping there is a query that will do this automatically. I have also thought about just looping through the records but before I do that it would be preferable to have a single query do the job.
Concatenating Field Values Into One Field For An Update And Insert Query
Hello,
I have an ADO based DB viewer and I have a field where I need to concatenate values from 2 other fields in order to get teh right value. Now the concatenating part is easy, however I need to save this in my DB and and that is where the problem is.
I can concatenate the 2 fields and it shows in the textbox (txtFruit = apple, txtVeggie = corn, txt combo = txtFruit & txtVeggie = applecorn), but when I save it or update it, well it never saves. How would I go about doing this, thanks
Code:
"UPDATE Table SET Fruit = '" & DB_Table.txtFruit.Text & "', Veggie = '" & DB_Table.txtVeggie.Text & "', Combo = '" & DB_Table.txtfruit.Text & DB_Table.txtVeggie.Text & "'"
Edited by - jjoseph on 6/4/2007 10:18:40 PM
Check For Duplicates Using VBA For MS Access 97
Howdy.
I am trying to basically compare two types of information. I import about 10 tables that need to be appended to a master table. but before I can append these tables to the master table, I need to check the individual tables to see if they have already been appended to the master table. (the fields i use to check are customer name and period end date...i added customer name because for example for april, 10 customers will have the period end date of 4/30/04) If the tables have already been appended, I want the message box to say 'already in the database'. If the tables have NOT been appended, then I go to the AppendImports module to run that. I am using Access 97.
PLEASE HELP!!
Thanks in advance for your help.
Below is the code:
Code:
Dim db As Database
Set db = CurrentDb()
Dim rst1, rst2 As recordset
Set rst1 = db.OpenRecordset("tblDateCheck")
Set rst2 = db.OpenRecordset("tblFilePath1")
rst2.MoveFirst
Do Until rst2.EOF()
If rst1.Fields(0).Value = rst2.Fields(3).Value & ".Period_End_Date" _
And rst1.Fields(1).Value = rst2.Fields(3).Value & ".Company_Number" Then
MsgBox ("The requested data is already in the database")
rst2.MoveNext
Else
End If
Loop
DoCmd.OpenQuery "Append Imports", acViewNormal
End Function
Access 95 Database - No Duplicates
Hi,
I've set one of the fields in a table to Indexed - No duplicates from the design mode. I would like to allow no duplicates in another field but do not want that field indexed. Any ideas how to do this?
Thanks,
met12
Removing Duplicates From A Table - Access 97
Hello there...i have a table where there is supposed to be no dupliactes. However, quite a few snuck in there. The table has no primary key so the suggested way of doing this task in the help files is not aplipicable. I am trying to work my head around the logic of writing this query but I must be having a dumb day!
Anyone out there having a smart day?
Thanx
Search Index Access Db With Duplicates & View
Hi All,
I have a little problem over here. My problem is that the database have its index duplicates (example: three Mr John Doe with different address)
So here is whole codes with searching etc:
Code:
Private Sub cmdOK_Click()
Me.Height = 1740
End Sub
Private Sub CmdOk2_Click()
Me.Width = 4695
End Sub
Private Sub CmdSearch_Click()
lstSearch.Clear
Set MyData = OpenDatabase(App.Path + "indata.mdb")
strFind = txtSearch.Text
LName = Trim(LCase(strFind))
Set MyRecord = MyData.OpenRecordset("SELECT Word " & "FROM Dictionary " & _
"WHERE Word Like '*" & LName & "*'")
lblSearchingFor.Caption = " " & txtSearch.Text
txtSearch.Text = ""
With MyRecord
If .EOF Then
MsgBox "No matching Word was found, please try again", vbCritical, "Result"
Else
Do Until .EOF
lstSearch.AddItem !Word
.MoveNext
Me.Height = 5940
Loop
End If
End With
End Sub
Private Sub Form_Load()
Me.Width = 4695
Me.Height = 1740
End Sub
Private Sub lstSearch_Click()
lblWait.Caption = "Please wait while processing database..."
Timer1.Enabled = True
End Sub
Private Sub Timer1_Timer()
txtTimer.Text = txtTimer.Text + 1
If txtTimer.Text = 20 Then
txtTimer.Text = 0
lblWait.Caption = ""
Timer1.Enabled = False
Set db1 = New ADODB.Connection
Set rsWord = New ADODB.Recordset
db1.Open "Provider=MSDASQL;DSN=Dico;Password=;"
rsWord.Open "SELECT * FROM Dictionary", db1, adOpenKeyset, adLockPessimistic, adCmdText
rsWord.MoveFirst
Do Until rsWord.EOF
If lstSearch.Text = rsWord!Word Then
txtExplanation.Text = rsWord!Explanation
Me.Width = 10065
End If
rsWord.MoveNext
Loop
End If
The search works perfectly and retrieve all the related search string, but however it cannot display the duplicates informations when you click on the search results. I hope you do understand what I am saying LOL because I feel that I'm lost as well.
Hoping someone would light up the path soon. Thank you before hand.
Regards
Query For MS-Access: SQL Differences In Access Query Window Vs. Using ADO In Code
OK, many of us have come across the "Like" wildcard issue. When doing an Access query in Access itself, the "*" is used, whereas doing the query thru ADO in code, the "%" is used.
What other situations are there like this? I have a query that uses IIF, INSTR, and UCASE. When I test it in the Access query window, it works fine, but when I produce the same SQL dynamically in my VB program and try to execute it (using ADO), it chokes. Obviously, "standard" SQL doesn't like these VBA functions in the query. Does anyone know (or know where I find documentation on) the specific differences when doing queries for Access in the Access IDE vs. ADO? 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
Prevent Duplicates Data Save To Access Table
hi,please help.how to prevent duplicates data save to access table??
for example:
i want to prevent EmployeeName and OvertimeDate duplicate entries to the table.
thanks for help!
Prevent Duplicates Data Save To Access Table
please don't cross-post
Discussion continued here: http://www.vbcity.com/forums/topic.asp?tid=151672
hi,how to prevent duplicates data save to access table??below is the current code,when run the code have error message index out of bounds.
for example:
i want to prevent EmployeeName and OvertimeDate duplicate entries to the table.
this is the current code:
Code:Private Sub Command1_Click()
Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim adoRS As ADODB.Recordset
Dim sSQL As String
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & _
"Overtime.mdb;Persist Security Info=False"
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
Set adoRS = New ADODB.Recordset
sSQL = "SELECT EmployeeName,OvertimeDate FROM Temp"
sSQL = sSQL & "WHERE EmployeeName = '" & ListView1.ListItems(2) & "' "
sSQL = sSQL & "AND OvertimeDate= '" & ListView1.ListItems(7) & "' "
adoRS.Open sSQL, ADOCn, adOpenStatic
If adoRS.RecordCount > 0 Then
MsgBox "Record for this person already exists"
Exit Sub
End If
adoRS.Close
Set adoRS = Nothing
'write code here to add the record if is doesn't exist
End Sub
thanks for help!
Edited by - bednarjm on 12/27/2007 11:07:14 AM
Call The Memo Field In Access From Text Field In VB????
Hello
I am running a SQL query in VB to search for anything in the [comments] field defined as Memo in Access database (not text field).
When I run search, I got the error "type mismatch"....
Here is the code:
"WHERE (([DAILY WORK].[Comments]) like ' * " & [txtCommentSearch.Text] & "* ')" & _
(The above code all in one line)
I tried [txtCommentSearch.Memo] but still not working. The txtCommentSearch is the field in VB that I want users to enter any keywords to search.
Can anyone help me, please? Is the wildcard right in VB statement?
I tried to define [txtCommentSearch.text] as variant but still not work. What I done wrong?
Thanks.
Bill
Count Field Incorrect Error/ Access 2K ? In Field Name
I have an Access 2K database with a field named [Shipped?]
When I try to do the following.
rs.open "Select * from [FO#/Main] Where [Shipped?] = False
I get the following error.
[Microsoft][ODBC Microsoft Access Driver] Count field incorecct
I assume it has something to do with the question mark.
I do not want to change the name of this field. That would require a lot of rewriting of code and editing many forms in the Access program.
Is there around this error?
I have not found anything in the MS knowledge base to help me with this.
Someone please help me.
Thanks
Write A VB Field Entry To An Access Table Field
I have a field called lstQuotedQty on my VB form of which I would like the data input by the user into it to go into a field in an Access table called tblStock and the 3rd field in the table called fldSold...
The code seems to run fine, looks at my A: as though searching for the Access Database, but when I look at the Stock table of course nothing new populates the fldSold as I hoped. This is my code: Also note the user may populate the lstQuotedQty with anywhere from 1-to-an infinite number of different items sold.
I only have fldProductNo, fldPrice, fldAmount, fldSold in my tblStock, and fldSold is the only one I want to populate with this data, I'm not sure how large a zip file we are allowed to upload, but will try to attach the whole thing...I am still in the baby stages here, and everything is pointing to the A: if anyone tests this or has opinions to share I'd appreciate it...
Private Sub Add_Item2_Click()
'==================================
Dim adoDSN As ADODB.Connection
Dim strSQL As String
Dim strConString As String
Dim i%
strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& App.Path & "Inventory.mdb" & ";Persist Security Info=False"
Set adoDSN = New ADODB.Connection
adoDSN.Open strConString
For i = 0 To lstQuotedQty.ListCount - 2
strSQL = "Insert Into tblStock (fldSold)"
adoDSN.Execute strSQL
Next i
adoDSN.Close
Set adoDSN = Nothing
End Sub
Populating An Access Table Field With Output From VB Field
I have a field called lstQuotedQty on my VB form of which I would like the data input by the user into it to go into a field in an Access table called tblStock and the 3rd field in the table called fldSold...
The code seems to run fine, looks at my A: as though searching for the Access Database, but when I look at the Stock table of course nothing new populates the fldSold as I hoped. This is my code: Also note the user may populate the lstQuotedQty with anywhere from 1-to-an infinite number of different items sold.
I only have fldProductNo, fldPrice, fldAmount, fldSold in my tblStock, and fldSold is the only one I want to populate with this data, I'm not sure how large a zip file we are allowed to upload, but will try to attach the whole thing...I am still in the baby stages here, and everything is pointing to the A: if anyone tests this or has opinions to share I'd appreciate it...
Private Sub Add_Item2_Click()
'==================================
Dim adoDSN As ADODB.Connection
Dim strSQL As String
Dim strConString As String
Dim i%
strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& App.Path & "Inventory.mdb" & ";Persist Security Info=False"
Set adoDSN = New ADODB.Connection
adoDSN.Open strConString
For i = 0 To lstQuotedQty.ListCount - 2
strSQL = "Insert Into tblStock (fldSold)"
adoDSN.Execute strSQL
Next i
adoDSN.Close
Set adoDSN = Nothing
End Sub
Getting A Certain Field From Query
i would like to get a data from a QUERY and display its "gotten" data to a datagride, but an error always appears when i run the program... it says [ADODC]: unknown error [ado].... here is a sample code...
Code:
If cboCategory.Text = "First Name" Then
SQL = "SELECT LogInLogoutTable.LogDate, Employees.FirstName, Employees.MiddleName, Employees.LastName, Employees.Position, LogInLogoutTable.Late, LogInLogoutTable.Overtime, LogInLogoutTable.Absent, LogInLogoutTable.NumberOfLate, LogInLogoutTable.NumberOfOvertime, LogInLogoutTable.NumberOfAbsent FROM Employees, LogInLogoutTable Where [Employees.FirstName] like '%" + txtSearch.Text + "%'"
End If
AdoEmployeeStatusReport.RecordSource = SQL
AdoEmployeeStatusReport.Refresh
i think there is still an error in my codes somewhere in the bold area, please correct my mistakes....
Query Field Reference
I am wanting to set a defined variable to a field within a query. Here is the following:
********************************
Dim qryLoc1Total As QueryDef
Dim strTotLoc1 As String
Set qryLoc1Total = CurrentDb.QueryDefs _("qry_SalesTotalCurMonth_TotalsLoc1")
*********************************
How do I make strTotloc1 variable equal a field called TotalSales within qryLoc1Total?
Thanks
Query Field On/Off Control?
In the Query design mode there is row titled "Show" with check boxes for each Query field.
Is there any way to check/uncheck that box through VB code?
One Field Is Missing From Query, WHY??
Here is my SQL statement to display all the query in the mshflexgrid:
VB Code:
SELECT Cutt_Master.Month, Cutt_Master.Prod_Code, Cutt_Master.Part_Code,Cutt_Master.Cutt_Date,((Cutt_Master.Qty/1000)*Cost_Price.Origin_Cost) AS Amount, Cost_Price.Origin_Cost,Cost_Price.Insp_Cost, Cost_Price.InProcess_Cost, Cost_Price.Sales_Price " & _ "FROM Cutt_Master INNER JOIN Cost_Price ON Cutt_Master.Part_Code = Cost_Price.Part_Code " & _ "WHERE Cutt_Date BETWEEN #" & dtpFrom.Value & "# AND #" & dtpUntil.Value & "#
but only cutt_date is missing in the mshflexgrid...
i dont know why...
anyone knows??
Memo Field In Query
Guys
I have something a tad strange happening but I'm sure one of you clever chaps will be able to figure it out. Ok, here goes.
This query:
VB Code:
SELECT tbljobdetails.jobno, tbljobdetails.clientID, tbljobdetails.cpc1, tbljobdetails.cpc2,tbljobdetails.cpc3, tbljobdetails.cpc4, tbljobdetails.prodname1, tbljobdetails.prodname2,tbljobdetails.prodname3, tbljobdetails.prodname4, tbljobdetails.plasno1, tbljobdetails.plasno2,tbljobdetails.plasno3, tbljobdetails.plasno4, tbljobdetails.batchesno1, tbljobdetails.batchesno2,tbljobdetails.batchesno3, tbljobdetails.batchesno4, '5137' AS clientid, 'blah blah'AS customer, 'L:mydatalahlah.dat' AS Infile,[b]tblerrortext.errortext AS Errortext[/b],Sum(([tbljobdetails].[plasno1]+[tbljobdetails].[plasno2]+[tbljobdetails].[plasno3]+[tbljobdetails].[plasno4]))AS sumPlas, sum(([tbljobdetails].[batchesno1]+[tbljobdetails].[batchesno2]+[tbljobdetails].[batchesno3]+[tbljobdetails].[batchesno4]))as sumBatches FROM tbljobdetails LEFT JOIN tblerrortext ON tbljobdetails.jobno = tblerrortext.JobNoGROUP BY tbljobdetails.jobno, tbljobdetails.clientID, tbljobdetails.cpc1, tbljobdetails.cpc2, tbljobdetails.cpc3,tbljobdetails.cpc4, tbljobdetails.prodname1, tbljobdetails.prodname2, tbljobdetails.prodname3, tbljobdetails.prodname4,tbljobdetails.plasno1, tbljobdetails.plasno2, tbljobdetails.plasno3, tbljobdetails.plasno4, tbljobdetails.batchesno1,tbljobdetails.batchesno2, tbljobdetails.batchesno3, tbljobdetails.batchesno4, '1234','blah blah','L:lah1234567lah.dat', tblerrortext.errortext, tbljobdetails.id HAVING (((tbljobdetails.id)=102));"
Gives me the information that I need but the tblerrortext.errortext returns a '?' in the query but the actual value in the tblerrortext table is 'Here is an error' or something.
Any ideas!?
Cheers guys
Query Field Error
I am using
myquery = "select * from act where Phone A = '2144331'"
to query my data base. The field name is 'Phone A'. It is a Text field. It seams that my field name is not exepted because it is made out of two words Phone & A. How can I fix this problem without renaming the field.
Thanks
Sanitago
Query An Encrypted Field
I inherited this system that encrypts data in a field that I need to query (In Access now) yet I cannot figure out how to query this field based upon the encryptions. For Example I need to query this where this field = "1". IN the DB this is how the field is encrypted from Table DCRLogFast on Field Books:
IINNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNINNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNINNNNINNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
'Now in the code in VB this is how the table is queried and 'it works! It replaces the position of the field with an I for the number entered. The 'temp' variable is the user input, whereas here I would have entered "1".
Books = "??????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????? ?"
Mid$(Books, temp, 1) = "I"
SQL = "Select * from DcrLogFast where Books like '" & Books & "'"
Anybody got any query suggestions for Access.
VB6 Query On A Date Field
I need assistance concatenating the # character to a variable that contains a date in an sql statement.
The portion of the statement in qestions should look something like this.
#variablecontaingdate#
Any help woulod be appreciated.
Query XML Data From DB Field
Hi All,
I have an XML Doc loaded into a SQL Server DB field of type 'text'. I want to query the XML info, here is the SQL Server code I have so far:
Code:
DECLARE @DOC varchar (8000)
DECLARE @idoc int
select @doc = xmldata from xmltest
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/NeededInfo')
WITH (NeededInfo ntext '@mp:xmltext')
This is OK but the XML field is of type text beacuse it's HUGE and I can't declare @DOC as type text. So, How do I get the entire XML info into the 'sp_xml_preparedocument @idoc OUTPUT, @doc' stored proc?
THX
Cyphin
~Of all the things I've lost, I miss my mind the most.~
~Iz vseh veshei chto ya poteryal, ya bol'she vsego skuchayu po rassudku.~
Edited by - Cyphin78 on 7/7/2005 1:40:10 PM
How Define An Excel Field In A SQL Query
Hi,
I need to realize a conditional insert from Excel to SQL.
The code is the following:
INSERT INTO MKT_TST.dbo.TEMP
(conto,importo,versione,anno,mese,rettifica)
SELECT * FROM OPENQUERY(BASE_DATI,'SELECT * FROM [BASE_DATI$]')
WHERE TEMP.versione <> (SELECT * FROM OPENQUERY(BASE_DATI,'SELECT * FROM [BASE_DATI$.Versione]'))
I have an error related to field VERSIONE;
the message error is:
OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'BASE_DATI$.Versione'
What's the correct way to call the Excel field?
Thank u!
Place Query In Text Field
I have a query which counts Rows;
SELECT tblEquipment.Location_ID, count (tblEquipment.Equipment_ID) as Total_Equipment
FROM tblEquipment
GROUP BY tblEquipment.Location_ID;
I also have a text field named txtNumber. what I want is for the number in the Total_Equipment column from the query to go into my text field? ANyone know how to do this?
Another problem may be that I already have a query for the recordsource. Anyone know how to put two queries in the load event?
Select Query Only Returning One Field
I'm using the following code to load account data into a flex grid table:
Code:
Public Sub LoadAccounts(ByVal myGrid As MSFlexGrid)
modAccount.DBConn_Open
Dim x As Variant 'represents the field in the RecordSet
Dim i As Integer 'represents the index of the field (i.e. x)
Dim LastRow As Integer
i = 1
RS.Open "SELECT AccountName FROM Account", Conn
For Each x In RS.Fields
Debug.Print RS.Fields.Count
myGrid.Rows = myGrid.Rows + 1
LastRow = myGrid.Rows
myGrid.TextMatrix(i, 0) = RS.Fields("AccountName").Value
i = i + 1
Next x
DBConn_Close
End Sub
However, the 'Debug.Print RS.Fields.Count' only returns 1 (there are four test accounts) and the flex grid only displays one name - the same everytime. What's going on?
Using A Field In A Table As Criteria For A Query
I have a query set up that needs to get a criteria value from a field in another table. So far I have tried writing the criteria as:
=[table]![Field]
The query asks for the parameter so it's not reading the value in the field. I really have no idea how to set that up, is there a way of writing the criteria to reference the value in the field in the table. The table I'm getting the value from is not the table the query is based on.
Using Other Field In Table As Query Criteria
Hi, I have 2 fields FieldX, FieldY and they're both datatypes. Ive added these both to a query, but is there a way to query FieldX to find any numbers less than 10% of FieldY
For example
No, Field X, Field Y
1 80 1600
2 40 80
3 9 100
so record 1 and 3 will be shown as there numbers are less that 10% of fieldY's. Record 2 want show as it is not less than 10% of 80 (which is 8)
Thx
------------------------------------------------------------------------------
And when a man gets to heaven,
To saint Paul he'll tell:
1 more soldier reporting sir,
I served My Time in Hell
6 June 1944
2GB DDR400 RAM, 3.4Ghz EM64T P4 Socket 775, 200gb PATA HD 8mb cache, 500GB WD 16mb Cache (Master), 256mb 9950 ATI RADEON AGP ,19" LCD, and a 250W PSU
Running Vs6 Enterprise, running all vs8 products express edition
Edited by - Shandy on 3/5/2007 3:59:18 AM
Pass Values From A Query To A Field
Looking through the Access Bible I am finding it difficult to find specific code for what i require.
I need to copy a field from query to a field within a form. Tried using the Dlookup function but no luck .......hope someone can help -
|
TRACKER
