SQL UPDATE on the fly

D

DS

I want to run an UPDATE based on field values. I'm not quite sure how.

DoCmd.SetWarnings False
Dim RSQL As String
RSQL = "UPDATE tblInfoItem SET [ItemPrice] =
([ItemPrice]*[TxtPercentChange])-[ItemPrice]" & _
"WHERE tblInfoItem.[TermID] = Forms!frmPriceChanger!TxtTerminal " & _
"AND tblInfoItem.MenuID = Forms!frmPriceChanger!TxtMenu " & _
"AND tblInfoItem.MenuCatID = Forms!frmPriceChanger!TxtSection " & _
"AND tblInfoItem.TypeID = Forms!frmPriceChanger!TxtType " & _
"AND tblInfoItem.DayID = Forms!frmPriceChanger!TxtDay;"
DoCmd.RunSQL (RSQL)

The problem is if any of the Txt* fields are left unfilled (they have a
0 as a default) I don't want them to be included in the UPDATE. So if
TxtMenu is left blank that means TxtMenu should not be included in the
WHERE part of the statement.

Any help appreciated.
Thnaks
DS
 
C

Carl Rapson

DS said:
I want to run an UPDATE based on field values. I'm not quite sure how.

DoCmd.SetWarnings False
Dim RSQL As String
RSQL = "UPDATE tblInfoItem SET [ItemPrice] =
([ItemPrice]*[TxtPercentChange])-[ItemPrice]" & _
"WHERE tblInfoItem.[TermID] = Forms!frmPriceChanger!TxtTerminal " & _
"AND tblInfoItem.MenuID = Forms!frmPriceChanger!TxtMenu " & _
"AND tblInfoItem.MenuCatID = Forms!frmPriceChanger!TxtSection " & _
"AND tblInfoItem.TypeID = Forms!frmPriceChanger!TxtType " & _
"AND tblInfoItem.DayID = Forms!frmPriceChanger!TxtDay;"
DoCmd.RunSQL (RSQL)

The problem is if any of the Txt* fields are left unfilled (they have a 0
as a default) I don't want them to be included in the UPDATE. So if
TxtMenu is left blank that means TxtMenu should not be included in the
WHERE part of the statement.

Any help appreciated.
Thnaks
DS
Click to expand...

You'l need to build your SQL string more dynamically. Something like this:

Dim flg As Boolean
RSQL = "UPDATE tblInfoItem SET [ItemPrice] =
([ItemPrice]*[TxtPercentChange])-[ItemPrice]"
flg = False
If (Forms!frmPriceChanger!TxtTerminal <> 0) Then
RSQL = RSQL & " WHERE tblInfoItem.[TermID] = " &
Forms!frmPriceChanger!TxtTerminal
flg = True
End If

