I am trying to using an iif statement to pick up null values, but I don't know what to type in the function. I tried using "is null", but got an invalid message.
What value I should type in to get null value in the function below?
IIf([popi_r2a] is null","",[popi_r2a])
After hours of playing around i finally have my IIF statement working the proplem is now is that when the Value is null it gives a blank value on my Report. How do i get it to show the value of 0 when it is Null. can i use an isNull withen my IIf statement maybe nested what is best practice?
View 6 Replies View RelatedMy issue is that I am trying to update a date field. When I do the date field may have a date or may be a null. When I try to pass in a NULL date with no quotes, I get a syntax error. When I have single quotes in the statement and a null value is passed in, I get an invalid use of date.
Dim DENIEDDATE1 As Date
If (Not IsDate(rs.Fields("DENIED_DATE"))) Then
DENIEDDATE1 = Null
Else
DENIEDDATE1 = "'" & rs.Fields("DENIED_DATE") & "'"
End If
update table1 set table1.denieddate = " & denieddate1 & " 'get Update syntax error with this statement
update table1 set table1.denieddate = '" & denieddate1 & "' 'fails due to invalid use of null
I have a calculated field (Bhours) with the following IIf function:
=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)
It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".
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
I have this IIF statement however all empty cells are also coming up as Expired. How do I exclude empty cells?
Status: IIf([Expiration Date]>= Date() ,"CURRENT"," EXPIRED")
I've built a form on my Access database that invites a user to enter a start date and an end date into two textboxes. When i press a command button, a parameter query runs which uses the two textboxes as the parameter's criteria. However, to ensure that both textboxes have a data entry, i have tried entering in some VBA that prompts the user to enter in a date if its left null...then set focus to that textbox: Here it is...
If IsNull(Me.txtWeight1) Then MsgBox "You must enter a minimum weight!"
Me.txtWeight1.SetFocus
Else
If IsNull(Me.txtWeight2) Then MsgBox "You must enter a maximum weight!"
Me.txtWeight2.SetFocus
Else
If IsNull(Me.txtWeight1) And IsNull(Me.txtWeight2) Then MsgBox "You must enter a min and max weight!"
End If
This will not compile for me! I've tried with and End If and without an End If and other stuff. Why won't it work?
Hi all!
I have a query that returns some fields with "#Error" ...
The reason is that I call a function in the query, and the function chokes if there are no matches in my query criteria and therefore no argument is sent to the function.
I tried putting this in the function:
var = Iif(IsNull(var) = True, 0, var)
-but I still get "#Error" when there are no matching records.
I presume this is because not even Null is given to the function as an arg.
Should I make the arg optional or how can I solve this??
Thanks :)
Im trying to concatenate a bunch of fields (50 arghh) which each are either blank or just contain one letter. This was someone elses setup for an attendance register which I think is an odd way of doing it. I would have used one field and then to get the mark for a week take a substring at the appropriate position. Anyway im trying to replace an empty string field with a letter to represent the register hasn't been marked using sql statement:
SELECT [400 Student Marks].acad_period, [400 Student Marks].student_id, [400 Student Marks].register_id, [400 Student Marks].register_group, Replace([1],"","U") AS attendance
FROM [400 Student Marks];
[1] being the first register week then I would have concatenated with [2] etc...
This however causes an error each time on the attendance field so im guessing replace doesn't work on empty strings. Is there a way round this/alternative.
Thanks for any advice.
I have a form with two list boxes on it: listAllBANANA and listActiveBANANA
The listboxes are populated from tblBANANA. Active is defined by the tblBANANA field "ResponseDate" being Null.
Also on the form is a CloseBANANA command button. When pressed with no selection, it opens frmCloseBANANA to the first record. If a BANANA is selected in listActiveBANANA, the form opens to the selected record.
Now comes the tricky part: if a BANANA is selected in listAllBANANA, I first need to verify they have an Active BANANA selected, before opening the form. I've poked and tested and searched and cut and pasted my little fingers off with no joy. What I've deduced is that I'm either messing up the syntax of the DLookup or I'm incorrectly referencing the selected record. There's also the third option that I'm going about this in entirely the wrong way.
All other code below works. If I delete or comment out the line in red, I have no issues other than I may open up a blank form because I'm trying to close a BANANA that is already closed.
Code:
'If a BANANA is selected in Active BANANA or All BANANA, open Close BANANA to that record. If not, open form to most recent record
Private Sub cmdCloseBANANA_Click()
If Not IsNull(Forms!frmGUI!listActiveBANANA) Then
[Code] .....
I have a report based on a query with 3 fields (Tested, RMA, OpenRMA). The query searches all the tables in my database and gives me a count of the equipment tested, RMA reported and open rma.
The report has two fields (Equipment, NumberTested) with a total below and also a total of the RMAs and OpenRMAs. I'm trying to hide RMAs and OpenRMAs from appearing in the body and only showing below the total. All three totals are showing correctly, but I am still seeing RMA and Open RMA with a null in the count in the body.
So in the report's Equipment.control I wanted to put
Iif([NumberTested] is null, null, [Equipment])
Meaning if the numbertested field is null the equipment field will be blank, if it is not just leave what is in the equipment field there, but it returns a #Error in the printout. It seems to handle the two null records correctly. Trying Equipment.Value also returned the same error.
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a form in my Access database that has 3 input boxes by which a user can locate a record by. The problem I am running into is that I can not get a nested IF statement to work properly to first check which of the search boxes are filled in and second search by whichever is filled in. I have come up with the following code that seems to be only searching by the first input box. If the first input box is blank it does not move to the second or third numbers so I imagine there may be something wrong with the order of my IF statement or the syntax is off.
View 14 Replies View RelatedHi everyone, please help, i am in depserate need here. Am i right in thinking nested IF functions are limited to 7 variables, i need to choose from 12, so i need to use something different
it seems like a CASE function could do what i want it to, but i really have no clue where to start, shall i enter this into a query criteria box, or on the form itself ina new field box...??
Can you give a few hints as to what the code would look like, i have two fields, obviously. One is 'lesson_type' and one is 'cost' i want the cost to change depending on what is selected in the lesson type box.
LESSON TYPE ----------- COST
Beginner 1hour ----------- 15
Beginner 2hour ----------- 29
Beginner 3hour ----------- 42
Test Retake 1hour -------- 13
Test Retake 2hour -------- 25
Test Retake 3hour ---------36
Disqualified Retake 1hour -- 25
Disqualified Retake 2hour -- 49
Disqualified Retake 3hour -- 72
Advanced 1hour ---------- 10
Advanced 2hour ---------- 19
Advanced 3hour ---------- 27
Is it possible to do that, change the cost correpsonding to what one is entered in lesson_type...??? And where do i put it??? What do i do with it??
Thanks, dragon2309
Hi, hope someone can help me. I want to run a query which acts on data in a table which has been imported from excel, where it was somewhat mangled. Basically, the field shows telephone numbers, but the leading zeros on the phone numbers have mostly been discarded. I want to replace the zeros if they're missing. (Here in the UK phone numbers always start with zero). What I'm trying to use is:
IIf(Left[fieldname],1)="0",[fieldname], "0" &[fieldname]))
But I get errors, I think maybe because of the first comma(?). Can anyone tell me how to achieve this? Any suggestions appreciated.
I have a table of dates that store all the Sundays of a year, as that is what all of our records are based on (how many items an employee sold on the week ending on such and such a date). The table is called WeekEndDates and the column is called WeekEndDate. I want to automate adding a new week, so I have a command button that has the following VB code:
DoCmd.RunSQL ("INSERT INTO WeekEndDates (WeekEndDate) VALUES (DATEADD('D',7, SELECT Max(WeekEndDate) FROM WeekEndDates)")
Any help would be appreciated, I don't know of another way to do this. I'm not sure if this is a SQL question or a VB question, so sorry if this is in the wrong spot.
I have a report where I use numerous DCOUNT functions, and it works fine. But now I need another report which has multiple conditions, and I can't seem to find examples anywhere to get my syntax right. For this example, the field 1 ENJOY in table CONGSITEALL needs to pull out the number of entries for the answer Yes, Definitely, and Most of the Time. I have tried putting the quotes, etc into diffeent places but I just can't seem to find the right combination.
=DCount("[1 ENJOY]","CONGSITEALL","[1 ENJOY] IN ('''Yes, Definitely','Most Of The Time'")
I have a form with couple of textboxes bound to a table. When the user opens the form to enter records, i want to write a function that would go through the textboxes to check whether the textboxes are left NULL. Now i can write code on button click for each form, but i was wondering if its possible to write a function that could be called for each form that i have to check for null values.
View 7 Replies View RelatedI have several records to search for. I need to select just 1 record for each Tkt_no group using Tkt_id (max no).
Example, I only want to select
Tkt_NoTkt_IDTicket_ProblemTicket_StatusTicket_Finish_Time
114problem 1Done
210problem 2Done00/01/1900 20:00
32problem 3Ongoing00/01/1900 20:00
43problem 4Done00/01/1900 20:00
711problem 5Done21/05/2007 9:15
913problem 6Done22/05/2007 0:00
from below data
Tkt_NoTkt_IDTicket_ProblemTicket_StatusTicket_Finish_Time
14problem 1Ongoing00/01/1900 20:00
114problem 1Done
25problem 2Ongoing00/01/1900 14:30
26problem 2Ongoing00/01/1900 11:30
27problem 2Ongoing00/01/1900 10:15
28problem 2Ongoing00/01/1900 15:00
29problem 2Ongoing00/01/1900 8:00
210problem 2Done00/01/1900 20:00
31problem 3Ongoing00/01/1900 20:00
32problem 3Ongoing00/01/1900 20:00
43problem 4Done00/01/1900 20:00
711problem 5Done21/05/2007 9:15
913problem 6Done22/05/2007 0:00
Any ideas?
Appreciate the feedbacks.
Hi friends,
I am new to this forum, and I am facing a problem while learning MS-Access.
I am preparing a database that stores the marks obtained by students in a particular paper in addition to their particulars in tables.
A query is then generated to canculate the aggregate marks and result (Pass/Fail) for the student. It is then presented in a report fomatted as a marksheet, as well as one tabulation chart.
The database is very crude in format, but I am happy with it since I do not know programming at all, and still I could make it.
I have used in built menu of "ms-access" and mouse clicks only for generating query. It was a learn by doing method.
The problem is that my marksheets require absent students to be marked as "ABSENT" or something of that kind. If I convert my Null values to :
Expr2: IIf(IsNull([E_1]),"ABSENT",[E_1])
It shows ABSENT for null value, but refuses to make column total for E_1; On the other hand if I do not enter such expression, it makes column total in tabulation report, but fails to mark "ABSENT" to those who did not appear. So I have to lose one thing to gain another, while I need both.
Can anybody help me at this?
pc
I am struggling trying to execute a function inside a Form_current event to display some stats.
The Function is this:
Code:
Function FlightsByAircraft(Aircraft As Long) As Long
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim str As String
str = "SELECT * FROM tblFlights WHERE AircraftID = " & Aircraft
[Code] ....
The code for the Form_Current event is this:
Private Sub Form_Current()
txtStats1 = FlightsByAircraft(Me.AircraftID)
Very simple. Well, the problem is when I move to a new record, a error message comes up: "Run-time error '94' - Invalid use of Null". It is because the AircraftID is not populated at that time. I tried to insert in the function code something like that:
Code:
If IsNull(Aircraft) then
exit function
else
.... (the DAO.Recordset code)
but it doesn't work.
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
Details:
I have a Profile form that tracks the expiration date for each client's various certifications. These dates are set up in the Short Date format in the table design of Access.
Problem:
When a user accidentally presses an alphabetic key while updating an expiration date, an Access error message is triggered. This is confusing to my users as these messages are written in Access lingo. I would rather that nothing occurs at all. I wrote a case statement to disable each letter of the alphabet and applied it to the On Key Down Event for each expiration date control on my Profile form to solve this problem, but this must be applied to 28 separate controls. I would rather call a function that disables alphabetic keys for each date control in my form when called.
Questions:
How do I transform my Disable Alphabetic Keys Case Statement into a function that I can call for each expiration date control? I know that when writing a function certain variables have to be declared and/or initialized.
Also, will I need to create a function to re-enable alphabetic keys or is this unnecessary because the disable alpha keys function will only be called for specific controls, not the entire form?
What I Have Tried:
I have tried copying and pasting my Disable Alphabetic Keys Case Statement into a module to attempt to create a function, but it needs work.
Below I have included 2 types of code:
(1) The original On Key Down code applied to each date control on my form
(2) The same code written as an attempt at a function
Original Profile Form Code to Disable Alphabetic Keys in the On Key Down event for each date control
Private Sub txtCert1ExpDate_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
'All message box text is for me to test the code, not for the user to see
Case vbKeyA
MsgBox ("you pressed the A key")
[Code] .....
I have a main form with many subforms. Each subform has couple of ComboBoxes or/and TextBoxes. I want to make sure that end users dont miss completing any of required values. For example, in the subform (Clients), an end user is supposed to put either "Male" or "Female" in the (Gender) ComboBox. In another subform (Job Info), the end user will put info of the job like Wage, Employer, Date of Job Start... ete.
What I want is to get VB IF statement which tests if the textboxes "Wage", "Employer", and "Date of Job Start" in the subform (Job Info) are not null, then the CobmoBox "Gender" in the subform (Clients) should not be null too, with a warning message to complete the missing value.
What I am trying to do is merge data from several tables into one query, based on user defined options through multiple listboxes. I've figured out the VBA for the union all for the different selections, my problem is I can't determine how to make it dynamic and skip over a select statement if the list value is null. My code looks something like this so far.
Code:
Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("Flex2")
[code]...
I have 3 queries, one generates a calculation for a phone extension, the other generates a calculation for another field for the same phone extension, in the third query, I am using the data from the first 2 queries to show the data side by side, but when I run the third query, if any of the calculations have a null value, it leaves the whole phone extension out. Does anyone know a way around this?:eek:
View 4 Replies View Related