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).
: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 need to figure out the amount of days between two dates that do not exist in my database...I use this calculation to do waivers...I was able to build a table and put in the two dates and then build a query which calculates the days between the two but I wanted to know if I could build a query that prompts on a [start date] which I would enter and then prompts on an end date [end date] which I would enter to get the number of days between...is that possible?
I had...
WaiverDays: [Enter start date]-[Enter end date] but it did not let me run...I added () and it still did not run...
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 have a table with a field TDate (dd/mm/yyyy format). A query with calculated fields is lying on this table. I want to put a date criteria in this query, by a combobox in an unbound form, where the date format has to be mmmm/yyyy and has to be updated as new TDates come in.
Trying to pick up values for the combobox from the TDates field (and formatting them), I get a list with several same values. This is of course expected as there are many records on the same month, even on the same day of the month.Is there a way to have this list with unique values for each TDates month/year?
Below is the SQL for a query I have, which returns events that are scheduled for today, I need to change this so that The user can specify events to be displayed in a date range and If possible only show those events for the users windows ID
Code:
SELECT tblEvent.EventStart, tblEvent.EventOwner, qryCompany.Company, ltDescriptionType.Description
FROM (tblEvent INNER JOIN qryCompany ON tblEvent.Company = qryCompany.ContactID) INNER JOIN ltDescriptionType ON tblEvent.EventDescrip = ltDescriptionType.[DescriptionType ID]
WHERE (((tblEvent.EventStart)=Date()))
ORDER BY tblEvent.EventStart, tblEvent.EventOwner;
I have a query with a date field that is formatted mm/dd/yyyy. I have a combo box that is formatted as mm/yyyy. I need the choice from the combo box to be the criteria for the date field in the query. the combo box has to contain the month and year only (which it does now) and the query must return mm/dd/yyyy. I have tried a few statements and the closest I got was a between statement that added 30 days to the combo box selection but that's not really accurate.
View 2 Replies View RelatedI have a table of data going back to 2007 that needs to be looked at on a monthly/quarterly/annual basis. I am able to filter the data when running a normal query by using
Between [Start Date] and [End Date]
in the Criteria section of the Date field. I now need to apply this same idea to a crosstab query. My current set up is:
[Gender]- Group By / Row Heading
[Plan Type]- Group By / Column Heading
[Pmt Amt]- Sum / Value
[Date]- Where / Criteria = Between [Start Date] and [End Date]
I am getting an error message that says:"The Microsoft Office Access database engine does not recognize '[Start Date]' as a valid field name or expression."Am I setting something up incorrectly or is it impossible to use input prompts in a crosstab query like this?
get the data from two tables on the basis of criteria...
I want to select the whole table1 which has 6 fields including Emp_id...
I want to select the single field from table2. field name is "Username" from second table2. will select the username on the basis of Emp_ID becaue both tables has same emp_ID.
It should be in order like. Emp id, Username, Startdate, Enddate, Hours, trainingNames.....
I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:
>=#06/04/2014# And <=#05/04/2015#
The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following
Format([Table].[Open Date],"yyyy-mm-dd")
The query then returns no data.
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
View 2 Replies View RelatedI am currently working on a project to develop an access database to manage a roster of calls to clients on a daily basis based on two general criteria:
1. Pre-determined days selected by the client. (e.g. Call Mon, Wed, Fri only. This can change as client requirements change.)
2. Ad-hoc changes based on the client’s circumstances. (e.g. No call from 27/7/2015 to 29/7/ 2015)
I have managed to successfully deal with the second of these with the following expression in a query:
CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]),"No","Yes")
However dealing with the first is a little more difficult to work out. I have tried a multivalue lookup field with multiple days selectable, but constructing an IIF query to deal with these multiple values is proving quite a challenge.
I am thinking of using a table with days of the week and a junction table to allow the multi-selection, but I may need constructing the relationships and the query here.
I have a form (frmMetrics) with 2 Combo Boxes: "Year" and "Quarter" These are just number fields (which might be my problem?)
I have a date/time field in a query, and I want the criteria to be based off the year and quarter selected in frmMetrics.
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 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
Hello,
I am importing a table into my access database from an oracle DB. The date field comes in as text (20060604 - for June 04, 2006). I am trying to build a delete query, macro, expression or anything else that searches all the records in that particular field and delete those particular records that have the actual month date. (ex:all the records that have 06 (for June) in the middle). Any help will be much appreciated.
Thank you
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","")))
Hi,
I have a crosstab query that groups by week to obtain columns for monday to sunday. Is there a way to obtain the actual dates that these columns represent?
Any help would be much appreciated.
I am currently using the =Weekday([Date]) formula to show which day of the week is it, but is it possible to show the actual day not a number?
Eg instead of "1" it shows "Sunday"
Cheers
I have a report based on a query and in that report I added a text box to be yes or no depending on 1) the number of days to complete and 2) the type of urgency. There's 3 different urgency levels so I was trying to create iif statements based on each one with the corresponding days using the OR operator between them like below:
=IIf([t_Days]<=2 And [Urgency Level]="Expedite (1-2 days)","Yes","No")_
Or IIf([t_Days]<=7 And [Urgency Level]="Normal (5-7 days)","Yes","No")_
Or IIf([t_Days]<=30 And [Urgency Level]="Low (14-30 days)","Yes","No")
Any 1 of those by themselves works fine but I need it based on all of that. So with that said, is there a way to do this? I tried using VBA code, but I think with the type of report I'm running that that wont work. It has a repetitive one line detail section with the data I'm trying to view.
How can I make that my access send an email to a user if there 7 days until en employee's birthday.
View 5 Replies View RelatedI currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
I have a query (Inventory Transactions Extended) in which i am trying to caculate current stock.
I have a form (Inventory Transactions Form) where i either add or remove Inventory Items.
I have used this statement to create a new field (Actual Quantity) to calculate current stock based on stock been added and removed:
Code:
Actual Quantity: IIf([Transaction Types].[Add/Remove]="Addition";[Inventory Transactions]![Quantity];-([Inventory Transactions]![Quantity]))
for some reason it does not work. It is not calculating current stock. If i remove stock it shows a negative amount based on the amount i removed, and when i add stock it shows a positive amount based on what i added
I have attached a sample.
[ATTACH][/ATTACH]
Sample.accdb
I am trying to find the best way to compare estimated expenses to actual expenses in Access. I am working with vessel and port call information, so each port call has a series of costs associated with it. I have two tables, each with multiple fields (around 100). Each of the fields contains a cost type that goes to a particular cost code. Each row of the table is associated with one particular port call. The call is estimated right after it happens, but the invoices are not all recieved for up to three months. Once all of the invoices for this port call have been recieved, we enter the actual expenses for the call. I am trying to find the best way to analyze the costs through finding the variances for one particular port call for each code, and also for multiple port calls to see which cost item has the largest/smallest variance for further investigation into how to estimate it more accurlately. I am thinking that I may have to move into Excel to do this, but would like to stay in Access if possible.
View 4 Replies View RelatedI have a query which includes a deposit field and a Transaction_Date field. I would like a cumulative deposit on each record (arranged according to date). The following is the expression I am using:
=DSum("[Deposit]","[myQuery]","[Transaction_Date]<=#" & [Transaction_Date] &"#")
I am getting completely nonsense values from the expression - some of the fields are blank (the first few fields) and then the values bear no relation to the deposits preceding them. I have tried all sorts of formats including using Format([Date], 'dd/mm/yyyy') on either side and then on both sides but with no effect.