Execute Expression In Query If Not Null
Jun 28, 2006
Can anyone help...
I am writing a query in which, amongst other things, I also want to perform a dateadd calculation.
I have the following
expr:dateadd("d", [lastduration], [lastlandingslot])
This fails to run as the expression is too complicated, I suspect this is because a lot of the records will not have a [lastduration] and its therefore null.
I have ran the code below sucessfully.
expr:dateadd("d", 1,[lastlandingslot])
How can I get this to execute only if the [lastduration] is not null??
Tried to search but network is really slow
View Replies
ADVERTISEMENT
Apr 25, 2014
A new query I am building, after entering an expression, gave the " You tried to execute a query that does not include the specified expression.." error. The new query is based on and uses expressions from another query. The error message listed the expressions from the query upon which this query was based. Copying in all the fields referenced in the error message did not work.
Here is the SQL code, the offending expression in red:
SELECT [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber, Count([Test Grade Results].[Test Number]) AS [CountOfTest Number], Avg([Test Grade Results].[41Grade]) AS AvgOf41Grade, Sum([Test Grade Results].Wghtd41) AS SumOfWghtd41, [GPA]=Sum([Test Grade Results]![Wghtd41])/[Test Grade Results]![CuumWeightDivisor] AS Expr1FROM [Test Grade Results] INNER JOIN [Test Parameters] ON [Test Grade Results].[Test Number] = [Test Parameters].TestNumberGROUP BY [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber;
Wghtd41 is the expession that is included in the error message.
View 5 Replies
View Related
Apr 18, 2008
This is the SQL from a query for a Payroll report. It was an already existing report, so I'm trying to modify it to meet the business's needs. The problem is that there are multiple entries for WOLabor.Hours for a given day. These entries come from the invoicing program when a mechanic's hours are logged for billable time. So there may be 3 hours here and 1.5 hours there...but all for the same day. Payroll is just concerned with total hours worked...so I'm trying to combine all the hours for a given day into one variable or something and use that. I know this is long, but apparently a lot of fields are involved:
THIS GIVES ME NO ERRORS:
Code: Original - Code SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode; SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode;
When I make the following changes to the SELECT section, I get this error:
You tried to execute a query that does not include the specified expression 'LastName' as part of an aggregate function.
Code: Original - Code ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ... ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ...
Any insight is greatly appreciated.
View 6 Replies
View Related
Aug 21, 2014
I am getting the following error message in strsql:You tried to execute a query thats does not include the specified expression 'ScanDate=20140730 and SCanhour=8' as part of an aggregate function in the following strsql.
Code:
Call func1("Z", 8)
Public Sub func1(b As String, a As Integer)
strsql = "SELECT Count(BatchNo) AS CountOfBatchNo, Sum(Envelopes) AS SumOfEnvelopes, Sum(Cases) AS SumOfCases, Sum(Pages) AS SumOfPages, ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') AS QueueNo " _
& "FROM jabberwocky " _
& "group By ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') " _
& "HAVING ScanDate=" & J & " and Scanhour=" & a & ""
End SUb
View 8 Replies
View Related
Feb 28, 2008
I have a combobox iBrand on a form frmFoodSub with the following query used to get its values (based on the imput of another combobox, iProduct, linked to the field Product in the table Products.) This code was generated by query builder, not myself:
Code:SELECT Products.BrandFROM ProductsWHERE (((Products.Product)=[Me]![frmFoodSub].[form].[iProduct]))GROUP BY Products.BrandORDER BY Products.Product;
And I keep getting this error message when the combobox is in focus on my form:
You tried to execute a query that does not include the specific expression 'Products.Product' as part of the aggregated function.
I have no idea what started this error. It was all working fine and then all of a sudden the error started appearing and I can't remember making any changes to cause it.
View 2 Replies
View Related
Mar 21, 2013
I am receiving this error when i try to run a query:
"You tried to execute a query that doesn't include specified expression as an aggregated function"
There are two images attached which show the SQL view, the error and the design view.
View 11 Replies
View Related
May 28, 2005
I have a query with Product, Product Directorate, Date Received, Date Delivered, and Date approved fields.
Product field criteria: "NAT CONV"
Product Directorate criteria:
([forms]![frmWhatDates].[cboPD]) or ([forms]![frmWhatDates].[cboPD is null)
Date Received criteria: (Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd]) or(([forms]![frmWhatDates].[txtRecvStart] is null) or ([forms]![frmWhatDates].[txtRecvEnd] is null))
and so forth
When I closed my query, and opened it up again it looked bizarre. Because of the Is Null it added criteria multiple times.. I don't know what's going on but this makes it hard if i want to edit it in the future.
This is what it looks like:
http://img.photobucket.com/albums/v332/youmnac/qryND.jpg
Why did Access do this to my query? Is there anyway I can make this simpler and how?
What I have is a form that filters records according to what is input and it generates a report.
If that date fields are left blank.. I want it to display all the records
http://img.photobucket.com/albums/v332/youmnac/form.jpg
View 6 Replies
View Related
Aug 16, 2006
Hi. I am using Expression Builder to total 3 columns for a query.
Total: [BaseTotal]+[ClickTotal]+[Adj Total]
The [Adj Total] is sometimes empty, and when this happens, Total is empty even though there are values for [BaseTotal] & [ClickTotal]. How can I make the Total show up regardless of whether any of the three items used to calculate it are Null?
Thanks
View 3 Replies
View Related
Aug 14, 2014
I have an expression in my query and i'd like to return a value of 0 is the expression yields a null value. Here is the expression i have:
IsNull(Sum(([qryTime].[hours]*[Rate])+([qryTime].[minutes]*[Rate]/60)+([qryTime].[seconds]*[qryTime].[Rate]/3600),0)
I'm getting an error "the expression you entered has a function containing the wrong number of arguments". How can i resolve this?
View 14 Replies
View Related
Dec 19, 2014
In Expression Builder, I have the query(in the Criteria row) set to run based on values in different fields on a form. When one of the fields is empty however, the query will not return any records.
How can tell Access to ignore the fields when those fields on the form are empty and only look at fields that are not null.
View 2 Replies
View Related
Apr 4, 2014
I am trying to use the expression below to transpose strings of 4-6 numbers (140504) to a string of 8 numbers with "20" in front (20140504). Some of the [Receive_Date] fields are empty or are 0, and in those cases, I need the expression to return a null value.
New_Receive_Date: IIf([Receive_Date]=0,"",IIf([Receive_Date]="","",Format([Receive_Date_YMD],"000000")+20000000))
The Format(...) part of the expression works fine on its own, but I am new to dealing with null values in my expressions, and when I add the rest of the expression, all '#Error' values are returned.
View 2 Replies
View Related
Mar 10, 2014
I'm trying to create an expression to calculate percentage.
The fields, Value A and Value B might both have null values, but I do not want Access to treat nulls as zero (as in the Nz() fn). How do I get the expression to exclude any nulls and not calculate for those rows, but set the calculated value for a row with null A or B to zero.
I want it to set no calculated value to zero so I don't get overflow errors and can therefore order the data by the calculated field.
View 1 Replies
View Related
Jul 3, 2007
I'v defined an update query as a string in my VBA code.
Then I execute the update query with : CurrentProject.Connection.Execute
however, I want the query out of the code and into the query folder where the other query's are.
How can I execute the query from within the VBA code when I do this ?
View 1 Replies
View Related
Dec 12, 2004
Can I execute a query I have stored in my Access database by using ASP from a webpage?
Thanks,
Been at the for 3 hours.
View 1 Replies
View Related
Sep 27, 2005
Can I keep my queries in the C drive and execute it against tables that are in the LAN network drive ? Right now the queries are also in the netowrk drive. So each time when I refresh the data into C drive, I have to import the queries too.
Is it possible to keep the queries in the C drive and execute against data in the netowrk ? Please let me know.
Thanks in advance.
View 9 Replies
View Related
Mar 11, 2008
Is there a way to execute two statements in one query in access? I tried the following but received the following: "Characters found at the end of the SQL statment".
Here is what I have in my qryUpdateRecords file:
"UPDATE dbo_CE, tblCE_Details SET tblCE_Details.LIC_NUMBER = [dbo_CE].[LICENSE_NUM], tblCE_Details.Credit_Hours = [dbo_CE].[CREDIT_HOURS], tblCE_Details.Course_Sponsor = [dbo_CE].[COURSE_SPONSOR], tblCE_Details.Course_Name = [dbo_CE].[COURSE_NAME], tblCE_Details.Auth_Code = [dbo_CE].[AUTH_CODE], tblCE_Details.Category = [dbo_CE].[CATEGORY], tblCE_Details.Course_Date = [dbo_CE].[COURSE_DATE], tblCE_Details.ImportDate = Now() WHERE ([dbo_CE].EXPORT_TIMESTAMP IS NULL ) ; UPDATE dbo_CE SET([dbo_CE].EXPORT_TIMESTAMP = NOW() WHERE ([dbo_CE].EXPORT_TIMESTAMP IS NULL);"
Thanks,
CRhodus
View 3 Replies
View Related
Oct 21, 2005
I have a stored query in my db. The query gets the recordcount of all the items in a table.
In my VBA, rather than code a new db conection, set a recordset etc, I want to execute the query using DoCmd
=======
DoCmd.OpenQuery stDocName, acNormal, acEdit
=======
The above line displays the results of the query.
What I really want is to run the query and store the recordCount in a variable - without displaying the results.
Can this be done?
Thanks all
View 2 Replies
View Related
Oct 9, 2006
SELECT FCST.REF_DT AS [Date Forecasted], ACTUAL.REF_DT AS [Date Shipped], FCST.QT AS [Qt Forecasted], ACTUAL.QT AS [Qt Shipped], FCST.PART_NR, FCST.REGION, FCST.TYPE, IIf(FCST.QT=0,'',formatpercent((ACTUAL.QT-FCST.QT)/FCST.QT,2)) AS MPE
FROM TBL_FCST_WKLY AS FCST, TBL_ACTUAL_WKLY AS ACTUAL
WHERE (FCST.TYPE=ACTUAL.TYPE) AND (FCST.REGION=ACTUAL.REGION) AND (FCST.PART_NR=ACTUAL.PART_NR) AND
FCST.REF_DT = (SELECT DISTINCT DATEADD("ww",-LT.LEADTIME,ACTUAL.REF_DT) AS FORECASTED FROM TBL_FCST_WKLY FCST2 INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST2.PART_NR AND LT.REGION=FCST2.REGION WHERE FCST2.PART_NR=FCST.PART_NR AND FCST2.REGION=FCST.REGION) AND
FCST.MONTH_DT = (SELECT DISTINCT DATEADD("ww",LT.LEADTIME,FCST.REF_DT) AS FORECASTED FROM TBL_FCST_WKLY FCST2 INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST2.PART_NR AND LT.REGION=FCST2.REGION WHERE FCST2.PART_NR=FCST.PART_NR AND FCST2.REGION=FCST.REGION);
i am querying two tables TBL_FCST_WKLY which has 29500 records and TBL_ACTUAL_WKLY which has 798222 records.
When i run this query it sites there forever. Is it because i dint optimize my query or is it because the number of records is too much for access? can someone help plz.
thanks
View 9 Replies
View Related
May 26, 2006
Because my tables were already large I split them up by environment to speed up data maintenence and retrieval. Thus I have table names which contain the environment as part of the table name (ex: tbl_TEST_MyTable, tbl_PROD_MyTable). Once the user sets the environment they want to use (by clicking on an option button) I update the form's recordsource to the appropriate table. My form has 5 subforms within it.
Even with the split loading the form was taking a long time because some of the queries the subforms are based on are joining tables so that they can be linked (parent/child) to the form. I created indexes to speed up the data access which helped a little but it was still running too slow. So, I attempted to change the subform's queries to only join the necessary data by re-creating the queries each time the user changes the form's current record.
For example, what I had originally was taking too long:
SELECT DISTINCT tb.*, pt.CollId, pt.ProgName, pt.QueryNo
FROM tbl_TEST_SysTables AS tb INNER JOIN tbl_TEST_Plan_Table AS pt
ON tb.Name = pt.TName
ORDER BY tb.Name;
and the parent/child link fields are CollId, ProgName, and QueryNo.
This is the change I made:
SELECT DISTINCT tb.*, pt.CollId, pt.ProgName, pt.QueryNo
FROM tbl_TEST_SysTables AS tb INNER JOIN tbl_TEST_Plan_Table AS pt
ON tb.Name = pt.TName
WHERE pt.CollId = 'BATCH_COLLECTION'
AND pt.ProgName = 'PROGRAM1
AND pt.QueryNo = 123
ORDER BY tb.Name;
where the pt fields are changed dynamically each time the user navigates to the next record in the form. This resulted in much faster access time; however I discovered that the form was always displaying the query that existed before the form was opened. I have not been able to figure out how to get the subforms to display the latest created query.
While debugging I discovered that the form's OnCurrent event was being executed 3 times before the form is opened. I don't know what I'm doing that's causing this.
I also discovered that the subform events are executed before the form's events, so I think that's why the latest query isn't being used. I attempted to requery the subform in the form's OnCurrent and OnOpen event, but to no avail.
View 4 Replies
View Related
Mar 21, 2014
I have a list box (1L) in a form (1F) that list member IDs from a table. Then I have a query (1Q) that use (1L) member ID to get data from a table another related table. When I change the 1L and run the query 1Q the results is correct. So I added a new list box (2L) in form 1F that show the results of the query. When I open the form 1F the 2L is populated correctly by the query. Here is my problem. I need to have list 2L update when I change 1L. Here is what I did. I created an On Change event for 1L that has a code Me.2L.Requery, but nothing happens.
View 1 Replies
View Related
Apr 1, 2015
UNION Query will not execute properly. Remove "WHERE" statement query runs.The WHERE statement is used to locate all duplicate records, based on serial number, in a table. Also, it excludes certain data contained in two of the four tables. There are four separate queries for four separate tables. They all work until incorporated into a UNION QUERY.
Question, is there another way to structure the "WHERE" statement for this UNION Query and achieve the same results?
View 6 Replies
View Related
Mar 21, 2014
I have a form which I want to load before it starts up a vba function. How can I do that? Right now I use form_load(), but the problem is that, it loads the msgbox before loading form.
Code:
rivate Sub Form_Load()
Dim sFolderSpec As String, sCSVSource As String, dFileDate As Date
sCSVSource = "MDM_Syndicator.csv"
[Code]....
View 1 Replies
View Related
Apr 18, 2006
Hello all,
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
Thanks.
View 4 Replies
View Related
Jan 23, 2006
Just had a query in my mind. I have a table master_data with 16 columns. so please tell me which one of the following would less time to execute.
1. select * from master_data;
2. select cust_name from master_data;
From the above example, i am interested to know, does number of parameters selected in the query effect the execution time.
View 2 Replies
View Related
Jun 17, 2007
Hi all
I'm stuck on the following SQL statement. It looks OK to me... but there must be something wrong with it because it doesn't execute.
I've tried the runSQL command and I've tried currentproject. connection.execute as well. Both fail to execute... it has to be the SQL string below.
Thanks for any advice.
SQLImportPE = "UPDATE data INNER JOIN data_import ON data.ID = data_import.ID" & _
"SET data.pe1 = data_import.pe1, data.pe2 = data_import.pe2, data.pe3 = data_import.pe3, data.pe4 = data_import.pe4, data.pe5 = data_import.pe5, data.effort_pe = data_import.effort_pe" & _
"WHERE (((data_import.pe1) Is Not Null));"
View 10 Replies
View Related
Aug 12, 2015
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
View 4 Replies
View Related
