Trying to create a calculation in a field (text25) that excludes weekends and holidays. I am getting an argument not optional error.
Code : Me.[Text25] = GetBusinessDay([Due_Date - Result_Date])
I keeping getting the error "Invalid use of Null" when i call my email function using optional parameters.
Code:
Email Me.ProjectID.Column(1), _
Me.ProjectAddress, _
Me.TaskDescID.Column(1), _
Me.TaskDescExt, _
strStatus, _
Me.TaskStatusID.Column(1), _
[Code] .....
Windows XP
Access 2007
I have followed Martin Green's steps (fontstuff) for creating an audit trail and am getting a compile error when I check my code.
This is my code and the error highlights 'Call AuditChanges' as the problem.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
Call AuditChanges("ImprovementID")
End Sub
I have a subform and when you double click on any field it remembers 3 fields from the record you are in and stores them. I have 3 fields called [section], [losthrs], [stdhrs]. It works perfectly and stores the data from [losthrs] and [stdhrs] fields, but throws up "Argument not optional" for the [section] field.... Can not see a reason why, it is just a simple text field...
I have even tried 'pausing the code' and using the immediate window to show the field values, again losthrs and stdhrs are fine, section gives "Argument not optional".
I have a query that add up the numeric values in a value list assinged in a combo box in response to each question, the row source for question 15 for exmaple is as follows:
ROW SOURCE: 0;"I have no idea";5;"I indicated that I wouldn’t have time today";0;"Was mentioned early on and then not offered again";5;"The salesperson said the vehicle wasn’t available"
I then run a query that adds together the responses to 3 questions, including question 15, the field in the query appearing as follows:
LS5: ([q14]+[q15]+[q16])
It was working fine but has stopped working, the fault lies with q15, if I take it out it works again. So I looked at the table as I am sure it must be the way it is set up, why it worked before I don't know and I attach a screen shot of how the field is set up in the table, which is no different to q14 and q16.
Anyone got any ideas?
One last thing is that it makes no difference if the fileds contain a number (including zero) or are blank
I have a Form based on a Table of Clients, names and addresses etc. One of the records appears to be corrupt in some way because any time I attempt to access this record, either on the Table or in the Form, I receive an error message "Invalid Argument". The database then shows #Error in every Text Field and I am forced to CTRL Alt Del to close the Database. I cannot even access the record in order to delete it. Any ideas anyone? Thanks.
View 1 Replies View RelatedI have a Access 2000 database that occasionally has a popup message "Invalid Argument", which prevents me from running any action queries or codes. Also another Access 2000 database that has the same "Invalid Argument" error from which prevents me to import the tables to another database.
Do anybody know WHY does the error happen? WHAT does it mean? and HOW we fix it??
Please help. Thanks so much
I have the following in a query to assign a sequence number to each record:
(SELECT Count(*) FROM
[qryPTLActivity] AS T WHERE T.EmployeeID=[qryPTLActivity].[EmployeeID] AND T.PickDateTime <= [qryPTLActivity].[PickDateTime] AND T.PickAisle=[qryPTLActivity].[PickAisle])
I have used this in other databases without issue but for some reason this time I keep getting an "Invalid Argument for Function" error.
I have a strange error:
I open a table by following
dim db as database
dim Rs as recordset
set _ db=dbengine.opendatabase "AMD",dbdriverprompt,FALSE,"ODBC;DATABASE=AMD;DSN=Remote")
set rs=db.openrecordset("AllAttendanceEvents",dbopentable)
when excutes the red line code, it generate the erroe: Invalid argument.
I already added the Micosoft DAO 3.6 Object Library.
PLEASE HELP,
I have a form that has several sub forms and what I want is that if the user chooses Plan Name in the main form "No Fault" or 'Workers Comp". I want message to pop and go to the field in the subform to enter data but I keep on getting an error.
Private Sub Plan_Name_AfterUpdate()
If ([Plan Name] = "No Fault") Then
MsgBox "Enter additional information in No Fault form before continuing."
DoCmd.GoToControl ([NoFaultWCompsubForm]![Attorney Name])
End If
End Sub
It says the action or method requires a control name argument?
What am I missing?
On the access form I have designed , I need to give optional search option to the users. I have the following search options on the form (screenshot attached):
From Date
To Date
Port
Vessel
Voyage
---------------------
The query is as follows:
SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD, dbo_VESSEL.VOYAGE_NUM, dbo_VESSEL.PORT_CD, dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD
FROM dbo_VESSEL
WHERE (
(dbo_VESSEL.VESSEL_CD Like [Forms]![VESSDAT].[Form]![txtvessel]) OR
(dbo_VESSEL.VOYAGE_NUM Like [Forms]![VESSDAT].[Form]![txtvoyage]) OR
(dbo_VESSEL.PORT_CD Like [Forms]![VESSDAT].[Form]![txtport]) OR
(dbo_VESSEL.DEPART_ACTUAL_DT BETWEEN [Forms]![VESSDAT].[Form]![txtfromdept] And [Forms]![VESSDAT].[Form]![txttodept]));
----------------------
However, the form still prompts me to enter values for all the boxes and still doesnt show the correct data.
I would Like to save the data to our Table
[InvoiceTotal] - [InvoicePersonal] "if Invoice Personal contains a value" = [InvoiceShareLoan]
If "InvoicePersonal" does not contain a value, no calculation will be made
I'm relatively new to MS Access (using MS Access 2013 but the db should work on 2010, too) and try to develop a database for an NGO I'm working in. I created almost all the tables (all that I need for now) and made the relationships.
However now I start to create forms and later reports for the actual user. The database will store information about clients and track consultations and assistance the NGO gives to them. There will be around 50.000 to 70.000 clients in the main table. Every client has a specific Individual ID and is member of a family which itself has another specific Group ID.
Now here is my problem: The User usually searches for the respective family by the Group ID. I implemented this with a search query using the ID number of a search text box. All done and no big problem.
But sometimes the ID number is not known so the user needs to search by name (First and Last Name). I use to different textboxes for this and it works in a similar way like the number search by query (Like "*" & [Forms]![frm_SearchIC]![txt_LName] & "*"). All still good However since most of the clients are actually from arabic speaking countries, converting the names into the Latin alphabet is bound to fail and produce a lot of misspellings. Therefore I added 2 more textboxes and 3 comboboxes for the user to give more information about the client and therefore make it easier to search for the person. I was able to produce a query which gives you the right result if you have ALL information at hand. However, this is not always the case.
1) But I cannot find a way to tell the query that if the a certain textbox or combobox is empty, it shall just "ignore" it and use the information at hand. I tried this in the query by adding in the criteria OR .... Is Null. This is alright for one or two textboxes but for the many I have, it seems to be too many different combinations for the criteria. It just worked with some fields but others always had to be filled in...
2) If no information is given at all, the database should inform the user that he needs to enter at least on field. If nothing is found the user should get a msgBox saying "No IC matches your criteria".
3) The results of the searches should be given out in another form where the user can pick the person from 1-to-many results.
I attached a sample database with sample data and reduced tables, fields, and entries ...
I don't think the below code is right, is it close?
Code:
Option Compare Database
Private Sub Test_AfterUpdate()
If Me.Test = "LR" Then
Me.Due_Date Date = [Date Received] + 4
End If
End Sub
Just trying to add 4 days to a field. (the Date Received field is in the format m/dd AM or PM)
I have an access form. It has
-Two textboxes with client first and last name
-Two textboxes with a contact 'email1' and 'email2' for the client
-A combobox with a list of templates to use for the email.
-A checkbox to include a copy of the statement on the email.
I want the user to be able to press a button which does the following
-Sends an email to both 'email1' and 'email2'.
-Attaches a secific report as PDF if the user has selected the checkbox
-The body of the email includes a greeting line using the clients name from the record.
-The body of the message includes below the greeting line a html email template depending on the users combobox selection.
I am developing a calender to display HOTEL room occupancy (past,present) and combine with future "outlook" dates and 1/0 values from active registrations that go beyond the present date.
I am working with MS ACCESS 2007. My problem is ONE SPECIFIC QUERY AND IIF STATEMENT. I want to concatenate some text (using &) along with numbers converted to text (using the CSTr function). I am using iif function and I want to use the full text string as a variable argument to be executed in the iif function. The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.
But, I do not want the final result as the argument. I want the query and iif to use the string expression written into the argument as the variable argument that can be calculated based on OTHER numbers that change everyday in the daily run of the calender.
The field in the statement [calc number] is a date conversion factor that changes everyday.
I want the iif statement to execute using the string as a variable argument. I am writing only within the QUERY to define the query object. I am not writing into any SQL module or code. My field definition and iif statement is below (calc number changes everyday)
CalDate18: iif(18 greater date();"[p" & [calc number] & "]";0
I do not have greater-than key on this international keyboard !
When I use this in an update query, I get format conversion error. When I use the same definition in MAKE-TABLE query, it gives the resolved value "[p1]" for the value of [CalDate18] ......... that is not what I want.
I want the string to be taken literally and executed. Seems there may be a special character to precede the argument or WRAP the argument such as done in Excel. Example # "[p" & [calc number] & "]" #
I don't know, I have noticed that the SETVALUE argument is not openly available in the lookup tables when setting a macro. (But that aside) I have set my drives c: and d: as trusted, and also thicked the sub folder option.
View 7 Replies View RelatedI want to be able to set text boxes so that if one enters a date and a time it displays in the format "dd/mm/yyyy hh:mm", but if one just enters a date is displays in the format "dd/mm/yyyy". Is this possible?
Stipulating "dd/mm/yyyy hh:mm" means that when you just enter a date it adds "0" values for the time e.g. entering "21/6/13" gives "21/06/2013 00:00".
General Date allows for an optional time, but it means that when you do enter a time it gives you seconds as well "dd/mm/yyyy hh:mm:ss" - and I don't want that.
Hi everyone,
What I wan't to do seems very hard, and am wondering if anyone would be able to help out.
Here is what I am trying to do:
First of all, so you somewhat understand my field headers and table, I work for an oil company. I have a form that is based off a table and some of the fields include "Date", "Well Name", "Stock", "Sales", and "Oil". In my table, data is entered for each well every day.
Here is an example of my table:
Date Well Name Stock Sales Oil
08/15/2005 Well1 140 44 25
08/16/2005 Well1 120 35 15
08/15/2005 Well2 150 49 22
08/16/2005 Well2 115 40 30
What I want to do is create a text box on my form and I want it to do a calculation to see if the data is correctly entered. Here is the formula that I want to use in this text box:
If ([Stock] - [StockFromPreviousDay] + [Sales]) = ([Oil])
{
Print "OK"
}
Else
{
Print "Error"
}
I just can't figure out how to get the Stock from the previous day to work with. I have been stuck on this problem for about a week now, so any help would be gratefully accepted.
Thanks,
Chris
Having a problem with results on a form. Three unbound text boxes. One called "BillableDays" second called "DailyAmount" and third "PartialInvoice"
Now "PartialInvoice" is a result of [BillableDays]*[DailyAmount]
But........ The figures are not correct.
For example.... [BillableDays] = 7 ... [DailyAmount] = $135.42 and the result in [PartialInvoice] is $947.91 ...........
Any thoughts on why this is happening and how to correct??
Hi!
I've encountered another stumbling block. My form is almost done except for some minor details. However, one calculation does not work even when I use the correct terms.
This calculates the tuition balance per customer based on data from 2 subforms on the main form: sbfTuitionAccount and sbfTuitionPayment.
My formula is =Nz(sbfTuitionAccount.Form![txttotaltuitionpayable])-Nz(sbfTuitionPayment.Form![txttotaltuitionpaymenttotal])
I've counterchecked all form and control names, however, when I test it, I only get a '#Error ' in form view. A similar formula has proven functional in another form. Do you have any ideas as to what the problem is?
Thanks!
I have a simple tabular form with the following:
cboItem_Name
txtItem_Cost
txtQuantity
txtExtended_Price
The AfterUpdate for txtQuantity = Me!txtExtended_Price = Me!txtItem_Cost*Me!txtQuantity, which puts the extended price in the control.
In the FOOTER of the form, I have txtSub_Total. It's Control Source is:
=SUM([txtExtended_Price]).
As far as I can tell from reading, when placed in the form footer and used in 'continuous form' layout, this ought to give me the total cost of ordered items.
Instead, I am getting #error. Can anyone help me with this?
I have a database which is used by operators to input manufacturing data i.e. LotNo, Customer, SalesOrderNo, PartNo, LineNo, ActualQty, Length, ScrapMat, (MatStdCost), StdLineSpeed, SetupTime, RunTime, LostTime and weightperpiece.
We then use this data to produce information like
Standard Run Time: (([ActualQty]*[Lenght])/[StdLineSpeed])/60
Machine Efficiency: ([Standard Run time]/[Runtime])
these work fine if values are inserted. The problem is that when a value is zero it returns a #error value in the field. This is probably caused by dividing by zero which can happen.
A test may occur where there is zero StdLineSpeed etc.
Is there any way I can bet access to return zero in these cases instead of the #error because I wish to do further calculations on totals and averages and I cannot if even one #error occurs.
Any suggestions or help would be appreciated.
Regards
Niall
I have a simple access database which up until now was working as it should. Then i made a Backup of the database Named it something different deleted certain data from the backup and when i went back into the original DB i now have #Name? where it used to calculate 2 fields.
View 1 Replies View RelatedI have a form for rentals where I have fields for car details and for client details. I have a list box for clients so that when I enter the Client Id I automatically get all the records filled out on the form, from the clients table...
This works but the problem is that sometimes I have a new client who rents a car that is not on the database so I insert the client details manually on the rentals form without filling in the client ID as this client does not exist on the database.
The problem comes when I want to save the form it wont allow me to leave the client ID field blank... I imagine this is because of the relationship...
KTYPE=text; EXPIRATION_DATE=date/time; OVERAGE=date/time
im doing a report based on a query (which has a few fields, including KTYPE, EXPIRATION_DATE, OVERAGE). im trying to determine the OVERAGE based on the following conditions:
here are the conditions:
if KTYPE="PO", then OVERAGE:=(EXPIRATION_DATE + 6 months)
ie.. KTYPE=PO, OVERAGE:=(01/04/2004 + 6 months) = 07/04/2004
if KTYPE="IAA", then OVERAGE:=(EXPIRATION_DATE + 20 months)
if KTYPE="FA", then OVERAGE:=(EXPIRATION_DATE + 12 months)
and so on and so forth.
I'm very new to Access. What formulas do I use and do I put the formula in the query or the report.
Please help. thanks in advance