Queries :: Numeric Expression May Contain Too Many Complicated Elements
Oct 31, 2014
Once again I am stuck. Basically I have a query as followed:Current: Sum(IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]<=30,[dbo_GD-AgedDebtors]![Outstanding],0))I'm trying to show outstanding bills if the bill date is less than or equal to my date parameter. However, it keeps coming up "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)"
I have created a basic client contact database, and dependent upon whether the client is classed as say, as a decision maker, or as an influencer will affect when they should next be contacted. I have attempted an expression in my Date of Next Contact Field using my Classification field, and Date Last Contacted field. If there is no date in the date last contacted field, I just want the current date returned.
Please note I have never typed expressions in Access before, only Excel !! This probably explains why this isn't working!!!!. It says "the expression you entered contains invalid syntax (you may have entered an operand without an operator)". Can any expert tell me what I've done wrong??? Don't tell me, it's simple.....?! Here goes:
=IIf([Classification]="Decision Maker" And iif(IsNull[Date Last Contacted], Date(), iif([Classification]"Decision Maker" And iif(IsNotNull[Date Last Contacted], [Date Last Contacted]+30,” ”))))
When I can get this to work, I want to add to the expression for different classifications.
I am trying to create a database which crosschecks specifications against building codes. My problem is that it's not feasable to put all of the information about a Building into the Room table, though many codes require information about the Building in order to classify the room. How would I count (or list/get details from) a room in the Rooms table, for use in an SQL statement about the Buildings table.If there are up to 5 rooms in the building, do at least 2 rooms contain a fire escape?
My Rooms table contains information about if the room has a fire escape, as well as references its parent Building ID. How would I query the rooms to count the Rooms within all buildings, and subsequently query all Buildings which have less than 2 rooms with fire escapes?I need it to return a list of the buildings which do not conform to this code.
Now this is literally my second day of programming/working with Access, but I've yet to find an answer dispite hours of searching.I think I can adapt it for the 100+ building codes/references I'll need to implement. It would be perfect to find an SQL-only way of doing this, without going into modules etc...
Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)
There are two key date fields in the Issues table :FlagDate (the date an Issue was flagged by a user for investigation)
ResolveDate (the date said investigation was brought to a conclusion)
There is also a date field in the Comments table :UpdatedWhen (the date any given comment was added)
So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)
I need to incorporate a trend graph which will show the counts ofNew (i.e. new issues flagged as of each day) Cleared (i.e. issues resolved each day)
Updated (i.e. issues not yet resolved but updated each day)
Unchanged (i.e. issues not yet resolved and not updated each day)
Outstanding (i.e. all unresolved issues as of each day)
This is the SQL I've put together to get that table of information on which to base my chart :
Code: SELECT [tblBalances].[BalanceDate] AS AsOfDate, (SELECT COUNT([tblIssues].[IssueID]) FROM [tblIssues] WHERE [tblIssues].[Flag] = True AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
[Code] .....
The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.
The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.
I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)
I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)
I think I need to add a subquery within the subquery () to only return the latest comment as of the date in question - something along the lines of :
Code: (SELECT TOP 1 [tblComments].[UpdatedWhen] FROM [tblComments] WHERE [tblComments].[IssueID] = [tblIssues].[IssueID] AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate] ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen
But how to do this, nor if it is even feasible in Access to begin with.
I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.
The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.
Code for automatic number generation is:
Dim dbs As Database, rst As Recordset, Response Set dbs = CurrentDb Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
[Code]....
My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?
I have tried to use a bit of code so return the Last Year and WeekNo in my database, to select some records but�. It currently fails, I think this because it returns the values as text and will then not compare to a numeric field. Is there an easy way to change this code so it returns numbers.
I've put in -100 as an arbitrary never possible number. Obviously, this does not return Null values.
Question: How can I return both Null & Numbers when the Textbox in the form is left blank? in all the columns. Currently, I am not getting Null values
Code: >IIf(IsNull([Forms]![MainForm]![Criteria]),SHOW ME EVERYTHING INCLUDING NULL VALUES & NON-NULL NUMBERS,[Forms]![Material Finder]![txtPS])
or in other words
If the textbox is blank, show me all the data available, else if it is not blank then show me only the values that are greater than the number entered in the textbox from within column A, B, C, D ....
I have a Dlookup which returns the correct value but returns the text of the instead of a numeric value. I have triple checked that the fields are all numeric. I suspect there is something wrong with my Dlookup syntax as it is my biggest weakness. In the following syntax what could be wrong?
DLookUp("SSER","TABSSS",[grosspay] & " Between [TABSSS].[Minrange] And [TABSSS].[Maxrange]")
I am making a report off of a query. The report is returning values from a Option Group. 1=Yes, 2=No and 3=N/A. The report returns the numeric values and I want the value labels instead. So, I created a column in the query for exp1 which looks like the following:
I have an ms access Database(2013 version). There are about 10000 records. There are some columns with field property of "short text" but contains the values like that 0.4,7, 9.0 etc I would like to convert the "short text" into "double" without loosing information.
I have a text percentage that reads 28.0%. I want to convert to a numeric percentage that reads either 0.28 or 28.0%, preferably the latter. The Val function returns an error.
I have been downloading .csv files from a construction website that we use to following projects as they develop. I import the file into Excel and then want to bring it into my database. The issue is with two fields I have that are numeric, however, when downloaded into Excel, the information in the two fields now have an (')added to the beginning and end of the string, i.e. and the second field has an (') and (-) to the data.
'201400409710' '201300697683'
The second field is a zipcode field that comes in like this:
'14063-1127' '14222-1004'
I would like to design a query to strip the ' from each field as well as the (-) in the zipcode field.If I have to write 2 separate queries that is fine.
I have a query which selects a material ID and material name from one table and the associated manufacturer, supplier, and packaging type from three other tables. Some of the manufacturer, supplier, and packaging data were imported from an Excel spreadsheet and did not have data for those fields, so those fields are blank. When I run the query, I only get the records which have all fields filled out. How can I get the records where the material ID and material name are filled in, but the manufacturer, supplier, or packaging type are blank? Here is the query I'm using currently:
Code: SELECT tblMaterialSpecifications.ID, tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply, tblManufacturer.Manufacturer, tblSupplier.Supplier, tblPackaging.PackageType FROM tblPackaging INNER JOIN (tblSupplier INNER JOIN (tblManufacturer INNER JOIN tblMaterialSpecifications ON tblManufacturer.ID = tblMaterialSpecifications.ManufacturerID) ON tblSupplier.ID = tblMaterialSpecifications.SupplierID) ON tblPackaging.ID = tblMaterialSpecifications.PackagingID WHERE (((tblMaterialSpecifications.ActiveInactive)=-1)) ORDER BY tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply;
i have to process 3 upcoming months bills .. say i want to show in query like this bill for Nov, Dec, Jan based on the issue date i mentioned earlier and the months to be find from the numeric month field like 3 i mentioned which mean 3 months ahead from issue date.
I have a table that has several fields including CallID (autonumber) and SKU (text)
SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...
Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:
Code: SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU FROM Calls GROUP BY Calls.SKU HAVING ((Not (Calls.SKU) Is Null)) ORDER BY Count(Calls.CallID) DESC;
The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.
Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.
Have a Make table query that needs to create (add) several new fields where each field must be numeric design.
Have tried:
Score1: Not Null - does not seem to work (results in a Binary field) Score1: 0 - which does give me the numeric field designation but every field in table contains a 0.
Would like to show Blank field (makes data input easier at a later time) but still have the Numeric designation.
Hello can anyone help me with a code or application example of how to hide all db elements. I have a database and i want only a main form to appear for users and tables, code, etc to be hidden.
I was hoping someone could let me know how I can convert my MS ACCESS database file (.mdb) so that when user(s) run/open the database, they cannot see, nor have access to the tables (which are linked from a separate DB with just the tables), forms, queries, etc... (the elements). Right now, you can open the file, the switchboard runs, but you can still see and navigate to the tables/forms/queries seen in the background.
Is there a quick and simple way to turn this into an executable-type file where you cannot see the elements in the background?
I wonder wether it is possible to copy elements from one database to another. I have two copies of the same database. One for development and one for user access. If i for instance would like to copy a form from the development database to the other, how can this be done?
Hello to all. The problem that I am faced with is that I have a master list with all the current users and an updated list with all the current users and new users. What I have been asked to do is to compare the information in these two tables and have been given the following steps. 1. If name (surname, first name) exists in both lists do nothing.
2. If name exists in Update list but not in Master list then insert a blank row in the Master list and add the following Update list fields to this row: ¨ 3. If name does not exist in the Update list then remove the entire row that contains that name from the Master list. 4. If 2. condition ("name exists in Update list but not in Master list") below applies, then insert the blank row with some kind of marker (e.g. "*") in a column on the far left. This will allow us to distinguish between:
So that is what I have to do can someone please tell me what a quick to do this in access would be. Thanks
I have a form with a couple of sub forms, one of the sub forms only has a check box and a txt field and both are required before moving on to another record.
I have this following code that works partly, but it only works if one of the fields have info entered. It doesn't however stop the user from tabing through the sub form without entering 'any' data in either one.
---------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer) If Trim(Me!txtContactAgentName & "") = "" Then 'Validate Name Field MsgBox "Gotta Enter Your Name!" Me!txtContactAgentName.SetFocus Cancel = True ElseIf Me!ynCCContactLogLeft <> True Then 'Validate CheckBox MsgBox "Gotta Have a checkmark in Logged!" Cancel = True End If End Sub --------------------------------------------- Also the below throws up an error when I know it's got all the correct information. ------------------------------------ Private Sub txtContactAgentName_LostFocus() Forms.frmAllCustInfo.SetFocus Forms.frmAllCustInfo![AddNewCustomerRecord].SetFocus End Sub
