Using <> Giving Me Records It Should Not
Oct 22, 2007
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.
Thanks
Anthony
View Replies
ADVERTISEMENT
Oct 31, 2013
I am trying to use DCount to count the number of records (speakers) for each session. Not being very savvy with DCount, I copied code that I had working for another instance of needing to count the number of records.
My query has three fields
Query used: Web/PrintReport_qry
Session Id - number
Speaker - string
Code:
SpeakerCount: DCount("*","Web/PrintReport_qry","[Session ID]='" & [Session ID] & "' And [Speaker]<" & [Speaker])+1
My output for SpeakerCount is "#Error"
View 10 Replies
View Related
May 24, 2005
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.
Thanks!
David
View 1 Replies
View Related
Nov 1, 2005
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;
View 2 Replies
View Related
Apr 25, 2007
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:
tb_parishioners
parishioner_id (PK), fname, lname, address etc
tb_FWO_Nos (FWO = free will offering)
FWO_ID (PK), fwo_Number
tb_contributions
Contributions_ID (PK), parishioner_ID (Both PK), week_no, amount, date
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?
Thanks.
View 1 Replies
View Related
Nov 3, 2005
SELECT Conversion.[Asset Class], iif([asset class]<4000, "1", "2")
FROM Conversion;
View 2 Replies
View Related
Apr 20, 2006
Hi all.
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.
strSQL = "INSERT INTO TblFieldValue ([QuoteID], [QuoteTypeID], [SectionID], [FieldID], [FieldValue], [Date], [UpdatedBy]) Values ('" & strQuoteID & "', '" & strQuoteType & "', '" & strQuoteSection & "', '" & strFieldID & "', '" & strValue & "', '" & strDate & "', '" & strUser & "');"
'MsgBox strSQL
DoCmd.RunSQL strSQL
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...
View 2 Replies
View Related
Nov 13, 2007
: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!!!
View 8 Replies
View Related
Mar 2, 2008
Hi all,
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.
thanks in advance.
rgs
Ginny
View 5 Replies
View Related
Jun 14, 2006
Hi Folks,
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.
Kind regards,
Keith.
View 3 Replies
View Related
Nov 3, 2005
Okay, I figured out that I can use the instrrev function to look at data from right to left.
I used this function:
Right([mydata], InStrRev(1, [Mydata], "-"))
in a query in an attempt to get all data right of the "-".
When I run my query, I get "error" instead of the data I was expecting.
What is wrong with the function? BTW i am using Access 2002.
TX
Al
View 4 Replies
View Related
Mar 27, 2006
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?
View 2 Replies
View Related
Jul 21, 2006
Hi There
we have a ms access application which pools data from AS400.
we have scheduled it for every week but there is a situation when its missing the data to feed the tables.
but when we run the queries manually it gives the desired results.
all kind of response are welcome
Best
danny
View 1 Replies
View Related
Oct 10, 2007
Ok baisically im doing a quick db for my company that is organising every file stored within the server and monitoring their last access'd, modified etc... I have a query which collates all the files which can be deleted on a server and file count is something alond the lines of 300K+ however i have also Sum'd the file sizes and instead of giving me some mental file size it has given me this result: 1.29659732507E+11
I mean i am no mathematician.. cant even spell it :S anyways im guessing that the number: 1.29659732507E+11 is actually 1.29659732507 to the power of 11?
Is there anyway i can get access to show the actual number i dnt care how long it is i just want a reliable, definitive result i can show my boss!
Cheers
View 1 Replies
View Related
Nov 29, 2007
After searching through 18 pages of search results I have not found the answer. Would someone please tell me why this expression is not working in my query?
RCStatement: """&[RootCauseStatement]&"""
The result is "&[RootCauseStatement]&"
View 4 Replies
View Related
Sep 27, 2004
Hey guys - I am creating a fake webpage looking database in MS Access Project. On the form I am using labels with hyperlinks that directs the current focused label to do something. I have user rights in my database, and depending on perimissions, i want to disable a lablel like you can do a textbox. I am trying to duplicate a disabled textbox with label look. My coworker had a procedure in API that allowed you to pass a control or label in it, and it will give it the disabled look. Problem is, he lost it can someone help me please. I don't want to go back into my system and change everything over to command buttons or textboxes.
Any help would be appreciated..
View 5 Replies
View Related
Nov 8, 2004
Hello everybody!
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
View 3 Replies
View Related
Jul 11, 2007
Hi ,
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..
Where am i going wrong..help pls!!!!
THANKS VERY MUCH!!
View 6 Replies
View Related
Oct 27, 2014
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.
View 2 Replies
View Related
Jun 19, 2013
So I've been working on this big project and part of it is to be able to input data into a form that looks just like the one everyone is using currently. The thing is that the form is made up of mostly checkboxes:
Eventually I want to be able to do some calulations with this data. The check boxes at Yes/No check boxes which works for the Satisfactory/Unsatisfactory part but I was wondering if I could assign a value to it (ex: 5).
I was thinking of different ways to go about this and was thinking along the lines of using the count function to get some data.
I read on the microsoft page about changing the countrol source with IIF([<fieldname>]=True,"","5").
What is/How do I get to the control source? Would I have to use Yes instead of True because I use yes/no boxes?
If this works to import it into excel or something that would work just as fine too.
View 2 Replies
View Related
Jul 12, 2005
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;
View 5 Replies
View Related
Mar 10, 2008
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.Hope I am making sense.
View 3 Replies
View Related
Jul 19, 2005
Help me! I'm creating a supplier database at the moment. I have a pivot table that allows users to filter the list of suppliers by programme, primary product & company name. I'd like to make it so that double clicking on a company name opens that suppliers details in a new form (which I have already made). How do I do this?
While the knowledge is flowing, I'd also like to make a button that will reset/remove the filtering from the pivot table. Any idea's?
Thanks guys!
View 3 Replies
View Related
Apr 15, 2015
In my database, I have a report called "rptEventsbyEmployee" and in that a subreport called "sbrptEventsbyEmployee". The master/child link is the employee name. Everything is fine until you get to page 3 where the subreport shows twice.
View 5 Replies
View Related
Aug 4, 2015
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];
View 4 Replies
View Related
May 10, 2012
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.
View 3 Replies
View Related
