Queries :: How To Get Zero As Result In Case Of Null Value
Jun 20, 2015
I have a query which i need to get Zero as a result if NULL value. How Can i do this ? Present SQL code is as follows;
SELECT tbl_Impts_main.Bkg_number, Count(tbl_Impts_main.Bkg_number) AS CountOfBkg_number
FROM tbl_Impts_main INNER JOIN tbl_booking ON (tbl_Impts_main.Voyage=tbl_booking.Voyage) AND (tbl_Impts_main.Vessel=tbl_booking.Vessel) AND (tbl_Impts_main.Bkg_number=tbl_booking.Bkg_number)
GROUP BY tbl_Impts_main.Bkg_number
HAVING (((tbl_Impts_main.Bkg_number)=[forms]![frm_Export_data_entry]![bkg_number]));
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
Days on Hold: calcworkdays([on hold date],[off hold date])-1
Now i have a module thingy set up (calcworkdays) which works out working days. What this expression does, when theres an on hold date and an off hold date is work out how many working days something is on hold.
Now, problem is, not always is something on hold, therefore fields are often blank and then i get a result in query that says "#Error", but i want to use the answer to this expression in another formula, but when error is displayed it makes the other query show error too.
What I want is some sort of If statement or similar so that if no results exist to display "0". Can anyone tell me how to add this in?
I'm a total Access Noob, it took me forever to work out this working days thing (damn access for not being as simple as excel!)
I think my problem might lie in the way the function has been written. I think i might have to modify this to show "0", rather than "error"
This is what ive got in the function.
Public Function calcWorkDays(dteStart As Date, dteEnd As Date) As Long Dim i As Long 'day counter Dim dteCurDay As Date 'set i = 1 if you want the first date to count as a full day 'or i = 0 if you do not want the first day to count as a full day
i = 0 dteCurDay = dteStart Do Until dteCurDay >= dteEnd 'check date against holiday table If 0 = DCount("[HolidayDate]", "tblHolidays", "[HolidayDate] = #" & dteCurDay & "#") Then 'continue checking for weekdays If Weekday(dteCurDay, vbSunday) <> vbSunday And _ Weekday(dteCurDay, vbSunday) <> vbSaturday Then i = i + 1 End If End If dteCurDay = DateAdd("d", 1, dteCurDay) Loop calcWorkDays = i End Function
I'm new to programming with Access but am attempting to create a new field in a table with an new date based on existing fields in the table.
The current fields are [Frequency], integer, [Risk], text, [Last Audit Date], date/time, and the new field is [Next Audit Date]. [Frequency] is a calculated field based only on [Risk] and is equal to "5" if [Risk] is "Low" and is "3" is [Risk] is "Medium" or "High", and [Frequency] is blank if [Risk] is (thus far it has never been empty).
What I need the new calculated field to do is return "N/A" (or blank, or anything easily separated really) if [Frequency] is blank, or if [Risk] is "Low" or "Medium". If [Risk] is "High", [Next Audit Date] should be equal to [Last Audit Date] plus 3 years. When I try to save the code, I get this message: "The expression could not be saved because its result type, such as binary or NULL, is not supported by the server."
This is my code now: IIf(IsNull([Frequency]),"",IIf([Risk]="Low","N/A",IIf([Risk]="Medium","N/A",IIf([Last Audit Date]="N/A","N/A",[Last Audit Date]+Year(3)))))
I am trying to select a different query based on the selection in the [Product] Field. I have 4 different products and 3 different queries to run. Each product is specific to a particular query.
What I would like is: When Product 3 is chosen "Query 1" is ran. When Product 1 is chosen "Query 1" is ran. When Product 2 is chosen "Query 2" is ran. When Product 4 is chosen "Query 3" is ran.
As it is set up now it appears that when choosing Product 1 I open Query 2 and when choosing Product 2 I open Query 1, but it is the opposite of that. I get the correct query with this code by choosing Product 1 and the Query that opens is actually Query 1, I don't know what is going on here.
When I open Products 3 & 4 it always opens Query 2, no matter what Query I specify.
Please help me sort this out. I am not very familiar with case select. Thanks
Private Sub run_query_Click() On Error GoTo Err_run_query_Click
Dim stDocName As String stDocName = "Query 1" (Should be for Product 1 & 3) stDoc1Name = "Query 2"(Should be for Product 2) stDoc2Name = "Query 3" (Should be for Product 4)Select Case stProtocolName Case [Product] = "Product 1": DoCmd.OpenQuery stDoc1Name, acNormal, acEdit
Case [Product] = "Product 2": DoCmd.OpenQuery stDocName, acNormal, acEdit
Case [Product] = "Product 3": DoCmd.OpenQuery stDoc1Name, acNormal, acEdit
Case [Product] = "Product 4": DoCmd.OpenQuery stDoc2Name, acNormal, acEdit
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
i just know some query designing and some amount of VBA for making small event procedures.I want to know how we can use VBA when our queries becomes complex to replace the queries and to extend the functionality.how to use recordsets in a VBA function.
I am create a database to track leave of my team.I have create the tracker and few reports, however now I want to create a report by applying filters in 2 places, for associate and for leave type or for associate and for joint leave or for associate and for Informed.I have SQL code which does not work. how can I select the other paramater with associate name using combox box in form.
Code:
SELECT Leave_Records.[Absent Date], Leave_Records.[Associate Name], Leave_Records.[Absent Day], Leave_Records.[Leave Availed], Leave_Records.[Joint Leave], Leave_Records.[Informed TL], Leave_Records.[Leave Type], Leave_Records.[Leave Applied], Leave_Records.Comments FROM Leave_Records WHERE (((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Leave Type])=Forms!Report_Form!Leave_Type)) Or (((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Joint Leave])=Forms!Report_Form!Joint_Leave)) Or (((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Informed TL])=Forms!Report_Form!Informed));
I know when creating a text field in the format option you can use the > or < sign so that when text is entered it automatically changes it to uppercase or lowercase - but i need it to be Title Case, any one know how I can do this....
Using Access 2010 and ODBC connections to pull data from 3 sources (SQL Server 2008, a customized Documentum application, and Windows AD accounts). Problem is user_login_name is sometimes recorded as lowercase, sometimes as propercase, and sometimes as uppercase depending on which table or source being used. For example, lowercase would be "abcd123", propercase would be "Abcd123", and uppercase would be "ABCD123". I thought I could just wrap each join comparison in the From stmt in the query(s) with the UCase() function, but Access doesn't like that.
How can I convert or cast the various user_login_names within the FROM join statement to be the same text case so joins will pull correct data?
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
I have a small issue where I do have names written in UPPER CASE, I would like to transform them in Capitalized (each word), I understand that I cannot use the same function as in excel (PROPER) but something like StrConv, but as I do have a ‘comma’ in between the names I have something like that
I have created the following function in my datebase so that i could specify the date a query works from.
Option Compare Database
Function GetParmValue() As Date
GetParmValue = InputBox("Please enter the date you wish to update!?")
End Function
I have a button on a form that runs the query in question, there is also an identical query that needs to be ran using the same result from the input box but rather than it display the input box twice i wondered if there was a way i could use the result from one of the boxes with both queries!?
Here is the code for the button;
Private Sub bImport5_Click()
DoCmd.SetWarnings False DoCmd.OpenQuery "Update" DoCmd.OpenQuery "UpdateFuture" DoCmd.OpenQuery "MoveFuture" DoCmd.SetWarnings True MsgBox "Files have now been updated to the specified date and moved to the Future Dated table!", vbOKOnly, "Update Complete"
If you have a form that is based on a select query that has selected no records, the form will display as totally blank.
One way to avoid this is to ensure that the query is always able to select at lest one record; but Is there a way a message can be displayed if no records are selected?
I have a combo box as a value list and all the values apart from one are valid data entries in the table but I want to combine two of the values and then get the query to run if the combined values are selected. The value list is this:
(All);Brand1;Brand2;Brand1 & Brand2;Brand3;Brand4
The criteria in my query is this:
IIf([Forms]![MyForm]![Brand]='(All)',[MyTable]![Brand],IIf([Forms]![MyForm]![Brand]='Brand1 & Brand2',"Brand1" Or "Brand2",[Forms]![FrmCustomListATM]![cbBrand]))
DateLimiter: (SELECT Date_Retro_Fees_Rate FROM tblRetroFees AS Alias WHERE Date_Retro_Fees_Rate = (SELECT FIRST(Date_Retro_Fees_Rate) FROM tblRetroFees AS Alias2 WHERE Alias2.Date_Retro_Fees_Rate > tblRetroFees.Date_Retro_Fees_Rate AND Alias2.Id_Product = tblRetroFees.Id_Product) AND Alias.Id_Product = tblRetroFees.Id_Product)
I have a POLEFFDATE field with dates in date/time format. A single record has POLEFFDATE equal to 12/15/2013. I calculated a simple field called EVALUATE as follows: IIF([POLEFFDATE]< 1/1/2014,1,0). EVALUATE should equal 1 for this record, but it equals 0. Why ?
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I couldn't find a similar question already answered, so here it goes. I have multiple tables used to store information on different research materials:
table Books: - BookID - Author - Title - Year - Cabinet
table Papers: - PaperId - Author - Title - Year - Cabinet
table Catalog: - CatalogID - Author - Title - Year - Cabinet
And I have a query for each of those tables that helps me find information. What I'm trying to do is to consolidate the result of those individual queries into one single query that later I can insert into a form to display a search result.
So the resulting query would look like:
Type.....ID....Author.....Title....Year....Cabinet Book.....001...John.....Good book....2005....C01 Book....002.....Mary....Other book...2006...C01 Paper....001....Albert...PaperABC....1987....C01 Paper...002....John.....PaperXYZ.....2006....C02 Catalog...001...Mark....Cat00A....1989.....C02 Catalog...002...Bill......Cat00B.....2004.....C03 etc.
