Update Query Gone Wrong

Dec 7, 2005

please help, i have a form where i enter goods recieved vouchers,in this form if the cost price on the form is higher than the price in the Stock table then the price must be updated, if the price is lower then the price in the stock table doesnt need to be updated.
I have a method which i have used before with no errors:
i run an if statement on my Lost Focus() event procedure on TxtCostPrice
it looks like this:

Sub txtCostPrice_LostFocus ()
Dim Cost1 As String
Dim Cost2 As String
Cost1 = txtCostPerUnit
Cost2 = DLookup("price", "Stock Numbers")

If Cost1 < Cost2 Then

Dim DocName As String
DocName = "updateIfCostPrice"
DoCmd OpenQuery DocName, A_NORMAL, A_EDIT

MsgBox "Cost price has been updated"

ElseIf Cost1 > Cost2 Then
MsgBox "Cost price does not need to be updated"

End If

End Sub
this code links me to my update query, everything on my update query looks fine but when i run the code it doesnt seem to be updating.

Please help as my deadline is fast approaching at an arlaming speed!

View Replies


ADVERTISEMENT

What Is Wrong With This Query?

Jan 15, 2007

SELECT s.weekth, s.contracttype, sum(case when s.[contract status]='advance complete' then price end)/sum([purchaing price])

from submissionandpd as s

THX

View 5 Replies View Related

What Wrong With This Query

Jan 22, 2007

t2 is a subset of t1, I want t1-t2.
but following code does not seem to work.


SELECT *
FROM t1
except
select *
from t2

How to fix this problem? Thanks.

View 1 Replies View Related

What Is Wrong With This Query?

Mar 14, 2007

