sql update script

[eric]

Hi,
with the help of the q and a already present on the disc group, I tried to
figure out an UPDATE script for my my own db, but I just keep getting error
statements

In a table TABLE I want to update the fields of column SCORE from "0" to
"1", where the fields RESULT and RIGHTANSWER in the same record have the same
value. All the fields are numeric

This is what i've got so far:

Private Sub cmdButton_Click()
Dim dbs As Database
Dim strSQL As String
strSQL = " UPDATE tblTable" & _
" SET tblTable.Score = 1 " & _
" WHERE tblTable.Result = tblTable.RightAnswer "
dbs.Execute strSQL , dbFailOnError
End Sub

VB points at the dbs.Execute - line, giving error 91 on "With" not being
started.

Can anyone please help me solving the problem ?
Thank you in advance

 

[Albert D.Kallal]

Can you paste the sql into a query..and run it?

It sounds to me like you have the wrong table name....

Build the query in the query builder..and work on it until it works....Once
you get that working..then you can switch into view mode, and look at the
sql. You can even cut/paste that sql into your code as a starting point....

So, check the table name.....

 

[Douglas J. Steele]

You haven't instantiated dbs.

You need something like:

Set dbs = CurrentDb()

before you try to use it.

 

[eric]

Hi Albert,
Thank you for responding
Perhaps it's possible you guide me a little further on this matter ?

As a reaction to your response I started from scratch building an
Updatequery (is that the correct English term for this type of query?)

I made:

UPDATE tblTable SET tblTable.A_score = 1
WHERE (((tblTable.A_result)=[A_rightanswer]));

and tested it => works fine
So now I have => where result = rightanswer => score = 1

Now in the same update I want: where result <> rightanswer => score = 0

I tried to put it somehow in the above update, but I only got errormessages.
Can you lead me further on this ?

Thank you in advance

 

[Douglas J Steele]

UPDATE tblTable SET tblTable.A_score =
IIf(tblTable.A_result=[A_rightanswer], 1, 0)

However, I wouldn't recommend storing the score in the table at all.
Creating a query that includes a calculated field to do that, and use the
query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Albert,
Thank you for responding
Perhaps it's possible you guide me a little further on this matter ?

As a reaction to your response I started from scratch building an
Updatequery (is that the correct English term for this type of query?)

I made:

UPDATE tblTable SET tblTable.A_score = 1
WHERE (((tblTable.A_result)=[A_rightanswer]));

and tested it => works fine
So now I have => where result = rightanswer => score = 1

Now in the same update I want: where result <> rightanswer => score = 0

I tried to put it somehow in the above update, but I only got errormessages.
Can you lead me further on this ?

Thank you in advance

--
eric

Can you paste the sql into a query..and run it?

It sounds to me like you have the wrong table name....

Build the query in the query builder..and work on it until it works....Once
you get that working..then you can switch into view mode, and look at the
sql. You can even cut/paste that sql into your code as a starting point....

So, check the table name.....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[eric]

Hi Douglas,
Thank you for helping me out again (Albert thank you again for responding in
first instance). Thanks for your advice about (not)storing the score. Be sure
I appreciate your help on that matter later on.

The goodworking script you gave me I put (under a button) in VB like this:

Private Sub cmdButton_Click()
Dim dbs As Database
Dim strSQL As String
strSQL = " UPDATE tblTable" & _
" SET tblTable.A_score = " & _
" IIf(tblTable.A_result=[A_rightanswer], 1, 0) "
dbs.Execute strSQL , dbFailOnError
End Sub

And again, as earlier, I get "error 91 => objectvariable or block(?)variable
With not started", with dbs.Execute... highlighted.
Yet the script looks so much like simulair scripts I saw on the discgroup.
Can you help me out on this ?

greetings

--
eric

UPDATE tblTable SET tblTable.A_score =
IIf(tblTable.A_result=[A_rightanswer], 1, 0)

However, I wouldn't recommend storing the score in the table at all.
Creating a query that includes a calculated field to do that, and use the
query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Albert,
Thank you for responding
Perhaps it's possible you guide me a little further on this matter ?

As a reaction to your response I started from scratch building an
Updatequery (is that the correct English term for this type of query?)

I made:

UPDATE tblTable SET tblTable.A_score = 1
WHERE (((tblTable.A_result)=[A_rightanswer]));

and tested it => works fine
So now I have => where result = rightanswer => score = 1

Now in the same update I want: where result <> rightanswer => score = 0

I tried to put it somehow in the above update, but I only got errormessages.
Can you lead me further on this ?

Thank you in advance

--
eric

Can you paste the sql into a query..and run it?

It sounds to me like you have the wrong table name....

Build the query in the query builder..and work on it until it works....Once
you get that working..then you can switch into view mode, and look at the
sql. You can even cut/paste that sql into your code as a starting point....

So, check the table name.....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Douglas J Steele]

Did you not read my original response to your question?

You haven't instantiated dbs. You need a statement like:

Set dbs = CurrentDb()

in front of your dbs.Execute statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,
Thank you for helping me out again (Albert thank you again for responding in
first instance). Thanks for your advice about (not)storing the score. Be sure
I appreciate your help on that matter later on.

The goodworking script you gave me I put (under a button) in VB like this:

Private Sub cmdButton_Click()
Dim dbs As Database
Dim strSQL As String
strSQL = " UPDATE tblTable" & _
" SET tblTable.A_score = " & _
" IIf(tblTable.A_result=[A_rightanswer], 1, 0) "
dbs.Execute strSQL , dbFailOnError
End Sub

And again, as earlier, I get "error 91 => objectvariable or block(?)variable
With not started", with dbs.Execute... highlighted.
Yet the script looks so much like simulair scripts I saw on the discgroup.
Can you help me out on this ?

greetings

--
eric

UPDATE tblTable SET tblTable.A_score =
IIf(tblTable.A_result=[A_rightanswer], 1, 0)

However, I wouldn't recommend storing the score in the table at all.
Creating a query that includes a calculated field to do that, and use the
query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Albert,
Thank you for responding
Perhaps it's possible you guide me a little further on this matter ?

As a reaction to your response I started from scratch building an
Updatequery (is that the correct English term for this type of query?)

I made:

UPDATE tblTable SET tblTable.A_score = 1
WHERE (((tblTable.A_result)=[A_rightanswer]));

and tested it => works fine
So now I have => where result = rightanswer => score = 1

Now in the same update I want: where result <> rightanswer => score = 0

I tried to put it somehow in the above update, but I only got errormessages.
Can you lead me further on this ?

Thank you in advance

--
eric


:

Can you paste the sql into a query..and run it?

It sounds to me like you have the wrong table name....

Build the query in the query builder..and work on it until it works....Once
you get that working..then you can switch into view mode, and look

at
the

sql. You can even cut/paste that sql into your code as a starting point....

So, check the table name.....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[eric]

Hi Douglas
To be honest with you: looking back at your first response in this thread,
I still didn't find the Set dbs statement. But I've got it now, implemented
it, and it works just fine.
A big learning-step again for me !!
Thanks for your patience and support !