Queries :: Returning Set Of Numbers From Custom Formatted String - Incorrect Use Of Join?
Mar 20, 2015
I suspect my design is flawed
Code:
SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;
The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?
View Replies
ADVERTISEMENT
Jul 6, 2006
Greetings all:
I have a database tracking customers and inventory, and I've tried to add in a feature where I can meet customers needs with future inventory. Key to this process is three tables:
tblCustomer - containing my customer information
tblInventory - with inventory data including a part number
tblNeeds - a table I've created for this purpose, containing CustomerID and Part Number.
Here's the premise: when speaking with a customer, I may learn they want a product I don't currently have in inventory, or may have never had in inventory, but that I may get in the future. I do this "on demand" in the form of a report, and it works really well ... except:
I have found that several similar products from the same supplier have similar product part numbers. What I would like to do is be able to match my query on a fractional part of the part number. In all cases, the part number would be the same over the first six characters (in a text field). Any ideas how I might do this? In my current query, I use a join, but I can't do that for fractional strings....
I've attached the query I am currently using (and running a bound report from this query):
SELECT DISTINCT tblCustomer.CustID, tblCustomer.CoName, tblInventory.ProductDescription, tblInventory.ProductPartNumber, tblInventory.UnitsAvailable, tblInventory.ProductID
FROM tblInventory INNER JOIN (tblCustomer INNER JOIN tblNeeds ON tblCustomer.CustID = tblNeeds.CustomerID) ON tblInventory.ProductPartNumber = tblNeeds.PartNo
WHERE (((tblInventory.UnitsAvailable)>0));
And what I want to do is match the tblInventory.ProductPartNumber on the tblNeeds.PartNo by matching only the first six characters....
Thanks in advance,
Don
View 3 Replies
View Related
Jul 31, 2013
I have a parameter query that contains information on a list of people and contains 3 checkboxes: alumni, parent, business
In this query, I am trying to use parameters to filter the results based on these three fields i.e.
true, false, true would return all records where either alumni, business or both are true, and parent can be either true or false.
false, true, false would return all records where only parent is true, and the other fields do not matter.
View 2 Replies
View Related
Jul 19, 2007
I'm trying to create an auto generating ID number field that contains a date followed by a sequential number for that date. In example...
YearMonthDay###
070719001
070719002
070719003
tomorrows numbering would be
070720001
070720002 and so on.
Each new day would start a new sequence of 001, 002, 003, etc.
I have searched these forums and haven't really found any answers. Could someone direct me in the correct direction. Also I need this number to be saved in the table and not just displayed or printed.
View 9 Replies
View Related
Apr 3, 2014
I have a query which runs fine, however one of the outputs is a calculated field and i use the Format(someValue,"Fixed") method of specifying the format for the % Change .Here is the query
Code:
SELECT tblTempBearsWeek3.SymbolCode AS [Short Ticker], tblTempBullsWeek3.SymbolCode AS [Long Ticker], tblTempBearsWeek3.[Week 3 Date], tblTempBearsWeek3.Short, tblTempBullsWeek3.Long, tblTempBearsWeek3.[Short Week 3 Close], tblTempBullsWeek3.[Long Week 3 Close],
[code]...
The problem is that the % Change calculated field for some reason comes out as a String. i know this because when I try and sort on that column, it sorts it as if it is text and not a number value i.e. it doesn't put it in Ascending order.
View 2 Replies
View Related
Oct 1, 2014
I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..
Code:
Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity
from(
SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center
FROM [Open Items]
)a
left outer JOIN [Open Items] AS [Open Items_1] ON
(a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity)
Where ([Open Items_1].MonthEndDate=#8/31/2014#)
View 1 Replies
View Related
Jun 16, 2015
I am trying to sum a column on a subform named Cost of New if another column named Final = any of the values listed in my code. However, it is summing the Cost of New column regardless of the values of Final. It is summing for all values.What do I need to modify to make this correct or should I do it another way?
=IIf([Final]="RPR-RPR & RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST",Sum([Cost Of New]),Null)
View 3 Replies
View Related
Apr 28, 2015
I think I know the answer but want to check. I've been asked to create a query, without querying a query first, but it's the only way I know.
I have two tables
Table1 will have data in a column that is 9 characters long
ULCABC123
ULCABC124
ULCABC125
PLTABC123
PLTABC124
Table2 will have data in a column that is 6 characters long
ULCABC
PLTABC
Question: Can I create a Join from Table2 Field with the Left(Field,6) from Table1
I was thinking something like this. (but then I can't enter design mode)
Query1 - Test
Code:
SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Left(Table1.ORDERDET,6)
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
I presume the only way to do this is first query Table1 (and call Query2) and return the first 6 characters and the create another query (Query3 in this case) using Query2 field joined with Table2 field.
Query2
Code:
SELECT Left([ORDERDET],6) AS NEWORDDET
FROM Table1;
Query3
Code:
SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Query2 INNER JOIN Table2 ON Query2.[NEWORDDET] = Table2.ORDDETTYPE
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
View 2 Replies
View Related
Jul 11, 2014
I am trying to extract the first instance of 3 numbers from a string using an update query to update another field in the same table(Master Equipment) titled "Code". the field is called "Equipment". the strings vary in length as well as the location of the 3 numbers needed, but I have examples of all possible locations the 3 number series could be located below. As you will notice, some fields do not contain 3 numbers together, for these I don't want to return a value.
07-FLP58351
07-MFDP58201
1PBE97601
1PT91105-2
2WPF/FF438582
A-WR-1
AAV58101-01
AC47201-01
View 13 Replies
View Related
Jun 7, 2013
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
View 13 Replies
View Related
Sep 13, 2013
I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:
Code:
Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant
Dim priSubID As Long
Dim priLocusID As Long
Dim priResult As Variant
priSubID = nrPotSubID
[Code] ...
However, when I use it in query it only returns Case else - option and everything else is empty.
View 7 Replies
View Related
May 2, 2014
Why is strCreditCheck returning the actual query string and not the result? Of course, this is not working.
Code:
Private Sub b_PrintWorkOrder_Click()
Me.Requery
Dim strCreditCheck As String
Dim strCompany_id As Integer
Dim stlinkCriteria As String
[code]....
View 6 Replies
View Related
Apr 9, 2008
I've have used the usual code format for creating a column of row numbers for my query, but it doesn't seem to work. Sequence: DCount("InventoryID","Chemical Location","InventoryID <=" & [InventoryID]) I got this error The Microsoft Office Access database engine cannot find the input table or query 'Chemical Location'. Make sure it exists and that its name is spelled correctly.
So I tried this: Sequence: DCount("InventoryID","qexpFacilityInventory","InventoryID <=" & [InventoryID])And I got this error: The expression you entered as a query paramenter produced this error: Business Emergency Plans can't find the form 'frmMain' referred to in a macro expression or Visual Basic code
I think my code doesn't work because its a join query; so I'm searching for an answer. I have attached an image of the query builder in Access 2007 to show my problem. Also I'm showing the SQL code. Can someone help me with putting row numbers in a Join Query "qexpFacilityInventory"?
SELECT tblChemicalInventory.[Business Name], tblChemicalInventory.[Item Number], tblChemicalInventory.[Chemical Location], tblChemicalInventory.[Chemical Name], tblChemicalInventory.[Common Name], tblChemicalInventory.[Map ], tblChemicalInventory.[Grid ], tblChemicalInventory.[Largest Container], tblChemicalInventory.[Average Daily Amount], tblChemicalInventory.[Maximum Daily Amount], tblChemicalInventory.Units, tblChemicalInventory.[Storage Container], tblChemicalInventory.[Usage Purpose], tblChemicalInventory.[Annual Waste Amount], tblChemicalInventory.STATUS AS Reviewer, tblChemicalInventory.STATUS AS [Modified Yes/No], tblChemicalInventory.STATUS AS Comments
FROM tblChemicalInventory INNER JOIN tblChemicalProperties ON tblChemicalInventory.ChemicalID = tblChemicalProperties.ChemicalID
WHERE (((tblChemicalInventory.BusinessID)=[Forms]![frmMain]![ctlGenericSubform].[Form]![BusinessID]));
Thanks,
PC
View 1 Replies
View Related
Aug 17, 2015
I've successfully created a DateDiff calculation that is generated with a d:h:nn format. Now the question:
How can i select or identify specific values, such as <= 2 days using those outcomes generated by the expression? Is it possible?
Example: a value is generated as 3:14:35. I would want to indicate this more than two days by putting a "N" in a separate column.
How is this done?
View 3 Replies
View Related
Oct 6, 2014
I am using VBA to execute a 'Make Table' (named 'DT'). One of the fields in DT (named 'Dollars') contains numbers that have 6 to 8 digits; some are positive and some are negative.
These large numbers with no commas (or parentheses when they're negative) are incredibly hard to read.
Any VBA coding that will change both the "Format" and the "Decimal Place" Field Properties on the table I make (i.e. "DT"). I want to the Format property to read (#,##0.00;(#,##0.00)) and the Decimal Place property to read "2"). This will allow me to display a number like -10326786.41 as (10,326,786.41) which is incredibly more readable.
Is it possible to do this programmatically; I've search the internet high and low and could find anything remotely useful.
View 8 Replies
View Related
Feb 22, 2014
I have a database with a table of employees, and that has a column named "DaysOff", into which I type a couple of days like this: "SUNDAY AND MONDAY"
Next, I have a form onto which I will display a query of those employees. The form contains a field called DATE in which I display the mm/dd/yyyy date. What I want to do is this:
I only want those employees that have a DaysOff field that DOES NOT CONTAIN the WEEKDAY NAME of the date in the DATE field. So, for instance, if the DATE field read 2/22/2014, and I have three employees as such:
Employee 1 DaysOff "Monday and Tuesday"
Employee 2 DaysOff "Friday and Saturday"
Employee 3 DaysOff "Sunday and Monday"
In that case, ONLY EMPLOYEES 1 and 3 should show up. Employee 2 will NOT show up, because his DaysOff field contains the word "Saturday", and the WEEKDAY FORMATTED value of the DATE field is "Saturday"
I hope that's not too confusing. Again, this needs to be the filter criteria for the query. That query will only return a list of employees that are NOT off on the day in question. If one of their off days is the day in question, the query won't return that record.
I've tried a few things, but I can't get it to work. If you want, I can list the various things I've tried, but I imagine one of you knows exactly how to do what I need to do, and you won't need my feeble attempts as a springboard.
View 2 Replies
View Related
Jun 12, 2013
In order to perform conflict searches in my Access 2007 database, you enter a bunch of information and then receive a report of cases with similar information in order to avoid any conflicts of interest.One of the fields is "Date of Accident". Currently I have its format set as yyyy/mm/dd. However, my expression
Code:
Date Searched: [Forms]![frmConflictSearch]![Date of Accident]
returns nothing (if I look at the query in Datasheet the column is blank).I found a fix for getting it to appear by removing the format on Date of Accident, however, my query doesn't retrieve cases with matching dates (the dates are formatted to dddd, mmmm dd, yyyy as a Date/Time Data Type).Is there any way I can get the query to both return the date searched, AND retrieve matching cases?
View 4 Replies
View Related
Feb 12, 2006
I have a column of data set up as follows:
Review July 2003
Review October 2003
Review July 2004
Review October 2004
Review July 2005
Review October 2005...and so on.
I know the data is not normalized, but if I want to use the Year part of the string in a WHERE clause, how would I do that? or in other words how do I extract the Year from the string.
Thanks
View 5 Replies
View Related
Jan 3, 2014
I have a series of IDs in an 'articles' table stored as text, e.g.
hb-123456789-e-068
hb-123456789-e-0069
hb-123456789-e-70
hb-123456789-e-00027
and I'm trying to pull the max value of the number after the -e- for a given set of them. In this example, I'd want to return the number 70. I'm then going to use that to create the next ID and populate another field.
The IDs are not used as the primary key. And while the previous IDs used leading zeros inconsistently, new IDs will not have leading zeros.
Here's what I have so far, but it doesn't seem to pull the number after the -e- at all. I think this section here is the problem, even though the same logic works in a query:
Code:
selectedERef = Val(Right(rs![masterArticleID], Len(rs![masterArticleID]) - InStrRev(rs![masterArticleID], " - ")))
Code:
Public Function MaxArticleERef(hbID As Long) As Variant
On Error GoTo err_handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim maxERef As Variant
[code]....
View 3 Replies
View Related
May 19, 2014
i have a query that pulls a read-only SQL table, i have check in that table and the date field is set up as a Date/Time type, however the db was set up with the date and time in the same field. (1/1/2014 9:00:00 AM)
I have set up the SQL code below to pull out just the Date and not the time. however when i enter my criteria on the form, only the correct month and days load, it pulls in all other years with that month/day.
my Access level - on the lower end of medium experienced.
SELECT IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," "))) AS [Date], IIf(InStr([dbo_Rides]![ApptDatetime]," "),Mid([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")+1)) AS [Time]
FROM dbo_Rides
WHERE (((IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")))) Between [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtFromDate] And [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtToDate]));
View 2 Replies
View Related
Dec 11, 2014
I have a POLEFFDATE field with dates in date/time format. A single record has POLEFFDATE equal to 12/15/2013. I calculated a simple field called EVALUATE as follows: IIF([POLEFFDATE]< 1/1/2014,1,0). EVALUATE should equal 1 for this record, but it equals 0. Why ?
View 3 Replies
View Related
Jan 15, 2014
I have to decode a string into numbers and to avoid to find out the values for 47 options by select case I though about an array.
I want to decode
Number Letter
10 A
11 B
12 C
13 D
14 E
15 F
16 G
17 H
18 I
19 J
20 K
...
For example the string "ADEG" would give as result
10 13 14 16
So I would have to loop through the string and "decode" each letter into a number.
As I have still problems to understand array, need to define the dimension of the array, it has fix 47 entries to decode
Dim myarray (47,2) as variant
mayarray=(10,"A",11,"B",...)
Correct?
View 10 Replies
View Related
Dec 16, 2014
I'm working on a database to track our program's performance under different grants. Services performed for each grant would be entered on a quarterly basis. I am tracking the number completed for each service during the quarterly period.
I want to create a report that shows progress against benchmarks depending on the most recent quarter completed. Rather than fooling with dates, I put a field in the table where the data is entered for the number of the quarter in which the service was performed. The field, 'Quarter', holds numerical values 1-4 corresponding with the quarter. The table holds entries from different projects, distinguished by the field 'GrantID'.
There will be more than one service entered for each GrantID.
I would like to be able to identify the most recent quarter in which services were performed for each GrantID.
I have tried using the MAX function in a query but it seems to be adding "1" to the highest quarter number in the field for each GrantID. The code is:
SELECT DevEntryQ.GrantID, Max(DevEntryQ.Quarter) AS MaxOfQuarter
FROM DevEntryQ
GROUP BY DevEntryQ.GrantID;
With this query, if the highest quarter number entered in the table for GrantID1 is 3, the query returns "4".
In Excel, I would use an array formula: = {MAX(IF(Table1[GrantID]=GrantID, Table1[Quarter])}.
I want to use the most recent quarter to retrieve that quarter's benchmarks and show the progress toward the benchmark rather than the overall progress toward the goal for that grant and service.
View 2 Replies
View Related
Aug 12, 2014
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
View 14 Replies
View Related
Mar 14, 2014
I have to join multiple values into a string for summarizing data on reports and exports. This process in vba is taking up to 10 minutes to process and will get worse as the size of these reports grow.
My method so far is to query the individual items into a recordset, loop through the values, adding them to the string then return the string in the query.
Here is an example:
Public Function SO_Description(intSO As Integer) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLSelect As String
SO_Description = "Profiles: "
Set db = CurrentDb
[Code] ....
View 1 Replies
View Related
Apr 26, 2013
I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:
Code:
Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
Dim strReport As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("7-ErrorsReport")
qdf.Parameters(0) = Forms!frmmain!dt1.Value
qdf.Parameters(1) = Forms!frmmain!dt2.Value
qdf.Parameters(2) = Forms!frmmain!d2.Value
[code]....
The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.
I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
View 3 Replies
View Related
