Reply:

Filter Query
Is there a way to filter for records that have one of two values. I have two Yes/No fields, and they can't both be True. I want the records that have one or the other. The way I have it now, the records that have one or the other are grouped on top, but after those records, the records that have neither appear in the form. Given Field1 and Field2, both yes/no, I'd put "<>Field1" under Field2 or visa versa. This will return only records where the two fields are unequal. Todd On Aug 2, 1:26 pm, DandimLee <Dandim...@discussions.microsoft.com> wrote: > Is...

Query based upon another query not returning decimal data
I have Qry2 based upon Qry1 and that query is based on a linked table in SQL 2005. Fld1 and Fld2 are in the table and defined as decimal(28,18) in SQL. Qry1 returns records containing Fld1 & Fld2 without any problems. Qry2 is a Crosstab query performing a Group By / Row Heading on Fld1 and a sum value on Fld2. Qry2 gets an error on Fld1 stating "Invalid Precision for decimal data type". If I use the CDec function on it, the error for this field does not appear any more - but then after some processing, I get this error on Fld2: "Invalid scale for decimal data type&...

Exporting a Query to Excel
Hi, I have a query of Purchase Oders, sorted by month, for a particular business unit and was wondering if there was a way to export the data to one excel workbook, but to have 12 tabs of monthly data instead of having to separate it out manually. thanks -- C. Williamson Here is some code that I wrote for a poster a few months ago that will do this type of export. You'll need to modify the code so that it will loop through a list of months instead of a list of managers. Post back with questions (and with more details) if you have problems. Generic code to create a temporary qu...

