Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

VBA SQL question

Hi

I'm using a SQL statement to update a table called tblMembers. In this table i want to update a field called "No_of_Rentals_at_Present". The following code works perfect but instead of letting it equal to 0, i want to decrease the current calue in the field by 1. I understand i have to change from "=", but i'm unaware what i should change it to.

"strSQL3 = "UPDATE tblMembers SET No_of_Rentals_at_Present = 0 where Member_ID=" & txtVideoMember"



Any help would be grateful.

0
rebel_vb
Asked:
rebel_vb
1 Solution
 
Arthur_WoodCommented:
try this:

strSQL3 = "UPDATE tblMembers SET No_of_Rentals_at_Present = iif(No_of_Rentals_at_Present > 0,No_of_Rentals_at_Present-1,0) where Member_ID=" & txtVideoMember


this will test to be sure that you are not trying to SUBTRACT 1 from the value when it is already 0, but will subtract 1 if the current value is 1 or more.

Arthur Wood
0
 
DeepBlueInVBCommented:
"UPDATE tblMembers
  SET No_of_Rentals_at_Present =
(case WHEN (No_of_Rentals_at_Present > 0)
THEN No_of_Rentals_at_Present -1
ELSE 0 END)
where Member_ID= '" & txtVideoMember & "'"
0
 
PeterNolanCommented:
Hi,

Instead of hard coding the value why not introduce another variable.

For instance

Dim intValue as integer

intValue = 1

strSQL3 = "UPDATE tblMembers SET No_of_Rentals_at_Present = " & intValue & " where Member_ID=" & txtVideoMember"

This way you can continually change the value you are looking for.

Peter
0
 
CleanupPingCommented:
Hi rebel_vb,
This old question (QID 20563711) needs to be finalized -- accept an answer, split points, or get a refund.  Please see http://www.cityofangels.com/Experts/Closing.htm for information and options.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now