Queries :: Query Returns (DELETE) As Field Value

Mar 18, 2015

I have a query, and when I execute it, the correct data set is returned. However one field shows "DELETE" as the value for all records.

when I ran it on the SQL server management studio as a test it ran fine. End user does not have access to the tables, so I created a small MS Access application. When I ran the query in MS Access, then connected table dbo_DocRef > Notes shows a value of DELETED.

What am I missing?

View Replies


ADVERTISEMENT

Queries :: Form List Based On A Query Returns Original ID Value Not Field Text

Aug 14, 2014

I have a table x where the field value is selected via a combo box in a form that is populated from another table z.

When I look in x, it appears to have correctly stored the text from z, not the ID number.

I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.

Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.

How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search.

View 5 Replies View Related

Query Returns WRONG FIELD!!!

Nov 9, 2007

This is at least the 3rd time I've come across something that is, to me, an extremely serious bug in Access. Anyone else seen this, and anything I can do to avoid it??
In a query, I ask for the values from Field A.
The query returns the values from Field B, but still calls it Field A.

This is in a fairly complex query. If I delete one particular field from the query, the bug disappears. If I put that field back, the bug returns.

Here is my current query:
SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, ByPN_1.ActualMachine, ByPN_1.PermQty, ByPN_1.FirstOfFeederSize, ByPN_1.Leaf1, ByPN_1.FirstOfType, ByPN_1.NonPerm, IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups])) AS QtyIfDed, [nonperm]*[setupsperday] AS MaxLoadsSavedPerDay, [qtyifded]*[setupsperday]/[concurrentsetups] AS DedLoadsSavedPerDay, IIf([maxloadssavedperday]<[dedloadssavedperday],[maxloadssavedperday],[dedloadssavedperday]) AS LoadsSavedPerDay, [loadssavedperday]*[laborrate]*[loadunloadperfeeder]*250/60 AS Labor_Annual, master_attr.STD_COST, [loadssavedperday]*[lostpersetup]*[std_cost]*250 AS Parts_Annual
FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.ActualMachine = FeederCost1.Machine) AND (ByPN_1.FirstOfFeederSize = FeederCost1.Size) AND (ByPN_1.FirstOfType = FeederCost1.Type)) LEFT JOIN master_attr ON ByPN_1.PartNumber = master_attr.ITEM
WHERE (((ByPN_1.PartNumber)=107573));

HEre's the pertinent part of the record from BYPN_1:
PartNumberLostPerSetupFirstOfFeederSize
107573 3 12X4

But here's what the query returns:
PartNumberLostPerSetupFirstOfFeederSize
107573 12X4 12X4

Now, if I just delete the "parts_annual" field from the query:
SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, ByPN_1.FirstOfFeederSize, ByPN_1.ActualMachine, ByPN_1.PermQty, ByPN_1.Leaf1, ByPN_1.FirstOfType, ByPN_1.NonPerm, IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups])) AS QtyIfDed, [nonperm]*[setupsperday] AS MaxLoadsSavedPerDay, [qtyifded]*[setupsperday]/[concurrentsetups] AS DedLoadsSavedPerDay, IIf([maxloadssavedperday]<[dedloadssavedperday],[maxloadssavedperday],[dedloadssavedperday]) AS LoadsSavedPerDay, [loadssavedperday]*[laborrate]*[loadunloadperfeeder]*250/60 AS Labor_Annual, master_attr.STD_COST
FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.ActualMachine = FeederCost1.Machine) AND (ByPN_1.FirstOfFeederSize = FeederCost1.Size) AND (ByPN_1.FirstOfType = FeederCost1.Type)) LEFT JOIN master_attr ON ByPN_1.PartNumber = master_attr.ITEM
WHERE (((ByPN_1.PartNumber)=107573));

Now the query returns what I expect:
PartNumberLostPerSetupFirstOfFeederSize
107573 3 12X4

View 14 Replies View Related

Queries :: Query That Returns All Record For Only Last Date

Apr 30, 2014

i want to write a query that display records for only last date entered in a table and i want to show all the records from last date entries in a table.

View 6 Replies View Related

Queries :: Query That Returns No-matching Records

Jul 15, 2013

Suppose I have two tables with fields as follows:

Assigned Courses (Courses that employees should do)Employee number
Assigned Course Name

Fulfilled Course (Courses that employees have done)Employee number
Fulfilled Course

Now I need to do a query that tells me which employees haven't done. Is there anyway to do that that is not an exclude query?

View 4 Replies View Related

Queries :: Query Returns Wrong Values

Aug 30, 2014

My problems is that when I use below setup without the "Notes" tabel, they query works fine, and returns the amount of spares and time used by a single WBS ID. (Customer). But when I add another tabel and link it together. Its returns load of lines with the same data - it looks like its copying its self, over and over again.

View 4 Replies View Related

Queries :: How To Handle Report From A Query That Returns No Records

Jun 30, 2014

A have a report that is based on a query that works fine when the query returns records.

However if the query (legitimately) returns no records then the report fields (numeric) are blank.

