Can you please help me with an “IF” statement. Here are the two formulas I want to use:
#1. ToDate Billing: DateDiff("d",[Start Date],Date())/30*20
#2. ToDate Billing: DateDiff("d",[Start Date],[End Date]/30*20
I want to use formula #1 (w/current date) if an end date is not entered. If an end date is entered, then #2 needs to be used.
I would appreciate any assistance anyone can offer.
THANKS!
So I have a company where the bonus amount for a calculation can change quarterly - if a person accomplishes 50-100% of plan they get that % of their bonus amount.
I have that working on a variable detail DB where the historical data is correct for the report.
i.e. if I want to look at January - the report looks at the requested date: January and calculates using the bonus number from the last update made before January (year is also factored in)
So: January 2014 if they make 50% of plan and their bonus is $100 this month - they receive $50
Good - no problem
NOW: Every year the formula on the report Could Change - so next year if the person makes 50-100% of plan and 30% of secondary plan - they get 30%(% of Bonus)
So now: January 2015 if they make 30% of secondary plan and 50% of plan with $100 bonus the report would give .30*(.50*100) = 15
I can change the calculation on the report - BUT then how would I go back and accurately show what they got in January 2014
Would it require a different report per year?
I have a query, and it has been working just fine, till today. I have it based on a field being null. This field shows null in the table, it is a text field but it is a 19 digit number if there is anything in there. The fields didnt come up in the query when it was based on Is Null. But if I changed it to a <1 they came up just fine. and After further testing, the change works just how I had it working with the Is Null or so I thought. Any ideas on the WHY behind this, I am really curious about it, thought I would get some opinions on why this happened.
View 8 Replies View RelatedIm trying to change the background colour of some fields if the value is null or missing, but I'm not quite sure how to handle the coding. The fields in question are title, given name and surname, and if they are empty I want to change the background colour to highlight to users that they need to collect this information.
Any help that you can provide would be great!
I am trying to change the button color on a subform if a related form data changes.Main form is products with a continuous subform with serial numbers of products i.e, serial number, location, price and a button to add addtional issues if there are any for this particular serial number (this will open up another form related to the serial number so I can add an issues if there are any).The reason I would like the button to be a different color is so I can quickly see if there are any additional notes been added to the serial number. Just in case you may ask why not add the field to the continuos form is that the issues and be quite lengthy and there may be lots of serial numbers on the form
2346 location warehouse price 29.99 (button - green)
2347 location shop price 29.99 (button - red)
Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
View 7 Replies View RelatedI have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!
View 2 Replies View RelatedHi there
I have some a little programing in microsoft access vb and so I am here asking for advice.
I need to copy a data value from one field down the list of null entries till it reaches the next value and then use that an so on in a loop.
E.G
[Area Code]
199a
null
null
null
null
132f
null
null
null
Regards
Luke
I have a question about reports in Access. Is there a way for report fields to be omitted from the report if the database value is null?
For example:
Name: Joe
Last Name: Smith
Company: (Is Null)
website: something.com
would become.....
Name: Joe
Last Name: Smith
Website: something.com
thanks!
I have a table in which i want a column name as output when the data in it is null.
View 2 Replies View RelatedHi,
I am getting this error 'You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)' I know why I am getting the error, I found out that Form and Subform should be implemented only with one-to-many relationships where the Subform is the many end. However, I have the opposite of that for a reason.
Is this bad practice? Is there anyway around it or to fix the error please?
Any help will be very much appreciated,
B
Query4 is the result from left joining of Query3 and Query1 and I would like to delete some of the null data in query 4
while I execute the following code
DELETE DISTINCTROW Query4.*, [working hour] AS Expr1
FROM Query4
WHERE (((query4.[working hour])=0));
I get the warning of "Could not delete from specific table"
So on a command button I have this code in the OnClick event. When I click the button it will ask me to enter the name if blank but if I enter something in that field and then delete the data is bypasses this. Does the same reason field, Why?
Code:
If IsNull(Me.CE) Then
MsgBox "Please enter your name"
Me.CE.SetFocus
Exit Sub
[Code] ....
The code below always displays opens form even when field being tested is null.
Private Sub Assign_Classes_Click()
On Error GoTo Err_Assign_Classes_Click
Dim stDocName As String
Dim msgboxstring As String
Dim stLinkCriteria As String
Me.name_found.Requery
[code]....
A have a table of volunteers and a table of assignments. I can run a query/report of volunteers who are assigned. How do I create a query of who is not assigned.
View 1 Replies View RelatedHello guys,
Does anybode has any idea, of how to do that ? I can do it very easy in VB.NET, or C#, but in Access I give up.
So, I have to take data from the form , and send it to Sub.
With data type of String , I have no any problem, use Nz funciton, end everything, goes well.
But with Date and Integer, I can not find solution.
So :
Date
Dim PensionerFromDate as Date
PensionerFromDate = IIf(IsNull(Me.txtPDPensionerFromDate) = True, ????, Me.txtPDPensionerFromDate)
(instead of ???? i tried everything .. dbNull, vbEmpty, vbNull,sqldatenull, and somtimes it works but int the table stores "12/301899")
Integer
Dim CompanyID as integer
CompanyID = IIf(Me.cboCompany.Column(0) = 0, ????? , Me.cboCompany.Column(0))
I tried here instead of ????, tu insert "", ",," , " " , Cint("") itd. itd. . but nothing works.
Has anybody any idea ?
100 x thanks in advance
I have a subform with continuous records. One of the fields in the recordset of the subform is a field named "Remarks". This field does not need to be visible on the continuous subform as it is rare that this field will have any entries.
I plan to apply conditional formatting on another field (IDcardNo) in the record line of the continuous subform so that when field "Remarks" contains any data it will show as a different format on the field IDcardNo.
I would like to make a small form appear when one points to the IDcardNo field with the different format, so that the data in the field "Remarks" pops up when one points to the field IDcardNo with altered format, showing that there is data in the field "Remarks".
I've created some code that enables a user to copy an existing record to a new record, which looks like this:
Private Sub btnCopytoNewRecord_Click()
Dim Salutation As String
Dim First_Name As String
Dim Surname As String
'Copy fields to variables
Salutation = Me.Salutation
[Code] ....
This code is working fine, until it hits an empty field, and then I get an error message: Run time error '94':
Invalid use of NullIs there some code I can use to tell it to skip any null fields?
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
If the data source changes for an Access database, do I need to recreate all the tables, queries, and reports? I am having quite a time with this issue. Thanks for any assistance you can provide.
View 1 Replies View RelatedI'm using Access 2002 and need to increase some field sizes, some by increasing the maximum number of characters and others by changing from Text to Memo. When I try to do this I get the message "Microsoft Access cannot change the data type. There isn't enough disk space or memory"
I have two 320GB hard drives and 4GB of RAM and the Access file is a mere 280MB. Can anyone please explain the problem and tell me how to achieve my objective?
Roger
Does anyone know if its possible to change the data type of a field from "Text" to "Number" by using a macro?
thx
I finally found and eliminated all duplicate entries in my tblStoreProducts table, so that I could create a 2-field Index called UniqueProduct that would prevent duplicate entries. Without it, uploading information from Excel (not the preferred method) would result in duplicated entries in this table which would ultimately reflect in the Hazardous Materials reports that must be submitted to the fire marshal.
Today I was going through data entries in the Product table and noticed some that are STILL coming up with the UPC (a text field) in Scientific Notation.
I know that I have to change the information in the linked table tblStoreProducts first. The most simple way would be to just change the UPC of the product in that table to the correct UPC. It shouldn't be causing a problem, because it doesn't create a duplicate record. I'm getting the error "The text you entered isn't an item in the list" now that I've created that index.
I changed the index to Unique = No and still get the same error.
I removed the index all together...still get the same error.
I'm making the changes directly in the BE database right now. If I can't enter information directly, the forms aren't going to work either.
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] ....
Hi,
I am trying to import an excel spreadsheet into Access2000 using the import wizard.
I choose my XLS file, I say whether or not the first row contains headings, (no is the anwser).
Then I want to change some of the fields to data type of memo from text, but all I can see if Field 1 and data type option is blanked out.
If I click or double click on another field it doesn't change.
so I cannot change the datatype of a field..
do you know why, and how I can fix this ?
The reason I want to do this is one of the rows in excel has 300 characters, and the text field in access only allows 255.
regards,
Jamie
my teacher asked me to do Access homework to change data type from Text to Boolean. Data in "Owned car" field show as Y or N
17178
so I click on Design View and change datatype of this field from "Text" to "Yes/No" , than when I saved it show a message
17179
When I back to Datasheet view, all data in "Owned car" field change to be "No".
How can I change data type to be Boolean type without deleting my original data??? I want them to show the same as Y or N.