Like Operator In Calculated Query Field
Aug 1, 2007
Good afternoon,
I have a field that contains semester info ie: F07 Sep.
What I am trying to do is using an if statement to test for which month and returning a section number based on the month. I can make it work using an exact match but am trying to make it work on the partial match "Sep" so I don't have to change it next year as F07 stands for Fall 2007
This is what I've tried and various combinations.
Section1: IIf([tblOnlineCoursesDetails]![Semester]="* Sep","70","")
Having trouble getting this to work.
Any help or suggestions would be appreciated.
Thx
Jerry
View Replies
ADVERTISEMENT
Jan 9, 2014
I tend to work with large amounts of data (extracts from company systems) and I create a lot of Access/VBA based tools to automate processes.
I have an annoying error which has always appeared but I don't understand the root cause of it.
When viewing a query, if I filter, I get an error message pop up (though after clicking through the error I can still use the filter function):
"syntax error missing operator in expression 'name of field'".
This seems to happen when I add several calculated fields. Here are some examples of the conditions and calculated field formulas I'm using in this current one:
Conditions:
<>"CINEMATIC" And <>"SFX"
Not Like "*_ZZ*" And Not Like "*test*" And Not Like "EP_*"
[Forms]![FRMscriptPrintReview]![selectLangCombo]
Calculated Field:
Audio Ref Guide: IIf(Left$(Right$([TBLdata]![Script Resource],2),1)<>"_","?",Right$([TBLdata]![Script Resource],2))
I get the impression that its more of a bug with Access as the formulas aren't complicated really but need confirmation on this and if there is a way I can avoid it.
View 2 Replies
View Related
Mar 11, 2014
I have a query which contains figures. i have a search form based on this query and need to search using comparison operators such as <10000 or >500.
Is there a way to pass these value to criteria field in query ?
View 14 Replies
View Related
Nov 23, 2007
I have an inventory transaction file that has quantity on hand as a calculated field. I am trying to add this unbound field to a report using a query but I can't figure out how to add my quantity on hand field to the query since it is a calculated field and not a bound control.
Any help would be appreciated.
Thanks
darrrellx
View 1 Replies
View Related
Nov 15, 2007
I can't seem to find a problem like this already answered....
I have these two calculated fields:
dtmNPacketDue: IIf([strRevTypeNIRB]="Full" Or [strRevTypeNIRB]="Termination(Full)",[dtmNIRBMtgDate]-28,[dtmNExpDate]-28)
dtmCOIDueDate: IIf([strRevTypeNIRB]="Termination(Full)" Or [strRevTypeNIRB]="Termination(Expedited)","N/A",[dtmNPacketDue]-42)
When I use the one in RED in a calculated field in a new query, things are peachy.
But when I use the one in BLUE in a calculated field in a new query, the message is #Error
My Calculated field (which produces an #Error) in the new Query is:
2ndCOINotification: [dtmCOIDueDate]-14
(if I insert [dtmNPacketDue] instead...it works
Perhaps my problem is that dtmNPacketDue (a calculated variable) is part of the BLUE calculated variable....??
Any ideas greatly appreciated....
View 4 Replies
View Related
Apr 24, 2006
Hola folks.
I am trying to create a calculated field in a query that subtract 2 fields within the same query.
That is, i have a FiscalWeekStart and FiscalWeekEnd pulling from a table. I created a CountofWeek field that count the weeks between the FiscalWeekStart and End using "DateDiff("w",[FiscalWeekStart],[FiscalWeekEnd])".
I also have a HolidayWeek field that pulls from a table using a
Count expression in the query.
My goal is to subtract the CountofWeek and the Holiday week calling it NonHolidayWeek using "NonHoliday Week: IIf(IsNull([CountofHolidayWeek]),[CountofWeek],[CountofWeek]-[CountofHoldayWeek])"
BUT IT IS ASKING FOR PARMETERS ON THE "CountofHolidayWeek".
Thanks for the help in advance.
TukTuk
View 1 Replies
View Related
Jul 21, 2006
I have a query that draws from two tables, and the field in question looks like this:
X: [TableData]![FieldA]*[TableNumbers]![A]+[TableData]![FieldB]*[TableNumbers]![B]
It all works fine and dandy, but once I set it to sort by this field and run the query, it gives me the parameter prompt, asking me to enter the Parameter Value of FieldA and then for FieldB.
Is there a work-around for this within the query?
The only other solution I have in mind is making another table from this query, and then creating another query just for sorting said table, but that seems inefficient at best.
View 2 Replies
View Related
Nov 8, 2006
I have the following calculated field in a query:
LoanNo:IIf([Stats].[LoanCnt]>1,[Stats].[LoanCnt] & " Loans",[Loan].[LnNo])
Within its query, it correctly displays the loan number associated with a particular loan.
However, when I run a second query
SELECT LoanNo
FROM qryLoanInfo
it comes up blank for every record (other fields are OK)
Does anyone have any idea what might be happening?
View 2 Replies
View Related
Dec 8, 2006
Hi,
I have a calculated field in my query called "outstanding".
this is how the query looks:
Expr: SubFormTotal
[qty]*[price]
Discount
...[Discount]
Expr:Total
.[SubFormTotal]-[Discount]
Deposit
.[Deposit]
BalanceToDate
.
[B]Expr: Outstanding
..[Total]-[Deposit]-[BalanceToDate]
PROBLEM: i want to search for all the "amount outstanding" that >0 HOWEVER when i put that as the criteria it asks me to enter values for other calculated fields
what am i doing wrong and how can i solve it?
thanks
View 7 Replies
View Related
Apr 17, 2007
I hope someone can help as I am stumped at present. I have created a calculated query using dateadd. I have a table that has date fields, ServiceContractAgreed and ServiceFrequency. The service frequency field holds a value of 3,6,9 or 12 in months. Using dateadd I can add the months to the ServiceContractAgreed field in a field called NextService, ie =IIf(IsNull([ServiceContractAgreed]),"N/A",DateAdd("m",[ServiceFrequency],[ServiceContractAgreed]))
This works fine but what I want to do now is create a parameter query to search the NextService field, using the between operator to search between dates. I've created a new query to do this but get every record back, no matter what dates I put in. It's probably something really simple to do but I'd be really grateful for any advice!
Cheers
Dave
View 2 Replies
View Related
Mar 23, 2015
This is not the normal calculated fields in query's. What I want is different, I want it to be like
IFF(ShowID="A",[TotalSales],0)
Thing I have is, I want to know all the Total sales in on row that has Show A in it and then Total sales for Show B. Problem is, I have over 130 shows. How would do that?
View 6 Replies
View Related
Nov 25, 2013
I Have some calculated fields in a query and want to update it in the table .So,is there anyway to store these fields.Otherwise can i store this Query data every month in database so that when i re-run the query the previous month data will not be affected.
View 1 Replies
View Related
Sep 10, 2014
I am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to
AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))
This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA
View 14 Replies
View Related
Nov 13, 2014
So I have the following query field which calculates another field.
How do I Filter the records in this calculated field to only return TRUE, as if I put "TRUE" in the Criteria for this field (or anything at all) then a parameter message box pops up asking for [Balance].
Code:
Balance1: IIf([Balance]<>0,"TRUE","FALSE")
View 8 Replies
View Related
Mar 17, 2014
I have a
Order Table:
Order ID(Autonumber)
Client ID(Text)
Client Name(Short Text)
Install Gross Rate (Currency)
Install Discount Given in % (Number)
Install Discount Given in GBP (Currency)
Then I designed a Query to calculate the Install Net Rate
Query
I selected
Client ID
Client Name
Order ID
Expression: [Install Gross Rate]*(1-[Install Discount Given in %]/100)-[Install Discount Given in GBP]
I run the query, but only ONE order calculated correctly, rest of Orders returned blank row.
View 10 Replies
View Related
Aug 30, 2013
I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example
SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot
FROM EmpTable S1
WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);
Can I not select a value that has been calculated this query?
View 5 Replies
View Related
Nov 20, 2011
I'm trying to make a calculated field in query that will number duplicates, not count them.
For example, I have a table with the following:
Joe
Joe
Joe
Mary
Mary
Henry
Dave
Dave
Dave
Dave
I want the query to display:
Joe #1
Joe #2
Joe #3
Mary #1
Mary #2
Henry #1
Dave #1
Dave #2
Dave #3
Dave #4
I'm fairly new to to Access and I've been trying to get that to work for awhile now.
View 1 Replies
View Related
Aug 4, 2015
I have a very basic query with the following calculated field in it:
ProjRevNRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentNRC]*[Rev Flow Through],0)
I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places and it worked fine.
But when I based a crosstab query off the query that contained the above calculated field, I cannot seem to get the numbers to format correctly. 1231313.424 is shown instead 1,231,313 and I don't have a line in the Properties window to even change the decimal places. It doesn't recognize when I change the format to Standard. I have tried using Round([ProjRevNRC]) which gets rid of the decimal places as desired but does not show commas.
I'm sure it's a simple part of the Round expression that I am missing but nothing has worked.
View 4 Replies
View Related
Nov 12, 2014
I am looking for a way to add a calculated field to the end of an existing query using VBA. Is there an easy way to do this?
The data I receive from an external supplier shows monthly data split by column with a new column added in each month. I then need to reflect this by adding a new column to the end of the query. It is currently a manual tweak, but I want to automate this with code.
View 6 Replies
View Related
Jul 17, 2014
I have query with a calculate field to finds the next service due date but I'm having a problem getting it to only show services due dates in the next 30 days.for some reason I cant add a criteria date()-30...I have to calculate the next service it takes service intervals from maskservicemonths field then find the last service date and generates the next service due date NextService: DateAdd("m",[MaskServiceMonths],[FindLast]).
View 14 Replies
View Related
Apr 25, 2005
I have a table with numerous fields including timeIn and timeOut field (these are date/time format). I need to find out how many people were clocked in during each operating hour (based upon their time in and time out). How can I run a query that will allow me to use the times in and out to 'flag' each hour that each person was on duty (a new field for each hour) so that I can run a count on the completed data for every hour?
View 4 Replies
View Related
Mar 28, 2015
I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:
Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)
100,000 1,000 Sum of total bonuses/salary*4%
90,000 900
How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?
View 13 Replies
View Related
Sep 4, 2007
Hi All
Can't understand why the following doesn't work when changing the operator.
Using the "<>"
SELECT qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift FROM qryColleagues, tblTempNew WHERE (((qryColleagues.WCN)<>[tblTempNew]![WCN])) GROUP BY qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift ORDER BY qryColleagues.ClgName;
the particaular part is (((qryColleagues.WCN)<>[tblTempNew]![WCN]))
it works if the operator is "=" but not "<>"
The thing is I'm sure this was working fine.
edit: I have just noticed that this works if there is only one record in the tblTempNew table. As soon as I put 2 records in there it does not work.
Any ideas.
Kev
View 7 Replies
View Related
Nov 11, 2007
Hello everybody,
I am having trouble with this criteria on one field in a query
<>19 Or <>15 Or <>14 Or <>16 Or <>17 Or <>1 Or <>3
The query is still showing records that contain 14, 3 and 16.
Basically, I don't want to show records containing 19, 15, 14, 16, 17, 1 and 3.
Thanks
View 3 Replies
View Related
Jul 3, 2006
I have two tables in the first table there is an outline number and the second table there is a task id (both are text strings, i.e. outline number and task id would be something like 8.15.3.1)
I want a query to return all the results in the first table that are not equal to the second table
I have tried the following but it returns all 54 records
Code:SELECT DISTINCT RawData.TeamLeadNumber, RawData.Category, RawData.OutlineNumber, RawData.OutlineDescription, RawData.Start, RawData.Finish, RawData.Milestone, RawData.PercentComplete, RawData.DeliverableDesc, RawData.ReleasePeriod, RawData.OutlineNumber2, RawData.OutlineDescription2FROM RawData, SharePointDataWHERE (((RawData.OutlineNumber)<>SharePointData.TaskID));
I have tried changing the where clause to have it as
Code:WHERE ((RawData.OutlineNumber)<SharePointData.TaskID) AND ((RawData.OutlineNumber)>SharePointData.TaskID);
but then no data is returned, and I know that of the 54 records 21 of them are the same so I should be seeing 33 records
View 1 Replies
View Related
May 30, 2007
Hi everyone,
I have a problem with a query.
Just for info, I export some table from SQL to access. The same query in SQL work fine but when I try in Access come out this error:
------------------------------------------------------------------
Syntax error (missing operator) in query expression 'tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid'.
------------------------------------------------------------------
The query:
SELECT tbl_Style.STYLECODE, tbl_Info_1.Infodata AS [Fabric Type]
FROM tbl_Style INNER JOIN
tbl_Info ON tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN
tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid
I appreciate your help.
Regards,
Tombino
View 3 Replies
View Related