How can i get the query or report to show either "0" or "no data" when no records are returned? I have tried the NZ function but that didn't work so i don't think that is the right approach here?

View 3 Replies View Related

Queries :: Grouped Query That Returns A List Of All Periods

Nov 21, 2013

I have a grouped query that returns a list of all periods qryPeriod

Code:
SELECT [tblMaster].Year, [tblMaster].Week, ([Year] & " - " & Right(0 & [Week],2)) AS Period
FROM [tblMaster]
GROUP BY [tblMaster].Year, [tblMaster].Week, ([Year] & " - " & Right(0 & [Week],2));

Year | Week | Period
2013 | 48 | 2013 - 48
2013 | 49 | 2013 - 49
2013 | 50 | 2013 - 50
2013 | 51 | 2013 - 51
2013 | 52 | 2013 - 52
2014 | 1 | 2014 - 01
2014 | 2 | 2014 - 02
2014 | 3 | 2014 - 03
2014 | 4 | 2014 - 04

[code]...

these are then used as the start of a between lookup against [tblMaster] along with the last record in the qryPeriod (2014 - 04)...I will need 4 queries so If someone can sort out last period - 8 I can probably do the others.would DLookUp do this say return the last 4 values, Last 6 Values and Last 8 Values

View 6 Replies View Related

Queries :: Query With Instr Returns Unexpected Results

Aug 26, 2014

I have Access 2010. If I run this query

Code:
SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE
(Reference ='ShouldBeOut'
OR
Reference = 'ShouldBeIn1'
OR
Reference = 'ShouldBeIn2')

It returns 3 rows, with values in MyField of 0, 23 and 355.

Now I add 1 more where clause to filter on MyField and the query looks like this:

Code:
SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE
(Reference ='ShouldBeOut'
OR
Reference = 'ShouldBeIn1'
OR
Reference = 'ShouldBeIn2')
AND
(
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) > 0
)

So, you would think that it should return 2 values (ShouldBeIn1 and 2), but it doesn't. It only return the value where the Instr returns value of 23. The one with the value of 355 also disappears. Why would that be? Surely Instr does not return a byte?

View 10 Replies View Related

Queries :: Date Formatting Criteria - Query Returns No Data

May 7, 2015

I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:

>=#06/04/2014# And <=#05/04/2015#

The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following

Format([Table].[Open Date],"yyyy-mm-dd")

The query then returns no data.

View 6 Replies View Related

Queries :: Recordset Is Showing As Empty But Query Wizard Returns Records

May 12, 2014

I'm trying to write record set contents to excel. My query runs perfect in access query wizard, but recordset showing as null. My VBA code

Code:

Dim cnn As ADODB.Connection
Dim recordst As ADODB.Recordset
Dim strSQL As String
Dim strPath As String
Dim appXL As Excel.Application
Dim wb As Excel.Workbook

[Code] ....

View 5 Replies View Related

Queries :: Combine 4 Tables Data That Falls Within Dates - SQL Query Returns No Records

Oct 2, 2013

I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:

Code:
SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC
FROM [1733_All Print Orders]
WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013#
GROUP BY [1733_All Print Orders];[Application]

[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.

I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records

To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.

View 1 Replies View Related

Queries :: Delete All Records That Are Numbers In Text Field

Oct 24, 2013

I have a field that is text format. However many of the records in that field contain numbers.I want to delete all the records that are numbers in this text field, leaving only records with textual data.

View 4 Replies View Related

Queries :: How To Run A Delete Query

Jan 13, 2014

When this query runs I want the popup parameter to open and ask for an ChID number. User enters that number and it deletes that record. I cant figure out what to enter on the criteria line. I have [ChID] and that wipes all records. I tried = [ChID] and it does not run.

View 4 Replies View Related

Queries :: Export Query To Excel Then Delete Query Data From Table

Nov 20, 2013

I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)

I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.

I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.

View 5 Replies View Related

Queries :: Delete Info From A Query?

Aug 18, 2014

Okay I have 2 tables and I used queries to filter and delete the info off those tables. Now I combined the 2 tables by using a query which is query 1. What I found was a few cells that are blank cells.

I tried creating another query based on query 1 from the 2 tables to try and delete it but it won't work. I tried everything and nothing seems to work. I can't filter the data since I am creating another query called query 3 to display the final results.

View 2 Replies View Related

Queries :: Linked Delete Query With A Max

Jan 29, 2015

I have a delete query where i want to delete only the row that contains the max value of the IDnum field from the table STM, where it links two tables on CellTell

I currently have this:

DELETE DISTINCTROW STM.*, STM.IDNum
FROM dpl_00c_tbl_StmCellDups
INNER JOIN STM ON dpl_00c_tbl_StmCellDups.STM_CellTel = STM.CellTel
WHERE (((STM.IDNum)=(select max(IDNum) from `STM`)));

It doesn't want to throw out any values when i run it, or view it.

View 2 Replies View Related

Queries :: UnMatched Query To Delete Records

Oct 25, 2014

I have a Unmatched query that displays what I need but I want to clean up my database and remove them but its not letting me. See sql below.

