Query Pulling From Form
Aug 10, 2007
I am having an issue pulling information from a form into a query. In my criteria column in the query i have:
[Forms]![frm_createcsv]![txtCriteria]
it pulls from a valid string from a textbox txtCriteria.
My vba code is:
Dim query as string
query = "pkg_not_registered_email"
filename = "C:querytester" & Format(Date, "YYYY_MMDD") & "_" & cboCountry.Value & "_" & ".csv"
DoCmd.TransferText acExportDelim, "", query, filename
DoCmd.Open query
____________________
This exact code worked previously with another function and a different query so I do not understand why it is not working now! Help! :(
View Replies
ADVERTISEMENT
Apr 25, 2014
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?
View 2 Replies
View Related
Feb 27, 2006
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.
http://www.access-programmers.co.uk/forums/showthread.php?t=100176
View 1 Replies
View Related
Jul 17, 2013
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?
View 14 Replies
View Related
Mar 11, 2007
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
%>
<strong><%=rs("subject")%></strong><br>
<strong>Q</strong> <%=replace(rs("question"),vbcrlf,"<BR>")%>
<br><br>
<strong>A</strong> <%=replace(rs("answer"),vbcrlf,"<BR>")%>
<br><br>
<%
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
Set objCon = Nothing
%>
------------------
The error message I receive is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
/faqs2.asp, line 13
If anyone could offer me any help, I would greatly appreciate it. Thank you.
View 4 Replies
View Related
Nov 22, 2006
I have 2 Tables and 1 form.
Table 1 has:
Product ID #, Product Name , Catalog Number
Table 2 has:
Date, Product Name, Catalog Number
with the Form, You select a Product Name (From table 1) and you can enter a date, which is entered into the Date column in Table 2. The form will also transfer the Product Name to Table 2.
Now, the problem is I can't seem to get the Catalog Number from the Form to enter into Table 2 automatically. It enters the Product Name just fine. I can, in Table 2, click the field and select the Catalog Number from a list, but that's not what I'm trying to accomplish. When a Date is entered into the Form, I'd like it to update Table 2 with the Date, Product Name AND the Catalog Number.. automatically.
is this possible? and if so, how?
I sorry I'm really new to Access, so my terminology is all out of wack. I hope the question is somewhat clear.
View 2 Replies
View Related
Apr 13, 2007
Hello Everyone,
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?
Thanks,
Pablo
View 13 Replies
View Related
Sep 9, 2004
Hi
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 ?
Thank you thank you
View 5 Replies
View Related
Dec 26, 2014
I have a table/form [tblStdAdv] [frmStdAdv] that contains all advisers a student had during their program. that form pulls from refAdv which has:
Type
1- initial
2- current
3- previous
and then an Adviser field, per record
I need to pull the adviser onto the student's main page [tblStdInfo][frmStdInfo] either "current" (if they have had changes) or "initial" (if they went through without an adviser change) .
I would like to pull the info into the main page so that I only have to change, or add to, the one form able and the main form stays up to date.
i tried using a query and was able to pull either "2" or "1" or "both" but could not make it look for "2" Current first and if not found then use "1" Initial.
View 2 Replies
View Related
Oct 6, 2014
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).
The SQL looks like thiss:
SELECT [SM_Antennas_DL-1_Q].[Site ID], [SM_Antennas_DL-1_Q].[Antenna Type], [SM_Antennas_DL-1_Q].[Electrical DT], [SM_Antennas_DL-2_Q].[Electrical DT], [SM_Antennas_DL-3_Q].[Electrical DT]
FROM ([SM_Antennas_DL-1_Q] INNER JOIN [SM_Antennas_DL-2_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-2_Q].[Site ID]) INNER JOIN [SM_Antennas_DL-3_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-3_Q].[Site ID];
So the data DL-1, DL-2, DL-3 is from the same database.
View 8 Replies
View Related
Nov 6, 2007
Is there a way to specify in the query sql, if column XX exists in a table, pull it, if doesn't exist, create a blank column "XX" ?
View 6 Replies
View Related
Nov 11, 2012
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"?
View 11 Replies
View Related
May 17, 2006
Hello,
New to access :eek:
Trying to create a simple data entry form to get information and populate fields from another table. i.e. type in info in one field and the other fields are displayed in the form with information from another table.
Any help apprecated :)
View 3 Replies
View Related
Dec 13, 2013
The new database that I am building will be used as a point system based on attendance. This means that I will have a main form that a few users will utilize to look up an employee's score. So far, I have a combobox (cmbEmployeeDropDown) that lists the query for each employee using the following:
Row Source:
Code:
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Flags]=0 ORDER BY [Name];
Row Source Type: Table/Query
From this combobox selection I would like the totals from the Total row in the query to appear in textboxes on the form (txtTotalPoints, for example).
Is this possible to do or is there a better way to do this?
View 14 Replies
View Related
Sep 21, 2014
I have a contributor tracking table that is linked to a form of the same name. I created a make table from a query that calculates the total to date for each contributor (based on their contributor ID in the tracking table). I want to place this sum to date, in read only mode, on each contribution record for each contributor in the tracking table and on each master record in another table with the contact information for each contributor.
The contact table is in the one and the contributor tracking table is the many. If this isn't clear, I can upload the database. I essentially want to link a field from one table to a form with a different table source. The sum to date should only show for the record with a matching contributor ID.
View 9 Replies
View Related
Dec 26, 2012
I need to pull data from multiple tables in order to show a "financial summary"..Currently I have: Company; BalanceSheet; Debt; Liabilities; Income..All tables have a lot of information (which is why I built them in multiple tables).
I need to build a form where I can use a combo box to select a company from a list.Once selected - I need to the form to pull selected information from each of the above tables. (As well as perform some math functions).I've been struggling with the relationships (They don't seem to make a difference) and I believe I am above and beyond what the wizards will accomplish.
I have read thread after thread but cannot seem to find a specific answer on how to accomplish this.To make matters more complex - Once finished I want to be able to select multiple companies and create a report from the fields mentioned above (IE: pick company A, B, and C and have all of there "current Assets" add up on one report)
This task was originally achieved using an Excel spreadsheet but it has become to confusing for users and difficult to save information for future use.I believe all of my fields are constructed correctly. how to compile the data from multiple tables into one form/report.
View 5 Replies
View Related
May 11, 2006
Hi
I have a table with
"starttime", "endtime", "agent", "phonenumb" and a datediff calculation that calculates the diff between "starttime" and "endtime"
the "starttime" and "endtime" columns are in "10/05/2006 12:57:55" format, i.e. the column holds both the date and time
The data is stored in an sql DB and is accessed by an odbc link using Access 2003
I want to be able to run a query within access that lets me chose "agent" and a specific date or a range of dates.
Any help would be greatly appreciated
Thanks
Ben
View 1 Replies
View Related
Feb 14, 2007
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.
Thanks in advanced.
View 9 Replies
View Related
Jun 7, 2013
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).
View 2 Replies
View Related
Apr 13, 2013
I am having a problem pulling some data from a query to populate text boxes in a form
Text27 = DLookup("'SumOfSumOfDocCount'", "SumTotalPerf", "DateReceived=" & Forms.Tracker.Text23.Value & "AND 'BookedInID'=" & Forms.Tracker.BookedInID.Value)
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.
View 8 Replies
View Related
Jun 19, 2015
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?
View 4 Replies
View Related
Mar 6, 2014
So I have two tables:
users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...
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...
View 7 Replies
View Related
Feb 19, 2014
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.
View 3 Replies
View Related
Jun 28, 2013
I created two functions to use when I'm pulling data using a query (QBE). I would like to combine the two into a 3rd Function that works like this:
BB_Only = DishNetActivityCode = 1 and VideoActivityCode = 0
Hybrid = DishNetActivityCode = 1 and VideoActivityCode = 1
V_Only = DishNetActivityCode = 0 and VideoActivityCode = 0
Function DishNetActivityCode(service_code_string As String) As Integer
Dim CodeArray, i As Integer
[Code] .....
View 14 Replies
View Related
Nov 7, 2005
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.
View 1 Replies
View Related
Dec 28, 2006
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
View 2 Replies
View Related
