Queries :: Limit Query Results To Numeric Value Of Text Field?
Aug 18, 2014
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.
I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
I have a query that calculates the number of games won and lost over the course of a season. I would like to export the query results as a report in Excel format.
What I need to do is take the won/loss field in the query WonLost:([GamesWon]&"-"&[GamesLost]) and export the result as text.
When I do it as above, the results in Excel are numeric.
Example: Games Won = 12 Games Lost = 3 it exports it as 9 (12 subtract 3).
HI! Can Anyone help me with this? I would like to put query results(one column) into one text field? Is it possible that results could be in such textfield separated? Thanks in Advance
I have an unbound form with a subform which is bound to a query. The subform prioritizes and individually lists all projects my company has on its agenda and the project’s corresponding cost. Is it possible to enter a dollar figure (which would represent our budget) on the main form and have the subform list only the top prioritized projects whose running total costs would fit within the budget figure stated? Any help would be appreciated as I don’t even know if this is possible. Thanks.
I am querying a linked ODBC table with a date column called "db_created_tms" that has dates in formatted like "2/25/2004 8:54:02 PM" , because it is a linked table I can't edit the data type. How can I limit my query results to the last 30 days.
My query without the date limit currently looks like this:
SELECT QBReportAdminGroup_v_txn_po_line.doc_num_h AS po, QBReportAdminGroup_v_txn_po_line.quantity_qnty AS qty, QBReportAdminGroup_v_lst_item.name AS REFERENCE, QBReportAdminGroup_v_txn_po_line.unit_price_amt AS cost, QBReportAdminGroup_v_lst_vendorODBC.name AS vendor, "" AS LOCATION FROM (QBReportAdminGroup_v_txn_po_line INNER JOIN QBReportAdminGroup_v_lst_item ON QBReportAdminGroup_v_txn_po_line.item_id = QBReportAdminGroup_v_lst_item.id) INNER JOIN QBReportAdminGroup_v_lst_vendorODBC ON QBReportAdminGroup_v_txn_po_line.vendor_id = QBReportAdminGroup_v_lst_vendorODBC.id;
Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No). So what im trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.
My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?
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 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 a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function to display the results onto a form called EnterInventory.
Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form.
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 a field called PRICE and obviously it is a NUMERIC field as it need to perform calculations such as calculating Total Quantity * Price etc. At the moment the PRICE is inputted manually by the User on a Form and when it has no Price it is simple left blank.
What I wish to include is that on the REPORT when the field is empty it writes the text FOC instead of leaving it empty.
Can you please anyone suggest a way of doing this.
On a form, is it possible to have #### appear in the form box when it loads and then the user would type a 4 digit number in place of ####.Also, for the table associated with that form, how do I limit account numbers to a 4 digit numeric value?
I am trying to build a calculated field that counts the number of times the letter E appears in 8 fields. the query currently looks like the attachment.
I need to have one more field , lets call it NetFlags, that is the number of times the Letter E is in the row for each Technician.
So for Brown, Tom NetFlags = 2, for White, Paul NetFlags = 4 and Wills,Fred = 0
I have several combo box fields in a bound form where they are selecting values from a list (values stored in a separate table) and then loading a number into the bound table field when selected.
How can I put a text prompt in these fields when loading the form which gets removed when focused and of course is not permitted to attempt a save into the bound numeric fields? I've done quite a bit of searching but can only find materials about doing this on bound text fields. I've also seen solutions using Nz which don't seem to work.
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
I have developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.
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.