If (Forms!frmPriceChanger!TxtMenu <> 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.MenuID = " & Forms!frmPriceChanger!TxtMenu
End If

If (Forms!frmPriceChanger!TxtSection <> 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.MenuCatID = " &
Forms!frmPriceChanger!TxtSection
End If

If (Forms!frmPriceChanger!TxtType <> 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.TypeID = " & Forms!frmPriceChanger!TxtType
End If

If (Forms!frmPriceChanger!TxtDay <> 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.DayID = " & Forms!frmPriceChanger!TxtDay
End If
RSQL = RSQL & ";"

Carl Rapson
 
D

DS

Carl said:
I want to run an UPDATE based on field values. I'm not quite sure how.

DoCmd.SetWarnings False
Dim RSQL As String
RSQL = "UPDATE tblInfoItem SET [ItemPrice] =
([ItemPrice]*[TxtPercentChange])-[ItemPrice]" & _
"WHERE tblInfoItem.[TermID] = Forms!frmPriceChanger!TxtTerminal " & _
"AND tblInfoItem.MenuID = Forms!frmPriceChanger!TxtMenu " & _
"AND tblInfoItem.MenuCatID = Forms!frmPriceChanger!TxtSection " & _
"AND tblInfoItem.TypeID = Forms!frmPriceChanger!TxtType " & _
"AND tblInfoItem.DayID = Forms!frmPriceChanger!TxtDay;"
DoCmd.RunSQL (RSQL)

The problem is if any of the Txt* fields are left unfilled (they have a 0
as a default) I don't want them to be included in the UPDATE. So if
TxtMenu is left blank that means TxtMenu should not be included in the
WHERE part of the statement.

Any help appreciated.
Thnaks
DS
Click to expand...


You'l need to build your SQL string more dynamically. Something like this:

Dim flg As Boolean
RSQL = "UPDATE tblInfoItem SET [ItemPrice] =
([ItemPrice]*[TxtPercentChange])-[ItemPrice]"
flg = False
If (Forms!frmPriceChanger!TxtTerminal <> 0) Then
RSQL = RSQL & " WHERE tblInfoItem.[TermID] = " &
Forms!frmPriceChanger!TxtTerminal
flg = True
End If

If (Forms!frmPriceChanger!TxtMenu <> 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.MenuID = " & Forms!frmPriceChanger!TxtMenu
End If

If (Forms!frmPriceChanger!TxtSection <> 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.MenuCatID = " &
Forms!frmPriceChanger!TxtSection
End If

If (Forms!frmPriceChanger!TxtType <> 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.TypeID = " & Forms!frmPriceChanger!TxtType
End If

If (Forms!frmPriceChanger!TxtDay <> 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.DayID = " & Forms!frmPriceChanger!TxtDay
End If
RSQL = RSQL & ";"

Carl Rapson
Click to expand...
Thanks Carl,
This helps, I'm still playing with it. I'm having a problem getting it
to update though. The first set doesn't have a AND as well as a nested
if, should it or...just askink as I am still on the learning curve here.
Thanks
DS
 
D

DS

Here's what I have. But I keep getting expected UPDATE, CANCEL, DELETE
Message....

If the fields are >0 then I want the value to be included in the SQL
statement, otherwise they are to be excluded.

Thanks
DS

RSQL = "UPDATE tblInfoItem SET [ItemPrice] =
([ItemPrice]*[TxtPercentChange])-[ItemPrice] " & _
flg = False
If (Forms!frmPriceChanger!TxtStation > 0) Then
RSQL = RSQL & " WHERE tblInfoItem.[TermID] = " &
Forms!frmPriceChanger!TxtStation
flg = True
End If

If (Forms!frmPriceChanger!TxtMenu > 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.MenuID = " & Forms!frmPriceChanger!TxtMenu
End If

If (Forms!frmPriceChanger!TxtSection > 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.MenuCatID = " & _
Forms!frmPriceChanger!TxtSection
End If

If (Forms!frmPriceChanger!TxtDay > 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & ";"
End If
RSQL = RSQL & "tblInfoItem.DayID = " & Forms!frmPriceChanger!TxtDay
End If
DoCmd.RunSQL (RSQL)
 
D

DS

OK it working. Just not correctly. 3 problems the first Statement RSQL
executes whether the txtbox value is 0 or not
this I'm sure will happen when the next 2 Statements decide to execute,
that being problems 2 and 3

I'm sure it's just a matter of syntax...
Any help is appreciated,
Thanks
DS

flg = False
RSQL = "UPDATE tblInfoItem SET [ItemPrice] =
[ItemPrice]-([ItemPrice]*[TxtPercentChange])"
If (Forms!frmPriceChanger!TxtStation > 0) Then
RSQL = RSQL & " WHERE tblInfoItem.[TerminalID] = " &
Forms!frmPriceChanger!TxtStation
flg = True
End If

If (Forms!frmPriceChanger!TxtMenu > 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.MenuID = " & Forms!frmPriceChanger!TxtMenu
End If

If (Forms!frmPriceChanger!TxtSection > 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.MenuCatID = " & Forms!frmPriceChanger!TxtSection
End If
If (Forms!frmPriceChanger!TxtDay > 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & ";"
End If
RSQL = RSQL & "tblInfoItem.DayID = " & Forms!frmPriceChanger!TxtDay
End If
DoCmd.RunSQL (RSQL)

flg = False
SSQL = "UPDATE tblInfoMod SET [ModPrice] =
[ModPrice]-([ModPrice]*[TxtPercentChange])"
If (Forms!frmPriceChanger!TxtGroup > 0) Then
If (flg = False) Then
SSQL = SSQL & " WHERE "
flg = True
Else
SSQL = SSQL & ";"
End If
SSQL = SSQL & "tblInfoMod.GroupID = " & Forms!frmPriceChanger!TxtGroup
End If
DoCmd.RunSQL (SSQL)

flg = False
TSQL = "UPDATE tblInfoSubMod SET [SubModPrice] =
[SubModPrice]-([SubModPrice]*[TxtPercentChange])"
If (Forms!frmPriceChanger!TxtSubGroup > 0) Then
If (flg = False) Then
TSQL = TSQL & " WHERE "
flg = True
Else
TSQL = TSQL & ";"
End If
TSQL = TSQL & "tblInfoSubMod.SubGroupID = " &
Forms!frmPriceChanger!TxtSubGroup
End If
DoCmd.RunSQL (TSQL)
 
D

DS

DS said:
OK it working. Just not correctly. 3 problems the first Statement RSQL
executes whether the txtbox value is 0 or not
this I'm sure will happen when the next 2 Statements decide to execute,
that being problems 2 and 3

I'm sure it's just a matter of syntax...
Any help is appreciated,
Thanks
DS

flg = False
RSQL = "UPDATE tblInfoItem SET [ItemPrice] =
[ItemPrice]-([ItemPrice]*[TxtPercentChange])"
If (Forms!frmPriceChanger!TxtStation > 0) Then
RSQL = RSQL & " WHERE tblInfoItem.[TerminalID] = " &
Forms!frmPriceChanger!TxtStation
flg = True
End If

If (Forms!frmPriceChanger!TxtMenu > 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.MenuID = " & Forms!frmPriceChanger!TxtMenu
End If

If (Forms!frmPriceChanger!TxtSection > 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & " AND "
End If
RSQL = RSQL & "tblInfoItem.MenuCatID = " & Forms!frmPriceChanger!TxtSection
End If
If (Forms!frmPriceChanger!TxtDay > 0) Then
If (flg = False) Then
RSQL = RSQL & " WHERE "
flg = True
Else
RSQL = RSQL & ";"
End If
RSQL = RSQL & "tblInfoItem.DayID = " & Forms!frmPriceChanger!TxtDay
End If
DoCmd.RunSQL (RSQL)

flg = False
SSQL = "UPDATE tblInfoMod SET [ModPrice] =
[ModPrice]-([ModPrice]*[TxtPercentChange])"
If (Forms!frmPriceChanger!TxtGroup > 0) Then
If (flg = False) Then
SSQL = SSQL & " WHERE "
flg = True
Else
SSQL = SSQL & ";"
End If
SSQL = SSQL & "tblInfoMod.GroupID = " & Forms!frmPriceChanger!TxtGroup
End If
DoCmd.RunSQL (SSQL)

flg = False
TSQL = "UPDATE tblInfoSubMod SET [SubModPrice] =
[SubModPrice]-([SubModPrice]*[TxtPercentChange])"
If (Forms!frmPriceChanger!TxtSubGroup > 0) Then
If (flg = False) Then
TSQL = TSQL & " WHERE "
flg = True
Else
TSQL = TSQL & ";"
End If
TSQL = TSQL & "tblInfoSubMod.SubGroupID = " &
Forms!frmPriceChanger!TxtSubGroup
End If
DoCmd.RunSQL (TSQL)
Click to expand...
Have Mercy! All is working now. The problem I had was in the setup of
the default in the comboboxs selecting the value. Also I'm using one
source to execute 2 different queries.. So I did a select before hand to
straighten things out...
Thanks everyone
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL UPDATE Problem 5
UPDATE SQL Problem 1
Update Query Problem 2

Top