Hi, hope someone can help. I want the option of leaving perameters blank in a query in more than one field. the problem is that if I leave one blank and fill the perameters in for the other it brings all records up anyway. Is there a way around this? :rolleyes:
View RepliesHello all,
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
Thanks.
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
I do not understand what is happening here. I have foll0wing line in a calculated query field:
m: Switch([EmpID]<5,1) ' run Query 18 in attached example, A2007/2010
this produces 1 for all EmpID<5 and Null for all other EmpID's. All as expected.
But if I do this:
m: Switch([EmpID]<5,1,[EmpID]>=5,Null) ' run Query 19 in attached example
then the entire column is set to Null
It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:
Code:
Private Sub cmdUpdateDates_Click()
'###################################
'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time].
'###################################
Dim intCounter As Integer
intCounter = 0
Dim rs As ADODB.Recordset
[Code] ....
I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)
I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?
Thanks for any help.
Attached are screen shots of three queries used to get a percentage of jobs. If there is no jobs within the date range query 3 shows nothing.
Can anyone tell me how if there are no jobs within the date range requested to get the query to give me 100%?
Any help greatly appriciated.
thanks
Hi All, I have searched and tried every post that I could find and can't seem to get this to work. What I have is 2 tables "training_tbl_200" and "topic_tbl_200". I have several querys "qry_training_215" thur "221" that make up my main query "qry_training_222" My main query will not show any records unless there is data in all of my subquerys. I need it to show a 0 if there is no training time in the category. Im not sure that I have explained this correctly so I have attached a copy of the database. I results that I need are if you run the query for August you can see that it should look like. In Feb there was training but nothing is showing up.
Thanks in advance for all your help...You guys and Gals are the best.
Don
I have a query with Product, Product Directorate, Date Received, Date Delivered, and Date approved fields.
Product field criteria: "NAT CONV"
Product Directorate criteria:
([forms]![frmWhatDates].[cboPD]) or ([forms]![frmWhatDates].[cboPD is null)
Date Received criteria: (Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd]) or(([forms]![frmWhatDates].[txtRecvStart] is null) or ([forms]![frmWhatDates].[txtRecvEnd] is null))
and so forth
When I closed my query, and opened it up again it looked bizarre. Because of the Is Null it added criteria multiple times.. I don't know what's going on but this makes it hard if i want to edit it in the future.
This is what it looks like:
http://img.photobucket.com/albums/v332/youmnac/qryND.jpg
Why did Access do this to my query? Is there anyway I can make this simpler and how?
What I have is a form that filters records according to what is input and it generates a report.
If that date fields are left blank.. I want it to display all the records
http://img.photobucket.com/albums/v332/youmnac/form.jpg
I need to create a query with the MTD field
I have created the MTD and it works!!! but now I need it to show all of the PartNum fields even if null. do I need to convert all nulls to zeros? if so how do I go about this?
Here are the fields in the query
First Field:
Field: PartNum
Table: Part
Total: Group By
Second Field:
Field: UnitPrice
Table: OrderDetail
Total: Group By
Third Field:
Field: NumOrdered
Table: OrderDetail
Total: Sum
Fourth Field:
Field: MTD: Sum([NumOrdered]*[UnitPrice])
Table: (blank)
Total: Expression
Fifth Field:
Field: CurrentYear: Year([OrderDate])
Table: (blank)
Total: Group By
Criteria: Year(Date())
Sixth Field:
Field: CurrentMonth: Month([OrderDate])
Table: (blank)
Total: Group By
Criteria: Month(Date())
When I run the query I only get 2 parts and their total. but I want all parts to show up even if they have not been ordered in the month. the parts that have not been ordered should say $0.00 in the MTD field.
Any Suggestions???
Yes, another of my query troubles. I am running a query that is showing the number of demos booked, number of demos executed, then the percentage of demos executed. I have 2 demos that have 1 or 2 booked, but they have not been executed. They should show up in the query so I can get a percentage for them, but they do not. Here is my SQL:
SELECT [Promo count].PromoNo, [Promo count].[# of Demos], Count(Query6.Status) AS CountOfStatus, IIf([CountOfStatus]=0,0,[CountOfStatus]/[# of Demos]) AS Percentage
FROM [Promo count] INNER JOIN Query6 ON [Promo count].PromoNo = Query6.PromoNo
WHERE (((Query6.Status)="E"))
GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos];
Anyone know what may be going on?
I am trying to build a table from data stored in our SQL server, my problem is I want all accounts specified in the query to be listed regardless if they purchased a particular item or not. If they didn't then I want a zero for that record however I am having trouble accomplishing this I have tried IIF IsNull and a left join could someone look at this code and see what I maybe doing wrong. The table Spring2007 is a list of the items I want to lookup the sales for but if a particular customer did not purchase anything in Spring2007 I want it to list the item and the customer number (CTM_NBR) as 0
PS: when I started the Left Join was an INNER Join and I did not use the IIF IsNull
Thanks in advance,
Terry
strSQL = "SELECT Spring2007.ITM_NUM, Spring2007.Title, (PROOLN_M.SHP_CTM) as CUST, Sum(IIF(IsNull([PROOLN_M.QTY_ORD-PROOLN_M.QTY_LOS]),0,[PROOLN_M.QTY_ORD-PROOLN_M.QTY_LOS])) AS UNITS_ORD INTO tblTOP15a " & _ "FROM (((PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM = PROORD_M.ORD_NUM) " & _ "INNER JOIN CDSITM_M ON PROOLN_M.ITM_NUM = CDSITM_M.ITM_NUM) INNER JOIN CDSADR_M ON PROOLN_M.SHP_CTM = CDSADR_M.CTM_NBR) " & _ "LEFT JOIN Spring2007 ON CDSITM_M.ITM_NUM = Spring2007.ITM_NUM " & _ "WHERE (((PROORD_M.ORG_NUM)=' ') AND ((PROOLN_M.QTY_ORD)<>0) AND ((PROOLN_M.ORD_NUM)<'90000000') " & _ "AND ((PROOLN_M.SHP_CTM)IN ('000000604974','000081275734','000000685361','000 080328280','000080291972','000000740071','00000070 4601','000080317502','000080332322','000080028698' " & _ ",'000080219488','000080101798','000080119800','000 080479597','000000740394','000080045644','00008008 9787','000000737063','000000782379','000000748880' " & _ ",'000080382653','000080422536','000080052398','000 080119747','000080562294','000080091055','00008011 9742','000080119785','000000795627','000080119706' " & _ ",'000080091050','000080091058','000080479638','000 080065432','000080547860','000000777499','00008002 9458','000080038894','000080051948','000080097377' " & _ ",'000080119680','000080119681','000080119685','000 080119688','000080119700','000080119872','00008012 0248','000080219492','000080265549','000080282591' " & _ ",'000080291985','000080317502','000080332322','000 080332323','000080348610','000080479638','00000077 7777','000080410980','000080740385','000080740387' " & _ ",'000080791774','000080851104','000080918031','000 080965290','000080965292','000070036325','00008120 3134','000081203135','000070037501','000070046822' " & _ ",'000000601248','000080369130','000000601133','000 080960480','000070047796','000000775986','00007004 8061','000070068299','000070036335')) " & _ "AND ((CDSADR_M.ADR_CDE)='STANDARD') AND ((CDSADR_M.ADR_FLG)='0')) " & _ "GROUP BY Spring2007.ITM_NUM, PROOLN_M.SHP_CTM, Spring2007.Title; "
I have a form which a user can select upto 3 different options to search the main database.
The main table has:
RVA Date
Council
Introducer
PS NO
Period
Asset Description
Current Cost
SSAP21
Position
The three fields the user can search on is:
Council
PS No.
Asset Description
I have created 3 individual queries to find the records for each of the above, as the other two choices could be left blank.
Not sure if one super query can be done to show the records based on the user input (as I say one or two choices could be left blank).
So far I have managed to get a main query (based on the 3 other queries) to work on all choices made by the user except Asset Description with the other two left blank.
I have made relationships between the three queries on the main query. Linking Council,PS No. & Asset Description to each other.
Almost there, just need the main query to work on the user selecting Asset Description only.....
Hi,
I have a problem with my query. I have a table which has a record of payments, the fields are
id/datepaid/reference/amount
My first query works fine. its simply to find the payments between two dates. The criteria is Between [Enter Start Date] And [Enter End Date] which is under datepaid. This query is saved as qrydatepaid.
The problem Im having is when I try to use this query in another query.
My other query is to show the dates not paid between the dates. I want to do this simply by adding qrydatepaid into a new query (qrydatenotpaid) and putting Is Null in the criteria under datepaid.
When I do this it just comes up blank.
Does any one know what Im doing wrong with this?
Cheers
Phill
I am attempting to run a query that only captures the fields that are null so that I can
run a report that shows outstanding information still required.
Essentially, each record is a provider. I have reviewers that complete an Access Form to
certify each provider. The form consists of approx. 120 checkbox criteria. If it is checked
they meet that criteria...unchecked they do not. The form is based off of 4 tables of which
I am querying from.
I'm trying to get a query to identify only those criteria that are unmet for each provider.
In other words...only show the unmet fields/checkboxes. From that I'll make the report.
Any help is greatly appreciated.
I am trying to build a table from data stored in our SQL server, my problem is I want all accounts specified in the query to be listed regardless if they purchased a particular item or not. If they didn't then I want a zero for that record however I am having trouble accomplishing this I have tried IIF IsNull and a left join could someone look at this code and see what I maybe doing wrong. The table Spring2007 is a list of the items I want to lookup the sales for but if a particular customer did not purchase anything in Spring2007 I want it to list the item and the customer number (CTM_NBR) as 0
PS: when I started the Left Join was an INNER Join and I did not use the IIF IsNull
Thanks in advance,
Terry
Code: strSQL = "SELECT Spring2007.ITM_NUM, Spring2007.Title, (PROOLN_M.SHP_CTM) as CUST, Sum(IIF(IsNull([PROOLN_M.QTY_ORD-PROOLN_M.QTY_LOS]),0,[PROOLN_M.QTY_ORD-PROOLN_M.QTY_LOS])) AS UNITS_ORD INTO tblTOP15a " & _ "FROM (((PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM = PROORD_M.ORD_NUM) " & _ "INNER JOIN CDSITM_M ON PROOLN_M.ITM_NUM = CDSITM_M.ITM_NUM) INNER JOIN CDSADR_M ON PROOLN_M.SHP_CTM = CDSADR_M.CTM_NBR) " & _ "LEFT JOIN Spring2007 ON CDSITM_M.ITM_NUM = Spring2007.ITM_NUM " & _ "WHERE (((PROORD_M.ORG_NUM)=' ') AND ((PROOLN_M.QTY_ORD)<>0) AND ((PROOLN_M.ORD_NUM)<'90000000') " & _ "AND ((PROOLN_M.SHP_CTM)IN ('000000604974','000081275734','000000685361','000 080328280','000080291972','000000740071','00000070 4601','000080317502','000080332322','000080028698' " & _ ",'000080219488','000080101798','000080119800','000 080479597','000000740394','000080045644','00008008 9787','000000737063','000000782379','000000748880' " & _ ",'000080382653','000080422536','000080052398','000 080119747','000080562294','000080091055','00008011 9742','000080119785','000000795627','000080119706' " & _ ",'000080091050','000080091058','000080479638','000 080065432','000080547860','000000777499','00008002 9458','000080038894','000080051948','000080097377' " & _ ",'000080119680','000080119681','000080119685','000 080119688','000080119700','000080119872','00008012 0248','000080219492','000080265549','000080282591' " & _ ",'000080291985','000080317502','000080332322','000 080332323','000080348610','000080479638','00000077 7777','000080410980','000080740385','000080740387' " & _ ",'000080791774','000080851104','000080918031','000 080965290','000080965292','000070036325','00008120 3134','000081203135','000070037501','000070046822' " & _ ",'000000601248','000080369130','000000601133','000 080960480','000070047796','000000775986','00007004 8061','000070068299','000070036335')) " & _ "AND ((CDSADR_M.ADR_CDE)='STANDARD') AND ((CDSADR_M.ADR_FLG)='0')) " & _ "GROUP BY Spring2007.ITM_NUM, PROOLN_M.SHP_CTM, Spring2007.Title; "
I have to give a three paramters queries. But i have to handle null responses also in such a way that in one parameter or two parameters i don't want to enter value and the result should bring all possible values with the parameters I entered
Like customer, status and order number in different fields
when i don't enter order number the result should bring all values in status and customer
I have a left-join query where I know that some of the values in the left-hand table have no corresponding values in the right-hand table...that's why I used a left-join: I want all the values in the left-hand table to show up. I would expect that records without corresponding values in the right-hand table would show up as nulls (I swear I have dozens of other queries that work this way) but in this one, they show up as "#error" instead.
This is a problem because I want to do some calculations on this field. Normally I would use the nz function to change the nulls to zeros.
So,
1. Any idea why this is happening?
2. Is there a way to change the #error values to zeroes, similar to using the nz function?
HEre's the SQL:
SELECT FeederInventorySummary.machine, FeederInventorySummary.Size, FeederInventorySummary.Leaf, FeederInventorySummary.Type, FeederInventorySummary.CountOfFeederID,FeederNeeds Summary_7.FeederQty FROM FeederInventorySummary LEFT JOIN FeederNeedsSummary_7 ON (FeederInventorySummary.Type = FeederNeedsSummary_7.Type) AND (FeederInventorySummary.Leaf = FeederNeedsSummary_7.Leaf) AND
(FeederInventorySummary.Size = FeederNeedsSummary_7.FeederSize) AND (FeederInventorySummary.machine = FeederNeedsSummary_7.ActualMachine);
For every record without a corresponding record in FeederInventorySummary_7, the FeederQty field shows up
as #error.
Hi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delte the records which are already null .
i tried delete query but it did not work
Please help me
Hi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delete the records which are already null .
i tried delete query but it did not work . This is in microsoft Access.
Please help me
What I need to accomplish is an alert if a query finds any records. In other words, When the main page is loaded a query will run in the background. This query is looking for any records that need to amended within the next 30 days. What i would like to do is if the query finds any records that need amending an alert "form" or conditional formatting in a field with turn it red as an indicator that attention is needed.
My problem is how do i write a code to see if the record set from the query is greater than null???
any ideas would be greatly appreciated.
Ricky
Hi,
I have a problem with one of my query, the query has 2 tables, the secondary field from Table2 is linked to the primary field of table1.
The primary field Doesn't allow null Value
The Secondary Field does allow null value which means that the Data in table 1 only concern Some of Table2's Data.
If I run the query with the two tables I can only see the records with the secondary field with no Null Value.
Is it possible to get the query Showing All the records of table1 2 and Table1 even if Table1 has no values?
Depending on the answer of a question, i would like to get the records that have no data in that specific field, otherwise i want the query to give me all records.
I've tried this in "criteria" but it doesn't work: IIf([Question? J/N]="N";"Is Null";"")
Who can help me?
Thanks
Fred
Hello.
I have a simple crosstab query, which is designed to give a monthly total (numeric) and a yearly total for the value of orders placed by a number of customers.
The query is based on another query which simply extracts order records for a particular year.
For some customers, certain months will be blank as there were no orders placed.
I would like to use the crosstab query on a report, and I would like to replace the blanks with zeros.
I have searched for examples of query expressions using the nz function and attempted to apply some. I have, however, had no success in converting the blank monthly totals to zeros.
Can anyone help me achieve this?
The query is as follows :
TRANSFORM Sum(AmountQueryLastYear.SumOfAmount) AS SumOfSumOfAmount
SELECT AmountQueryLastYear.DeliveryName, Sum(AmountQueryLastYear.SumOfAmount) AS [Total Of SumOfAmount]
FROM AmountQueryLastYear
GROUP BY AmountQueryLastYear.DeliveryName
PIVOT Format([ShipmentDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
An example of expressions I have tried is :
Expr1: NZ([SumOfAmount],0)
In it's own column in the query design grid.
Hi,
I have 2 tables... one with 'answers' (on questions) and one with these 'questions'.
Now I want to show the questions with their answers on a rapport
(based on a query) .. but only these whereby the answer-field is NOT NULL..
So I have already:
ansques1: IIf([answers.1] Is Null;Null;[questions.1]+" "+[questions.1])
But now if the answer-field is NULL, then the ansques-field is also NULL...
I want if the answer-field is NULL, to have no new field.. like ansques1.. just want to leave it out of the query... can someone help me with this?
Thanks in advance,
Ankie
Can anyone help...
I am writing a query in which, amongst other things, I also want to perform a dateadd calculation.
I have the following
expr:dateadd("d", [lastduration], [lastlandingslot])
This fails to run as the expression is too complicated, I suspect this is because a lot of the records will not have a [lastduration] and its therefore null.
I have ran the code below sucessfully.
expr:dateadd("d", 1,[lastlandingslot])
How can I get this to execute only if the [lastduration] is not null??
Tried to search but network is really slow