Hi, I was wondering if someone could check this code over. I am trying to pull a query (titled: Codes_+_Conduct:_General)which was already created in Access. Here's the code: (the red is where the error message I get is)
-----------------------
<%
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open connectionstring
Set objRS = Server.CreateObject("ADODB.Recordset")
strQuery = "SELECT * from Codes_+_Conduct:_General order by subject asc"
objRS.Open strQuery, objCon
IF rs.EOF Then
Response.Write("<tr><td colspan = 3>No Tips</td></tr>")
Else
Do While not rs.EOF
x = x + 1
%>
I've been trying to figure out why my query only pulls record Object ID 10011399 and not the other one, but I can't figure it out. Can anyone please help me see what's wrong?
Apologies if this is really basic - I am trying to write a query to show all records from table A linked to table B so that if a field is blank in table B, it will show. At the moment, every time I run the query, it only shows me records which have entries in both tables. (They are linked by ID no). ie there are 77 records in table A. Only 23 in table B but I want a query showing which ones in table A do not have something in field "x" on table B. Does that make any sense ? As ever, I'd be ever so grateful for help.
While I'm at it, does anyone know anything about getting a database to produce reminders ie based on date entered, highlight when 3 month review is due ?
I am currently trying to pull a query off from a table that needs to have 3 columns side by side, linking to the same ID number. I seem to have managed to do this by making three seperate queries for each column I need, however, when I put it into one query, it is only pulling through the data form the second column (a total of 273 rows), when it should be pulling through the data from the first column (800+ rows).
I have several (about 10-15) queries that I have designed to run monthly reports. Various pieces of the criteria for each query need to change regularly. For example, in design view, I currently have a 'Where' field for each of our eight products. Where 1= include the product in the query analysis. Where 0= do not include the product in the query analysis. We would like to have a form that has entry boxes for each product where the person doing the analysis could type '1' or '0' and then the query could update the criteria entered before running.
We also need dates to update via the form. I currently have [Current Period End Date] and [Prior Period End Date] pop up boxes on each query that allow the person running them to enter their desired dates. But again, my boss wants to limit their involvement with the actual queries. Plus, you end up entering the dates for all 10-15 queries, which is a hassle. We would like to have entry boxes for the desired dates in the form and have the queries pull date criteria from the form.
If we can pull query criteria directly from a form, is it possible to create a table from a form and then pull the query criteria from the table?
In the Owner Name field, I am looking for a way to pull out all estates. They are abbreviated with "EST" or "Estate". The problem is throughout the database, if you do a simple filter for "contains est" it will include names such as "Forester" or "LHB Investments".
How do I query, or filter this database such that it only returns the estates abbreviated with "EST" or "Estate"?
Hi guys. For my coursework I was told I need complex queries to gian high grades.
This is my problem: I have many many tables all identicle. They have the same field names just different information on them. I wanna be able ot query ALL those tables to bring 1 result which I will then make a report out of.
I have tried everything, I fidled with the relationship but can't understand it.
Oh, and the tables have been linked form excel. I know i can't edit it then, but i dont need to.
I would really really apretiate all responses as this needs to be done asap.
So, I've been searching through this forum and can't seem to find the answer to this one. I would like to capture a value from a main form and have it used as a value in an append query, in order to populate a subform based on the main form, like so:
INSERT INTO tblTakenSurveys ( VisitID, SurveyQuestionID, ResponseCodeID) SELECT Forms![frmMyFormName]!VisitID, tblSurveyQuestions.SurveyQuestionID, 66 FROM SurveyQuestions WHERE SurveyID = 3;
Might help to explain some of the terms in this statement: tblTakenSurveys is where I need the new data to be entered via the subform. Forms![frmMyFormName]!VisitID is a textbox control bound to a PK in another table that has a one-to-many relationship with tblTakenSurveys. tblTakenSurveys.ResponseCodeID is a foreign key that represents respondents actual answers to questions. 66 is a value for a ResponseCodeID that stands for a dummy value meaning "data not yet entered"
As per advice I received from others on this forum, I have set a query like the above to run from a command button to populate the subform (in theory). But I'm sure I've done something wrong within the query because it will not return a value from the form "VisitID" control and therefore will not append the rows. Without the appended rows, my subform will not populate. And this has me running in circles...
I pasted the link to another thread below, where I originally received a lot of input as far as the table structure. I did not start this one, but my posts are the most recent (as of now anyway). Pat Hartman had given me a lot of the guidance here.
I've got two nested queries. One finds the oldest inspections from an import table, the second compares that query to the main table again and pulls all 'expired' inspections for each Service Order in the first.
These queries are trimmed down for the essential elements of my question. I can post the full SQL if necessary.
queryDoTheseFirst:
Code: SELECT TOP 18 ImportTemp.[SO ID], ImportTemp.[Inspection Activity], [Activity Created]+[AddDays] AS [Due Date] FROM [Priority List] INNER JOIN (ImportTemp INNER JOIN queryNeededFirst ON (ImportTemp.[SO ID] = queryNeededFirst.[SO ID]) ON [Priority List].Activity = ImportTemp.[Inspection Activity]
[Code] ....
The refinement I would like to make is, rather than having to pull TOP 18 activities in the final query, just pull TOP 12 [SO ID]s and however many activities come along with them (usually 1 or 2, averages out to about 1.5 so 18 is my compromise). In theory an inspector could have two inspections due on every single property, and would only get 9 unique addresses/[SO ID]s. But I can't figure out how to do that when [SO ID] is no longer unique in the second query.
I suppose I could 'number' the rows in the subquery and add a <=12 criteria on that calculated field, but I'm leery of the processing required (that table contains ~14,000 rows, and most methods of numbering seem to want to use DCount).
I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.
I have a form set up (in a list box) to show our salespeople what parts they have yet to get out of inventory but have a sales order for. The list box shows a list of all the salespeoples names. My manager wants me to show ONLY the salespeople that have populated fields in this list box. The list box currently shows all the salespeople, but I want to see only the ones that have inventory that has yet to shipped. How do I go about this?
user_change primary key = user_change_id (AutoNumber) user_id (Number) which relates to the user_id from "users" table change_type (Text) action_date (Date/Time) ...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
I'm trying to update a record in a table, from a query that is run as part of an event from a command button on a form.
I have a table called 'Assets', a table called 'Disposals', and a form called 'Disposal Entry'. I would like the user to select an Asset ID from a combobox on the form, then when the button is clicked it adds a record to the 'Disposals' table, and updates the Status for that specific Asset in the 'Assets' table to "Disposed".
It adds to the Disposal table fine, but I can't get it to update the Asset table.
My query looks like: Field: Status Asset ID Table: Assets Assets Update To: "Disposed" Criteria: [Forms]![Disposals Entry]![Asset ID]
I've checked the spelling and everything looks ok.
The [Asset ID] control on the form is bound to the Assets table. If I edit the control and clear out what is in Control Source, then it updates the table and works fine.
However, I want to keep it bound as I have a subform on my home page showing the latest disposals.
How I can get the query to use the Asset ID on the form as the Criteria?
Unable to use Between query without pulling dates out of range.I
f I set the Criteria to Between [Enter the start date:] And [Enter the end date:] and input the dates 1/2/2014 & 1/15/2014 i'm pulling data for those dates but for year 2011, 2012, 2013 and 2014 as well.
if I enter in Between #1/2/2014# And #1/15/2014# it gives me just the dates I requested.I need to be able to have a user use the macro and just enter in the dates they need data for.
Also, I am using the criteria on a date ime field that I have set up an expression on - CreateDate: DateValue([TABLE]![date])the date in the table is stored with time and I just need to show the date.
I have a member roster. I have members who have attended. How do I subtracta list of those who have attended from the roster to find those who havent attended. The only way I know those who have attended is by their sign in date.
I have created several Queries and I would like to have a column that numbered the results. I have looked all over saw a couple of samples but havent had any luck. Anyone have any suggestions. Thanks for your help and have a good New Year.
Just to clarify basically this is what I need. Loan Number is the Primary key.
# Column Borrower Loan Number 1 smith 123456 2 jones 023567 3 jang 102569 JW
How could I pull in someone when they do not have data?
There is a name table and data table. My goal is that when I run a parameter query if someone does not have data for that time period to have that name pulled in so that eventually a report could be generated stating there is no data for this person for the time period.
I'm trying to figure out how I can pull the most recent entry in a table for each of the properties in our database. Let me give you some background. This access database is used to format reports from an SQL database - all of the tables are linked and all of my other reports work great except for the one I'm trying to create from a table that gets updated every day by way of a stored procedure that runs every night on the SQL server. In my mind this shouldn't make a difference and I should be able to select whatever data I want from this table/query based upon whatevery criteria I want. The field in the table I'm trying to use is a "date stamp" of when it was updated via the stored procedure. I want the last entry made for each property so I selected "last" as the criteria which should get me approx. 20 lines - one for each property. But when I use this I still get back multiple entries for each property. This seems like it would be easy to do but everything I try doesn't seem to work.
That's why I've turned to the experts here for help.
I'm certainly not new to Access, but this is the first time encountering a problem like this. I have a table with 3 Field Names, [Return], [Header] and [Date]. An example table looks like so..
I need to take to have group the Header's together, for instance 10, and have it return the [Date] for whichever line has the lowest [Return] value. I just can't figure out how to do it. Any help or insight would be GREATLY appreciated. I'm 90% done with the project, and this last part will seal the deal.
I have a field that is updated with =Now() now when I try to run a regular query with the criteria being =Date()
I get nothing And the "=" is always disapearing out of the query. I dont know what is going on. I got the field set up in the table section as a date field with short format.
Can somone please tell me what I am doin wrong when I have followed instructions up to this point....
I trying to build a usage tracker. Users will take a reading once a week and enter data. I want to open the form and have 2 fields filled in based off the last reading entered into the table. I have an ID field (autonumber) and I tried using that field -1 to populate my other fields but it does not work. any suggestions
Ok, so I am creating an Access Database to track the progress of our collections staff. I need to pull data from a remote SQL database and filter it so that the appropriate records are appended to the appropriate tables in teh Access DB. Any ideas?