Code:
DELETE Warranty
FROM Warranty LEFT JOIN Serials_Repair ON Warranty.[Serial] = Serials_Repair.[Serials_Repair]
WHERE (((Serials_Repair.Serials_Repair) Is Null));

I changed "Delete" it was "Select" thinking that would work but it does not.

View 3 Replies View Related

Queries :: Can't Delete Record In Unmatched Query

Jan 12, 2014

Access 2010

I created 3 find unmatched queries from the query wizard. The first one works great. I can delete a record in the query and it deletes the record in the main table.

The second one allows me to delete a record and gives me the pop up box that says " you are about to delete 1 record". I click yes, but when I check the table or reopen the query the same record reappears.

The third query does not even give me the option to delete.

I can create a regular select query and delete the records form 2nd and 3rd but not from the queries made from the unmatched wizard.

I am using the same fields in all 3 queries.

I do not have enforce referential integrity or the other 2 options checked in the relationship properties. (not sure if this makes a difference.)

I even tried to redo the unmatched queries but I get the same result for all 3.

I want to be able to delete the records in the other 2 queries.

View 4 Replies View Related

Queries :: Custom Delete And Duplicate Query

Jun 12, 2013

I am trying to create a query to find duplicates and delete the duplicates. The result will eventually be used in another query (append query) to update a table.I have a table with 4 columns lets say for simplicity they are A, B, C, D

I want my query to find duplicates within B and deleting them. The catch is before deleting them I need to look into column A to ebsure they are actually duplicates. Example below

Example
A B
John Doe Tires
John Doe Wipers
Allison Doe Tires
Allison Doe Tires

As you can see from the above Tires is a duplicate and need to be deleted.

A B
John Doe Tires
John Doe Wipers
Allison Doe Tires

View 7 Replies View Related

Queries :: Delete Query From Two Linked Tables

Jul 9, 2015

I have two linked tables (Unique_ID)

Table1 has a record of unique tasks.
Table2 has a list of people to do those tasks

They are linked by a Unique_ID (one to many - Table1 to Table2).Sometimes there can be an instance where there are Unique_ID's in Table2 but the reference Unique_ID is missing from Table1.

I have a select query that can show me these like so.

table2 table1
Unique_ID Unique_ID
Like * Not Like *

I can open the query and simply delete the records fine however i wish to automate this.What I want to do is delete the unrefenced Unique_ID's from table2.I have tried to just convert this select query into a delete query but it asks me which table I wish to delete and I cannot see where to enter this information.

View 3 Replies View Related

Queries :: DELETE Query Syntax With Subquery

Feb 27, 2014

How to DELETE rows in one table that have a field value that matches a field value from another table? Tried these but sytax errors:

DELETE * FROM tmpBankDebitsMinusJE
WHERE tmpBankDebitsMinusJE.ConcatenateBankDebits IN
(SELECT tmpJournalEntryChangeOrders.Concatenate);

DELETE FROM tmpBankDebitsMinusJE
WHERE ConcatenateBankDebits IN
(SELECT Concatenate IN tmpJournalEntryChangeOrders);

View 2 Replies View Related

Queries :: How To Delete Evaluated Data On The Query

Aug 26, 2014

I have a Query which can pull data to a form, then the form will evaluate the ID, after saving/recording, I need to delete the evaluated data on the query.

My problem is how can i delete it? There is Table1 for outputing the evaluation, Query1 where the evaluation can get the Data.

Process Flow:
> Form pulls data from Query1
> Evaluation was saved on Table1
> Evaluated ID should also be removed on Query1

View 1 Replies View Related

Queries :: Delete Query Where Code And Client Are Equal

Aug 8, 2013

I am trying to delete a record in tblinclude where record from tblexclude are equal to clientid and codeid

Here is the sql
DELETE tblinclude.ClientID
FROM tblexclude INNER JOIN tblinclude ON (tblexclude.ClientID = tblinclude.ClientID) AND (tblexclude.CodeID = tblinclude.CodeID)
WHERE (((tblinclude.ClientID)=1));

I get the error Specify the table containing the records you want to delete. I've searched for this but I am just not getting it today.

View 1 Replies View Related

Queries :: Pass Table Names To Delete Query

Aug 27, 2014

I use the following code to delete from a table all records except those meeting the WHERE criteria:

Code:
DELETE tblABC.*, tblABC.SubjectID
FROM tblABC
WHERE (((tblABC.SubjectID)<>99 And (tblABC.SubjectID)<>432));

I'd like to run this exact same query, but on many other tables, all of which are stored in tblTablesToClean (TableID, TableName).

Any good way to have Access loop through the list of tables in tblTablesToClean, each time passing the name of the table into the DELETE code and running the code, until all tables have been processed?

View 5 Replies View Related

Queries :: Append And Delete Query - Single Records

Mar 27, 2013

I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.

I understand this does not sound like a traditional database, as the data is not normalized. However, this is being used more as a filing system than anything else. Their data is primarily paper based (for legality reasons), but wishes to keep it organized in a database so they can run queries and print reports.

Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.

How to make the append query move only one student's record at a time.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved