Queries :: Saving A Working Query
May 21, 2013
I have an simple Access 2001 database with one table in which I want to search multiple fields for entries which match upto four keywords using subqueries.I have entered the following sql code:
SELECT Components.[Component-type], Components.Value
FROM (SELECT Components.[Component-type], Components.Value
FROM (SELECT Components.[Component-type], Components.Value
FROM (SELECT Components.[Component-type], Components.Value
FROM Components
[code]....
and then refuses to save it giving the error: "Invalid bracketing of name 'SELECT Components.[Component-type"If I remove the changes it will save ok. This is only a test query as I will want to add many more fields and it is my first use of sql code.
View Replies
ADVERTISEMENT
Jun 5, 2013
I have a form with a child that holds a query and for 99.9% of the time remains locked as it's for viewing data only. However, if the user wishes to make a change then they need to press an unlock button first then make the changes.
The problem I have is when closing the form; if this child's query has been unlocked I'm getting a message asking do I wish to save the changes to the layout of the child's query. The answer will always be "No".
I've added the following line of code to all sorts of procedures with no success.
DoCmd.Close acQuery, "LinkTableManager_Frmqry", acSaveNo
how to stop the message appearing when the form is closed.
View 5 Replies
View Related
Jun 26, 2014
I have this update query that is triggered by an after update event on a main form. The record being updated are in a continuous subform. It works well except from the last added/modified record. If I save and close the form and then open it again it works for all records but if modify or add a record, the update query will not work for that last modified/added record.
I have tried several things such as save record, use dirty = false for the on exit event of the subform control but nothing works. Here is the procedure:
Code:
Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
Dim db As Database
Dim strSql As String
Dim lngID As Long
Dim dblRate As Double
Set db = CurrentDb
[Code] ....
View 14 Replies
View Related
Apr 17, 2014
I have a table like this
Cultureworkload
ReasonfortestingIRLNumberDiagnostic sputum specimens processedFollow up specimens processedReception
Follow up7/20130102-Jan-13
Diagnosis8/20131002-Jan-13
Follow up9/20131002-Jan-13
Diagnosis10/20131002-feb-13
Follow up11/20131003-feb-13
Diagnosis14/20131003-Mar-13
Diagnosis15/20131003-Apr-13
I built a query with the wizard to group by Reason for testing
The SQl was
TRANSFORM Count(Cultureworkload.IRLNumber) AS CountOfIRLNumber
SELECT Cultureworkload.Reasonfortesting, Count(Cultureworkload.IRLNumber) AS [Total Of IRLNumber]
FROM Cultureworkload
GROUP BY Cultureworkload.Reasonfortesting
PIVOT Format([Reception],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");
Although I filtered reception field by month to 1st quarter it shows empty months like Apr,May Jun etc.
View 3 Replies
View Related
Sep 10, 2013
I have a DCount Ranking query that worked fine last week and with this week's data came up with a glitch. Here is my DCount statement in the query:
Rank: CInt(DCount("*","qry_YTD_Territory_Totals","[Total]>=" & [Total]))
Here is my result which you can clearly see is not a tie.
Territory Total Rank
A 540.86 1
B 398.85 1
C 341.75 3
[code]...
View 1 Replies
View Related
Sep 9, 2013
I am trying to run a change an existing query in real time to allow date filtering for 4 different categories. I can get two of them to work. Calibration Date and Icepoint Date. But for The two calculated fields Calibration due date and Icepoint date I cannot get it to filter properly e.g. for 2013 dates it also includes 2014 dates and just does not work properly. I am thinking its due to it being a calculated field but don't have a clue how to fix it. see pasted code for calibration due date filter where Todate and Fromdate are the 2 dates used. I also have the on current code and the exit code to reset the query to its original status.
Public Sub SetDate1()
'Apply date filter and rebuild query in real time
On Error GoTo Err_SetDate1
If IsNull(Me!ToDate) And IsNull(Me!FromDate) Then
MsgBox ("Please Enter Date First"), vbExclamation
GoTo Exit_SetDate1
[code]....
View 3 Replies
View Related
Jun 27, 2014
Why update query isn't working when trying to update an multivalued field. In this table, there are 647 records and only 9 of which already have a value set in the "AssociatedProject" field. More than one AssociatedProject is associated with a vendor, so this field allows for multiple entries. I've looked at all the information on update queries and updating a multivalued field, but it will only update the existing 9 records. It does not recognize the remaining ones (they have no values yet).
Here's the SQL:
UPDATE Tbl_Vendor SET Tbl_Vendor.AssociatedProject.[Value] = 3
WHERE (((Tbl_Vendor.AssociatedProject.Value) Is Null));
View 9 Replies
View Related
Feb 7, 2015
I have a question about errors on null value.I have made a small database for tryout, it has to be implemented in another one.And the small database is working.I have one table where there is one field called BatchInput.I scan a barcode into it and let two query's breaking it apart. I scan this batch into the table field
BatchInput: 20 MAY 2004H149-082-79 A4147011A05
Then I have my first query (Qrybreak1) extracting the date and deleting H14
Date: Left([BatchInput];11)
PartCertNr: Right([BatchInput];Len([BatchInput])-14)
The result is this:
Field date: 20 MAY 2004
Field PartCertNr: 9-082-79 A4147011A05
The second query (Qrybreak2) I look for the first space:
space: InStr([PartCertNr];" ")
Then with the result I cut it into two pieces
PartNumber: Trim(Left([PartCertNr];[space]-1))
CertNumber: Trim(Right([PartCertNr];[space]+2))
[code]...
And query (QryResult) even wont start, giving a popup with Invalid procedure call..How could I handle Null on the part where there is no space after the partnumber (missing Certnumber)?
View 7 Replies
View Related
Apr 23, 2014
I got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
'Gross monthly Salary: IIf([Salaries Master]![Pay Period] = "weekly";[Salaries Master]![Daily Wage]*5*52/12,IIf([Salaries Master]![Pay Period] = "monthly";[Salaries Master]![Daily Wage]*20))'
View 10 Replies
View Related
May 22, 2013
Access 2010. I've had to learn it at work because our DBA was let go and I was the only one willing to give it a go. Read a book or two and picked up some stuff on the internet.
Here's my problem:
I have a simple table -Employee with 4 fields. FirstName, LastName, Office and JobTitle. I have form called Form1 that has 3 control fields cboJobTitle (a combo box that is populated by a query that finds all the unique values of that field in the Employee table), cboOffice (same as above) and txtName (a text box to allow user input) that are used as the criteria for a multi-field query triggered by a button at the bottom of the form. The idea being that you could do a search using this form to find all the employees in one office or all the accountants in one office, or any other combination.
The main search query has the following criteria for each field -
Like [forms]![Form1]![cboOffice] & "*"
Like [forms]![Form1]![cboJobTitle] & "*"
Like [forms]![Form1]![txtName] & "*"
It works great...until I enter a record where one of those fields may be null, such as if I leave the JobTitle blank.
If I have two employees in an office in one city and then do a search for all the employees in that office, it only returns one record and ignores the one that has the null value in the JobTitle field.
View 14 Replies
View Related
Nov 12, 2014
I have a running total query that seems to run but when I try to total the query results then Access will be "Not Responding". I tried to change it to a Make Table query because I need to use the running total result in another query. So I created a table but when I try to run the make table query it just says "Run Query" at the bottom. Here is the query:
SELECT [OTMissing].[Employee], [OTMissing].[AsOf], [OTMissing].[HRsEarn], (SELECT Sum(OT1.[HRsEarn]) FROM [OTMissing] As OT1
WHERE OT1.[Employee]=[OTMissing].[Employee] AND OT1.[AsOf] <=[OTMissing].[AsOf]) AS RunningTotal, [OTMissing].[RemainPP] INTO OTGenerated
FROM [OTMissing]
ORDER BY [OTMissing].[Employee], [OTMissing].AsOf;
My OTMissing query is 47061 rows. Does that have something to do with it? The only other thing it might be is that most of the records have 0 although I'm not sure why it would be a problem I thought I would at least mention it.
View 2 Replies
View Related
Dec 23, 2013
Why is this update query not working, I'm trying to update a date field stored in a table.
The new date is passed from a txtbox on a form to the update query!!!
SQL code
UPDATE TblDietPlantemp SET TblDietPlantemp.MealDate = [Forms]![FrmSwitchBoard]![txtCusDate];
View 3 Replies
View Related
Mar 25, 2013
Attached I have a database that I've been working on which has a form called "frmCriteriaSearch". It is based off of the qryCriteriaListBoxUpdate query. I am trying to get the listbox in the second tab of the results section to work. It queries fine for the checkboxes, but I cannot get the comboboxes to affect the query (unless a checkbox has already been selected)
View 6 Replies
View Related
May 22, 2006
When I try and save an older query my entire access shuts down without warning. I tried "Save" and "Save As". I even tried saving to a different name. Any Ideas?
View 4 Replies
View Related
Jun 12, 2005
Hi all,
I've got this form working ok but need to add some extra functionality but haven't a clue how to do it!
The uploaded database has two main forms. The first is used to enter customer data, the second runs from a query and shows all those records that have been completed in the first form (frmCustInfo - AgentLog = Yes) but not completed in the second form (frmControlsSource - AnalystLog = No)
Then more data is input into one of the subforms (frmControlChecks). Now in order for me to see the results of the queries connected to two other subforms I have to move off the record and back to it (for the record to be saved and then the queries run)
After moving back to the record, the query results on the right (Information Only) are manually added to the last subform 'Input Two', then the user would move on to the next record and do the same.
-------------------------------------------------------------------------------------------------------------
What I need it to do then is this: When the user tabs off the last field in the subform (frmControlChecks), the queries to the right (two subforms) would run, showing the results and also populating the last subform (frmAnalystInput)
The user would then click AnalystLog and move to the next record.
I hope this is possible without redoing the forms/queries because it's taken ages to get this far :o
I've also added info in this sample database and some working data if you want to take a look...thanks for any help
View 2 Replies
View Related
Sep 6, 2005
Morning All,
Just wondering.
When a form needs a calculated field. Say something simple, like
TotalCost = Quan*Cost
Is it better to create a query?
Use a table and create the calculated field in the query builder?
Or use a table and create a new unbound field on the form with the formula in it?
I never really know which one to do.
Lately I’ve been using saved queries for almost everything and now I have several dozen in this data base and its only half way done. Soon I’ll have to create a data base just to keep track of the queries in this one.
View 7 Replies
View Related
Sep 24, 2013
I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!
Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.
Code:
SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT
FROM HeciFail1
ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;
View 1 Replies
View Related
Nov 4, 2013
MS Office 2003..I have 7 queries that I use to export data to one excel workbook. Inside the workbook are 7 worksheets, one for each query. I have it working but I dont know how to save the exports steps like I did in AC2010.
I export the data by right clicking on each query and select export , then I browse to the workbook and because each query is named differently , it names the worksheet. I have to run these 7 queries each month , so I would like to save the steps and then build a macro that would run all seven steps at the push of a button.
I have saved the steps on a PC running MS office 2010 and the macro works great. The problem is this resides on a PC that cant be upgraded and I need to make it work on Access 2003 .
View 1 Replies
View Related
Feb 14, 2008
Any reason why some of the queries do not display the info correctly? as in the many to 1 or 1 to many relationships etc.. its just blank lines. How would I fix it (if nessicary)
http://img152.imageshack.us/img152/2986/queryqr8.th.jpg (http://img152.imageshack.us/my.php?image=queryqr8.jpg)
Thanks.
View 2 Replies
View Related
Aug 13, 2005
I have two tables linked by a one-to-many - Demographic and Episodes. I have been running numerous queries with no problem until now - and I can't find any reason that these are not working.
I need to print up a bunch of different reports based on the criteria entered into the fields (yes/true; date ranges; is null or is not null, etc.) For some reason, records that are not supposed to come up (out of date range, or all records whether yes or no) are coming up.
Could it be some simple thing that I've obviously missed? The only difference with these new queries is that significantly more fields have criteria entered, instead of just one or two fields. Do the fields have to be put in some particular order from the design view? When it didn't work the first time, for example, a field that should be "Is Not Null", I ended up entering the "Is Not Null" on the second criteria line also - hoping that would make a difference....or in another field, "Yes" on the first line and 'True" on the second. In some queries, it made it better - but it still pulled up a few odd records, due to the date range being ignored, or another field criteria not working, etc.
I am grateful for any help I can get on this - I'm going insane trying to figure it out!!
Thanks,
Janis :-)
View 5 Replies
View Related
Sep 15, 2005
I have set up an Attendance database at work, where a record is created for each member of staff when they are absent. The tables are set up as follows:
[tblStaff]: StaffID, FirstName, Surname, Team, JobTitle
[tblAbsence]: StaffID, StartDate, EndDate, Issue
What I want to be able to do is set up a query to search for a reason for absence on a certain date (btw my date format is set at dd/mm/yyyy). The problem I have is if the user needs to check a date in between the start and end date i.e. Start Date is 11/11/2005 end date is 11/12/2005 and the user checks on 01/12/2005 then this record will be found.
I know there is a simple way to do this but I can't seem to figure it out. Any help would be greatly appreciated!
View 2 Replies
View Related
Jul 3, 2014
I am trying to create a query in VBA to extract data for a report.
I am using the "Like" operator for one of the variable fields.
It works correctly if you use the full value, however when using a wildcard it returns no records. Example below.
MOP - Works returning several records.
M* - returns no records.
I am getting very frustrated - I am sure its something simple.
I hope I have uploaded a screen dump of the SQL statement that is being produced.
View 14 Replies
View Related
Jun 14, 2005
Oper sys is XP. Office 2000 version of Access. Extensive experience with Excel but very little with Access.
I'm creating an astronomical database that includes a function that calculates the distance to galaxies in millions of light years. These databases are massive. Total database has about 1.6 million galaxies (100 Meg). My query concerns about 500,000 of these.
Have the original database and done the query on the objects of interest. It includes the cacluations. All perfect.
Now, it is possible to save just the query so that I can copy it onto a CD and take to the observatory for field reference. Query has about 500,000 lines, one for each galaxy, but is only six columns wide. But at 500k lines it cannot be exported into Excel. Or at least I don't know how to do that.
Thanks for your assistance
View 2 Replies
View Related
Mar 4, 2006
Hi...
I am relatively new to access.
I have spent a few days coding a database search page consisting of various comboboxes and textboxes. the user can select or enter data in these and when he/she clicks the search button an SQL select statement is generated on the fly and updates an existing query and this is used to query the database. I want to be able to give the user the option to save the query he/she generates and reuse it. How can I prompt him for a name and turn the SQL string strSQL that he has generated into a query and save it for later use?
Any help greatly appreciated..thanks
View 4 Replies
View Related
Apr 28, 2006
Hi there
I've got an access file which was probably created with access 97 or 2000, and inside a query that I know for a fact works with SQL Server 7
I was upgrading to SQL Server 8 and I needed to modify that query because it didn't work with the new version.
Now, when I tried to save the query, it gave me a certain error about invalid JOIN statements.
The query I wrote runs perfectly when I try to execute it manually on the SQL server, but MS Access just won't allow me to save it!
Any ideas how I can make it save my new query without it trying to verify my SQL statement?
Thanks
View 3 Replies
View Related
Apr 28, 2006
Hi there
I've got an access file which was probably created with access 97 or 2000, and inside a query that I know for a fact works with SQL Server 7
I was upgrading to SQL Server 8 and I needed to modify that query because it didn't work with the new version.
Now, when I tried to save the query, it gave me a certain error about invalid JOIN statements.
The query I wrote runs perfectly when I try to execute it manually on the SQL server, but MS Access just won't allow me to save it!
Any ideas how I can make it save my new query without it trying to verify my SQL statement?
Thanks
View 3 Replies
View Related
