Parameter Query - IIF Or Nz Not Working
Sep 24, 2006
Hey Everyone, This one is driving me crazy. I have a query that is based on 2 tables. I need the result of the query to be the sum of hours or if no hours then I need a 0. I have searched and tried just about every IIF and NZ function I could find and when I add them I get no results. Which then gives me #error on my report. I have attached a pic of my query and results. I would be forever thankful if someone could help we with this as I have been struggling for a week on this issue and my boss is getting on my case about why this is not done.
Thank you in advance for all your help with this.
View Replies
ADVERTISEMENT
Jul 12, 2007
I'm using an Excel spreadsheet that is importing external data from an access database in which I've got a field where the name of a person checking materials out is entered. it is currently set up, and I cannot change it, as a free form field. So folks enter information in a variety of ways.
For example, Larry Martin might be entered as "Larry Martin" or "Martin, Larry" or "larry.martin@somewhere.com". I'm trying to run a query that would look in that field for any entry with the string I enter, such as "Martin."
I've tried setting the criteria like this:
Like "*" & [Which Last Name] & "*"
However, when I try and run the query I get a message telling me the system is expecting two parameters.
Does anyone have any idea what I'm doing wrong? I've been banging my head against this for awhile now and am thoroughly stumped! All assistance, as usual, is greatly appreciated!
View 1 Replies
View Related
Feb 7, 2006
Hi to all
I am setting up a query to report on the turnaround time for appication to be processed.
I have a calulated column to calulate the numbe rof days betwen receipt and approval of applications. I now want to be able to run the query and subsequently the report by having a prompt to enter anlower and upper number of days range.
I have used the experssion Between [Lower range] and [ Upper range] to prompt and when I enter say 1 to 5 days the query returns all days.
Any thoughts as to why it is not working and what can I do differnetly?
Regards
mgerada :(
View 6 Replies
View Related
Jul 12, 2005
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
View 2 Replies
View Related
Nov 9, 2006
Hi,
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
View 2 Replies
View Related
Jan 30, 2007
Hi,
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:
SELECT [Casual Roster Information].ID, [Casual Jobs/Dates].LinkID, Sponsors.SponsorID
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
View 3 Replies
View Related
May 12, 2005
Hi all,
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.
Thanks in advance.
View 4 Replies
View Related
Aug 2, 2005
Hi all,
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.
Can anyone shed some light on this for me?
Thanks in advance
View 2 Replies
View Related
Aug 18, 2005
SELECT count(Workitem) AS ["Total Workitem"]
FROM qryReport
WHERE qryreport.date >(08/10/2005); 'MM/DD/YYYY
Secondy how do i supply dates dynamically.
View 4 Replies
View Related
Sep 15, 2005
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:
View 4 Replies
View Related
Sep 21, 2005
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
the tables are imported from Excel
View 2 Replies
View Related
Sep 29, 2005
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 :(
View 8 Replies
View Related
Oct 9, 2005
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"
View 4 Replies
View Related
Nov 30, 2005
Hi, i have the below code in a field in a query
PD1ACTFORVAL: IIf([ACTUALS_TO_PD]>=1,[PD1ACTVAL],IIF([FORECAST_1_INC_BASE]=TRUE,[FORECAST_1_BASE_VALUE]+[FORECAST_1_PROMO_VALUE]),[FORECAST_1_PROMO_QTY])
but a message says:"the expression you entered has the wrong number of arguments" .....Any ideas what im doing wrong?
(if forecast_1_inc_base is false id like just the last part of code to be done...ie FORECAST_1_PROMO_QTY)
Any help would be great, cheers
View 10 Replies
View Related
Jan 12, 2006
Hello all,
I have the below update query running
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?
Thanks in advance,
Vassago
View 2 Replies
View Related
Feb 3, 2006
I have a query with several criteria
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?
Thanks
Toni
View 2 Replies
View Related
Mar 3, 2006
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?
View 2 Replies
View Related
May 10, 2006
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
TABLE Employees
EmployeeID (AutoNumber)
EmployeeNumber (Number Long)
FirstName (Text)
LastName (Text)
TABLE SOPTraining
TrainingID (AutoNumber)
EmployeeNumber (Number Long)
SOPNumber (Text)
RevisionNumber (Text)
TrainingDate (Date/Time)
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
View 4 Replies
View Related
Jun 25, 2006
Hi Lads
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
thanks in advance
View 2 Replies
View Related
Aug 31, 2006
Does anyone know why this is not working for me?
I am trying to change a query to add additional criteria and the records that I am trying to exclude keep showing up.
Original query
Field - "otherloc"
Criteria - <>"Supplies forecast" Or Is Null
New Criteria - <>"Supplies forecast" Or Is Null Or <> "PO*"
I am trying to tell it to exclude any record in which "PO" is the first term in the field. The data in the fields in my main table are like PO 702968.
I have tried changing it to PO702968 but nothing seems to matter, the records won't go away. I also tried to duplicate all of the criteria in my query on a second line and add the new criteria and I get the same results.
I've never seen this before and I can't figure out why it would be doing this.
Thanks
View 1 Replies
View Related
Oct 11, 2006
I have a six table database and have a query to show all records,(at the moment) but although i have put 3 records in for testing it only shows the first record. Any help gratefully recieved i'm pulling hair out.:mad:
View 3 Replies
View Related
Nov 1, 2006
Hi, i have a problem
I need to create a query that shows me only days which r:
1. working days
2. which r not in the table on which the query is based
Thanks in advance
View 1 Replies
View Related
Apr 1, 2007
I have a query built by someone else that I think is the root of my problem. The query runs and selects Officers of an Insurance Company based on the dates in a table stating when they were officers. The problem is one of the date (enddate) is blank since he/she may be a current officer. The query reads ">[RPT_NAIC_AF1]![BeginDate] And <=[RPT_NAIC_AF1]![EndDate]" It is not returning all records after the start date which has no enddate. If I change the "And" to an "Or" I get the records I want, and a lot more so I know the problem lies here. Help please! I am a novice user but willing to learn.
View 14 Replies
View Related
Apr 24, 2007
Hi Everyone,
Here's the situation:-
I have a patient record with a specific consultant assigned to the patient. In some instances, the patient may request a different consultant. What I want to do is to allow the user to change the consultant and to save details about the change e.g. file no, old value, new value, date etc.
This is what I have done so far:-
I created a Query to retrieve details from 3 tables and assigned the value to be changed to a variable (oldvalue)
I then allow the user to select from a combo box the new consultant.
Details of these changes are then written to a table called tblchanges.
All the above is working.
The problem occurs when I try to update the consultant table, by replacing the old consultant with the newly selected consultant.
Can someone look at the code and let me know first if I am approaching this problem correctly and then why the code is not working.
Private Sub Combo36_Change()
Dim flno As String
Dim fldnme As String
Dim oldval As String
Dim newval As String
Dim tdate As Date
Dim ttime As Date
Dim sqn As String
Dim yrn As String
flno = PFILENO
fldnme = "Radiation Oncologst"
oldval = Text29
newval = Combo36
tdate = Date
ttime = Time
'Insert change values into tblchanges
strSql = "INSERT INTO tblchanges " _
& "( fileno,fieldname, oldvalue, newvalue, datechg,timechg ) " _
& "VALUES ('" & flno & "','" & fldnme & "','" & oldval & "','" & newval & "',#" & tdate & "#,'" & ttime & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
strFILENO = (Left(Me!PFILENO, 4) & "/" & Right(Me!PFILENO, 4))
'update consultant table with new value
stsql2 = "UPDATE tblatdocts SET roncologist = '" & newval & "' "
stsql2 = stsql2 & "WHERE tblatdocts.FILENO = '" & strFILENO & "' "
DoCmd.SetWarnings False
DoCmd.RunSQL stsql2
DoCmd.SetWarnings True
End Sub
Thanks so much for your help.
Will
View 1 Replies
View Related
Jun 5, 2007
Hi, i have a simple query but for some reason it's not working.
Let me get to the point.
2 tables - table1(id,txtfield1) table2(userid, idfield)
idfield contains id so foreign key (although hasnt been set up as that yet)
Aim: select all fields from table1 where table2 doesn't contain them.
e.g if table 1 had, 5 rows containing:
1 - test
2 - test2
3 - test3
4 - test4
5 - test6
and table 2 had:
1 - 4
2 - 5
Then when the query ran, it would only pick up 1 - test, 2 - test2, 3 - test3.
Any ideas. I have tried but with no luck:
SELECT [ID], [txtfield1] FROM table1 WHERE NOT EXISTS (SELECT [idfield] FROM table2)
View 2 Replies
View Related
Jun 29, 2007
I have a query that needs a calculation field for a report.
This is my field in the query.
Gross: [TBLinvamt]-[TBLinvqty]*67-[TBLfrtamt]-[TBLocamt]
Here lies the problem
example: 3210.81 - 25 * 67 - 0.00 - 0.00
If the frtamt and ocamt fields are 0.00 then my Gross field does not display any value
But if i put 1000.00 for frtamt and 500.00 for ocamt then the gross field returns a value.
Help is greatly appreciated
View 1 Replies
View Related
