asked on

Help with Update statement

Hello, I have a table called tblMDRStatusReports where I first insert a row of zeros like this:

Insert into tblMDRStatusReports (QTYMDR, OpenMDR, ClosedMDR, MDRwRMA, MDRwShipperID, MDRSentToAccounting, MDRScrap) Select 0, 0, 0, 0, 0, 0, 0

I now want to come back and update the row with several update statements to populate the row with the correct data

For example I want to use the following statement to update the QTYMDR of the row previously inserted:

Update tblMDRStatusReports Set QTRMDR = ( SELECT COUNT(*) FROM [DMR Tracking] WHERE [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM')

The first thing that occurs is that I'm asked to provide QTRMDR
Then I get the following error:
operation must use an updateable query.

What is wrong with my update statement. I just want to update the existing row with a count of rows that fall within where parameters.

rshq

Hi
Maybe you have a relation between tblMDRStatusReports and [DMR Tracking] .
If any relation please delete it and test again.

gogetsome

ASKER

No relation between the tables. I deleted the row, added it and still have the same issue.

gogetsome

ASKER

I thought that perhaps the table needed a primary key so I added one and changed my query to this:

Update tblMDRStatusReports Set QTRMDR = (SELECT COUNT(*) FROM [DMR Tracking] WHERE [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM') Where ID=1

Same issue as before.

This part of the statement returns the desired result set.

SELECT COUNT(*)
FROM [DMR Tracking]
WHERE [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM';

This appears to be a simple update statement. What gives?

rshq

Please use simple update like this
Update tblMDRStatusReports Set QTRMDR =5

gogetsome

ASKER

Yup, same issue. Why is that?

Rey Obrero (Capricorn1)

<The first thing that occurs is that I'm asked to provide QTRMDR>

you don't have a field named "QTRMDR", the name is "QTYMDR"

Update tblMDRStatusReports Set QTYMDR = ( SELECT COUNT(*) FROM [DMR Tracking] WHERE [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM')

gogetsome

ASKER

This works:
Update tblMDRStatusReports Set QTRMDR =5

but this does not

Update tblMDRStatusReports Set QTYMDR = (SELECT COUNT(*) FROM [DMR Tracking] WHERE [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM')

This works:

SELECT COUNT(*) FROM [DMR Tracking] WHERE [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM'

So why when I combine to I get
operation must use an updateable query.

gogetsome

ASKER

From what I have been reading perhaps access can do this sort of query. Such a limitation if that is true.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gogetsome

ASKER

Very cool! Thank you so much for your assistance!

gogetsome

ASKER

Capricorn1, I hate to come back once closed but I need a little help converting the SQL to run from VBA.

My VBA likes the following syntax but I'm getting the following error when I execute the DoCmd.RunSQL

Error:

Error number=3075, syntax error(missing operator) in query expression

DoCmd.RunSQL "Update [tblMDRStatusReports] Set QTYMDR = dcount('*' ,'DMR Tracking','[Date Issued]>=#" & Me.Start_Date & "# and [Date Issued]<=#" & Me.End_Date & "# And Supplier = " & Me.Supplier & ")"

Open in new window

Rey Obrero (Capricorn1)

DoCmd.RunSQL "Update [tblMDRStatusReports] Set QTYMDR = dcount('*' ,'DMR Tracking','[Date Issued]>=#" & Me.Start_Date & "# and [Date Issued]<=#" & Me.End_Date & "# And Supplier = '" & Me.Supplier & "')"

gogetsome

ASKER

Thanks for coming back!
That is giving the following error. See image