Query, Change SortVal
SortVal: IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1) This Code is sorting in this order $20 $0 -$20 How can I change it to this order $20 -$20 $0 So as zero balance will be last to show Thanks for any Help........Bob On Tue, 4 Dec 2007 13:35:05 +1300, "Bob V" <rjvance@ihug.co.nz> wrote: I might add another column, which displays True or False depending on if the SortVal is 0 or not: (IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1)=0) Then sort by that column first (I&...

Why does query convert "Like" criteria to "ALike"
Sometimes queries seem to go corrupt and when I type "Like" (without quotes) into my criteria as soon as I click out of that criteria, it changes to "ALike". Any ideas? Uncheck the option to use "SQL Server Compatible syntax." In Office 2007, click the Office button (top left) and then Access Options (bottom of dialog.) In earlier versions, it's Tools | Options. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. &q...

Calculating time in query
Hi there, I'm searching for some help with my query. I'm making access to be my administration program for my freelance job. I've made a table with a row 'starttime' and a row 'endtime'. Now in my query I want to calculate the difference between these two rows. And then I want to make another query to calculate the totals of these differences. Is this a logic way to do this? And I'm trying to make a expression to calculate the difference but with no luck. The result is something like this: 3,2515785. I've found a kb article but I still can't figu...

ACCESS 2007 Running Balance in query
Hi, I have a problem in putting a running balance in my query.. I have no idea about it because im new to acces 2007. I just need to subtract the Debit to the credit while having a running balance. Can anyone help me? Please, i really need it! Here's a screenshot of my query. Thank You! http://img.photobucket.com/albums/v631/angeljocelyn/HELP.jpg Right now here is the SQL code that i have in my query: SELECT [Copy Of General Journal].Month, [Copy Of General Journal].Day, [Copy Of General Journal].[Account #], [Copy Of General Journal].Explanation, [Copy Of Gene...

Best way to set up Query to get a percentage
I have a "classified" field that is a yes/no check box to determine if a supplier has been classified or not. I need to get the sum of suppliers that are and are not classified and then I need to get a percentage of suppliers who is and who isn't classified from the total suppliers. I also want to be able to chart this without exporting to Excel. Any ideas on the best way to set this up? Marc - You can do that in a query. Change the fields and table names to be yours: SELECT Sum(IIf([Classifed],1,0)) AS Yes_Count, Count(SupplierID) AS Total_Count, Sum(IIf([C...

Select Top With parameter
Hello everyone.... i force that one problem that when i wan select top 5 record from one table on reporting service ....it exist "must declare scalar variable".... example: select top @Top from table1 Error Message : "must declare scalar variable".... In sql @top is a variable named top. to select the top 5 rows you should use select top 5 * from table1. or if you want to make the number of rows returned variable use select top (@top) * from table1. Then you need to define the variable in the report designer; to do this goto the query designer, query properties and on...

Opening an excel file which has a query to external data
I have an access application which opens an excel file which has a query to that access application to import data. * I open the excel file when the access application is closed, i get the message "this workbook contains etc ... Enable automatic refresh". Until here everything works as expected and the data is refreshed fine. * When I open the excel file when the access application is opened i get the message that the db is opened exclusively by another user. This I understand ass well, but is it possible to resolve this? * Now the thing I'm looking for is: Is it possible to ...

MS Query and date format
Hi I have a csv file that I try to link with Excel spreadsheet using MS Query. I have one issue: the date format in MS Query is incorrect. MS Query imports a column as YMD when it should be DMY. How can I change this? Kind regards IgorM hi MS Query is good but sometimes you may have to do a tad bit of clean up after the refresh. how are you refreshing now? all of my MSQ's refreshed from a command button on the sheet. if i had your problem i might do something like this...... Sub RefreshData() Sheets("Sheet1").range("A1").Refresh Backgroundquer...

Query for earliest due date
I have table A with promises of payment, Amtpayable , Duedate and CustID. I have table B with actual payment details, AmtPaid, PaidDate, Cust ID.Now i am creating a query to show the total AmtPayable as on date, amount paid, and LastdueDate. Now i have no problem getting the payable and paid columns. Problem is i get only the Lastduedate till date but i need earliest unpaid due date.for eg, due for jan 1 is 1000, for feb 1 is 1000, mar 1 is 1000, etc. if only 1000 has been paid so far, the query as on date should show Payable 3000, Paid 1000, Due date Feb1. But i get duedate as Mar 1.Any h...

Old CRM Error Returning in 4.0 (Query Builder Error, No Attribute)
I had a requirement to add one pick list value to the Appointment Entity, that's it. I added the entity and I am getting the below error. It appears this issue is cenral to the Appointment entity itself as I cannot delete the new attribute from it, import a fresh Appointment Entity over it or do anything with it. I searched the forum and found references to this issue as far back as 2004, I assume 1.2. Well I am getting this error and I am not able to figure out a resolution. Any suggestions are appreciated. Thanks. Query Builder Error No Attribute I figured the issue out. I had a custom...

Pivot Table Query
Hi, I'm not sure if this is the exact forum for this post, but it looks like the closest one I'm working with a Pivot Table based on a dataset that contains three fields which I'm trying to manipulate into a particular table. Basically, the fields are Location, Type and Date. The values of the first and last fields are self explanatory. The values of the Type field will be either A, B or C. Basically I have data for 10 locations over three months on various dates which is of one of the three types. I'm comfortable in creating a table that will display the occurance of categori...

Outputting multiple query results as VB variables
I have a form that show results from a query that I need to use to print multiple pdfs. The query usually has multiple results and I need to print specific pdf files based on those results. For example, if the query returns A, B, and C, I need to print A.pdf, B.pdf, and C.pdf. I can print the files with VB code once I get the results as variables, but I don't know how to convert the multiple query results as multiple variables. I hope that makes sense. I am relatively new to access and especially to VB so any help (or alternate way to accomplish what I am doing) would be appreciated. ...

blank values giving me validation error in append query
Hi all, I'm probably doing something stupid here but here goes... I'm running some append queries...if there is no value in some of the fields I get a validation error and it says Access can't append all of the records. The table that its appending to does not have the "Required" field set to Yes for any of the fields. Any ides? All of the data types of the table thats receiving the records are Text. TIA Ted Does the field have a Validation rule? If so, then add to it OR NULL so it will accept nulls as valid. Is it a foreign key in a one-to-many relations...

Parameters in Queries 04-17-10
How do I make it so when a query is opened a dialog pops up asking for an input or Parameter?? Jdaw94 wrote: >How do I make it so when a query is opened a dialog pops up asking for an >input or Parameter?? Just use whatever you want to appear in the pop prompt as a field name in the query. Eg. [Enter your name] -- Marsh MVP [MS Access] On Sat, 17 Apr 2010 06:13:01 -0700, Jdaw94 <Jdaw94@discussions.microsoft.com> wrote: >How do I make it so when a query is opened a dialog pops up asking for an >input or Parameter?? Put the desired prompt in sq...

Web query again
Hello, I looked on MSDN about web queries with VBA. But the code I took from there doesn't work. Dim BaseURL As String BaseURL = "some URL" Const StartDate As Date = #11/2/2004#, EndDate As Date = #4/20/2005# Dim d As Date For d = StartDate To EndDate Dim newSheet As New Excel.Worksheet Set newSheet = Worksheets.Add With newSheet .Name = Replace(CStr(d), "/", ".") Call .Activate End With 'MsgBox ("URL;" & BaseURL) 'Next line gives an error 'Run-time error '5': 'Invalid ...

Using Count or DCount in a query
I have several tables in a database, tracking membership signups by generation. Because it is important for us to track that each member gets 2 and only 2 sign-ups underneath them in a particular affialiate program, I designed the tables by generations of sign-ups, starting with a table labeled as FrontLine, and then 1stLevel, 2ndLevel, etc. I link the tables by using a one-to-many link in Front line from MemberId to MemberJoinedUnderId in 1st level, and so on and so forth. I now need to perform a query that displays each member that does not have two members signed underneath them. I w...

Updating a query criteria from a listbox
Hi, I've written a query that is dependent on a date selected from a listbox the result from the listbox is linked to the querie as follows: [forms]![Main Menu]![Date_Select] This works great when I clickthe button that loads the subform that uses the results from this querie... However when I select a different date in the listbox and click again the new date is not being passed down to the querie. I've tried everything I know and it just doesn't want to work... The listbox is linked to a table containing all the dates to be displayed - if that makes any difference. Any h...

Query multiple Criteria, pick one!
Hello all- I have a query: SELECT [Sheaves Limits Query].Ratio, [Sheaves Limits Query].ASheave, [Sheaves Limits Query].BSheave, [Sheaves Limits Query].HPRating, [Sheaves Limits Query].ADiameter, [Sheaves Limits Query].BDiameter, [Sheaves Limits Query].CONVENTIONAL, [Sheaves Limits Query].ProposedBeltLength, [Sheaves Limits Query].MinProposedBeltLength, [Sheaves Limits Query].MaxPorposedBeltLength, [Sheaves Limits Query].ArcLength, ArcOfContact.ArcOfContact, ArcOfContact.CorrectionFactor, Belts.PartNumber, Belts.LinearLength, Belts.Type, Belts.LengthCorrectionFactor, [Sheaves ...

which query is faster, better to use?
There are multiple AdjournmentDates for any given NoticeID in tblNotices_AdjournemntDates. I want the most recent date for each NoticeID. Both of these produce the correct results. Is either one better than the other? SELECT DISTINCT NoticeID, (SELECT TOP 1 AdjournmentDate FROM dbo.tblNotices_AdjournmentDates WHERE NoticeID = A.NoticeID ORDER BY AdjournmentDate DESC) FROM dbo.tblNotices_AdjournmentDates A SELECT NoticeID, AdjournmentDate FROM dbo.tblNotices_AdjournmentDates A WHERE (AdjournmentDate IN (SELECT TOP 1 AdjournmentDate FROM tblNotices_AdjournmentDates WHER...

Combo query refresh
Hello Guru's, I have a database of animal records. Two of the tables are an "animal table" and a "persons table". When I create a new animal record, one of the fields is "breeder name" which is selected by a combo box in the form, which offers a dropdown list from the "persons table". If the breeders name isn't in the list, then it takes you to the "person entry" form to enter the new breeder into the "persons table" and then returns you to the "animal entry" form. It all works well. The next box on...

Queries 02-13-08
Hello, I am trying to run a query to find every instance that a number lower than 255 appears in a field for each record. The query should run for several fields for each record. I have tried typing <255 in criteria, but I am having a problem. If one of the fields in the record ever has a number greater than 255, then that record does not show up when I run the query. What I am trying to do is run a query that will indicate anytime a number smaller than 255 appears in any field on a record. Any help would be appreciated. Thanks! Open the query in design view. Put the criteria...

When I close a form with DoCmd.Close I get a parameter popup
This is a repost. I will try to explain the problem a little better than I did the last time. When I execute DoCmd.Close on one of my forms I get one of those little unknown parameter popups, the kind you get when there's something wrong with your query. But all I'm trying to do is close the form, not read any data. This popup appears to be gernerated by the row source query in a list box on the form. This row source query references another text box on the form and this appears to be what the popup is asking for. But there's a value in this text box. And why should th...