I have a query that I just can't figure out. We are a facility that works on rail cars. We have over 200 cars in house at the moment and the database has a daily production report in it with the status of all of the cars.
I need a query that will pull out the "AAAA" and "XXXX" cars from the list only IF the cleaning field or the mechanical inspection or BOTH fields are blank.
I've a nice formula which work the majority of the time, the only time this doesn't work is Bank Holidays. Is there a way to build on this:
>=Date()-IIf(Weekday(Date(),2)<2,4,2) And <=Date()-1
The above simply runs a report for the last working day, expect if today is a Monday then it runs Fri-Sun.
Im assuming if I know the dates of the Bank Holidays I could hardcode them into the beginning with multiple IIF statement followed by the one above??
I am trying to get the output of several nested IIF expressions to show in only one filed in the table created. The output needs to be captured in a table. I am staring with create table to get it to work before I go ahead and use the sql to update the original table (the table the data is from)
I can get each to run fine independently and the whole thing runs too, the issue is it wants to put each expression in a new column, thus it is forcing me to label the expression.
Can I change this to only output in one column or add some more sql to do so?
Here is the SQL (table and filed names have been changed to protect the innocent)
BTW, I cannot change the Table the data comes from, I am stuck with the single table structure (BOO).
************************************************** *************
SELECT TABLE1.ID,
IIf([TABLE1]![STAGE]="D",
IIf([TABLE1]![PROJ_ACTUAL_D_DATE] Is Not Null,"Tier 1",
IIf([TABLE1]![APPROVAL_D]="Y","Tier 2",
[Code] .....
I'm trying to run about 300 update statements and keep getting a syntax error of missing operator... what could be wrong?
UPDATE Applicants set [NBCC ID] = '351174' where SSN = '136861387'
UPDATE Applicants set [NBCC ID] = '350960' where SSN = '138885740'
UPDATE Applicants set [NBCC ID] = '350817' where SSN = '140082154'
UPDATE Applicants set [NBCC ID] = '351013' where SSN = '145766662'
Hi everyone,
I am writing a query that is master-detail in principal. But need other data from other 2 tables. I wonder if I can construct the query so that it contains the result set of more than one SQL statement. Thank you for your kindly help.
Gary AU
I have a table where I have 5 columns, (BatteryL1, BatteryL2, BatteryR1, BatteryR2 and BatterySize).I want to update BatterySize, with a value if any one of the above listed columns contains a specific value using something like 'LIKE' in the query.
For example:
UPDATE Customers
SET BatterySize=13
WHERE BatteryL1 LIKE '%13%'
[code]...
I have 2 queries serving useful routines, one to filter out duplicate addresses for a mailing label routine, the second to convert tick boxes into Y/N answers for a Tab Delimited Text file export.can I combine the 2 SQL statements in the 2nd query without amending them in any way.
The SQL statements are;
FROM [Mail List] AS a
GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-News List], a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1]
HAVING (((a.[e-News List])=False) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1]
From [Mail List] as b
Where b.[Address 1] = a.[Address 1]
Group By [Address 1])));
[code]...
im working in MS Access 2007.what im trying to do is have a query run specific parameters if a check box is selected. So if the check box is selected than the query filters the "Tranche" column so that the only records that shown are records that have the "Tranche" coloumn = 1.
I know this can be done either in vba code or in the criteria section of a query but i dont know that appropriete language for either.
In vba code i was able to get this far. But..... i dont know how to call the criteria line from a query?
This is my very simple unfinished code.
My query is called [Tranch Query] and the column i want to filter with is call [Tranche]. The check box is called [Check0].
Private Sub Check0_AfterUpdate()
If Check0 = True Then
'How do i set the query criteria?????
End Sub
I am working with Access 2007. I have a little problem building the iif statement within a query criteria in [Actual Date] which is the following:
iif([Actual Date] Is Not Null And [Document]=0;>#01.01.2012# Or Is Null;999)
When I enter this I get no records out of about 8000. The table fields behind the query are in the right format (date/time and number).
I'm trying to compile a number of IIF statements.
I have 6 IIF statements, but would like to put them all in to one IIF statement.
Could anyone help me with this one?
OrderTrack: IIf([O_SRC_CD]="2005WMDT","Web","Phone")
OrderTrack: IIf([O_SRC_CD]="2005WMKE","Internet","Phone")
OrderTrack: IIf([O_SRC_CD]="DOGNET23","Web","Fax")
OrderTrack: IIf([O_SRC_CD]="CANTRY3J","Web","")
OrderTrack: IIf([O_SRC_CD]="MAMTWOSE","Web",",")
OrderTrack: IIf([O_SRC_CD]="2005W999","Web","Mail")
Thank you for your help and time
Corey
I have a query which works fine for one if statement, but i neewd to have 10, whats the best way of sorting this, can it be done in one query, or should i use a module or should i use the code?
my if statements are as follows:
IIf([eventcode]="n204",[totaltime]-[totalcipstd],0)
IIf([eventcode]="n301",[totaltime]-[totalPaststd],0)
IIf([eventcode]="X117",[totaltime]-[totalMaintstd],0)
etc....
can someone please help me sort this...
a problem may be that the list of eventcodes may increase... again whats the best way to sort this.
Andy
I would like to update a lot of fields in my tables; about 120 different fields in 12 different tables. I would however like to keep it simple by using the SQL editor in MS Access by writing all the UPDATE queries and leave them to run while I do smething else. It seems to me however that you can only run one query at a time using the SQL editor. I thought I could separate one SQL editor from the other by using semicolon as in Oracle SQL editor etc., but that does not seem to work. Does anybody know how to do this in Access without writing a VBA code etc.?
View 3 Replies View RelatedI'm trying to put multiple IF statements into one procedure like the below:
Dim Answer As Integer
Answer = MsgBox("Have You Selected The Correct Outcome?", vbCritical + vbYesNo, "Continue Request?")
If Answer = vbYes Then 'Works fine here
'The bit I can't get right
[Code] .....
I have a list of projects and I need to display their status (Red or Green) in a text box.
My fields are [PercentComplete],[StartDate],[EndDate],[ReportDate]
There are two ways a project could have a red status.
1. [PercentComplete] < 100 AND [ReportDate] < [EndDate]
OR
2. [PercentComplete]<100 AND IsNull([EndDate])
There is one way it could have a green status:
[PercentComplete] = 100 AND Not IsNull([StartDate])
This is the best I could come up with for the Iif statement, but I get "invalid argument" which I suspect relates to the AND portion.
Code:
Iif([PercentComplete] < 100 AND [ReportDate] < [EndDate], "Red",
[PercentComplete]<100 AND IsNull([EndDate]),"Red",
[PercentComplete] = 100 AND Not IsNull([StartDate]),"Green","")))
I have a query that is called up when data is selected from a form and you hit the submit button to go get the report. The report calls up the query which all is working except the if statement in the query criteria. Here is a look at the code.
Like IIf(IsNull([Forms]![TPKG_Status_Generator]![System_List]![column(1)]),"*",[Forms]![TPKG_Status_Generator]![System_list]![column(1)])
What it should do is if the field selection in the form is left blank and they hit submit then the query should return everything. If they pick a system number then it should only display the items in the query that have to do with that system. My problem is that no matter if I leave it blank or pick and item in the drop down box it still displays every line item. Any thoughts?
I am using access 2010. I have a query with an iif statement on a calculated field im trying to round up. ex.
Code:
TotalDiscount: Round(iif([field1]="Piece",[int]-[decr],[field2]*[field3]/[field4],3))
I get an error something like "wrong number of arguments or expression on comma.
I am writing some iif conditional statements in one of my tables to assign new values in that table.
Code:
FP: IIf([cohort with status].[Intake Date] Between #04/01/2012# And #26/04/2012#,201201) Or IIf([cohort with status].[Intake Date] Between #27/04/2012# And #24/05/2012#,201202)
When I run it, I am getting some -1 values in the new column FP.
but if I get rid of the second IIF, then it works.
how do you combine multiple IIF statements in a query?
I am trying to update and insert records with SQL statements. Below my code:
Select Case FirstGRV
Case "Y"
sql = "UPDATE Tbl_SellingPrices " & _
"SET SellingPrice = " & Me!Text2 & ", SellingPriceDateFrom = date(), SellingPriceDateTo = #" & Me!Text3 & "#" & _
"WHERE SellingPriceStockCode = Forms!Frm_GRV!GRVStockCode and SellingPriceDateFrom =#01-01-1900#;"
[Code] ....
My problem is I need to know if the execution of the SQL statement was successful or not. I use the RecordsAffected method, but it always returns 0, no matter what.
:confused:
Hi I need help inserting IIF statements as criterias in my query.
Here's the query I have created from a table called XML_EXPORT which consists of a list of ORGANIZATIONS with various fields. What I'm trying to do is display:
-the name of the EXEC_NAME_1 (primary executive's name)
-the ADDRESS
-the CITY
-the POSTAL_CODE
-the PROVINCE
-the PUBLICATION
-the ORG_LEVEL_1 (the organization's main name)
-the ORG_LEVEL_2 (the organization's departments 2 to 5)
-the ORG_LEVEL_3
-the ORG_LEVEL_4
-the ORG_LEVEL_5
Now not every record contains data in ORG_LEVEL_2 to ORG_LEVEL_5 so we want to display only ORG_LEVEL_1 and the last ORG_LEVEL that is not blank.
i.e. If a record has ORG_LEVEL_4 and ORG_LEVEL_5 blank then we want to only display: ORG_LEVEL_1 and ORG_LEVEL_3 (even though there is something in ORG_LEVEL_2)
Here's my query:
------------------------------------------
SELECT XML_EXPORT.EXEC_NAME_1, XML_EXPORT.ADDRESS, XML_EXPORT.CITY, XML_EXPORT.POSTAL_CODE, XML_EXPORT.PROVINCE, XML_EXPORT.PUBLICATION, XML_EXPORT.ORG_LEVEL_1, XML_EXPORT.ORG_LEVEL_2, XML_EXPORT.ORG_LEVEL_3, XML_EXPORT.ORG_LEVEL_4, XML_EXPORT.ORG_LEVEL_5
FROM XML_EXPORT
WHERE (((XML_EXPORT.ADDRESS) Is Not Null));
------------------------------------------
Can anyone show me how the Iif statement syntax would be ?
Allan
I'm using Access 2003 and excel 2003.
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export
Spreadsheet Type Excel 8-10
Table Name (query Name)
FIle Name (FIle location)
Has field names No
Range Blank
----
This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
I had been using some SQL in Access with many IIf statements. I understand the equivalent in SQL is CASE, WHEN, END.
However, I'm really stuck with the following:
oh..ISNULL used to be Nz as well
IIf(ISNULL(ZI.InvoicesRaised,0)=0,0,IIf(IIf(ISNULL (ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalDebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalDebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0),0))
+IIf(ISNULL(ZIMinus1.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0))*30)/ISNULL(ZIMinus1.InvoicesRaised,0))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0))*30)/ISNULL(ZIMinus1.InvoicesRaised,0))),0),0))
+IIf(ISNULL(ZIMinus2.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0))*30)/(ISNULL(ZIMinus2.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0))*30)/(ISNULL(ZIMinus2.InvoicesRaised,0)))),0),0))
+IIf(ISNULL(ZIMinus3.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0))*30)/(ISNULL(ZIMinus3.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0))*30)/(ISNULL(ZIMinus3.InvoicesRaised,0)))),0),0))
+IIf(ISNULL(ZIMinus4.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)-ISNULL(ZIMinus4.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0))*30)/(ISNULL(ZIMinus4.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)-ISNULL(ZIMinus4.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0))*30)/(ISNULL(ZIMinus4.InvoicesRaised,0)))),0),0)) AS DD
Do I have to put in some seriously heavyily nested CASE statements or are there some other ways round this?
Hi,
I am going to be creating a iif statement update qery, to update a column on dates, E.g. would be....
IIf([FinMonth] between 01/01/2008 and 31/01/2008,1)iif([FinMonth] between 01/02/2008 and 30/02/2008,2)iif(etc etc
2 questions??
1) Is 12 nested iif's statements are allowed within one IIF statement?
2) iS THERE AN EASIER WAY TO DO THIS??
Hope this explains enough.
Thanks
I have two true/false check boxes on a form, one for Paid and one for pickedup.
On the query builder I have these two fields listed, and I want the records to show on the form if either box is not checked(false). So in the Query builder, I have False for the paid and false for the pickedup fields in the criteria portion.
However upon running the query, if I click on ethier box, the form will update and will not show the record. I have to have both fields True to update.
In other words it's acting like a "or" instead of a And.
SELECT SO.SOID, Players.FName, Players.LName, SO.Completed, SO.Priority, SO.SODate, Players.PLAYERID, Players.Phone, SD.Service, SO.Paid, SO.Picked_Up, Frames.Frame, SD.Amt_Due, SD.Paid, SD.Exteneded_Price
FROM ((Players INNER JOIN SO ON Players.PLAYERID = SO.PlayerID) INNER JOIN SD ON SO.SOID = SD.SOID) INNER JOIN Frames ON SD.FrameID = Frames.FRAMEID
WHERE (((SO.Completed)=True) AND ((SO.Paid)=False) AND ((SO.Picked_Up)=False));
I have create few option buttons in a form and assigned values. when each option button selected it will store a value in tbl.attend.log
Exp - Opt 1 button = 1 = OnDuty
Opt 2 Button = 2 = DutyTravel
Opt 3 Button = 3 = SeekLeave
[code]....
I have made a database to show me dates that I need to check various documentation from my contractors.
Once a month I want to print out a query/report to tell me what checks need to be made in the following month.
The dates are
Licence Check
Licence Expiry
Van Ins Exp
GIT Expiry
MOT Expiry
Passport Check
Vis Expiry
I need a query table that shows a list of names that have anything to be checked in the month.
So the Column headers would be Name, Surname, Licence Check, Licence Expiry, Van Ins Exp, GIT Expiry, MOT Expiry, Passport Check, Vis Expiry.
Its easy to do a list with one date but when I add multiple dates into the query it looks for names and surnames with the date within the next 30 days for every date and therefor brings back no records.
I have attached the picture. Obviously not all the records will show dates. Some will be blank.
I want to run a query that returns a "small" or "medium" or "large" etc on each respective row against another columns respective numeric value...
< 500 = "small"
500 up to 1000 = "medium"
1001 up to 10000 = "large"
10001 up to 100000 = "very large".