Queries :: Proper Syntax For FindFirst Method
Nov 15, 2013
I can't seem to figure out the proper syntax for the FindFirst method. I am using several variations of this effort:
Dim dbs As dao.Database
Dim rst As dao.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInvoice", dbOpenDynaset)
rst.FindFirst "rst!ID = frmInvoice!txtID"
I get an error message that says that Access doesn't recognize rst.ID as a valid field. But, it most certainly is. I tried substituting tblInvoice but got the same error.
View Replies
ADVERTISEMENT
Aug 1, 2005
I am writing an event procedure to check to see if a particular Project number exist in a recordset. I am trying to use the findfirst method and are having some problems. Here is my code.
Private Sub Command3_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset, ProjectNo As String, SqlStr As String, StrProjectNo As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblTrackingSheetFrm", dbOpenTable)
StrProjectNo = Me![ProjectNumber]
rs.FindFirst StrProjectNo
If rs.NoMatch Then
Forms![frmProjectCriteria].Visible = False
DoCmd.SetWarnings WarningsOff
DoCmd.OpenQuery "(1)qryDeletetblTrackingSheetFrm"
DoCmd.OpenQuery "(1A)qryDeletetblTrackingSheetTMP"
DoCmd.OpenQuery "(2)qryAppendProjectTasks"
DoCmd.OpenQuery "(3)qryMaketblLaborActuals"
DoCmd.OpenQuery "(3A)qryUpdatetblTrackingSheetTMP"
DoCmd.OpenQuery "(4)qryDeletetblMaterialActualsTMP"
DoCmd.OpenQuery "(5)qryAppendEquipment"
DoCmd.OpenQuery "(6)qryAppendInventory"
DoCmd.OpenQuery "(7)qryAppendPayables"
DoCmd.OpenQuery "(8)qryAppendPurchaseOrder"
DoCmd.OpenQuery "(9)qryUpdateMaterialActuals"
DoCmd.OpenQuery "(A)qryAppendtblTrackingSheetFrm"
DoCmd.SetWarnings WarningsOn
DoCmd.OpenForm "frmTrackingSheet", acNormal
Else
MsgBox " Project worksheet already opened by another user."
rs.Close
End If
End Sub
What this does is check to see if another user has a project open and if so doesnt allow that user to access that project. I am getting the following error when I execute the procedure on the findfirst Code line.
Runtime error 3251 Operation is not supported by this object type.
Can someone take a look and see what I am doing wrong.
Any help is greatly appreciated.
View 2 Replies
View Related
Mar 8, 2014
I am trying to locate a record on a multirecord form by using Findfirst method. Here is the code.
Dim UA1 As String, UAE1 As String, UA2 As String, UAE2 As String, UA3 As String, UAE3 As String, apost as string, repl as string
apost = "'"
repl = "''"
UA1 = Nz(Forms(ParName).Form.NAME, " ")
UAE1 = Replace(UA1, apost, repl)
[Code] ....
This code sometimes works and sometimes it does not work. The field CNO is a text field of 5 characters but contains the card numbers that is numeric data or nothing.
View 2 Replies
View Related
Mar 25, 2008
Hi, I need some syntax help on the following line of code:
rs.FindFirst (rs![NewRecord] = True And IsNull(rs![Event_No]))
where NewRecord is type boolean and Event_No is string. Ive used this function mainly on strings so am not sure what to do with this one!
thanks!!
View 2 Replies
View Related
Aug 3, 2005
hello,
table1_ProductList -> has the following fields:
ID (primary key,auto number)
SERNO(text)
DESCR(text)
PRICE(currency)
table2_Jobcard -> has the following fields:
ID (primary key,auto number)
PROD_DESCR_ISSUED (droplist, get 'prod desc' value from table1)
PROD_QTY_ISSUED (decimal)
DATE_ISSUED (datetime)
___________________
PROBLEM/QUESTION :
Let's say I change the PRICE(in table1) on 1 Jan 2005, then all EXISTING records (before 1/1/2004) will also be updated with the new(changed) PRICE.
This is WRONG, right?
What is the correct procedure to use/update the PRICE value ??
Any advice will be greatly appreciated.
Regards,
Jamie.
View 1 Replies
View Related
Dec 2, 2014
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
View 2 Replies
View Related
May 4, 2015
I have a query, that contains the field, weeknumber and weekday value, and year.
now i want to convert this values to proper/standard date format.
Example:
Weeknumber: 19
Weekday: 3 (Tuesday)
Year: 2015
Expected Result: May 5, 2015
View 5 Replies
View Related
Dec 16, 2013
I have a small issue where I do have names written in UPPER CASE, I would like to transform them in Capitalized (each word), I understand that I cannot use the same function as in excel (PROPER) but something like StrConv, but as I do have a ‘comma’ in between the names I have something like that
Code:
StrConv(Left(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",")-1), 3)&", "&StrConv(Mid(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",") + 1), 3) AS CompleteName
Which as you can see is quite a complex statement, how can I simplify it?
Example of a name: VITOR, FONSECA, preferable output, Vitor, Fonseca
View 14 Replies
View Related
Jun 18, 2014
Is there a way of finding out who is turning18 years old in the next 6 months on a set day.
Specifically, I want to minus a date of birth from todays date (DateDiff?) and then for it to show those people who will be turning 18 in the next 6 months.
I've tried a range of Datediffs but I can't seem to get what I need.
View 11 Replies
View Related
Jul 30, 2014
I am using MS Access 2007.
I would like to display a report based on the table called "expenses", filtered by:
- "from" (datefrom field) and "to" (dateto field) date on "payment date" field;
- multiple criteria on same field called "payment method" (I would like to include only payment methods "check" and "credit card", but not the other payment methods in the field, such as "cash", "transfer", etc.)
For that end I made a query based on the table "expenses", and in the "payment date", in the criteria field, I entered:
between [form]![formname]![datefrom] and [form]![formname]![dateto]
This works fine so far, however when I attempt to add multiple criteria on the "payment method" field, it does not filter accurately any longer. In the same row of the criteria field where I completed the date criteria, I enter "check". In the next row, same field, I enter "credit card".
Since it doesn't work, I tried putting both arguments in the same line as the date criteria (always in the payment method field) as: "check" and "credit card" but still does not work (now it filters the payment method correctly, but the dates filter appear as if I have never completed them).
View 3 Replies
View Related
May 9, 2005
Hello,
I hope I have posted this to the correct forum?
Anyways, I have started porting my database backend to MySQL after a spate of corruptions and speed degrading daily, I have managed to get most things functional but one thing which I can not get working is an odd thing with the FindFirst statement.
I have the following line of code...
rst2.FindFirst "AwaitingStock=True and StockIn=False and DOA=False and Model='" & Trim(Me.Model) & "'"
which works find with Access backend but with MySQL I get the error...
Run-time error '3761':
The decimal field's precision is too small to accept the numeric you attempted to add.
This is obviously not the correct error as I am not trying to add anything!
If I remove the bit about the Model, the code executes fine, also, if I remove all the =True parts and just leave the Model part everything works fine so I guess it is because I am mixing string and integer fields in the search???
Any help greatly appreciated.
Kind regards,
Tom Findlay
View 2 Replies
View Related
May 1, 2006
When, id do press the 'RecordWeergeven' button, Microsoft Visual Basic returns with a error. Method or data member not found. I think the error is located bij Me.Keuzelijst0. Do i have to declare a Listbox or create a second recordset. Please help.
Private Sub RecordWeergeven_Click()
'Me.RecordsetClone.Findfirst "[ID] = " & Me.lstList.ItemData(lstList.ListIndex)
'Me.Bookmark = Me.RecordsetClone.Bookmark
Dim rst As ADODB.Recordset
Set rst = Forms![FMR_users].RecordsetClone
rst.FindFirst "usr_id=" & Me.Keuzelijst0 & ""
Forms![FMR_users].Bookmark = rst.Bookmark
DoCmd.Close acForm, "GaNaarRecord"
End Sub
View 3 Replies
View Related
Mar 2, 2005
Hello All
I just discovered the reason why my table has not been working the way I want it to. In my code below, I have set my rst to find the first record of the previous month which in itself is correct, however I just discovered that it is picking up the records in ascending order.
Here is my code:
Option Compare Database
Option Explicit
Private Sub Button5_Click()
Dim prevMonth As Integer
Dim curMonth As Integer
Dim prevYear As Integer
Dim curYear As Integer
Dim CurRecordMonth As Integer
Dim rst As Recordset
Dim rst2 As Recordset
Dim db As Database
Dim monthText As Variant
Set db = CurrentDb
'fill an array with the text for months names
monthText = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
' find previous and current Year and month. If current month = jan then go back to Dec of the year before
curMonth = Month(Date)
curYear = Year(Date)
prevYear = Year(Date)
prevMonth = Month(Date) - 1
If prevMonth = 0 Then
prevMonth = 12
prevYear = prevYear - 1
End If
' count number of existing records for current year and month
' If DCount("Month", "TTransactions", "month = '" & monthText(curMonth) & "' and year = " & curYear) = 0 Then
If DCount("Month", "tTransactions", "month = " & curMonth & " and year = " & curYear) < 2 Then
'if current month and year does not exist in table
' open table and find last months record
Set rst = db.OpenRecordset("tTransactions", dbOpenDynaset)
rst.FindFirst "month = " & prevMonth & " and year = " & prevYear
' open table again to write a new record
Set rst2 = db.OpenRecordset("tTransactions", dbOpenDynaset)
Do Until rst.NoMatch ' loop through all records meeting the criteria
rst2.AddNew
rst2![TelNo] = rst![TelNo]
rst2!Year = curYear
rst2!Month = curMonth
rst2!Rental = rst!Rental
rst2![Fees] = rst![Fees]
rst2![Vat] = rst![Vat]
rst2.Update
rst.FindNext "month = " & prevMonth & " and year = " & prevYear
Loop
rst.Close
rst2.Close
Set rst = Nothing
Set rst2 = Nothing
End If
Set db = Nothing
End Sub
Function CallButton5()
Call Button5_Click
End Function
I have put in an autonumber to assign sequential numbers to the records as they are entered. This I had hoped would allow me to sort my query by autonumber, however if the findfirst keeps finding the records in ascending order then I'm lost. Is there any way to get round this?
Thank you Kindly
View 3 Replies
View Related
Feb 23, 2005
Hello,
I have a procedure that searches a table (recordset 1) for values in a lookup table (recordset 2) using FindFirst / FindNext. The routine worked fine until recently, I now have two issues;
I have included a new country in the lookup table - Cote D'Ivoire, now I get an error message when it reaches this name. I guess it's the " ' " that is causing the problem but don't know how to get round it!!??
I have modified the program so I can select the field I want to search from a form (thanks John) but it won't accept the field name as it's not part of the recordset e.g. rstTempTable.findfirst "[Field] = etc. How can I pass the chosen field from the form to the recordset?
Thanks in advance .....
View 4 Replies
View Related
Sep 27, 2005
Need a little help with a record selector.
I ask it to find a record and bookmark it. No problem.
If record doesnt exist I get the value from a control and run a Insert Into command into my table creating a new record with that case #.
Now how do I modify the code below to make the new record just inserted into the bookmarked record. See sample code below. I'm not too practiced when it comes to this recordset business.
Any help is appreciated
Private Sub FindTheRecord()
' Find the record that matches the control.
Dim rs As Object
Dim Answer As String
Dim aSQL As String
Set rs = Me.Recordset.Clone
rs.FindFirst "[CaseNo] = " & Str(Nz(Me![CaseNo]))
If rs.NoMatch Then
Answer = MsgBox("No Matching Case Number Found." & vbCrLf & "Would you like to start a new" & vbCrLf & "record using this case number?", vbYesNo)
If Answer = 6 Then
DoCmd.SetWarnings False
aSQL = "Insert Into Main ([CaseNo])Values ([Forms]![frmMain]![CaseNo]);"
DoCmd.RunSQL aSQL
DoCmd.SetWarnings True
DoCmd.GoToRecord acDataForm, "frmMain", acLast
Code: Original - Code ' does not recognize the last record just added to the table, is there a command I can use here? ' does not recognize the last record just added to the table, is there a command I can use here?
Else
MsgBox "Action Cancelled"
CaseNum = ""
CaseNumYear = ""
DoCmd.GoToControl "CaseNum"
End If
Else
Me.Bookmark = rs.Bookmark
Call EnableControls
End If
End Sub
View 3 Replies
View Related
Mar 4, 2005
Hi!
I think this is propably an easy thing to do, but nevertheless I have not been able to make it work in MS Access...
(Let's say) I have a table with information about people. Sometimes I need to get a subgroup of the table_people, like a group with a certain age, or who live in a certain area, or who like certain things. And sometimes a need to work with the whole table.
To be able to do this basic filtering I have a form with check boxes, like "check this if you want to filter by a persons hobby" and appropriate fields where to choose a hobby from a list. My problem is: how do I add this "condition" to my query? I've tried eg. using "Like" as a filtering tool and IIF() to add the condition, but this doesn't work (in the criteria row):
Like (IIF( Forms![basic]![Filter_by_hobby] , "[Forms]![basic]![Hobby_from_list]", "*"))
So the idea is that if the box is checked, the condition is true and the first text is chosen, and if no filtering by hobby is required, all people are chosen regardless of their hobbies (Like "*")
How to fix this - is there a syntax error or a profound mistake in my thinking - or how to do the same more elegantly?
Thanks in advance,
garcanrya
View 2 Replies
View Related
Mar 3, 2014
I have a query written in Visual Basic as follows
UA1 = Forms(ParName).Form.NOM
UA2 = Forms(ParName).Form.PRENOM
UA3 = Forms(ParName).Form.CARTE
Forms(ForName).Recordset.FindFirst "[NOM] = '" & UA1 & "'" & " And " & _
"[PRENOM] = '" & UA2 & "'" & " And " & _
"[CARTE] = '" & UAE & "'"
This query works and I can spot the record based on 3 fields and display it. Now I have discovered that we have a person who has an apostrophe in his name like O'Brian. The above code gives a syntax error in this case.
View 3 Replies
View Related
Apr 10, 2015
I have table where First Name, Last Name and Initial are all in one field. What is the syntax to remove initial? Been playing with Right and Len with no success, I'm sure this has been done before.
View 14 Replies
View Related
Jun 4, 2013
I have a combo box on a form where I allow the user to pick an office. The primary key is the bound column which I then want to use in a stored query to limit the query results to the specific office selected. I can easily do this with the docmd.runSQL in the module, but I'd like to use a stored query instead. My exact problem is I can't get the syntax correct in the Where clause in the query design. I use the build feature and get the following: [Forms]![frmReports]![cboOffice] which doesn't work. If I manually put the key value in the Where clause, it works fine. tell me the correct syntax for the Where clause.
View 4 Replies
View Related
Sep 5, 2014
Trying to parse first two sections of a five section string. Now getting a SYNTAX COMMA ERROR. Th Left((([ArborID],InStr([ArborID],"-")+1,(InStr(InStr ([Arbor ID],"-")+1,[ArborID],"-")-InStr([ArborID],"-"))-1)),10)
View 14 Replies
View Related
Jan 19, 2015
Code:
Dim AppendUsers As String
AppendUsers = "INSERT INTO tblPermissions ( empid )" & _
"SELECT tblEmployee.empid" & _
"FROM tblEmployee LEFT JOIN tblPermissions ON tblEmployee.[empid] = tblPermissions.[empID]" & _
"WHERE (((tblPermissions.empID) Is Null) AND ((tblEmployee.active)=Yes) AND ((tblEmployee.inputname)=Yes));"
If MsgBox("This will search for new users with valid logins and add to the permissions table, do you wish to continue?", vbYesNo, "Append Users") = vbYes Then
DoCmd.RunSQL (AppendUsers)
View 4 Replies
View Related
Nov 19, 2014
why the first code is working and after adding a few lines it gives me a syntax error. It highlights the AS ETI. I usually work on the query grid, but I read somewhere there is a limit to the length of expression. So I ended up editing the expression in SQL view, but somehow doesn't work.Working before editing:
Code:
SELECT [Salaries YTD].[Emp#], [ETI Filter].ETI1, Sum([Salaries YTD].DaysWorked) AS SumOfDaysWorked, IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5),IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000,1000,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000))))) AS ETI, DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6) AS Weeks, Sum([Salaries YTD].Gross) AS SumOfGross1
[code]...
View 14 Replies
View Related
Nov 27, 2014
I have 2 tables called MakeTable1 and DBO_TBL_Activity
Im trying to update MakeTable1 with the values from TBL_Activity when both activity.StartDate and maketable1.Dates match but also acticity.IDStaff and Maketable1.ID Match
Below is the SQL i have so far
Code:
UPDATE [MakeTable1].[Detailsa] SET [dbo_tbl_activity].[details]
WHERE [MakeTable1.Dates)=[dbo_tbl_activity].[StartDate] AND [MakeTable1].[id]=[dbo_tbl_activity].[idstaff]);
The error is :syntax error in update statement
View 2 Replies
View Related
Apr 2, 2013
why this query comes back as invalid syntax?I am attempting to find all records older than two quarters, Month of Contact actually displays the date as ##/##/####
View 4 Replies
View Related
Mar 23, 2013
Correct syntax/expression for log of a number. The number i am getting in Access is twice as big as the correct number i get in excell
View 3 Replies
View Related
Feb 27, 2014
How to DELETE rows in one table that have a field value that matches a field value from another table? Tried these but sytax errors:
DELETE * FROM tmpBankDebitsMinusJE
WHERE tmpBankDebitsMinusJE.ConcatenateBankDebits IN
(SELECT tmpJournalEntryChangeOrders.Concatenate);
DELETE FROM tmpBankDebitsMinusJE
WHERE ConcatenateBankDebits IN
(SELECT Concatenate IN tmpJournalEntryChangeOrders);
View 2 Replies
View Related
