Queries :: Bank Holiday Date - Multiple IIF Statements
Jun 1, 2015
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??
View Replies
ADVERTISEMENT
Jan 30, 2015
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.
View 6 Replies
View Related
Mar 25, 2013
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] .....
View 1 Replies
View Related
Jun 22, 2015
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'
View 11 Replies
View Related
Jul 9, 2013
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).
View 1 Replies
View Related
Mar 25, 2015
I want VbA code to find a date 1 less than todays date taking into account weekends/Bank Holidays.
View 8 Replies
View Related
Nov 16, 2006
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
View 2 Replies
View Related
Dec 15, 2004
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
View 1 Replies
View Related
Jun 8, 2005
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 Related
Feb 17, 2008
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
View 1 Replies
View Related
Jun 26, 2013
I'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] .....
View 2 Replies
View Related
May 7, 2013
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","")))
View 4 Replies
View Related
Aug 13, 2014
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]...
View 3 Replies
View Related
Jun 19, 2015
I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.
1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.
2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.
View 11 Replies
View Related
Jul 6, 2007
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?
View 2 Replies
View Related
Sep 3, 2006
Just a reminder if you're expecting responses to your question that Monday September 4 is a holiday in the U.S. and Canada.
View 7 Replies
View Related
Sep 16, 2014
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]...
View 7 Replies
View Related
Jul 25, 2013
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
View 8 Replies
View Related
Jan 25, 2006
I have read more post than i care to think about but I can't stil get my head round the following problem.
I have created a database (with this forums help) but I am stuck on the following query/form/vba etc....
Our staff holidays run from 01/05/05 until 30/04/06 and i have a query that calculates what holiday entitlement staff are due from 01/05/05 on a daily basis up to 23 days worth of hols ending on 30/04/06, but I can't work out how to set next years holiday period 01/05/06 - 30/04/07 without changing the form manually, is there a way for it to auto update??
View 2 Replies
View Related
Mar 24, 2014
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.
View 2 Replies
View Related
Jul 27, 2006
hi,
i need to design and build a access database for school where staff for a business could request holidays with there holidays then appearing on sum sort of calendar which can be viewed.
does nebody know where i can find an example to help me with my desing?
thanx
glen
View 1 Replies
View Related
Sep 1, 2006
Hi all I am quite new in access, and I have been looking around the forum, but have not find anything that really matches my project, I don't even know if I can achieve in access!!
There is a screen shot of what I would like to create wich is a screen that shows me the full calendar year, the year can be changed with arrow buttons next to the year, the grey area are the saturdays and sundays per each year. Finally for every they in the calendar including the saturday and sundays I can insert a letter or a number the mark as holiday or other events and the field would change color as in the picture!!!
Is this possible?
Thnks
M
View 2 Replies
View Related
Apr 11, 2013
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?
View 1 Replies
View Related
May 19, 2006
Hello All,
I am trying automate some report printing.
I have around 20 reports most of which require a from date and a two date.
If I use the code below, it asks for the from a two date on each report print, but the dates are always the same.
Is there a way of take a two date and a from date at the start of the sequence and insert it into each reports date range prompts?
Private Sub btnME_Click()
MsgBox "Please note, this process can take upto 5 minutes to complete."
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry1"
DoCmd.OpenReport "rpt1", acNormal
DoCmd.OpenReport "rpt2", acNormal
DoCmd.SetWarnings True
MsgBox "Reports have been printed, aggregated results have been inputed into 'workstats_automated.xls'"
End Sub
View 14 Replies
View Related
Jul 25, 2007
Hi all,
Can anyone tell me if it is possible to input just one date range into a report that is accessing data from multiple table and multiple queries. I can create the report which gives me the info needed but I have to put the same date range in 3 or 4 time before the report is generated.
Thanks
View 4 Replies
View Related
Jul 28, 2014
How do you return the most recent date of multiple columns.
I have a table (tbl_courses) that has a list of training courses. We want to know when a client completed the course most recently.
The problem is, for one course there has been up to 4/5 different variations of the course with different names over the years. E.g. "Drug awareness" has also been known as "Drug Aware" "Illegal Substances" and "Stoppers". I want to pull through the most recent date for all of the above.
We have a field in the Courses table that links the courses into groups (e.g. All drug aware courses come under "23"). Not sure if that works?
Is there a way to do this? The Tbl_Courses is linked to Tbl_Clients via a ClientID.
I've managed to do it in SQL using GREATEST() but that isn't an option in Access.
View 6 Replies
View Related
