hi im trying to do the following if statement in a query but its not working, anyone know what im doing wrong?
any help would be great.
-------------------------------------------------------------------------
New: IFF [(TABLE-SALES]![PERIOD]<=[TABLE-AVERAGE]![ACTUALS_TO_PD] then [TABLE-SALES]![ACTUALS_TO_PD]) else
[TABLE-UPDATE]![FORECAST_1_BASE_VALUE]
------------------------------------------------------------------------
im trying to say if the field "period" in the table: "TABLE SALES" is the same or less than the value in the field "actuals to pd" in the table :"TABLE-AVERAGE" then the value displayed in this field ("new") should be the value: "actuals to pd" in the table "table-sales" otherwise the value in the field "forecast1 base value" in the table "table update"
FixedExpenseOccurance is the the field and "*" is the values which get looked up from a table. I am trying to create an expense report. The query runs but no values are entered into the box?
For some reason it is asking for a parameter when I try t orun it. I don't have anything entered into the Criteria.
hi i am trying to use the following iif statement in a query.
IIf([Forms]![MainForm]![cmb_ExcessStatus]="Resolved",1,IIf([Forms]![MainForm]![cmb_ExcessStatus]="Outstanding",2,IIf([Forms]![MainForm]![cmb_ExcessStatus]="All Records",([MainTable].[Status])=1 Or ([MainTable].[Status])=2,([MainTable].[Status])=1 Or ([MainTable].[Status])=2)))
So, if user selects 'Resolved' then use 1 if the user selects 'Outstanding' then use 2 if the user selects 'All records' then i want both 1 and 2 but this will not work. i also want the default to be 'All records'
I have this in a query and the query runs, it just doesnt do anything. FixedExpenseOccurance is the field name, "*" is the field values (they get looked up from a table). Any clues why this is not working?
For some reason it is asking for a parameter when I try t orun it. I don't have anything entered into the Criteria.
Says I have the incorrect number of Arguments? Any clues? These are all field name: NextActivation (Is a date) Today (is today's date) DueToday (is a yes/no)
Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < aa ORDER BY [Tasks].Staff_Name;"
how can i make it look the the data i put in "aa" rather than prompt a msg box asking for "aa"?
hi guys i have a form that comes from a query with a parent and child table. Main table pk is pricingid and then the foreign key in tblHistory is pricingid.
I am trying to do an append table but i keep getting an error message Pricing ID is the pk autonumber in tblPricing (parent table) historyid is the pk in tblHistory and pricingid the fk(child table) oldprice should be a currency value the other fields are text.
why this IF and Nz statement in my code below is not working. I know the me.txtrefNo value is "" (null) on a form field with property set to General Number. Therefore the Nz statement should return a "0" as I specified making the statement true as in 0 = 0 and then execute the actions below to generate a reference number however this it now happening as it's rendering the if statement as false and showing me the message " Whats going On?" which is after the else statement.
Code: If Nz(Me.txtrefNo, 0) = 0 Then Me.txtrefNo = DMax("[refNo]", "[R_P_Data_P]") + 1 tmpRefNo = Me.txtrefNo 'testing variable value MsgBox (tmpRefNo) Else MsgBox ("What's going on?") End If
PS. Does this have anything to do with the table field format that the me.txtrefNo value will be saved to?
I've created a table for a college project and realised that one of my calculated fields isn't working. It doesn't produce an error message, it just doesn't calculate.
I hope someone is able to help me. I've been working on this for days. I have a report which has a 2 subreports in it. I have the subreport bound and have a query working in the recordsource. Here is the recordsource:
I have one report with the Master/Child fields filled in and set but the sponsors subform I cannot fill in the Child field. It says I need to put SponsorID in the recordsource. I thought I did have this(see above.) That's my first problem.
My bigger problem which I think the previous problem is contributing too is my subreport shows up blank. When I run my query it works perfect. But, when I run it with the rest of the program the subreport shows up blank.
Anyone know how I can go about fixing this?? Thanks!! Nick
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 problem with a delete query i am trying to set up and hopefully one of you experts can help me out.
I have a table called [PMI] and a table called [Referrals]
The tables have a one to many relationship and are linked by the primary key field Patient_No.
I want to delete all records in [PMI] that have no records in [Referrals] and this is what i'm having problems with. I have created a select query where i can return the records i want to delete but its the removing of them from PMI that i cannot figure out.
Hope that makes sense, all help is greatly appreciated.
I'm sure this is easy to do but for some reason i just cannot get it working. Hopefully someone here can tell me where i'm going wrong.
I have two tables in a database and the have a linking unique field. I want to update a column in one table from a column in another where the linking ID field matches.
Here's what i have at present.
Update Table_1 inner join Table_2 On Table_1.ID = Table_2.ID Set Table_1.[CHI Number] = [Table_2]![CHI_No]
This is just returning the CHI Number column as blanks instead of what i expected would be the same as Chi_No.
we have a main table for tracking pickups....some of the fields are combo boxes with relationships to other tables....one such relationship is units (ml, gallon, ounce, etc)...in the main table everything works just fine....a user enters the location info, the amount and the corresponding units. thus, an example would be a user picks up something from building X, room 101, 10, Gallons.....the 10 refers to the gallons, the user picked up 10 gallons...
now for the query: i'm creating a query for which i will base a report on...in query design if i just select the above info everything works fine....it comes out on the report as 5 Gallons....however, what i'd like to do is concatenate the two fields so that they take up less space on the report and are more visibly appealing...here's what i tried in query design: Amt: [amount]&Space(1)&[units] this does not work....in the units table that provides Gallons as a selection in the combo field, Gallons has a key value of 5.....thus, when i run the query using the above code i get 10 5 - where 10 is the amount the amount and 5 is the key from the combo box...
i've tried bringing in the units table but then i get a type mismatch....how can i concatenate the two fields and show the proper units? this has to be simple but i cannot get it to work... :mad:
I have a a table that changes monthly, and each month I need to find the records from the old table that are not in the new table and visa versa. I made the entire row in the table the key. and wrote two find unmatched queries. However, the queries are not finding all the information that is no longer there or that has been added. Can someone please take a look and see what I am doing wrong? Any help appriciated!! Thanks
Ive posted similar in the forms section but it has gone unanswered so I've pulled out the offending query and hope someone here can offer insight into this frustrating problem.
I have a form [Training Record] that has 2 combo boxes.
Box A (course_title_combo) has the titles of the various training courses. It pulls 2 columns from the training_courses table, namely courseID and course_title. Only course_title is shown in the box. The control source of this is set to CourseID as this is the field to be written to the training_record table, showing who attends what course.
Box B (course_date_combo) is MEANT to pull through the dates that each course is on, by pulling through the course dates for each course where courseID (as selected by combo box A) is equal to the courseID in the course_dates table.
Here is the current rowsource query for box B - which is basically showing blank fields...
SELECT tbl_course_dates.course_date, tbl_course_dates.CourseID FROM tbl_course_dates WHERE forms![training record test form]!course_title_combo=tbl_course_dates.CourseID
I cant see what is wrong with the query, but Im guessing it must be wrong as it is the rowsource which determines what is included in combo box B.
Any help would be appreciated no end - im totally stuck now :(
UPDATE tbl_master SET tbl_master.COLLRAW = Iif(tbl_master.PSTATE = "WV","WV", Iif(tbl_master.PSTATE = "MA","MA", Null)) WHERE tbl_master.EXCLUDEREASON Is Null
but when I add this line:
UPDATE tbl_master SET tbl_master.COLLRAW = Iif(tbl_master.PSTATE = "WV","WV", Iif(tbl_master.PSTATE = "MA","MA", Iif(tbl_master,MSPBANK in ("751","752","753","854","855"),"GS", Null))) WHERE tbl_master.EXCLUDEREASON Is Null
It give me an error message that says "Wrong number of arguments in Query Expression." Can anyone tell me what is wrong with the query above?
SELECT tblProjectDetails.projectid, SubType.SubType, tblProjectDetails.status, tblProjectDetails.product, tblProjectDetails.description, tblProjectDetails.type, tblProjectDetails.producttype, tblProjectDetails.confidence, tblProjectDetails.flatamount, IIf(IsNull([reduction]),(([currentprice]-[newprice])*[volume])/(13-[cutinmonthnmbr]),(([reduction]*[volume])/(13-[cutinmonthnmbr]))) AS Calc, ([flatamount]/(13-[cutinmonthnmbr])) AS Calc2, IIf(1>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Jan, IIf(2>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Feb, IIf(3>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Mar, IIf(4>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS April, IIf(5>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS May, IIf(6>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS June, IIf(7>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS July, IIf(8>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Aug, IIf(9>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Sept, IIf(10>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Oct, IIf(11>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Nov, IIf(12>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS [Dec], tblProjectDetails.cutinmonth, tblProjectDetails.DMO, tblProjectDetails.fcstloc, tblProjectDetails.projecttype, tblManagers.last, tblManagers.managerid, tblProjectDetails.currentsupplier FROM tblManagers RIGHT JOIN (SubType RIGHT JOIN tblProjectDetails ON SubType.SubTypeID = tblProjectDetails.subtypeid) ON tblManagers.managerid = tblProjectDetails.managernew WHERE (((tblProjectDetails.status)<>"Concept" And (tblProjectDetails.status)<>"Tabled") AND ((tblProjectDetails.producttype)<>"3") AND ((tblProjectDetails.confidence)="1") AND ((tblProjectDetails.cutinmonth)<>13) AND ((tblProjectDetails.DMO)<>Yes) AND ((tblProjectDetails.fcstloc) Is Null) AND ((tblProjectDetails.projecttype)<>"Other") AND ((tblProjectDetails.cutinyear)=2006) AND ((tblProjectDetails.currentsupplier)<>"Venture"));
Everything works great except for the very last criteria <> "Venture". I have one project that this query should leave out but it eliminates any project with a Null in the supplier field instead of just supplier "Venture". Why would it do that and what would be the solution?
I made some minor changes in my data entry form, the table and the query form. I believe they are all consistent. But now my query comes up empty no matter which parameters I choose. How do I figure out what went wrong?
I have designed a database to store training records. I created a form with a multi-column text box so I can select all employees who have attended a training session that day, and update all records at once. The form has a combo box, as well as two text boxes. Even though the listbox has three columns, I only need to pass the first column. The three columns (in order) are Employees.EmployeeNumber, Employees.FirstName and Employees.LastName. Selecting the command button should run an append query to enter the EmployeeNumber, SOPNumber, RevisionNumber and TrainingDate to the SOPTraining table.
FORM frmAppendSOPTraining lstEmployees (unbound) cboSOPNumber (unbound) (the numbers are pulled from an SOP table) txtRevisionNumber (unbound) txtTrainingDate (unbound) cmdAddRecords
I had some errors in the code at first, but with help from another site, I was able to fix those. The problem now is, that the SOPTraining table is not being appended. :(
Private Sub cmdAddRecords_Click()
Dim frm As Form, ctl As Control Dim varItem As Variant Dim strSQL As String Dim strSQL2 As String Set frm = Forms!frmAppendSOPTraining Set ctl = frm!lstEmployees strSQL = "INSERT INTO SOPTraining (EmployeeNumber, SOPNumber, RevisionNumber, TrainingDate) VALUES (" strSQL = strSQL & "'" & Me.cboSOPNumber & "', '" & Me.txtRevisionNumber & "', #" & Me.txtTrainingDate & "#, "
For Each varItem In ctl.ItemsSelected strSQL2 = strSQL & ctl.ItemData(varItem) & ")" CurrentDb.Execute strSQL2 Next varItem End Sub
Hi guys, I want to return all the records which contain information under a particular column heading, hence the statement thats supposed to return all tables where Sector colum is not empty. I am new to SQL and Access so not sure why this simple query isn't working. It is a subform and displays the results in the "details" section of the form below the controls.
I think it could be something to do with the fact that its a subform, but Im not sure about this.
Me.RecordSource = "SELECT * FROM [tblRecordInfo] WHERE Sector <> "" "
any ideas guys? any assistance is greatly appreciated
