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]));
View Replies
ADVERTISEMENT
Mar 5, 2014
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.
View 1 Replies
View Related
Jan 7, 2007
Hi,
I'm trying to work out a formula in a query.
At the moment it looks like this:
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
Any help would be much appreciated!
Thanks
View 1 Replies
View Related
Oct 12, 2013
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies
View Related
Sep 23, 2014
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)))))
View 4 Replies
View Related
Mar 16, 2013
Is there any way of making data that is inputted in lower case to automatically change to the first letter of each word being a capital ...
View 4 Replies
View Related
Oct 27, 2005
Please help with the code below.
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
Exit_run_query_Click:
End Select
Exit Sub
Err_run_query_Click:
MsgBox Err.Description
View 2 Replies
View Related
Jan 21, 2005
Hi everybody,
Beginner here needs help !
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?
Thanks in advance !
View 1 Replies
View Related
May 29, 2014
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.
View 5 Replies
View Related
Jun 30, 2015
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));
View 1 Replies
View Related
Aug 24, 2004
hi
help is needed yet again :-)
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....
View 11 Replies
View Related
Apr 2, 2013
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?
View 1 Replies
View Related
Mar 15, 2014
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"));
View 2 Replies
View Related
Apr 9, 2013
I have a simple SELECT CASE query. I'm not sure how the syntax goes and I want to learn about it.
In the attached file, if you click on "cohort table", you will see the categorization for each unit under "field1".
how will I be able to use a SELECT CASE statement in a query to populate each unit with assigned category?
View 1 Replies
View Related
Dec 16, 2013
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
Code:
StrConv(Left(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",")-1), 3)&", "&StrConv(Mid(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",") + 1), 3) AS CompleteName
Which as you can see is quite a complex statement, how can I simplify it?
Example of a name: VITOR, FONSECA, preferable output, Vitor, Fonseca
View 14 Replies
View Related
Apr 2, 2014
I am trying to combine the result of two specific (and different) queries on a set of loans. As a result I have to sets of query results:
+ result query 1: with first field LoanID and several other fields
+ result query 2: with first field LoanID and several other fields
Now I want to create a list of the combination of all LoanID's, without duplicates.
How do I do that? I read about UNION but that appears to work only on tables.
View 7 Replies
View Related
Feb 25, 2005
Hi,
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"
End Sub
Any ideas guys?
Many thanks
Tim
View 2 Replies
View Related
Dec 15, 2005
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?
View 3 Replies
View Related
Feb 28, 2014
I'm trying to create a query which returns me the next higher number..My table: tblPersons
Code:
ID, Name, balance,
10 John 1000
11 Alice 2000
12 Bob 3000
My query:
Code:
SELECT TOP 1 name FROM tblPersons WHERE( (balance)>([InputBalance])) ORDER BY balance ASC
Input:
[InputBalance] = 500
Result: John
[InputBalance] = 1234
Result: Alice
[InputBalance] = 9999
Result: EMPTY
Result should be Bob
it's possible to combine this logic into one query?
View 4 Replies
View Related
May 3, 2013
How can list the contents of one field columns in one row in a report.
Example:
ID: 1
A1
A2
A3
A4
Should be: ID1 - A1, A2, A2, A3, A4
View 3 Replies
View Related
Mar 22, 2013
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]))
Its the OR part that doesnt work.
View 9 Replies
View Related
Aug 29, 2013
Why this SQL will return only one query maximum?
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)
A picture of the table is included.
View 1 Replies
View Related
Mar 27, 2013
I have record like below:
id s q
1 11 11
2 14 15
I want to sum record 1 and record 2 and put them in next record.
View 2 Replies
View Related
Dec 11, 2014
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 ?
View 3 Replies
View Related
Aug 18, 2013
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'm using access 2003.
View 4 Replies
View Related
Dec 17, 2006
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.
Is this possible?
Any help is appreciated!
Thanks.
Daniel
View 4 Replies
View Related
