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]....
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.
I am trying to select all projects with the keyword "sett" in them. About a thousand rows with this keyword exist but why isn't my query returning any data? :confused:
Here's an example of the data "STPT-SETT-NY-EQ Legacy"
Now i want to return all rows with "SETT".
What am i doing wrong here please :)
SELECT projectList.*
FROM projectList
WHERE (((projectList.projectName) Like "%SETT%"));
this doesn't work either. No records are returned when query is run:
SELECT projectList.*
FROM projectList
WHERE (((projectList.projectName)="%SETT%"));
Hello, I am sure this is quite simple, however I cannot figure it out. I have a query that shows tblPartNumber and tblAverage. The average is calculated in the query by taking the total minutes to assemble the parts, divided by the number of parts completed. What I am trying to do is run a query that will show me the most efficient (lowest average), time for each Partnumber. Ideally the query would show me something like this;
Part # Average
1234 .72
1234 .86
1234 .94
etc...
I searched on here and it seemed like the Top 10 is used, but in my application that ommits all of the other partnumbers because it only shows ten of the records for the first partnumber. I only want to see the five most efficient averages. Any help is greatly appreciated.
Thanks, Tim
I have a db with 2 tables, one containing basic info FirstName, Surname, YearGroup, and another table with other data. I imported a list of names into the first table, firstname and surname only were imported, other field was left empty, other table was also left empty. I made a simple select query to pull all records from both tables and i get no results at all. None of the imported names show on the query results. what I am missing?
View 2 Replies View RelatedI am building a select query which is grabbing data from multiple tables with items being linked by a unique field "Certificate_ID".
I have created joins between table A and Table B, and Table A and Table C, linking both by Certificate_ID
All have the join property set to select all Records from table A, but only those from Table B and Table C where the joined fields are equal.
Table A has 5000 records. Am I correct to assume that my query should only return a max of 5000 records as well? When I select Certificate_ID from A and another column from B it only gives me the 5000 unique records. When I add in a column from Table C it is however returning something like 7500 records, with several being duplicates with the same data in every column.
Why it is choosing to duplicate records and give me more than I want. I am sure I am overlooking something simple.
Hello!
My problem is the following:
My select works fine when I select 3 fields, but trying to select more than three, when I add two fields more, it doesn't give me the last two fields, being these two fields added lattely in the table...
I mean... I added two fields more to the table one it was created, and the script executed.
After that, if I try select * from table, the recordset does not have this two fields... only the old ones...
Any idea?
what I am trying to do with pretty much create a search query through code. So what's happening exactly is that the user enters a part number and expects to get 2 values: total orders and total items associated with the part number he/she entered. I have 3 tables and 2 of them are related. So I began my creating a query relating table 2 and table 3. I cannot include table 1 in the query.
1) Part Number is input by user
2) Search that Part Number into Table1
3) Take returned Parent Values associated with Part Number and store in Array
4) Modify Array values
5) Search Array values using a For Each loop into the query
6) Take the returned values found in query associated with each parent that was retrieved from the part number (user input) and return that through a table or query.
7) Also return the total rows in a specific column (Order Numbers)
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?
Hello!
I've good a very very strange problem...
I do an insert of a new row into the table TRA. That insert creates an id for the new row.
Right after that I do a select of the new row, trying to get the new id created.
Well, the insert is working fine, but the select sometimes gives me values, sometimes not.
How is it possible?? I've tryied closing the database and opening it again, but is not working anyway...
Any idea??
My code right now is the following: (closing the database, setting recordset to nothing... and still not working properly)
sql = "insert into tra (semana, empleadoid) values('" & semana & "'," & empleadoId & "); "
DoCmd.RunSQL (sql)
db.Close
Set db = OpenDatabase(database)
sql = ""
Set rcset = Nothing
sql = "select * from TRA where (semana='" & semana & "' and empleadoId =" & empleadoId & "; "
I have a problem that I haven't seen discussed here yet. (I know this is not good practice, but we're doing this for transfer to another application that wants and accepts data this way)
1) I have a multi select list box.
2) I have code that saves all the values in a semi colon delimeted list in a single field in the DB. (As I said, the application that this data is going to (and from) wants and accepts data in this way)
3) My problem is this. When I re-open my form, how do I get the values that are stored in the database to be highlighted in the multi select list box. I have code similar to this running, but still can't figure it out.
Dim lst() As String
rst.Open sSQL, cnn, adOpenStatic, adLockOptimistic
lst = Split(rst(0), vbCrLf, -1) 'Split the recordset into an array
For i = 0 To List96.ListCount
For j = 0 To UBound(lst) 'lopp until the upper bound of the array
'If the item in the list box = the item in the array then highlight the item in the list box
If List96.ItemData(i) = lst(j) Then
List96.ItemsSelected(i) = True
End If
Next j
Next i
In a nutshell, that's the problem. My list box contains a list of about 5 or 6 things, the user can select any number of them, and I store it in a single field in the DB semi colon delimited. However, I don't know how to return that field to my list box with the selected values highlighted. IE if the list contains A, B, C, D and the user selected A, C the field in the DB looks like this A;C. When I open the form again, I want to highlight A and C in my list box since those were the fields the user selected.
What happens is A;C gets passed to another application (which wants it that way). However, in that other application those values may or may not change, and then they will be passed back to me. (IE the other application may change those values to A;B;D and pass it back to me (which I accept no problem). However, when the user opens the form I want A B and D to be highlighted in the multi select list box
Any help is much appreciated
Robbo
My statement below is current returning 4 records. Two of the records have the same GBPAmount value.
RequestID is the Primary Key
Code:
SELECT TOP 3 RequestID, GBPAmount, Currency, RequestDate
FROM PayRequest
WHERE (((Currency)="CAD Canadian Dollar") AND ((RequestDate)>#11/16/2014#))
ORDER BY GBPAmount;
Code:
RequestID GBPAmount Currency RequestDate
10207 8.17 CAD Canadian Dollar 03/02/2015
9874 33.82 CAD Canadian Dollar 20/01/2015
11327 109.58 CAD Canadian Dollar 23/02/2015
10495 109.58 CAD Canadian Dollar 05/02/2015
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
I have a query that returns the string to use but I am not sure how I can actually use the returned value to do what I want.
e.g.
my table:
Partner ID Backup Field
5023949 UPC
501354 GTIN
my query will return 1 [backup field] value from the table above, and I then want to use that value to get another value that was previously declared in my code.
eg
GTIN = "ABC123"
UPC = "123ABC"
if my query returns the value "GTIN" then need to use the value of GTIN (ABC123) in my next part of the code.
msgbox GTIN would result in a message box ABC123
But I am not sure how to get the result value. I can say msgbox [backup field] but that returns "GTIN" not "ABC123"
I am currently trying to create an update query (building a SQL String in VBA for a command button click event) to update a variable's value into a table.
Basically, there are 2 tables, displayed in 2 sub froms within the same main form. (OldTable and NewTable for arguments sake)
The basic method I want to implement is that a user highlights a record in the subform of "NewTable". (This value is stored as a variable "NewJPNUM" This value is then to be inserted into the highlighted row (or rows) of table OldTable on command button click.
So the basic idea is a user highlights a row in one table and this value is stored as a variable "NewJPNUM" . The user then highlights a row or rows in "OldTable" and the value from variable "NewJPNUM" is then written to field "NewJPNUM" in "NewTable" on command button click.
I am not experienced with Access but have decent experience in Excel / VBA so not really sure of best practice methods etc.
How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:
Code:
Dim NETWORKBOX As String
NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE")
sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;"
DoCmd.RunSQL sql
If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.
I have a simple select query , and I want to add a field that will take the value from another field and increment it based on a condtion.
i know i need a case statement but not sure how to go about this ..
column 4 is my desired result
i want to increment the text string from column 3 based on column 3 and restart the increment based on column 1
values of A would increment in the following manner 01,11,21,31,41 etc
values of O would increment 02,12,22,32,42,52,62
i'd also like the option that if a new value were introduced to increment it
values of x would increment 80,81,82,83,84
Example data .. apples and oranges ..
column1 column2(name) column3(type) column4
mary braeburn A 01A
mary blood O 02A
tom cortland A 01A
tom fuji A 11A
tom navel O 02O
tom mcintosh A 21A
[Code]...
I am using a public function to feed a variable string to a query. So far I have got:
Code:
Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String
Dim cslStr1 As String, newStreet As String
newStreet = Right(streetVar, Len(streetVar) - Len(newFL))
[code]....
However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. Why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?
Could someone tell me if I need to break this select statement down? I'm running into problems with the DMax statement since it requires double quotes around it's arguments. When the parser hits the first argument, it closes the string like it's supposed to, but not the way I want it to.
In essence, the query returns 4 fields: unique ID, week number, computed score (TotSum), and the computed handicap (which is half the difference between your score and the high scorer):
Quote: strSQL = "SELECT tblRoster.HEDR, tblScores.WeekNo, " & _
"[A1T1]+[A1T2]+[A1T3]+[A2T1]+[A2T2]+[A2T3]+[A3T1]+[A3T2]+[A3T3] AS TotSum, " & _
"Round(((DMax("[TotSum]", "[qryHandicap]") - [TotSum]) / 2) + 0.1) AS Handicap " & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _
"WHERE (((tblScores.WeekNo) = " & inpWeekNum & ") And (TotSum > 0)) " & _
"ORDER BY TotSum DESC;"
What would the proper syntax be and/or is there a better way to obtain this information. After acquired, it is being salted away in a table which is recomputed after each competition.
Thanks for the input.
-Brian.
p.s. I know storing computed fields is a big no-no, but if you do not compete in a given week, the handicap from the last time you participated is used. This table will store the participants handicap as well as the last competition they were in. It seemed easier this way.
I currently have 2 queries..
Query 1 select statement getting count of field
Query 2 getting max of Query 1
Instead of the queries I want to do this in vba with a recordset
Code:
' Select from first query
sql = "SELECT tblOriginal.ROUTE, Count(tblOriginal.ROUTE) AS CountOfROUTE " & _
"FROM tblOriginal " & _
"GROUP BY tblOriginal.ROUTE " & _
"ORDER BY Count(tblOriginal.ROUTE) DESC"
' Select max from result of first query
sql2 = "SELECT Max(qryRouteCount.CountOfROUTE) AS MaxOfCountOfROUTE " & _
"FROM qryRouteCount;"
The slq works fine for the first Query, I could then do a iMax = rs("CountOfROUTE") to get the max value as they are ordered.
but how can I write sql 2 so that it selects the Max from the Query 1 select statement.
I'm using Access 2007.
So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.
I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter.
Here is some of my code:
Code:
Option Compare Database
Option Explicit
Private Sub Form_Current()
Dim oItem As Variant
Dim bFound As Boolean
[Code] ....
Here's my current query in SQL:
Then finally how to I get the query to execute and create a report based on all of this?
Picture, if you will, a table with multiple fields, each of which contains the date of a certain action in the process tracked by each record. I have a standard report format that will be used to view the progress of the actions. I want to be able to sort the report - on demand by non-technical users - by whichever date field they choose.
I've created a form with a combo box that is linked to a table with the name of each field in the source table. A Macro has been configured to open the form when the report is opened, which will prompt for the selection of the sort field. That value is then captured into a TempVar.
When the user clicks OK control passes to a procedure in a module that executes a SELECT statement (SQL) that configures the Query that is used for report generation. The "ORDER BY" portion of the statement needs to be modified with the name of the sort field desired. If the statement is hard coded as, for example:
...ORDER BY Main.[Initiated Date] DESC;
it works fine. I want to use the value of the TempVar to provide the name of the date field (ex.: [Initiated Date]), but I can't determine the correct syntax to get the statement to accept it. Concatenating doesn't seem to work, and I've searched high and low for hints on the web. Here's one version of what I've tried (and which fails):
"...ORDER BY Main." & [TempVars].[SortParm] & ";"
The error returned by the above version is: "Object doesn't support this property or method"
(SortParm is the name of the TempVar, and it is correctly populating, and keeping, the value I need from the form.)
I was beginning to think I had got the hang of creating queries on the fly in VBA. After several hours I have just learned that docmd.runSQL does not work for simple SELECT statements. I'm not going to worry about why that would be. I'm sure there's a jolly good reason.
So... I am able to dynamically create the SQL string for the SELECT statement that I need.
How do I run it? It just needs to return results as a datasheet so I can see them. I'm running Access 2013. I've been searching on this subject for quite a while and found many references to DAO, ADO, ADODAOD, YODELAEYYOUDELAYIO! and other things I don't yet understand ...
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
View 2 Replies View RelatedI am using Access 2013. I have the ability to pull a selection from a listbox. I can create a Select Sql string using that variable
sql As String, strCompany As String, strWhere As String
strCompany = strCompany & Me.lstResource.Column(0, varItem)
strWhere = "[Company name]=" & "'" & strCompany & "'"
sql = "select * FROM tblResources WHERE " & strWhere
From here I have trouble. I see lots of examples to run an active query but not much on a select query. I have tried a number of things with no success. How to use this select statement to actually run against an existing access table? I am not putting it into a form or report at this time, just running the query to check results.
see below the code . The select statement searches the Printpoolno value from the top to bottom in table tblmaster. As in my table tblmaster there are thousands of records and it takes long to search for that Printpoolno from the table . Is there anyway we can write a query that will search the table from bottom to top as the Printpoolno will always be in the bottom records and not in the top records.
Code:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
[Code].....