Select Not Returning New Values
Nov 28, 2007
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 & "; "
View Replies
ADVERTISEMENT
Sep 29, 2005
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
View 4 Replies
View Related
Nov 29, 2007
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?
View 1 Replies
View Related
Jan 13, 2006
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%"));
View 2 Replies
View Related
Mar 30, 2006
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
View 3 Replies
View Related
Mar 24, 2015
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
View 3 Replies
View Related
Sep 12, 2012
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 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
Sep 18, 2014
I 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.
View 1 Replies
View Related
Jul 20, 2007
Hi anyone!
I have a table that tracks data for parts across different depots. Thus some depots might not have records for a part in a particular depot whilst others do on a given day. My table does not record a 0 value for parts in a dept that don't have a part on a given day, it simply does not record anything.
I would like to query all depots and have it return a 0 value where there are no records on a given day for a given part in that depot. Does anyone know how to do this? i.e. if no record return 0 as default.
Does anyone know how to do that? At the mo my query simply exlcudes all records on taht day for all depots?
Thanks in advance
View 2 Replies
View Related
Jan 21, 2006
Hi.
Can someone help me?
I am trying to make a query return all values (please see attached jpg)
It returns some of the values, but is not returning values that have nothing in the "material" field (Null Values?)
In this example, I would like it to return all values that fulfill the Completion_Date, CW_Department, Handed_Over and Chargable fields, whether they have any data in the Material field or not.
Do I need to use the NZ, IIF or IsNull function? I have seen this is previous threads, but nothing like this example.....I apologise if there is.
Many Thanks.
Frank.
View 2 Replies
View Related
Oct 10, 2011
2007 version - my child query is not returning null values. I have 2 fields, both pulled from a parent query. One is "DaysSinceRecd", the other is "AmountPaying". The parent query returns multiple results, which is fine. For example, the parent query might have 10 different records for "0" "DaysSinceRecd", with different "AmountPaying" for each of those records.
In the child query, I am trying to condense that down, so that I only have one "AmountPaying" for each "DaysSinceRecd". It's working fabulously, except that there are records in the parent query that have a value for "AmountPaying" but a null value for "DaysSinceRecd". The null value for "DaysSinceRecd" cannot be changed to another value, it needs to stay null. My child query is not returning the null records. I tried adding to the criteria: IsNull Or IsNotNull, >=0 or <=0. That's not working.
View 5 Replies
View Related
May 19, 2014
I have two class variables (both arrays) among about 10 other class variables, that are not returning any values but "" for the string or #12:00:00AM" for a date. Here are my class variables:
Code:
'UPSData Class Module
Private p_LetterArray() As String
Private p_date() As Date
Private p_LetterArraySize As Integer
My Properties
Code:
'Properties
Public Property Get LetterArray(index As Integer) As String
LetterArray(index) = p_LetterArray(index)
End Property
Public Property Let LetterArray(index As Integer, NewValue As String)
[Code] ....
As I said, tLet and tDate result in "" and #12:00:00AM# respectively. When I step through the code, the values for tmp.LetterArray(0) is assigned "src" and tmp.UPSDate(0) stores "12/25/2013" correctly.
When I assign tLet and TDate, the same thing happens when stepping through the code. I'll use the LetterArray property to describe what happens:
Get LetterArray is called. p_LetterArray(0) does equal "src"
Let LetterArray is called. NewValue is "src" and p_LetterArray is "src" when End Property is highlighted in the debugger
Scope returns to Get LetterArray with End Property highlighted. In checking the values, LetterArray(0) = ""
Same steps happen with the same results ("12:00:00AM" vice "")
View 5 Replies
View Related
Jun 18, 2013
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)
View 10 Replies
View Related
Aug 19, 2005
i have a multiselect listbox in my form.
The multiselectlistbox contains the names of different persons from tblUsers.
it's allready possible to write the id's of the names to another table (tblPresent).
But what I can't manage to do is re-select the values in another multiselect listbox. This multiselectlistbox is located on my editform.
I can display the values using a valuelistbox, but i need to see the non-selected items too..
hope someone can help me out
View 1 Replies
View Related
Nov 4, 2006
Hey there!
I've been searching about this forum and found a lot of threads on this sort of issue but haven't been able to figure it out from those alone, so feeling a bit useless :o long winded explanation by the way... I'm useless at explaining! :D
Well basically I have a query that is supposed to power a flexible search form, where you choose the fields you want to search by and enter your criteria. The form contains labelled fields with checkboxes next to them, which you tick if you want to search for something in that field. The results are displayed in a subform which reads from the query.
The problem is with the query! My solution seemed to work fine when I put my criteria in just one field when testing (that is the fields in query design builder, not the form), but when I applied the same to two fields it didn't. Instead, it would return nothing unless I specified exact and correct criteria for BOTH fields I had set up with my solution. So whereas before I could tick "Customer" and search for a customer account number and it would display ALL relevant records, now if i just ticked "Customer" and entered the same number, it would display nothing, unless I ALSO ticked "Booking" and entered a correct booking number that matched a record for that customer in the tables.
I've heard iif statements don't do too well in the criteria fields in the query design builder?? But I know nothing about using SQL really.
For example for the invoices field have used:
IIf([Forms]![frmFindInvoices]![ChkInvNo]=True,[Forms]![frmFindInvoices]![InvNo])
So:
IIf(form checkbox = true, form text box value)
However it seems to think that if the checkbox is not ticked, I want it to only display EMPTY invoicing fields. This is never the case and so no records are shown! If the tick box is not shown, there is not supposed to be criteria for this field, in other words it should allow ANYTHING through.
What I don't know how to do is tell the bloody thing that if the box is NOT ticked, it is NOT to check for anything in this field, because the user does not want to specify any criteria for this field! Things like "Is Null" produce the same results, probably because the query still thinks I want it to find records that have empty fields.
Any suggestions to specify to the query what I want? I really need to get this done! Thanks! :)
View 11 Replies
View Related
Jun 27, 2013
I have two tables that will contain the information that I need the third table that will be used for constant data entry to auto fill in two of the fields (Class, Rate)based on four criteria from the first two.
Table 1
EmpID Name
1 EMP1
2 EMP2
Table 2 (A Subtable of Table1 based on EmpID)
EmpID ACDate Class Rate MJob SJob
1 1/6/13 A 15 100 1A
1 2/6/13 B 20 100 1A
2 1/6/13 A 18 100 1B
Table 3
Name WDate MJob SJob Class Rate
EMP1 1/7/13 100 1A A 15
EMP1 2/5/13 100 1A A 15
EMP1 2/6/13 100 1A B 20
EMP2 1/6/13 100 1B A 18
View 2 Replies
View Related
Jun 14, 2013
I have a form in which users can enter data in several textboxes to filter the listbox below it, this works great except for the fact that when a record lacks certain data it doesn't show up
Basically there are 4 filters, one for the name/id which works great, as the entries without id's show up just fine but this filter needs to be checked against the other 3 filters, for their group, education and type.
Lets use group as an example.
When a student has no group it should only show up when the group filter is an empty string. AFAIK the wildcards should see to that.
Currently however, when a student has no group, it doesnt show up at all unless I remove the | Like '*' & [groepFilter] & '*' | part from the query.
I've never used complicated WHERE's like this so it might be something very simple. I think I could get it to work using VBA and modifying the rowsource of the listbox from there but this would be a lot easier and I'm curious as to why this doesn't work.
The SQL:
SELECT Student.Studentindex, Student.studentid, Student.studentvn, Student.studenttv,
Student.studentan, Student.Groepcode, Opleiding.opleidingsnaam, Opleiding.type
FROM Student LEFT JOIN Opleiding ON Student.opleidingid = Opleiding.opleidingid
WHERE (((Student.studentid) Like '*' & [naamFilter] & '*') AND ((Student.Groepcode) Like '*' & [groepFilter] & '*')
[Code] ....
View 2 Replies
View Related
Jan 2, 2014
I have created a link to a CRM system that we have, and am using access as an interface for a specific task with the data contained within this CRM.
I have written a query that pulls to fields of data together but cannot get the two to calculate.
Within SQL server management studio I use the following query to give me a numerical response.
SELECTPRCPRICE,SUM(CAST(VL2BENEFICIALASNUMERIC (20,6))*CAST(PRCPRICEAS MONEY)))ASHOLDINGVALUE
Access doesn't appear to accept the cast functionality / how I can perform a similar functionality with access?
View 9 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
Dec 11, 2013
I want to be able to display the result of a difference between Date/Time values in "HH.MM" format (i.e. yes I want the result in decimals and I don't want Access to round up or down just because it feels like doing it!). I have used the following:
Example 1:
Dim ActualManHours as Long
ActualManHours = (txtEndTime.Value - txtStartTime.Value) * 86400
txtActualManHours.Value = ActualManHours
Example 2:
Dim ActualManHours as Long
ActualManHours = DateDiff("h",txtStartTime.Value, txtEndTime.Value) & "." & Format(DateDiff("n",txtStartTime.value,txtEndTime. value) Mod 60, "00")
Example 3:
Dim StartTime as Double
Dim EndTime as Double
Dim ActualManHours as Long
StartTime = CDbl(txtStartTime.Value)
EndTime = CDbl(txtEndTime.Value)
ActualManHours = EndTime - StartTime
txtActualManHours.Value = ActualManHours
View 7 Replies
View Related
Nov 18, 2013
I have a list of employees and sort criteria. for example
empID....Criteria
1234......T
1234......F
1234......T
1234......F
1235......F
1236......T
1236......F
1236......F
1236......F
1236......T
1236......T
1237......F
The output I am looking for is a count of the number of times T appears by an employee, BUT is there is no record it would return 0
E.G.
empID......Count
1234...........2
1235...........0
1236...........3
1237...........0
I can get it to return:
empID......Count
1234...........2
1236...........3
using Count and the criteria Where Criteria="T" but not returning zeros.
View 2 Replies
View Related
Feb 10, 2014
In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.
The SQL used for the combobox is as follows..
Code:
SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact
FROM tbl_Contacts
WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company]))
ORDER BY tbl_Contacts.ContactForename;
Why some contacts work and some don't ??
View 7 Replies
View Related
Apr 28, 2007
My thinking must not be right as I can't get this to work but i have a table with
Date AMT
02/05 50
02/06 20
and soforth....
I am summing up each week and putting this in a table with values
WeekBeginningDate WeekEndingDate SumofAmt
I am trying to do
INSERT INTO tblTwo ("02/05/2007", "02/09/2007", SumOfAmt)
SELECT sum(Amt) FROM tblOne
where TDate >=#02/05/2007# and TDate <= #02/09/2007#
The two dates in the INSERT INTO are just values and the SumOfAmt is from the select. Can someone help me with this? Thanks in Advance!!
View 2 Replies
View Related
Nov 7, 2004
Hello!
I have a combobox on a form and it shows values from a query. I pick one value and presses a button, then a new form opens with a lot of information based on the value in the combo box. In this combobox I would like to have a value called "All", if I choose this one I want All of the information to be shown. I would like to know how to get this to work (create a question with a term like this: IIF([Forms]![frmMaskin]![cmbAvd]="All";Like "*";[Forms]![frmMaskin]![cmbAvd])). When I do this, I se nothing. Please give me some advice.
View 2 Replies
View Related
Aug 14, 2015
I Want to Create an Query to Find Different Values in A Table/ Query
Like
Month Year Name School Post
Jan 2012 Ankur School 1 Post 1
Feb 2012 Ankur School 1 Post 1
Mar 2012 Ankur School 2 Post 1
Apr 2012 Ankur School 2 Post 2
May 2012 Ankur School 2 Post 2
June 2012 Ankur School 3 Post 2
Now i want in result of Query is only like
Month Year Name School Post
Jan 2012 Ankur School 1 Post 1 (Starting Ledger)
Mar 2012 Ankur School 2 Post 1 (Change in School)
Apr 2012 Ankur School 2 Post 2 ( Change in Post)
June 2012 Ankur School 3 Post 2 (Change in School)
View 1 Replies
View Related
