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
On Aug 2, 1:26 pm, DandimLee <Dandim...@discussions.microsoft.com>
> 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
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.
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
This Code is sorting in this order
How can I change it to this order
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" <email@example.com> wrote:
I might add another column, which displays True or False depending on
if the SortVal is 0 or not:
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
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
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
Can anyone help me? Please, i really need it!
Here's a screenshot of my query. Thank You!
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?
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
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
* 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
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?
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......
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
Query Builder Error
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
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
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??
>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]
MVP [MS Access]
On Sat, 17 Apr 2010 06:13:01 -0700, Jdaw94 <Jdaw94@discussions.microsoft.com>
>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
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
.Name = Replace(CStr(d), "/", ".")
'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
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:
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!
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
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
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
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...