What's Wrong With This Formula???
Dec 8, 2006Here it is:
=Count(IIf([Calendar Date]>=[First Day] and <=[Actual Last Day])
???
Maria
ADVERTISEMENT
Whats Wrong With This Formula?
Oct 23, 2005As state above that whats wrong with the following formula?
=iff([Product]=Home,1,0)
:confused:
Convert Formula Into Formula Result
Apr 6, 2007Hi 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 RelatedUsing Query Formula Information To Create Another Formula In A Seperate Query
Nov 20, 2006I 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 RelatedFormula Help
Apr 28, 2005i have a list of check boxes which state whether a ticket has been sold or not. what formula can i use to find the percentage of tickets that have been sold (by counting the number of checked boxes). also how can i implement that onto a report to display it next to a pie chart of the information. if it cannot be put on a report how can i put it onto a form?
thanks in advance
Formula Help
May 23, 2005I have a nice database which is working well apart from one part
I have 2 date fields, you enter in any date in the first and i need a formaula which will generate a date 6 weeks in the future
can anybody help or suggest what to do?
Formula
Apr 16, 2007Hi
I am trying to create a formula that will update a field in my table - I have created an update query and want to update the field GPA.
I have tried using the iif formula without success. Here is what I want to calculate.
I have two fields one is Score the other is Baseline. The formula needs to place the Score of 4.0, 3.5, 3.0, 2.5, 2.0, or 1.5 in the GPA field given the following.
If the difference between the field score and baseline is .00 then the score 4.0 goes in the GPA field, if the difference between the field score and baseline is .05 then the score is 3.5 in the GPA field, if the difference between the fields score and baseline is 1.0 then the GPA is 3.0....
If both fields are equal then score is 4.0
.05 score is 3.5
1.0 score is 3.0
1.5 score is 2.5
2.0 score is 2.0
2.5 score is 1.5
Can anyone help me with this formula.
Sum Formula?
Apr 6, 2006I have a customers table, products table, and a transaction table that brings them together fields in tblTransaction:
Transaction ID
Order Number
Customer ID
Title ID
Date
Status
I want to create a query so that you can type a name of a customer in, and it will bring up all the items they have bought, and will add up all the prices (from tblTitle) and show it at the bottom but im not sure how to get this calculated field into the query? Whats the sum formula that you put into a query?
Formula...
Nov 7, 2006I have a form that people fill in where they can choose from 4 boxes (and they can tick as many as they want - it is a yes/no box)
I have tried to write a formula for a queryto show where none of the boxes have been ticked.....this doesn't work:
=(IIf(IsNull([Q3(i)]) And iif(IsNull([Q3(ii)]) And iif (IsNull([Q3(iii)]) And iif(IsNull([Q3(iv)]),"1","0"))
???? what should I write?
Formula
Nov 4, 2004Because I am stupid at math, can some please help me out with the formula to get the percent of something that has changed.
Say I have October usage of 2685 and November usage of 6294. I need to know what the percentage of the change is from Oct to Nov Basically the formula to make this calculation.. The calculation will be done in Excel. I know this is an access help area, but you guys have really helped me in the past.
Formula
Mar 21, 2008I need a formula that will give me the count of the number of files I scored 3.0, 2.5,2.0,1.5, and 1.0. These scores are in one field called Claim Quality Index.
What I am trying to do in a query is to count the number of files I scored a 3.0 and in the next column of the query give me the count of the number of files I scored a 2.5 - same for score 2.0, 1.5, and 1.0.
I have chosen the count in the query but I cannot get the criteria to only give me the above. I believe I need to use the expression and name the field CQI3: ????(formula that I cannot get right.
Can anyone help - I have tried various ver. of the IIf formula but to no avail.
Sum Formula
Jul 6, 2006I have the following idea in my mind. The only problem is that I have no clue how to make this work.
[IMAGE]
Anynone know how to make this happen?
Need Help With Formula...
Dec 7, 2006Could anyone give me information on learning how to use the formulas in Access 2003? A good book, site..etc. Example I need to know the formula that takes
20061015 and puts it into 10/15/2006 format. Any help is appeciated!
Formula?
Feb 1, 2007I am trying to Automate the creation of a quote number. I plan on making a form that contains a SalesID ComboBox and a Command button that opens a new form. On Click I want the new form to open a new form and insert the created quote#
Basically the number needs to consist of:
The letter "Q-", "Date", "SalesID" (selected from the drop down menu) and "-#" (sequenced number)
this should give me something like this:
Q-13107BOB-1
Q-13107BOB-2
Etc.
How can I do this?
Could someone provide a name for what i am trying to do so I can look it up on Google?
Abs Formula Help
Jun 9, 2007Hello, I have a formula on my report in a text box that just shows the total delay for a crew by using the supervisors name. This information isnt stored anywhere its just to show a percent on the report! the formula is:
=Abs(Sum([Supervisor]=”Chad Zablackas”)*([DT REGULAR]+[DT MAINTENANCE]+[DT Reason 1]+[DT Reason 2])/Abs(Sum([Supervisor]="Chad Zablackas")*[EMPLOYEE TIME]))
I keep getting an error saying invalid syntax: Your may have entered an operand without an operator? What am I missing in my formula? Thanks!
Now Its All Gone Wrong!!!
Sep 21, 2005Ok Guys,
Here goes...
all of a sudden now, when i open my form, its not displaying any of the previous data that i stored in it!! All the data is still in the table, and when i add data it goes into the table!! I checked properties and the record source and everything is ok there, what have i done wrong guys...HELP!!!!
What's Wrong?
Jun 9, 2006I purchased a copy of Office 2003, on Ebay, for less than half the price it normally sells for. I created databases, which work well on my computer. However, when I copy them over to the office computer from my computer, via Flash memory stick, the program does not run. The message seems to indicate a problem with registration. When I installed Access, it asked me for a registration key, which I did provide. After that it worked-not activation process. Does this mean that you can only use the computer you created the database on to run it, or that I do not have a proper version of Access 2003? I cannot transfer, from my home computer, to the office computer. Is this Microsoft paranoia again?
Subsequent uninstalls and reinstalls of the program did not ask for the installation key.
What should happen? I would like to resolve this major problem and your help will be greatly appreciated. The program is flaky. Also, Switchboard does not work-must use Option Box.
These questions most user must know about initial installation and copying mdb files to other computers -please reply.
What's Wrong
Oct 16, 2006Hi,
Can anyone tell me what's wrong with this:
DoCmd.RunSQL "INSERT INTO tblPersoaneJuridice (Denumire,Localitate,CodUnicInregistrare,NumarInre gistrareRegComertului,Telefon,Fax) VALUES ('" & Forms!frmPrincipal!subfrmTipVersus!txtDenumire & "','" & Forms!frmPrincipal!subfrmTipVersus!txtLocalitate & "','" & Forms!frmPrincipal!subfrmTipVersus!txtCUI & "','" & Forms!frmPrincipal!subfrmTipVersus!txtNumarInregis trare & "','" & Forms!frmPrincipal!subfrmTipVersus!txtTelefon & "','" & Forms!frmPrincipal!subfrmTipVersus!txtFax & "')"
cui = DLookup("[CodUnicInregistrare]", "[tblPersoaneJuridice]", "[NumarInregistrareRegComertului]='" & Me!subfrmTipVersus!txtNumarInregistrare & "'")
i have a string variable denoted by cui and i need it to take value from table tblPersoaneJuridice from field CodUnicInregistrare. CodUnicInregistrare and NumarInregistrareRegComertului is text.
Would Someone Tell Me What's Wrong With This
Jun 14, 2007Have not coded for about 5 years and need a little refresher. Can't remember what I need to do. Thanks
Dim SelectedCompany As String
Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim sSql As String
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
SelectedCompany = Me!cboCompanyName.Text
sSql = "select Customername, AddressLine1, AddressLine2, AddressLine3, AddressLine4, AddressLine5, AddressLine6 from customers where "
sSql = sSql & "CompanyName = '" & SelectedCompany & "'"
rs.Open sSql, Cn, adOpenStatic, adLockReadOnly
this last line fails with error "No value given for one or more required parameters."
The code is executed when someone selects something in a combo box on a form in an access database.
Thanks
Pete
Am I Doing This Wrong
Oct 4, 2007I think I'm just tired, and not seeing what to do here.
I have my main table.
MainID PK
bunch of other fields...
I also have a 'created by table'.
Createdby_ID PK
User ID
Main ID
Date_created.
I also have a user table, with a list of all the users.
User_ID PK
User_Name.
Is this the right way to setup these tables?
Only 1 person can create any paticular record.
I want it to be required tha whoever creates the record selects their name and dates it.
What Is Wrong Please Help
Dec 13, 2005strSQL = ("SELECT onespace2(MeetingMinutes!MinutesID, '.' ) & " - " & [MinutesTitle] AS Item, MeetingMinutes.MeetingCode, Minutes.*, MeetingMinutes.MinutesCode, MeetingMinutes.MinutesID ")
strSQL = strSQL & ("FROM Minutes INNER JOIN MeetingMinutes ON Minutes.MinutesCode = MeetingMinutes.MinutesCode ")
strSQL = strSQL & "WHERE MeetingMinutes.MinutesCode= " & MinutesCode & " AND MeetingMinutes.MeetingCode= " & [Forms]![MinutesMFnew]![ComboMeetingNav] & ";"
cant get it running.
i got the same query running made in the querybuilder, but somethings change, so i need to adjus it in vba. But it doenswork, and get a type error
please advice
What Is Wrong With This SQL?
Jul 13, 2007DoCmd.RunSQL "INSERT INTO tblAccessLog " _
& "(DateLoggedOut, TimeLoggedOut) VALUES " _
& "(#" & Date & "#,#" & Time & "#) WHERE " _
& "(LogID = '" & LogsID & "');"
Could anyone possibly tell me why this isn't working? Probably something stupid i've done bus still.
When i run it, I am getting the error message "Query input must contain at least one table or query" yet i've told it to use tblAccessLog so i'm just confused.
Thanks
What Is Wrong With This SQL?
Aug 14, 2007StrSQL = "INSERT INTO LV (Directory_Name:,Filename:, Total_Files,Total_Records:,Total_Batches:, Total_YH) VALUES ('" & DirectoryName & "', '" & FileName & "', TotalFiles , TotalRecords , TotalBatches , TotalYH);"
Have tried loads of combinations of the " ' and ' " to get it working but just keeps giving me the damn syntax error!!! :mad:
What's wrong with it? Thanks
Anything Wrong With This Sql
Oct 11, 2007strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('" & [Forms]!FRM_ImportSubmission.submission_filename & "','" & [Forms]![FRM_ImportSubmission]![cboAgent] & "','Recieved','True','');"
CurrentProject.Connection.Execute strSQL
i've posted it as the sql string is built in the code
i get a 'catastrpohic error' when running it from vb like this and when i build put the string into a query and run it through the access user interface it worrks
Where Have I Gone Wrong!!
Oct 23, 2007I made a number of changes to a DB that was working fine (normalised further). So what's wrong? I have a number of forms (for my sample DB I have stripped down to just one, if I can resolve for this I can apply to others).
qryENTRY_COMPUTERS
ENTRY COMPUTER FORM
The user adds a new computer then selects the Computer Make (qryLOOKUP_COMPUTER_SUPPLIER) & Computer Model (qryLOOKUP_COMPUTER_MODELS).
This now returns error 'recordset is not updateable'.
The query on which the form is based is incorrect (qryENTRY_COMPUTERS). Previously I was able to add tblSupplier & tblSupplierProducts to the query then add field ProductDescription fom tblSupplierProducts to the query. This allows users to select a value from cboComputerModelID (ProductID), field ProductDescription then displays the related descriptive value for the ProductID selected (value remain on screen when user naviagtes between records.
I have attached a sample database (access 2002-2003 format).
I am sure the answer is staring me in the face but I have now spent hours trying to resolve!!
Help very much appreciated.
Phil.
Update: One difference I can see is that on the query 'qryENTRY_COMPUTERS' where i have added tblSupplierProducts & tblSupplier the join properties are in reverse i.e. Left Table Name = tblSupplierProducts Rigth Table Name = tblComputers Left Column Name = ProductID Right Column Name = ProductID ????
I can't edit this.......................
Could Someone Tell Me What I'm Doing Wrong
May 6, 2005i cant seem to get this to work.
any help would be greatly Appreciated
=sum(iif([SADate]>=DateSerial(Year(Date()),Month(Date()),[p]))
thanks in advance