Problem With OpenRecordset And SQL Statement
Jan 10, 2005
Hi there,
Hope someone can help
I am opening a recordset like this, where strDatabase is an SQL Statement
Set rstDatafile = dbDatafile.OpenRecordset(strDatabase, dbOpenSnapshot)
SQL Statement =[tblName] ORDER [Name]
It opens and everything is seemingly OK. However I then run a loop that deletes records as per a set of criteria. If I run this on the same unaltered table I get different results each time. It's almost like the SQL statement is not ordering it the same each time.
Am I doing something worng or is there another way to open a table order it's contents and modify it?
Thanks for your time
View Replies
ADVERTISEMENT
Sep 13, 2006
I’ve the following query definition “selOrders”
PARAMETERS [DateFrom] DateTime, [DateTo] DateTime;
SELECT * FROM Orders WHERE OrderDate BETWEEN [DateFrom] AND [DateTo]
I want to open this query as a DAO.Recordset but have problems to assign values to the parameters. I tried different possibilities but invain. My latest try was as follows:
Dim QryDef As QueryDef
Dim Date1, Date2 As Date
Dim Orders As DAO.Recordset
Set QryDef = CurrentDb.QueryDefs("selOrders")
QryDef.Parameters("DateFrom") = Date1
QryDef.Parameters("DateTo") = Date2
Set Orders = CurrentDb.OpenRecordset("selOrders")
During execution error 3061 (Too few parameters, expected: 2) occurs.
View 1 Replies
View Related
Nov 18, 2004
I am trying to open a recordset object using a reasonably complex query. The query uses other queries to get data and some of these queries are totaliser queries (I dont know the proper terminology but they are the ones that group and sum up data in the query).
Dim D As Database, R As Recordset, R2 As DAO.Recordset
Set D = DBEngine.Workspaces(0).Databases(0)
Set R2 = D.OpenRecordset("Qry Daily Losses", dbOpenSnapshot)
The error Message I get is:
Run-time error "3061"
Too few parameters. Expected 4.
Most of the information I have seen on this error message talks about 1 parameter expected not 4.
I can open the query directly by clicking on it in the objects list and it looks fine. It is only when I try to open it in my code that it goes wrong.
I am using Access 2002 (SP3)
View 2 Replies
View Related
Nov 7, 2004
Hello. I am trying to sort this out and hope someone can help.
Using Access 2002 front end, sqlserver 2000 back end.
In my front end database I have a query called "queEEGBilling" that is used to populate a report. It is quite a complex query which I did using the query designer, NOT with a WHERE sql command. I am not very good with SQL syntax.
The report works fine, and displays the records I want it to display.
However, after the report is displayed, I want to modify one field in each record to indicate that the report for hat record has been printed out (it is a billing package, and I want to indicate that the bill has been sent).
In order to do this, I need to access the recordset that was used to create the report. As I understand it, I cannot do this with a report (no equivalent of the recordsetclone property or anything similar). What I have to do is create the recordset that was used to create the report in the first place. No problem right?
set mydb=dbengine(0)(0)
set rs1=mydb.openrecordset("queEEGBilling")
However, here I get the infamous error 3061. Too Few Parameters. Expected 1.
I have googled this up and down, and it seems to be related to my query being too complex. However, I did not write this query by hand, it came from Access2002. And this query worked fine to generate my report. So, why does it crash now??
Any help would be greatly appreciated.
Cheers
Gyl
View 1 Replies
View Related
Oct 24, 2013
I would like to join my two tables in vba together so I can reference them in the same way,
ie.
Set rs = dbs.OpenRecordset(SQLstr)
Me.Summarystr = Nz(rs![Summary], Null)
so I want to be able to combine two tables together in the SQL string in order to have both information inside the same record set...
Code:
SQLstr = "SELECT * FROM [table1] " & _
"FULL OUTER JOIN [table2] ON [table1].[sequence]=[table2].[sequence] " & _
"WHERE [table1].[sequence]='" & _
Me.seq.Value & "'"
I just tried this but it didnt seem to like it
View 3 Replies
View Related
Feb 24, 2015
The code below should add a record to another table when a box is ticked. It worked fine when I wrote it in a local Access 2003 form.I have since migrated the tables to Azure and connect via ODBC, and now I get this error:nOpenRecordsetBug.PNG
A search for Identities told me to modify the code as in the second screenshot, and I now have a different error: InvalidArgument.JPG
View 4 Replies
View Related
Jan 27, 2006
How can I have a user enter the WHERE criteria in the Set Rs statement below.
Set Rs = CurrentDb.OpenRecordset("SELECT [Net] FROM [tblRecap] WHERE [Yr] = 2000 and [Event] = 'lancaster'")
I would like a message box to ask the user to define the [Yr] = and [Event] = fields since they would change from time to time and then pass the values to the OpenRecordset method. Is this possible? Can someone help supply the code?
A parameter query won’t work because when you manipulate these queries in DAO through VBA you need to supply the parameter value before you open the recordset object. If you don’t DAO generates an error.
Here is my code:
Option Compare Database
Option Explicit
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim ConsqWin As Long
Dim ConsqLoss As Long
Dim tmpWin As Long
Dim tmpLoss As Long
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("SELECT [Net] FROM [tblRecap] WHERE [Yr = 2000 and [Event] = 'lancaster'")
Do While Not Rs.EOF
Do While Rs!Net > 1
tmpWin = tmpWin + 1
Rs.MoveNext
If Rs.EOF Then Exit Do
Loop
If Rs.EOF Then Exit Do
If tmpWin > ConsqWin Then ConsqWin = tmpWin
tmpWin = 0
Rs.MoveNext
Loop
Set Rs = Nothing
End Sub
By the way the YR field is a number and not a date so the WHERE clause doesn’t need #2000#
Any help would be appreciated.
View 2 Replies
View Related
Jan 16, 2013
I split my database into a FE and BE to simplify the upgrading of the clients copy. I had a chance to implement that change today and except for this error all seems to be well.
The error is "Invalid Operation 3219 " and flags this line of code "Set rsTable = dbsCurent.OpenRecordSet("Import650tbl, dbOpenTable, dbAppendOnly) " when the error is triggered. The table Import650tbl is a temporary holding table to which I enter imported records. I then add some supporting information and then the records are added to live data table for further processing. After the data is added to the live table the information in the Import650tbl is deleted by a query to be ready for tomorrows processing.
This error prevents the process from working. I got around the problem by deleting the link to the BE for this table and creating the table in the FE. After that all was well.
Question: Why does the above OpenRecordSet fail on the linked table?
View 1 Replies
View Related
Sep 9, 2013
I have a library function that will allow the user to nominate a query (as one of its arguments) in the calling application which must have an email field. The function will then Do Loop the email field, concatenating it before creating an email and addressing it. The intended functionality is that a developer can easily create a group email, just by creating a query.
This works fine if the query is filtered "statically" - i.e. I specify which group of people by typing in their "Site_ID" in the criteria. However I want developers to be able to creating dynamically filtered queries (perhaps by the group's ID on a calling form). Within the query (to test it), the filter is therefore [Forms]![Test Function Calls]![Site_id]. When I run the code, I am then presented with "Run-time error 3061: Too few parameters. Expected 1". The code in question is:
Dim rst As DAO.Recordset
Dim stTo As String 'one of the function's arguments received from the calling function.
Dim stToString As String 'the built up concatenated emails
Set rst = CurrentDb.OpenRecordset(stTo, dbOpenDynaset, dbSeeChanges)
[Code] ....
View 6 Replies
View Related
Feb 12, 2014
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
View 2 Replies
View Related
Oct 31, 2014
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
View 4 Replies
View Related
Apr 28, 2015
I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
View 11 Replies
View Related
Sep 25, 2005
I have a text box that I'm trying to control the color of based off an IF then statement. Based off to If's
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - A" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
This part works but I need to add a second one
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - B" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
So I want the text box to be RED if the status of both Ch A and Ch B is 0
but if either one has a status greater than 0 then the text box would be green.
Thanks
Jon
View 4 Replies
View Related
Oct 15, 2005
I'm using this code to do some calculations on a form. This code works fine
as long as only one IF statment is true. My problem starts when the one of the values is equal (meaning qual is 4) so I try to add < or = to and then I end up with more then one IF statement being true. Is there a better way to do this?
If Me.[qual] < 4 And Me.[completed] < 61 Then
Me.[GtoG] = [GtoGtotal]
Else
End If
If Me.[qual] < 4 And Me.[completed] > 61 Then
Me.[GtoG] = Me.[qual] + 61
Me.[temp1] = Me.[completed] - 61
Me.[Delayone] = Me.[Delayone] + [temp1]
Else
End If
If Me.[qual] > 4 And Me.[completed] < 61 Then
Me.[GtoG] = Me.[completed] + 4
Me.[temp2] = Me.[qual] - 4
Me.[Delaytwo] = Me.[Delaytwo] + [temp2]
Else
End If
If Me.[qual] > 4 And Me.[completed] > 61 Then
Me.[GtoG] = 65
Me.[temp1] = Me.[completed] - 61
Me.[Delayone] = Me.[Delayone] + [temp1]
Me.[temp2] = Me.[qual] - 4
Me.[Delaytwo] = Me.[Delaytwo] + [temp2]
Else
End If
View 3 Replies
View Related
Jun 21, 2006
Is there a better way to do this as it's not giving me the right results - so maybe I should say a correct way to do this!
=IIf([PMtype]="3" Or "4" Or "5",2,1)
Thanks
DBL
View 8 Replies
View Related
Nov 21, 2006
I am trying to do something really simple, but my lack of experience in Access has bitten me.
I have a report... there is a field called payor_code and one called bill_time. These are both based on tables.
The variable is payor code... it could be 2 different things.
All I want to do is make a calculation for TOTAL that multiplies the bill time, times the correct rate. (which is determined by the payor code)
I want to basically do this:
If payor_code = "02" Then
total = Bill_Time * 1
Else
total = Bill_Time * 2
Any suggestions?
View 14 Replies
View Related
Jul 19, 2007
Hellow :(
Question.....
i have a report in access on which grades of student are shown, like the following example:
Lifescience 6
Brainstorming 7
learningdev. 9
communication 5
My goal is to, next to the grades, also to put the grades as words...
Lifescience 6 six
Brainstorming 7 seven
Learningve. 9 nine
Communication 5 vife
I have no clue actually how to do that, i of course thought of an IIF statement, the only thing i know is the crystal reports syntax, but in this case i am not sure how to 'translate' it to the expression on the field.
(that is: i think i have to use the field where the grade comes from?)
Thanks for this, i think, relatively simple question........
Tj
View 3 Replies
View Related
Mar 31, 2008
Ok guys, I am stuck. If I have the following codes that works fine when I separate each "IF" statement, but I was trying to combine it all into one code. When I combine the code, I continue to get the msgbox even when the criteria has been met. Please tell me what I am doing wrong. Just trying to simplify the codes.
If Me.cboFat = 1 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If
If Me.cboFat = 2 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If
If Me.cboFat = 3 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If
If Me.cboFat = 4 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If
If (Me.cboFat = "1") Or Me.cboFat = "2" Or Me.cboFat = "3" Or Me.cboFat = "3" _
Or Me.cboFat = "4" And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken,Closing Date is a required field!"
Cancel = True
End If
View 4 Replies
View Related
Apr 17, 2005
Hi,
I'm trying to calculate the value of a field based on the other columns in the table. It's working in a form but I need to save the value in the table. I'm selecting the default value of the field and entering the following:
IIf(DateDiff('m',[Vesting Start Date],Date())>12,0.25*[Number of Options]+(DateDiff('m',[Vesting Start Date],Date())-12)*0.03*([Number of Options]-(0.25*[Number of Options])),0)
It doesn't recognize any of the column names like Vesting Start Date. Is it possible to calculate the value of a field based upon the other columns?
Thanks
View 2 Replies
View Related
Jul 6, 2006
On the following code I want to attribute the value of the sum([price]) to an variable called txtFullPrice:
Is there anybody that could help me please.
Dim dbs As Database, rst As Recordset
Dim strcriteria As String
'return reference to current database
Set dbs = CurrentDb
'Define serach criteria
strcriteria = "SELECT SUM([Price]) FROM tblRBS WHERE [USERID]='" & FOSUsername & "'"
'Create a dynadet-type Recordset object based on tblUser table
Set rst = dbs.OpenRecordset(strcriteria)
rst.Movelast
*******
'close connection on tblUser table
rst.Close
Set dbs = Nothing
This Forum have been helping me to improve my skills a lot. Thanks to everyone.
Jony Bravo
View 5 Replies
View Related
Jun 27, 2005
I have a nested IIF statement in one of the parameters of my query that checks for the value in an option box and returns the results for a specific record based on the value. This works great for what we need and we've been using it for over a month without a problem.
I just found out that there may be instances where management might want to see a group of records with combined data. I've racked my brain and searched this forum and have not come up with a solution yet.
Here is my IIF statement:
IIf([Forms]![frmRejectopt]![optgrpWC]=2,1,IIf([Forms]![frmRejectopt]![optgrpWC]=3,2,IIf([Forms]![frmRejectopt]![optgrpWC]=4,5,IIf([Forms]![frmRejectopt]![optgrpWC]=5,9,IIf([Forms]![frmRejectopt]![optgrpWC]=6,10,IIf([Forms]![frmRejectopt]![optgrpWC]=7,11,IIf([Forms]![frmRejectopt]![optgrpWC]=12,4,IIf([Forms]![frmRejectopt]![optgrpWC]=8,3,IIf([Forms]![frmRejectopt]![optgrpWC]=9,6,IIf([Forms]![frmRejectopt]![optgrpWC]=11,7,IIf([Forms]![frmRejectopt]![optgrpWC]=10,8,[tblProcess]![ProcessID])))))))))))
In the first line is where I tried expanding the criteria to a group but I either get blank results or an error depending on how I've tweaked it.
This is an example of what I'm after:
IIf([Forms]![frmRejectopt]![optgrpWC]=2,([tblProcess]![ProcessID]=1 Or [tblProcess]![ProcessID]=9 Or [tblProcess]![ProcessID]=10 Or [tblProcess]![ProcessID]=11),IIf([Forms]![frmRejectopt]![optgrpWC]=3,2,IIf([Forms]![frmRejectopt]![optgrpWC]=4,5,IIf([Forms]![frmRejectopt]![optgrpWC]=5,9,IIf([Forms]![frmRejectopt]![optgrpWC]=6,10,IIf([Forms]![frmRejectopt]![optgrpWC]=7,11,IIf([Forms]![frmRejectopt]![optgrpWC]=12,4,IIf([Forms]![frmRejectopt]![optgrpWC]=8,3,IIf([Forms]![frmRejectopt]![optgrpWC]=9,6,IIf([Forms]![frmRejectopt]![optgrpWC]=11,7,IIf([Forms]![frmRejectopt]![optgrpWC]=10,8,[tblProcess]![ProcessID])))))))))))
I hope I was clear in my explanations. Thanks in advance for your help.
View 11 Replies
View Related
Sep 1, 2005
What i am trying to do is:
IIf([Me].[Custom1Checkbox]=-1,([tblPart].[Description]) Like "*" & [Forms]![frmParts]![TxtFilter] & "*",([tblPart].[Description]) Like [Forms]![frmParts]![TxtFilter] & "*")
have tried:
(IIf([Me].[Custom1Checkbox]=True,[tblPart].[Description] Like "*" & [Forms]![frmParts]![TxtFilter] & "*",[tblPart].[Description] Like [Forms]![frmParts]![TxtFilter] & "*"))
and
(IIf([Me].[Custom1Checkbox]=-1,[tblPart].[Description] Like "*" & [Forms]![frmParts]![TxtFilter] & "*",[tblPart].[Description] Like [Forms]![frmParts]![TxtFilter] & "*"))
but with no luck
what i have done to get over it for now is:
Link to post (http://www.access-programmers.co.uk/forums/showthread.php?t=92802)
View 12 Replies
View Related
Oct 12, 2005
Hi there
I am having problems with an IIF statement and I was wondering how I can get the query that I need to work.
I have the following columns
application_type: this has three values in it - Major, Minor and Other
numberofdaystodecide: this is the number of days taken to decide a file
what i want to do is an iif statement that works as follows
iif the application type = major and the numberofdaystodecide >92 then over 13 weeks
iif the application_type = major and the numberofdaystodecide <=92 then less than 13 weeks
iif the application_type = minor and the numberofdaystodecide >56 then over 8 weeks
iif the application_type = minor and the numberofdaystodecide <=56 then less than 8 weeks
iif the application_type = other and the numberofdaystodecide >56 then over 8 weeks
iif the application_type = other and the numberofdaystodecide <=56 then less than 8 weeks
Any ideas how I put this in to a query to get the information that I need?
Tried a couple of things to no avail:(
Thanks for any help :D
Jools
View 5 Replies
View Related
Feb 20, 2006
Hello Guys,
I have a question.
I have a query that looks like this:
Category Customer_ID
1 123444
1 233732
1 87667
1 3343
1 99876
2 123
2 775446
2 332334
2 22311
2 1121
Now, from Category 2 I want to see the top 3 and from Category 1 I want to see all.
I know I can't use Select TOP because it cuts random.
I just want to cut from Category 2.
Thank you in advance guys
View 1 Replies
View Related
Mar 4, 2006
I am using a database that holds (amongst other things) payments received from a membership. This info is held in tblPayments. When payments are received I enter a new record against the membership number and in a field named PdForYr I enter the year for which they are paying, i.e. current year is 2005/6. I need to be able to extract members who have NOT renewed, i.e. this info is not in the database. So I think I need a SQL statement asking for records that paid in 2004/5 but not in 2005/6. Can't quite get it. Any help please?
View 6 Replies
View Related
Mar 24, 2006
I am trying to display Shift information in a query by using the following
IIf(Time([CALL IN]) Between (07:00) and (15:30),"1st Shift",IIF(Time([CALL IN]) Between (15:31) and (23:30) ,"2nd Shift",IIF(Time([CALL IN])Between (23:31) and (06:59) ,"3rd Shift"," ")))
and I am getting a message "The expression you entered has too many closing Parentheses"
Can anyone spot my error
Thanks
View 3 Replies
View Related
