I have a query that updates a field on a table with the value in another field.
When I run the query from the Access control panel, the query does exactly what it should do, 100% perfect. When I call the query using DoCmd.OpenQuery in VBA in an OnClick function on a form, it does not work properly, only appending certain amounts of information, leaving some fields blank.
Can anyone shed some light on this.
the SQL for the query is:
UPDATE tblHolding SET tblHolding.CostGRV = [tblHolding]![OrderCost]
WHERE ((([tblHolding].[Item Code])=[tblHolding]![Item Code]));
It populates another field in the same record in the same table as itself.
tblHolding is populated by a Subform on the Form that has the button that calls this query when clicked.
This query is getting complicated for me... I need help please! :( I need it to give me the data for people between FirstMonth, Year and SecondMonth, Year... Right now it's giving me only the FirstMonth, Year and SecondMonth, Year... I think I need a between there somewhere but not sure where to put it...?? Hope this makes sense. I'm doing the query in Access 2002
Thanks Sarah
SELECT [FirstName] & ", " & [LastName] AS FullName, TriOct10.FirstName, TriOct10.LastName, TriOct10.Address, TriOct10.City, TriOct10.Prov, TriOct10.PostalCode, TriOct10.VolScreenCode, DatePart("m",[PRCDate]) AS Month2, DatePart("yyyy",[PRCDate])+3 AS PRCDueY2, TriOct10.PRCDate, TriOct10.MemberType, TriOct10.MemberStatus, TriOct10.ExpandName, TriOct10.RegOrgName, TriOct10.RegisteredRole FROM TriOct10 WHERE (((DatePart("m",[PRCDate]))=[Enter 1st Month]) AND ((DatePart("yyyy",[PRCDate])+3)=[Enter First year])) OR (((DatePart("m",[PRCDate]))=[Enter Last Month]) AND ((DatePart("yyyy",[PRCDate])+3)=[Enter Last Year]) AND ((TriOct10.MemberType)="volunteer") AND ((TriOct10.MemberStatus)="Active" Or (TriOct10.MemberStatus)="probationary") AND ((TriOct10.ExpandName) Like "*" & [What Area?] & "*")) ORDER BY DatePart("m",[PRCDate]), DatePart("yyyy",[PRCDate])+3;
I have a sub query that was working until a couple of days ago when it decided to stop.
I have a Table called TblFieldValues which whenever a new value is entered into my relational Db. Each new Value is given a ValueID and is Dated (full date/time stamp)
My Sub Query ensures that for each FieldID (ie if more than 1) it selects/shows the top most Date....But it is not grabbing all the fields for some reason???
Here is the SQL limiting criteria to a bare minimum (QuoteID) SELECT Main.FieldID, Main.QuoteID, Main.QuoteTypeID, Main.SubSection, Main.FieldValue, Main.NumberFieldValue, Main.CalcFieldValue, Main.Date, Main.ValueID FROM TblFieldValue AS Main WHERE (((Main.QuoteID)=[Forms]![FrmQuote]![QuoteID]) AND ((Main.ValueID) In (SELECT TOP 1 Sub.ValueID FROM TblFieldValue AS Sub WHERE Sub.FieldID=Main.FieldID ORDER BY Sub.Date DESC)));
I had a thought it might be the way the info is put into TblFieldValues, as it is often put in via Code, in fact only when it is put via code is it not showing up as a rule. So I had a look at the code that inserts it.
I had Now() in replace of strDate, but tried changing to strDate and diming strDate as Date and then setting strDate = Now() but doesn't really change it in the table.
I am certain it is in this somehow? Any ideas???? Your help will be greatly appreciated...
Would appreciate help on this. I have a query which combines the results of 3 queries. Once the underlying queries each has a result I get a result in my query, but if 1 of the underlying queries has no result I get nothing. (I hope this makes sense).
How can I set my query to show results even if the underlying query doesn't.
I have a query that selects from a table base on 3 entries (Name, BeginDate and EndDate) and should show me 12 other columns and their entries... I have 9 entries for a particular Name, but when i run the query i get 15 results. Some are duplicated but others are not and I don't know where to begin narrrowing it down. Any ideas where to start?
I 've created a query that copies the records of a table into the same table, creating new records. (I don't know how this is called in english, sorry). For example if have a table with 2 records when the query is run I have 4 records with duplicates. The fields of each record I want to remain the same except for one, called code. For example: this is the table before the query is run:
surname name code tracy john 1 spencer bud 1
and this is the table after the query is run:
surname name code tracy john 1 spencer bud 1 tracy john 2 spencer bud 2
the code is also in an another table and is included in a textbox in my main form. Everything works fine when I run the query but I must type a parameter (code) to create the duplicated table. How can I pass the parameter to the query "automatically", using the textbox value? Thanx in advance
I am using Access Application with Orcale Db as backend and this application is already in production. The problem is.. USers Complained that the system is slow, this is because there is refresh (requery) for each Insert (When they click submit, that particular record is inserted and then a refresh has to be done to bring only unused member IDs on the User screen), i had used a Sql query using NOT IN , then i realized and now changed it to NOT EXISTS, but with this new query , if i test recordset. EOF , even though the query returns more than 13000 ..records, EOF sets to TRUE and the other part (not supposed to) gets executed. I really cant figure out why, i took the same query put it in query builder in Access and ran it , it gave 13000 records..but in VBA , this recordset is not giving expected results and so..i am totally confused..The part of the Code is shown below.
Code:newquery = " SELECT A.MEMBER_ID, A.MEMBER_NAME, A.ADDRESS_LINE1.................. FROM TABLE A WHERE NOT Exists ( SELECT '' FROM TABLE B where A.MEMBER_ID =B.MEMBER_ID)AND A.MATCH_LEVEL <> 0 ORDER BY A.MATCH_LEVEL DESC"rst1.Open newquery, Cnt, adOpenDynamic, adLockOptimisticIf rst1.EOF = False ThenForm_PHS_ASSIGN.RequeryForm_PHS_ASSIGN_SUB.RequeryElse MsgBox " No records to Process!" End If
EVEn though it should requery , it gives a message No records to process which is wrong. It works with NOT IN.... BUT VERY SLOW, NOT EXISTS IS FAST BUT DOES NOT GIVE ME WHAT I WANT..
SELECT Count([Unsafe Act ].[Tag]) AS [CountOfTag], [Unsafe Act ].[Audit], [qryTag].[Tag] FROM [Unsafe Act ] LEFT JOIN [qryTag] ON [Unsafe Act ].[Tag] = [qryTag].ID WHERE ((([Unsafe Act ].Date) Between [Forms]![frmSafetyReportOut]![startDate] And [Forms]![frmSafetyReportOut]![endDate])) GROUP BY [Unsafe Act ].[Audit], [qryTag].[Tag] HAVING ((([Unsafe Act ].[Audit])=[Forms]![frmSafetyReportOut]![cboConditionAct])) ORDER BY Count([Unsafe Act ].[Source Of Tag]) DESC;
I am trying to run this query and query works fine however I am trying to run a chart on the report which shows ID's of Tag instead of txt of Tag.
Can someone tell me where I might be going wrong here. The following query works in SQL, but somewhere in the LEFT JOINS area, Access gets a little confused and says I'm missing an operator.
SELECT Tariffs.TariffCPUCID, Tariffs.TariffID, AdviceLetters.ALCPUCID, Tariffs.ALID, Tariffs.ScheduleID, Schedules.SheetTitle, AdviceLetters.[Filing Date], Tariffs.[C&E], SheetsCancelling.CancellingID, SheetsCancelling.CancellingCPUCID FROM Tariffs LEFT JOIN Schedules ON Tariffs.ScheduleID = Schedules.ScheduleID LEFT JOIN AdviceLetters ON Tariffs.ALID = AdviceLetters.ALID LEFT JOIN SheetsCancelling ON SheetsCancelling.TariffID = Tariffs.TariffID WHERE Tariffs.Type="E" ORDER BY Tariffs.TariffCPUCID DESC;
I have a unique query which lists all the films that we are screening over the next 3 months. I have added a COUNT field so that I can see how many of each films we are screening.
The problem is that i get duplicates of some films - and this may be because we may hold several copies of some films. I have attached two images which might explain this better!
What I could do with is knowing how to make it so that i get a list of films booked and how many of each, regardless of which copy of the film is used.
The SQL is:
Code: SELECT DISTINCTROW dbo_Films.[film name], Count(dbo_Films.[film name]) AS [CountOffilm name] FROM ((dbo_Films INNER JOIN dbo_filmCopies ON dbo_Films.ID = dbo_filmCopies.tblFilms_ID) INNER JOIN dbo_EventsFlicks ON dbo_filmCopies.ID = dbo_EventsFlicks.filmCopyID) INNER JOIN dbo_Venues ON dbo_EventsFlicks.venueID = dbo_Venues.ID WHERE (((dbo_EventsFlicks.datefield)>=#8/1/2015# And (dbo_EventsFlicks.datefield)<#1/1/2016#)) GROUP BY dbo_Films.[film name], dbo_Venues.southhub, dbo_Venues.northhub, dbo_Films.Specilaised ORDER BY dbo_Films.[film name];
I am using a MS access mdb file to display some record from oracle database using odbc connection.I have a table (linked table) called map_detail in mdb as well as oracle with same table structure.I formed one query in mdb (sql query) select * from map_detail where batch_no="SSO15121".It is always fetching some other result, but when I am changing the query by changing the batch no "SSO15148" it is working fine. I noticed that for cases it working.
I have a form that has several fields that a user will enter data. When finished, the data is inserted into a table. Works great. What I want to do now is look at one of the fields entered and if it is equal to a certain value, I want to disable one of the other text boxes (fields) on the form AND pop a screen that will allow the user to enter a list of names that will update a different table.
So, if user inputs a directory name GENERIC, the txtScriptName box needs to be disabled/greyed-out and I need a form to pop so the user can put in a list of script names. If any other directory name is entered, the txtScriptName box needs to remain editable. (For directories equal to GENERIC, there will be multiple script names instead of just one)
I've been successful at getting the form to pop if GENERIC is entered but can't figure out how to grey-out the txtScriptName field. I tried using Me.txtScriptName Locked in the Before Update of the preceding field but it didn't work - could still update the txtScriptName field.
quick question... i have a form with a "Save" button. i wanna disable/grey all the fields when user push the button, so they will not be able to make change... please help.
I have a group of option buttons. A user can select more than one. However, currently all of the option buttons have a grey spot in them except for the first one which is blank. I want all of the option buttons to be blank.
I have a checkbox that has a grayed out box inside of it. What causes this/ what is it? I looked up the record and there should be a checkmark in the box. In the table it is set to have one.
I know that if the prefeances of Enables is set to No then all of the check box and the text label box is all grayed out, But in my case it is only the inner part of the box…. (And it is a Checkbox…)
The box name is ABC1 and it just reads the record, that’s it... It is used for reports later down the road. So there is not any code set to this check box.
I have a form with two cascading combo boxes. The options available in the second box depend on what is selected from the first.
Some of the selections which can be made in the first box do not have any options to be selected in the second box and so it currently just pops down empty.
Is there a way that I can make the second box "greyed out" if there are no options for it?
Hopefully just a simple one??? I am using a popup form to input data into a display form which is on one page of a tabbed control. The display form updates through an afterupdate requery command. This all seems to work OK but when I enter the new record via the popup form then close it, it leaves a greyed out space and I lose input ability. I need to close down and when opened up all records are OK. If I drag the popup box on the screen it leaves a grey trail behind it. Any ideas on what could be the problem?
I am trying to use a check box to either grey out certain text field(s). For example If the box is checked true then certain fields would become available to input data. If the box is not checked it stays grayed out.
Hello everyone, Problem: I have an MS Access database in Frontpage that I'm using for my online site. Table is set up w/ a field named, "Artist" (amongst others). In this field I have artist's last name followed by a comma, a space, then the artist's first name. I am using the CONTAINS operator for my sql query statment. Everything works fine on the site if you search only by the first name, last name, or by typing in, exactly, the artist last name, comma, space, then the first name i.e. Presley, Elvis. However, if one were to type in "Elvis Presley", no records return. Why is this? I have also tried the LIKE operator and I get the same results. Here is the code where 45s is my table name and the others are just additional fields of which i have no problems:
SELECT * FROM 45s WHERE (Artist LIKE '%::Artist::%' OR Title LIKE '%::Title::%' OR Record_Label LIKE '%::Record_Label::%') ORDER BY Artist ASC
I'd appreciate any help as I've failed to get a solution from other forums and computer scientists that I've asked.
I have a query that uses "<>" to find records that are not equal between 2 tables. The formula looks like this:
<> [tblWorkPerformed]![ID]
It works fine as long as there is only one record in tblWorkPerformed, but once I add a second record I get the records I dont want plus the records I do. The records I do what are there twice. I found how to only get single records of each, but it still desplays the records it should not. I tried a dummy database, and it does the same thing. Do I have something wrong or is this just not possible.
I'm trying to construct a database to track church funds. I posted before and was given a link to a suitable schema but it was too comnplicated for me! Basically I need to track money given to a church through weekly transactions - money is given weekly through a numbered envelope. Each envelope has a number corresponding to the giver. I came up with this:
This doesn't really work. One giver can have only one FWO envelope but each envelope has many weeks/dates and I'm not sure how to link this. I want the form to have the week no and corresponding date already there so I just enter the amount. Then it has to be able to switch to a different year. Hope this is clear? Any suggestions?
:o Okay. I have a frustrating one. This is embarassing to me, because it seems so easy. Anyways, I have a form, where a person could select up to 3 different months and up to 3 different paper types. These combo boxes are listed in the criteria of those fields in my query. If I use the form and try to run my query, it gives me blank results. If I run the query and fill in the pop ups that show up asking for the information that the form is referring to, and I type in the same information, I get the results I expected. What am I missing here? Please help before I go bald!!!
I have answered my own question so I thought I would share as I couldn't find the solution in any posts. I confess that I don't understand why my results were wrong, but I managed to get them right. :rolleyes:
When using Dcount in a query, I was getting results which did not match the query results. For instance:
Phase_2: DCount("Project_Phase_ID","tbl_Prj_Details","Project_Phase_ID = 2") gave an answer of 27 when there were in fact 41 projects in that phase.
Searching the forum I came accross this:
Count() always counts the entire domain.
So, Dcount is not counting the record set of my query but something else. I have 4 tables in the query and no idea what domain my dcount was looking at. I presume the various join types were messing with it somehow. :confused:
To get round this, I stripped out the Dcount expressions and changed the query to a make table. I then used the created table as the basis of a query in which I had my Dcounts. The dcount results now agree with the query recordset. :) :) :)
Any background on the bits I clearly don't understand will be gratefully received. I hope this helps someone else sometime.
