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
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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
Make Reference To A Field In Ado
Hi everyone.
How i do to make reference to fiel in ado that the name is like
"MYTABLE.MYFIELD" ?
if i put somethind like this
dim rs as ADODB.RECORSET
' *** Initializes connection, ejecute the query, etc.
.
.
.
.
x = rs("MYTABLE.MYFIELD")
i get an error.
If put this in the query: "MYTABLE.MYFIELD AS OTHERFIELD" and i make the
reference like rs("OTHERFIELD") it's works, but i don't wan to make it.
it is the only way ?????
Thanks
Reference Existing Field
Please,how can I reference an existing field in my database in a calculation procedure in my vb Program?
For example if I want a calculation of the form
Total_Time_Run = Total_Time_Run + txtTimeRun.text where Total_Time_Run is a field in my database and txtTimeRun is the textbox on my form.I have tried it and the Field seemed not recognised.
Reference To Field In DoCmd.SendObject
This (hopefully) is the most simple question to have appeared on this forum, but being new to VB, it has me stumped.
In MS Access 97, I am trying to reference the field Start_Date from the table tblBounds in a VBA module. I have this so far:
Function mcrSend()
On Error GoTo mcrSend_Err
DoCmd.SendObject acQuery, "Query_Name", "MicrosoftExcel(*.xls)", "main_address@mail.com", "cc_address@mail.com", "", "subject " & ????, "e-mail body", False, ""
mcrSend_Exit:
Exit Function
mcrSend_Err:
MsgBox Error$
Resume mcrSend_Exit
End Function
Where the ???? is, I would like to have it include the Start_Date from the table tblBounds. What this does is place the date of the data being sent into the subject line of the e-mail it creates. I can replace ???? with DateValue(Now()), but the date is not always today. Again, I hope it's fairly simple to pull the value from the Access table.
Thank you in advance for the information.
**RESOLVED** How To Reference A Field In A Joined Table??
I have an SQL SELECT statement that is pulling data from a query that has 2 tables joined together...
The problem I am having is when I want to refer to a field within the query...look at the code below:
VB Code:
Qsql = "SELECT * FROM qryCPMS WHERE [tblHBUS.Unit Manager] = '" & mgrName & "' AND [tblProductivity.Unit Manager] = '" & mgrName & "' " _ & "AND [tblHBUS.Quarter] = '" & modUserdata.Quarter & "' AND [tblProductivity.Quarter] = '" & modUserdata.Quarter & "' " _ & "AND [tblHBUS.Year] = '" & modUserdata.Year & "' AND [tblHBUS.Year] = '" & modUserdata.Year & "' ORDER BY RepName" 'here is where I refer to a specific field name Round(prstQuality("tblHBUS.Month"), 2) & ""
The field tblHBUS.Month is not coming through, it will either say it cannot be found or that it is the wrong data type...
Is there a special way that I need to write the code so as to capture the data under these circumstances???
Thank you..
Multiple Field Search Using One Data As Reference
i have a database that has two text boxes A_End and B_End that are populated by a combobox selection Town so on some records town a will appear in A_End and on some records Town A will appear in B_End i have created a command button that once a combobox has selected the Town in question i want to open the form up and show records that has town a in A_End or B_End i.e. all records where Town A appears i can't seem to pass two types of where condition into the DoCmd
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
Dim stDocName As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
stDocName = "Progress"
stLinkCriteria1 = "[A_End]=" & "'" & Me![Town] & "'"
stLinkCriteria2 = "[B_End]=" & "'" & Me![Town] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria1 or stLinkCriteria2
DoCmd.Close acForm, "Search for Town"
Exit_Command2_Click:
Exit Sub
Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click
End Sub
E-mail 'Subject' Field To Auto-fill From Reference Cell
I have an Excel project that includes several Command buttons for sending e-mails.
What I want to happen is the ‘Subject’ field in Outlook/Outlook Express to be populated by a default name (Survey Return), followed by the current File name, (A55 *****)
I can make these happen separately.
For the File Name (A55 *****) by using:
Private Sub CommandButton3_Click()
dlgSaveAsString = Application.Dialogs(xlDialogSaveAs).Show
End Sub
And the default name (Survey Return), by using:
Private Sub CommandButton3_Click()
Application.Dialogs(xlDialogSendMail).Show "", "Survey Return"
End Sub
Is there a simple code on these lines that can make this happen?
Alternatively is there a code that could enter the default name, followed by a reference on a Work-sheet Cell?
Any assistance to a Newbie would be appreciated.
Using Combobox In A WHERE Clause Reference In An ADO Query
Hey Guys,
I am trying to use a combobox list as a WHERE criteria. My code continues to return the MsgBox "ERROR: No records found" which indicates that it is not reading the combobox correctly.
The code:
Private Sub cmdGetProduct_Click()
Dim myDSN As String
Dim mySQL As String
Dim conntemp As ADODB.Connection
Dim rstemp As ADODB.Recordset
mySQL = "SELECT DISTINCT PRODUCT FROM OP_CONTR_RPT WHERE OP_NAME = '& cboOperator.Text&'"
myDSN = "DSN=HydraLite;UID=hydralite;PWD=hydra"
Set conntemp = New ADODB.Connection
conntemp.Open myDSN
Set rstemp = conntemp.Execute(mySQL)
If rstemp.EOF Then
MsgBox "ERROR: No records found."
Else
cboProduct.Clear
Do Until rstemp.EOF
cboProduct.AddItem rstemp(0).Value
rstemp.MoveNext
Loop
rstemp.Close
Set rstemp = Nothing
conntemp.Close
Set conntemp = Nothing
End If
End Sub
I am using VB 6.5 and I am querying SQL Server 2005.
I would also like the ability to query a textbox to allow the user to tune his date range... but I have been unsuccesful in getting this to work either. Any help is much appreciated...
Invalid Path Reference Error Opening In Query Design
I have an Access DB that was on my C: drive. It contains tables & queries. I've moved it to a shared network drive. Now, I get an error that occurs when I open the Query in Design view. I receive a message that says "Unable to locate C:...InactA". If I try to run the Query, I get the same message and it doesn't do anything. I've looked in the View, SQL View for my query & it doesn't reference a path. InactA is a table in my database - so I can't figure out why it thinks it needs to be on the C Drive!
As usual, thanks in advance for all your suggestions.
Nate
SOLVED:Question About Setting Up A Cross Reference Query In MS Access
First off I'm very new to Access. I've had some help from a friend that programs databases as his job so he told me that I should set up my database with as little overlap of data in each table.
My question is how do you design a cross reference query. My data structure is two tables. In table one (All_Player), I have all the information about the people that are going to be on the teams. I have set the primary key to be autonumber so I can have people with the same in the data. In my second table I have (Teams), where I want to put the autonumber value from the All_Player table in each of the available positions. So far that has not been a huge problem but I would like to run a query that would allow me to Print the team data and replace the autonumber value with the Players Name. I'm a bit lost so any help would be greatly appreciated.
Thanks,
Max
Edited by - Maxizarius on 11/1/2004 10:59:24 AM
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 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
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
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!
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
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?
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
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 -
Query By A Field Value In The Current Record
I have a table with 3 fields. One with the record index. One with a the next record Index I want to be in my list. This field is called FLINK. How can I build something in acces97 to show one record at a time, and when the user hits next. The next record in the current records FLINK value.
Hopefuly something like a macro or query that I can use in different forms
Updating A Specific Field From Another Table Using A Query.
I am tyring to set two labels equal to the current rev version and date of my application. This information is being read from a table using DAO in Access 2000 with VBA.
The variables are being set fine in the module, yet I am having trouble getting the label captions to populate with it on my form.
Module Code...
Option Compare Database
Option Explicit
Dim msVersion As Integer
Dim msRevChk As Date
Sub setVersion(ps As Integer)
msVersion = Format(ps, "000")
End Sub
Function getVersion() As Integer
getVersion = msVersion
End Function
Sub setRevChk(ps As Date)
msRevChk = Format(ps, "mm/dd/yyyy")
End Sub
Function getRevChk() As Date
getRevChk = msRevChk
End Function
Function app_start()
Dim rs As Recordset
Dim sql As String
Dim sqlV As Integer
Dim sqlD As Date
sql = "SELECT * from tblVersion"
Set rs = CurrentDb.OpenRecordset(sql)
rs.MoveLast
sqlV = rs!Version
sqlD = rs!RevChk
Call setVersion(sqlV)
Call setRevChk(sqlD)
rs.Close
Set rs = Nothing
MsgBox sqlV
MsgBox sqlD
End Function
Form Code...
Private Sub Form_Open(Cancel As Integer)
Me.lblVersion.Caption = getVersion()
Me.lblRevChk.Caption = getRevChk()
End Sub
I have tried making some variables public, but then I have trouble starting the application altogether.
Any help that can be provided would be greatly appreciated...
Updating A Specific Field From Another Table Using A Query
I am tyring to set two labels equal to the current rev version and date of my application. This information is being read from a table using DAO in Access 2000 with VBA.
The variables are being set fine in the module, yet I am having trouble getting the label captions to populate with it on my form.
Module Code...
Option Compare Database
Option Explicit
Dim msVersion As Integer
Dim msRevChk As Date
Sub setVersion(ps As Integer)
msVersion = Format(ps, "000")
End Sub
Function getVersion() As Integer
getVersion = msVersion
End Function
Sub setRevChk(ps As Date)
msRevChk = Format(ps, "mm/dd/yyyy")
End Sub
Function getRevChk() As Date
getRevChk = msRevChk
End Function
Function app_start()
Dim rs As Recordset
Dim sql As String
Dim sqlV As Integer
Dim sqlD As Date
sql = "SELECT * from tblVersion"
Set rs = CurrentDb.OpenRecordset(sql)
rs.MoveLast
sqlV = rs!Version
sqlD = rs!RevChk
Call setVersion(sqlV)
Call setRevChk(sqlD)
rs.Close
Set rs = Nothing
MsgBox sqlV
MsgBox sqlD
End Function
Form Code...
Private Sub Form_Open(Cancel As Integer)
Me.lblVersion.Caption = getVersion()
Me.lblRevChk.Caption = getRevChk()
End Sub
I have tried making some variables public, but then I have trouble starting the application altogether.
Any help that can be provided would be greatly appreciated...
Comma Seperated Values In A Field Query
I have a itemmaster field with itemid field as the primary key char(10) field
i have another table say xyz, where there is a column items varchar(50) containing sample data as follows
XYZ table
Items
I000000001
I000000002
I000000001,I000000003
I want the following query
select * from itemmaster where itemid in (select items from xyz)
to return all items having itemcode I000000001,I000000002,I000000003, but what is happening it is returning only items
I000000001,I000000002
Is there a way to write a query to return the results inthe format i want
ADO Query With AdOpenStatic Cursor Not Returning 1st Field???
I was using this line to run my query with a clientSide cursor:
Rs1.Open querySQL, Conn1, adOpenForwardOnly, adLockReadOnly, adAsyncFetch
I wanted to change this to a ServerSide cursor to speed up the query. The problem when I did this was I wasn't able to get the RecordCount of my Recordset. (RecordCount = -1). I then decided to change the cursor type in my statement from adOpenForwardOnly to adOpenStatic so that I might get the RecordCount. This was my line to run the query:
Rs1.Open querySQL, Conn1, adOpenStatic, adLockReadOnly, adAsyncFetch
Everything works fine, but my query doesn't return the first field now... I am baffled.
MsgBox Rs1(0) returns the second field!
Why is this?
Create Table Query: Format Field
I'd like to run a CREATE TABLE query and have a field (Tag) formatted in a special way. For instance, instead of the Tag being '1', I want it to show as '00000001' in the table. I know it can take the format like that if the field is text, but I'd really prefer to have the field as an integer. I know in Access you can set the Format to '00000000' and it will make sure the leading 0s are there but how do I do this with a CREATE TABLE query? Does anyone know how to do this? Thanx in advance.
Selecting Time Field In Query - Urgent
Hi All,
pls help me with this.
I am having a query in which one field is a datetime. I am using MS Access and VB 6. The data is stored as time in hh:mm format. Usually for a date field we enclose the value within ## (like#31/07/2006#). This does not work with time field. what is the syntax for time field?
Thanks in Advance!
Binish
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
Get Field Header Names From Crosstab Query
I am using VB6 DAO 3.6 and an Access 2K database. I used Access to create a Crosstab query and pasted it into my program as seen below. The Query works fine, but I need to retrieve the names of the Field Headers it creates in the query.
I tried mTmpMaterialsUsed.Fields(0) but this gives me data, as I would expect. I cannot find a mTmpMaterialsUsed.Header property. I can not seem to figure this out. Any pointers?
Wayne
Dim gDB As Database
Dim SQL As String
Dim mTmpMaterialsUsed As Recordset
SQL = "TRANSFORM Sum(MaterialsUsed.Qty) AS SumOfQty " & _
"SELECT MaterialsUsed.Description " & _
"From MaterialsUsed " & _
"GROUP BY MaterialsUsed.Description " & _
"ORDER BY MaterialsUsed.WhatMonth DESC " & _
"PIVOT MaterialsUsed.WhatMonth; "
Set mTmpMaterialsUsed = gDB.OpenRecordset(SQL)
Can I Calculate The Date Field And Retrieve From Query, Is It Possible !
Hai,
I am retrieveding some fields like this TotQty * TotCost, in the same way can I calculate and retrieve the date field. Is it possible.
and
My requirement is , I am having a field RcvdDate i want to minus this date from the Current Date, I need this to store in a variable.
I also seen in the forums and i tried my sellf but not successfully completed. for testing i stored in a textbox using the DateDiff Function, may be the syntax problem or coding problem, i am not able to execute. the below is what i tried. Kindly suggest me how to correct it and how can I solve this problem.
Form_Activate()
Dim Numofdays As Long
Dim sdt As Date
Dim edt As Date
sdt = txtprd.Text
edt = Format(Date, "DD/MM/YYYY")
Numofdays = DateDiff("d", edt, sdt)
Text1.Text = Format(Numofdays, "d")
End Sub
The requirement what i need is for example if
RcvdDate = 01/07/2003 and Date[today date 03/07/2003]
then i need to minus (Date - RcvdDate) in a variable
The number of days between the RcvdDate and Date shoulbe stored as below.
NumberOfDays = 3
Thank you very much,
Chock.
<HTML>
<B><font color="#008000">HAVE A NICE DAY !</font></B>
<HTML>
How To QUERY A FIELD W/ A Data Type - DATE
This is my query without an output but there is a data in a database.
1) Select * From tblProduct Where OrderDate=2/22/2006
This is my query with an error message.
2) Select * From tblProduct Where OrderDate='2/22/2006'
This is my query that will output all products being sold from 2/22/2006 to 2/26/2006
3) Select * From tblProduct Where OrderDate>=2/22/2006 And OrderDate<=2/26/2006
My problem is, i want to view only all records base on a single date, lets say, i am going to view all the products being sold last 2/22/2006. How am i going to manipulate my query. I am using ms-access as my database.
Edited by - gee on 3/2/2006 6:14:55 PM
[SOLVED] Add AutoNumber Field With SELECT INTO Query?
Hi all..
Anyone knows of a way to add an autonumber field in a make table query.
I'm current making a table with the following SQL string along with a schema.ini
Select * INTO myTABLE From OpenRowSet('Microsoft.Jet.OLEDB.4.0','text;Database=C:','Select * from Data.txt')
but I need to add an autonumber field to it in the same string. Is it possible? What will the best solution be?
Edited by - Jinn on 1/14/2005 8:17:36 AM
Query On A Database Field Of Type Logical ?
Hi,
In my .DBF file, I have a field of type Logical.
In the table I can see 3 values for the field ( T, F or blank )
I need to select all line where this field is not T ... not True
How do I do that? I always get Operand/Operator error.
I would need something like this ... but it doesn't work.
"Select * from MyTable where Not is True MyLogicalField"
Thanks for helping.
Edited by - dbelley_office on 12/3/2004 11:26:26 AM
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
Boolean Field In Create Table Query
hello
i am writing a query to create table which contains boolean fields
i tried following code but getting syntax error.
con.execute "create table exa (boolean paper('TRUE','FALSE'),boolean TW('TRUE','FALSE'))"
can anybody help me to solve the prob
thanks
Invalid Field Comparison Error In SQl Query
Hello
I am using EVB 3.0 with pocket PC 2002
I am getting an error message in this SQl query:
Dim paramVal1 as single
paramVal1 = CSng(val1.Text)
sql1 = "Select metal from Table1 where v1=" & paramVal1 & " "
the error message says invalid field comparison. My database is pocket Access and the query gives me no error if i compare a string value. But when i use a numeric value for comparison then it gives me this error. The v1 column is of number type.
Please help..
Mahrine
Edited by - mahrine_r on 11/3/2003 11:54:21 PM
Query Statement Error With Size Field
Hello everybody!
In my database a table have Size field with many other fields.
The error message occurs - " Method 'Open' of object '_recordse't failed " when executing the Statement - "Select Name, Price, Size, Qty FROM Goods",.....
But when I remove the Size field from the above statement it works properly.
Is the Size word Reserved Keyword or something other?
|
TRACKER
