I have an access db that is linked to a sharepoint list (the list originated in Access).I tried a simple select query with parameters to search for by id and another query of the same table to search by date.They both come back with the same error of "cannot reference a table with a multivalued field using an IN clause that refers to another database"Iv'e gone through the table and these fields that the query is using and none seem to have any multivalues.
Code: TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQAs SELECT month(qaDate) AS QAmonth, Count(T_qa.qaQAPK) AS QAs FROM Q_ALL_qa GROUP BY Month(qaDate) PIVOT month([qaDate]) IN (1,2,3,4,5,6,7,8,9,10,11,12);
This query is record source for a report, then this report show all calculations in a form.
I have 7 of each (query + report) all showing on the same form.
All those queries calculate data for all departments.
In the form, I have placed a combobox.
What I want is to create a vba code which will add clause WHERE to all queries at the same time and then run it.
Code: WHERE qaDeptFK=Forms!F_CompLvl.cboDeptStats
However, if nothing has been selected in the combo, I want the queries to calculate data as normal, for all departments.
Where do I place the vba statement? Is it under combobox AfterUpdate event?
I am planning to use this: (As I never done it before) [URL] .... Modifying SQL On-The-Fly section
I'm trying to get an SQL statement to take a value from a combo box in a WHERE LIKE clause.
For example:
INSERT INTO tblInspectionTempp (BuildingID, DoorNumber) SELECT tblDoorData.BuildingID, tblDoorData.DoorNumber FROM tblDoorData WHERE tblDoorData.BuildingID LIKE = '[Forms]![fmInspectionColumns]![cmboBuildingID].Value'"
The errors I'm receiving are either Missing Match or incorrect Syntax, depending on my trial and errors methods regarding the WHERE clause.
I have an update sql statement that isn't quite working properly.
My where clause has 3 criteria. *print = -1 *stDocCriteria (project_num and client_id)
Below is what the code looks like.
Code: updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print = -1 and " & stDocCriteria
Add watch: so you guys can see "stDocCriteria"
Code: updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print =-1 and ([project_num]= '140012' And [client_id] = 87)"
I want to only updates records that meet all three criteria. The above sql not only updates all records that satisfy stDocCriteria regardless if print is -1(true) or 0(false).
I am trying to dynamically change the IN sql from within VBA using parameters. for some reason i have no luck, no errors shows up, but it's actually not picking up the criteria.
Code: THE SQL IN STATEMENT
In (select RemID from [ReminderAssignees] Where RemDate between [Date1] and [Date2] And [sDismiss] )));
Code: THE VBA CODE
qdf.Parameters("date1") = Date1 qdf.Parameters("date2") = Date2 Select Case iDismissed Case 0, 1 qdf.Parameters("sDismiss") = "1=1" Case 2 qdf.Parameters("sDismiss") = "(not isdate(Dismiss) or Dismiss > #" & Now & "#)" Case 3 qdf.Parameters("sDismiss") = " isdate(Dismiss) and Dismiss < #" & Now & "#" End Select
I am having trouble opening Form 2 to the same record as the record in Form 1. Form 1 is a continuous form of questions. When certain response is given, I want to be able to add more information to the "additionalcomments" column for that record. I want to have the additional comments pop up in a new form.
Form 1 is based off of a query with no unique ID. I need the second form to open on Eval_Number and Question_Number. I have tried...
Code: If Me.Response = 0 Then DoCmd.OpenForm "frmadditionalcomments", acNormal, , "[Eval_Number] =" & Forms!ESVWL1Trainee!subfrmreponses.Eval_Number And [Question_Number] = " & Forms!ESVWL1Trainee!subfrmreponses.Question_Number " End If
and this tells me the object doesn't support the method. Is the SQL incorrect? Is it the way its setup?
I have this code below which pulls a report based on the current date, I wanted to be able to pull the same report by entering between 2 dates as is done in a query using parameters.
This probably sounds stupid but I need to ask because well, I'm stupid. I have created a separate table of partnumbers that DO NOT get included in a SELECT Query for another table (tblmain). How can I use this table in my WHERE clause? Below is my SQL statement and the table with the partnumbers is tblreportpartnumbers.
strSQL = "SELECT SUM([Yield]) AS [My Sum] FROM tblmain" strSQL = strSQL & " WHERE(([Area]='Cell 1 Supfina' AND " strSQL = strSQL & "([Date entered]=#" & dtmdate & "#" strSQL = strSQL & " AND([Part Number]=???????)))" strSQL = strSQL & " Group By [Date entered];"
I want to that the WHERE clause for a SQL statement that I am using options on a form to build. I intend to use the clause in opening a datasheet form.
This is the code I have for getting the substring
Code:
Dim intPos As Integer Dim tempString As String Dim BaseQueryFormStr As String 'BaseQueryFormStr is used to reopen the BaseMasterQueryFrm with the specified parameters tempString = "WHERE"
I am trying to calculate annual percentiles of a large set of data and I have only been successful at retrieving the percentile of the entire data set (and not by the grouping). See provided example database for code/query. Query1 is what I want to happen to make the Percentiles table.
Connecting Access FE to SQL SERVER BE Connection is fine. I can open and close it and other queries work fine. I have only one problem with the SQL in one query
Code: With rstRPT If FirstRecord = True Then .Open "SELECT Min([" & SourceTbl & "].[3Order ID]) AS MinOf3OrderID " & _
[Code].....
why it wants to see the variable as a column name?
I've been trying to work up a where clause that is generated by a button click event on a report. The workflow that i'm trying to obtain is as follows:
1) A report is run to determine the remaining work orders that need to be processed. 2) A button that is placed on that report is to be clicked, taking the user to the form associated with that work order, so it can be processed.
What i've been able to do so far is capture the unique ID for the work order and then print that in a message box. I can then open the form.
What i haven't been able to accomplish thus far is to open the form to the correct work order.
Things I've tried : I started trying to use the macro with the search for record option and using the where clause. Not successful. I am a little more comfortable in using vba so i switched to that pretty quickly.
Code: Private Sub btnJobEntry_Click() 'GOAL: open the work order form to the correct entry 'METHOD: store the uniqueID to a variable, then use that in the open command's where clause Dim strJobID As String 'store the unique ID in the variable
[Code] ....
I've put the strJobID variable in both the filter and where clause sections of the DoCmd but it just opens the form to the first entry. I'm fairly confident i'm not applying the filter/where clause correctly by using the incorrect syntax.
Now I require to join these both, the table and the output of the query on the condition where query.fileName like table.fileName.
There is no key in this field. Why I need this because the table has the sort order which the user can change when needed, if I put the sort order in the query then each time there is a change then the query needs to updated which the user can go wrong. Also the filename in the query has date associated which changes every day so I need to pick the unique part of the file name and associate it with the query to get the output from query and sort order from the table.
The first table is created just fine. And if I leave the constraint clause out, the second table is created also. If I try to run it as an SQL query right in Access, after the error is delivered, it highlights the word DELETE, and if I reverse the update and delete portions, it highlights the word UPDATE.
I have looked up the clause in Access help and even using their example, i get the same error.
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code: SELECT Unique_No, Table_Name, List_Order FROM My_Table WHERE Table_Name = 'Titles' AND List_Order IN (3,1,15,4,5,12,7,2) ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
I am trying create some code that checks if the contents of the status field in a table is "Authorised" where the ID = something specific for multiple records. If all records witht the specific ID are "Authorised" Then generate a new record in another table. Where to start, perhaps a Dlookup?
Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.
What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).
I have a database that has 2 tables. Table A and Table B. Table A is my primary table. On this table I have 2 fields. The first field is a LOOKUP Field that looks up information from Table B and displays my selection in the field on Table A. Then using DLOOKUP I automatically input the information in the Second Field on Table A based upon the selection from the First Field.
This is working mostly correctly. However, the problem is, when I click on the next record in the table, it automatically changes the Second Field on that record to the same value as the record before it and continues this trend each time I click on another record. This occurs without me making a selection in the first field. If I make a selection in the first field it does change the Second Field to the Correct Value, but then the next Record has the same issue.
How do I go about fixing this so it doesn't change the value with the change of the record. Only change if I change that particular field within that 1 record?is there a way to restrict the Value's in my lookup field to only include the Values from Table B that aren't already in Table A?
Hello. I'm making a database to store movie info for a DVD collection. I have a table with fields: title, year, runtime and such. I want an indefinite number of genre to be associated with a movie, so I created a "genre" table and a junction table between that and the "movie" table. Now, on form I want to be able to filter a list of movies based on a genre selection from a combo box. The only way I can see to do this is to make the rowsource of the listbox a query with "tblMovie.MovieID, tblMovie.Title, tblJunction.GenreID" connected through MovieID. This would work except that if a movie is associated with more than one genre on the junction table, the movie appears on the list more than once. I want a movie to appear only once. That's my problem. Thanks in advance for the help.
I inherited an old database. Not designed too well. Nearly 100 tables with different types of names. Some table names start with and "_", some with "tbl" and some just names.
I want to add a new field called "TimeStamp", date/time, and default value Now() to all the tables.
I have the following vba script and I can not get the yes/no field to work, also with adding field properties of required = yes and the text field to be 50, just examples are fine and I will update what I want.
Code:
Sub test() Set Db = Application.CurrentDb Set tdf = Db.TableDefs("1Testing") ' 1Testing is your table name ' First create a field with data type = Text Set fld1 = tdf.CreateField("FieldT1", dbText) ' Field1 is your Field Name and formatted as text
I want to save the timestamp created in a table when a user opens the database. My vba saves the computer name and user name and sets the currentuser = yes. The table is mysql odbc, and has the requisite timestamp and id fields. I have a form that I can open that shows who is online at that moment. When a user closes the database, the currentuser field is set =no. All of that works well. Now, I have added a "starttime" field in which I would like to store the timestamp that was set upon opening, since when a user logs off, the timestamp gets changed to the log off time.
Code:
Dim myQry As String Dim sUserName As String Dim sUserComputer As String Dim currentVersion As Date
[code]....
The UserTimestamp in red is the field value of the first record in the table, not the usertimestamp in the user's record. How do I store the log on timestamp in the "starttime" field?