SELECT Orders.DelAdd1 AS Street, Orders.DelAdd2 AS [Street 2], Orders.DelTown AS Town, Orders.DelCounty AS County, Orders.DelPostcode AS Postcode, Sandwich.SandwichName AS Sandwich, sum(Orderline.Quantity) AS Quantity
FROM Orders, Orderline, Sandwich
WHERE Orderline.Sandwichno=Orders.SandwichNo and Orders.DeliveryDate=[Please enter Today's Date]
GROUP BY Orders.DelPostcode;

It says I've not specified street as an aggregate function, but I don't want to!

View 1 Replies View Related

What Wrong With My Query

May 15, 2007

Hi Guys,

what could be the probelm with my query? Here:
******************
SELECT tblPartnerProfile.PartnerType AS [Org Type], tblPartnerProfile.PartnerName, tblPartnerProfile.PartnerLoc1, tblPartnerProfile.PartnerStatus AS Status, Count(PartnerType) AS TotalType,
FROM tblPartnerProfile
WHERE PartnerLoc1 = 1 OR PartnerLoc1 = 2
******************************
I would like to have it give a count of how many [Org Type] 1 and 2 for Loc1 1 and Loc2 2.

Thanks in advance,

Samia

View 4 Replies View Related

What Is Wrong With This Query

Jul 24, 2007

Hello,
I have a Query that I wrote as follows:

SELECT [TableA].[FieldA], [TableA].[FieldB], TableB.FieldC
FROM [TableA],TableB
RIGHT JOIN [TableA] ON [TableA].[FieldB] = TableB.[FieldD]
GROUP BY [TableA].[FieldA], [TableA].[FieldB], TableB.FieldC;

What happened was that TableA has Fields A and B, and Table B has a field C and D. Tables B and D have the same content (Not necessary unique) and I would like to see a mapping from Field A -> Field C by joining on Field B and D.

However, when I ran it, I got an "invalid arguement" error.

Could anyone help? Thanks a lot!


Regards,
Anyi

View 1 Replies View Related

Wrong Data In Query

Jan 4, 2006

I am not sure how this query I made is pulling up wrong data. Everything else is fine, except for 1 row of data. It is supposed to pull how many demonstations were scheduled for that day and how many were executed. It comes up with 4 demos and 8 executed. That can't be right. I checked it with the actual data and there are 6 demos total and 4 executed. Anyone know what could be going on there? For the other 30 something records it works just fine.

SQL:
SELECT [Promo count].PromoNo, [Promo count].[# of Demos], Count(DemoOrder.Status) AS CountOfStatus, CountOfStatus/[# of Demos] AS Percentage
FROM [Promo count] INNER JOIN DemoOrder ON [Promo count].PromoNo = DemoOrder.PromoNo
WHERE (((DemoOrder.Status)="E"))
GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos];

View 1 Replies View Related

Whats Wrong With This Query?

Aug 15, 2006

trying to create a table using a query, and I'm not sure whats wrong with this query:

CREATE TABLE FYIAdmin
(
FYI_ID COUNTER CONSTRAINT PRIMARY KEY,
FYI_Title varchar(100),
FYI_Text memo,
FYI_Date date
)

Sorry it its noobish :(

View 2 Replies View Related

Wrong Query Return

Feb 14, 2006

I placed ="G*" in a text field to get all names that begin with G and a blank return was issued. Maybe the manual I have has the wrong info. I have Access 2003 and a Access 2000 manual. Please help.

View 1 Replies View Related

Union Select Query (What Am I Doing Wrong)

May 28, 2006

Please see attached database

If you open the form called people you will see a command button
Called add charge once pressed will open a form called charge

On the Bill To combo Box I have the following Union Select Query

SELECT PrimaryInsurance, PersonID FROM tblpeople UNION Select SecondaryInsurance, PersonID FROM tblPeople

What I want the bill to combo box to display is only the Insurances that are related to the
Patient that the charge is for and not to display other patients insurances


Any Ideas

View 2 Replies View Related

Some Record Not Showing On Query.something Wrong?

Oct 27, 2007

I have make relational table on query, but some record not sohowing on query, although i have the record on table.

for example, i can't see the record with code :
131401.007
541000.00*

is the anything wrong?

thanks

View 3 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 Correct But Report Wrong?

Aug 3, 2015

Query 1: has all the data (description, account, amount).
Query 2: sums Amount by Account No.
Query 3: combines Query 1 and 2.

Query 3 also has a join by the Account No. There is a report based on query 3.

Issue: I can run query 3 to equal 0 and generate the report correctly but when I generate the query to not equal 0 the report brings in all the data.

View 4 Replies View Related

Queries :: Query Returning Wrong Results?

May 7, 2014

I have two tables Contacts & Allowance. There is a relationship defined between the two based on Contact ID (primary field/contacts - foreign key/allowance). The Contacts table has Firstname + Surname. I have produced a query based on certain fields in both tables however the result is totally pulling incorrect info.

View 11 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

Access Giving Wrong Result Using Sql Query

May 10, 2012

I am using a MS access mdb file to display some record from oracle database using odbc connection.I have a table (linked table) called map_detail in mdb as well as oracle with same table structure.I formed one query in mdb (sql query) select * from map_detail where batch_no="SSO15121".It is always fetching some other result, but when I am changing the query by changing the batch no "SSO15148" it is working fine. I noticed that for cases it working.

View 3 Replies View Related

Query With Multiple Outer Joins Gives Wrong Result

Dec 15, 2007

Hello,

My query references 2 related tables: one for persons (PERS) and one for telephone/fax numbers and email addresses (CONT, for Contacts).
The relevant fields are:

PERS
pers_id (long) ---> primary key
pers_forename (text)
pers_surname (text)

CONT
cont_id (long) ---> primary key
pers_id (long) ---> foreign key
come_id (long) ---> foreign key
cont_number (text)

The foreign key come_id refers to a table for contact methods (COME), either "Phone (Home)", "Phone (Work)", "Mobile", "Fax" or "Email".

Now i want to list all persons with their home phone number and email address, also if they don't have one. It seems to be impossible to get it ...
I will explain what happens. Lets start simple: first list all persons with their home phone number (come_id = 1):
SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone
FROM PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id
WHERE (((IIf(IsNull([come_id]),1,[come_id]))=1));
This works fine. The IIf expression is necessary since we are dealing with an outer join: not all persons have a home phone number. If we would simply put "WHERE come_id = 1" then the query produces only the persons that have a home phone number.

But now i also want to see the email address (come_id = 5):
SELECT PERS.pers_forename, PERS.pers_surname,
CONT.cont_number AS Phone, CONT_1.cont_number AS Email
FROM CONT AS CONT_1 RIGHT JOIN
(PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id)
ON CONT_1.pers_id = PERS.pers_id
WHERE (((IIf(IsNull([cont].[come_id]),1,[cont].[come_id]))=1)
AND ((IIf(IsNull([cont_1].[come_id]),5,[cont_1].[come_id]))=5));
It seems perfectly logical: i added a second alias CONT_1 for the email address. Since this is also optional we have a second outer join, and the WHERE condition should also use an IIf expression.
The result is not correct though: the resulting recordset shows only the persons that have both a home phone number and an email addres or neither!
I have a lot of experience with SQL and queries, but i know i am not infallible. Nevertheless i am quite convinced that i should get all the persons: those that have a home phone number or an email address, or both or neither ...
I hope that someone of you can explain this.

View 3 Replies View Related

Modules & VBA :: Query Inserts Wrong Data In Table

Apr 30, 2015

I am writing the following query to insert data in tblpostroom from another table. Both the tables have same field names like Date1,582,1810.Now If I run the following query then in the fields 582,1810 of tblpostroom , the data gets stored 582,1810 respectively for all the records. But in actual fields 582 and 1810 store different data.

Code:
INSERT INTO tblpostroom ( Date1, 582, 1810 )
SELECT Date1, 582, 1810
FROM Sheet1;

View 5 Replies View Related

Can I Get An Update Query To Not Add Records To Tables Only Update?

Jun 28, 2005

Hi Guys,

I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.

It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.

Any suggestion guys & gals?

Many thanks
Tim

View 9 Replies View Related

Using Criteria To Update Fields In A Update Query

Nov 29, 2006

Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers

View 3 Replies View Related

Update Query Won't Update In Access 97

Feb 10, 2008

I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?

Thanks in advance

Wayne

View 5 Replies View Related

Using Update Query To Update Values

Oct 3, 2007

I have a database where two tables contain information that I need to update based on the 4 right most digits of a field. There are only 11 values that will need to be updated out of a large list of values. I'm not quite sure how to set up the update query so that I can do this.

View 1 Replies View Related

Now Its All Gone Wrong!!!

Sep 21, 2005

Ok Guys,

Here goes...

all of a sudden now, when i open my form, its not displaying any of the previous data that i stored in it!! All the data is still in the table, and when i add data it goes into the table!! I checked properties and the record source and everything is ok there, what have i done wrong guys...HELP!!!!

View 3 Replies View Related

What's Wrong?

Jun 9, 2006

I purchased a copy of Office 2003, on Ebay, for less than half the price it normally sells for. I created databases, which work well on my computer. However, when I copy them over to the office computer from my computer, via Flash memory stick, the program does not run. The message seems to indicate a problem with registration. When I installed Access, it asked me for a registration key, which I did provide. After that it worked-not activation process. Does this mean that you can only use the computer you created the database on to run it, or that I do not have a proper version of Access 2003? I cannot transfer, from my home computer, to the office computer. Is this Microsoft paranoia again?
Subsequent uninstalls and reinstalls of the program did not ask for the installation key.
What should happen? I would like to resolve this major problem and your help will be greatly appreciated. The program is flaky. Also, Switchboard does not work-must use Option Box.

These questions most user must know about initial installation and copying mdb files to other computers -please reply.

View 2 Replies View Related

What's Wrong

Oct 16, 2006

Hi,
Can anyone tell me what's wrong with this:
DoCmd.RunSQL "INSERT INTO tblPersoaneJuridice (Denumire,Localitate,CodUnicInregistrare,NumarInre gistrareRegComertului,Telefon,Fax) VALUES ('" & Forms!frmPrincipal!subfrmTipVersus!txtDenumire & "','" & Forms!frmPrincipal!subfrmTipVersus!txtLocalitate & "','" & Forms!frmPrincipal!subfrmTipVersus!txtCUI & "','" & Forms!frmPrincipal!subfrmTipVersus!txtNumarInregis trare & "','" & Forms!frmPrincipal!subfrmTipVersus!txtTelefon & "','" & Forms!frmPrincipal!subfrmTipVersus!txtFax & "')"
cui = DLookup("[CodUnicInregistrare]", "[tblPersoaneJuridice]", "[NumarInregistrareRegComertului]='" & Me!subfrmTipVersus!txtNumarInregistrare & "'")

i have a string variable denoted by cui and i need it to take value from table tblPersoaneJuridice from field CodUnicInregistrare. CodUnicInregistrare and NumarInregistrareRegComertului is text.

View 3 Replies View Related

Would Someone Tell Me What's Wrong With This

Jun 14, 2007

Have not coded for about 5 years and need a little refresher. Can't remember what I need to do. Thanks

Dim SelectedCompany As String
Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim sSql As String

Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection

SelectedCompany = Me!cboCompanyName.Text

sSql = "select Customername, AddressLine1, AddressLine2, AddressLine3, AddressLine4, AddressLine5, AddressLine6 from customers where "
sSql = sSql & "CompanyName = '" & SelectedCompany & "'"

rs.Open sSql, Cn, adOpenStatic, adLockReadOnly

this last line fails with error "No value given for one or more required parameters."

The code is executed when someone selects something in a combo box on a form in an access database.

Thanks

Pete

View 4 Replies View Related







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