How to create if-condition for delete-query

  • Thread starter Thread starter Janetzky
  • Start date Start date
J

Janetzky

Hi,

I dont know why i can't figure this. I like to define the criterias for
a delete-query in a form. if there is no criteria selected in the form
it should leave the criteria empty and delete all datasets.

i try to cope this with help of following formula in the query I
If(IsNull([forms]![switchboard]![cmd_splash_cpy]),Like
"*",[forms]![switchboard]![cmd_splash_cpy])
 
Janetzky,

[Forms]![switchboard]![cmd_splash_cpy] Or
[Forms]![switchboard]![cmd_splash_cpy] Is Null
 
Thanks, you just saved my day!!!




Steve said:
Janetzky,

[Forms]![switchboard]![cmd_splash_cpy] Or
[Forms]![switchboard]![cmd_splash_cpy] Is Null

--
Steve Schapel, Microsoft Access MVP

Hi,

I dont know why i can't figure this. I like to define the criterias for
a delete-query in a form. if there is no criteria selected in the form
it should leave the criteria empty and delete all datasets.

i try to cope this with help of following formula in the query I
If(IsNull([forms]![switchboard]![cmd_splash_cpy]),Like
"*",[forms]![switchboard]![cmd_splash_cpy])
Click to expand...
Click to expand...
 
Hi,

I dont know why i can't figure this. I like to define the criterias for
a delete-query in a form. if there is no criteria selected in the form
it should leave the criteria empty and delete all datasets.

i try to cope this with help of following formula in the query I
If(IsNull([forms]![switchboard]![cmd_splash_cpy]),Like
"*",[forms]![switchboard]![cmd_splash_cpy])
Click to expand...

Don't use IIF at all for this purpose. Use a criterion instead. If you
want to delete all records where the field splash_cpy is equal to the
value in the (textbox? combo box? some other control? certainly not a
command button, which has no Value property!) cmd_splash_cpy, or
delete all records in the table if that control is NULL, use a
criterion

= [forms]![switchboard]![cmd_splash_cpy] OR
[forms]![switchboard]![cmd_splash_cpy] IS NULL


John W. Vinson[MVP]
 
Thanks, you just saved my day!!!




Steve said:
Janetzky,

[Forms]![switchboard]![cmd_splash_cpy] Or
[Forms]![switchboard]![cmd_splash_cpy] Is Null

--
Steve Schapel, Microsoft Access MVP

Hi,

I dont know why i can't figure this. I like to define the criterias for
a delete-query in a form. if there is no criteria selected in the form
it should leave the criteria empty and delete all datasets.

i try to cope this with help of following formula in the query I
If(IsNull([forms]![switchboard]![cmd_splash_cpy]),Like
"*",[forms]![switchboard]![cmd_splash_cpy])
Click to expand...
Click to expand...
 
Ok i added the critereon, by the way i have 7 critereons to filter for.
When i ran the query, it has not kept the string as an critereon for a
later use, instead the querry is filled with 100 critereons. I really
want to use the querry more than once!
Help

Thanks, you just saved my day!!!




Steve said:
Janetzky,

[Forms]![switchboard]![cmd_splash_cpy] Or
[Forms]![switchboard]![cmd_splash_cpy] Is Null

--
Steve Schapel, Microsoft Access MVP

Hi,

I dont know why i can't figure this. I like to define the criterias for
a delete-query in a form. if there is no criteria selected in the form
it should leave the criteria empty and delete all datasets.

i try to cope this with help of following formula in the query I
If(IsNull([forms]![switchboard]![cmd_splash_cpy]),Like
"*",[forms]![switchboard]![cmd_splash_cpy])
Click to expand...
Click to expand...
Click to expand...
 
Janetzky,

Yes, when you save a query with this type of a criteria entry, Access
will optimise it by reorganising into separte columns. So, as you see,
it can get very complex if you are doing the same thing simultaneously
with multiple fields. In such cases, I would usually prefer to use a
VBA procedure to build the Where clause of the query in code. What are
you using this query for... a report, or to display the selected subset
of the data on another form, or... something else?
 
Hi Steve,

I try to manipulate a cube-like data structure which i keep in a
backend database. I want to enable the user to select the data he might
like to delete, duplicate, or to update by changing either one or up to
seven criterias (Company, Year, Period, Account, Cost Center, Datatype,
Order + Value.) For this reason i thought using combo-boxes in the
frontent might be suitable for choosing the criterias for the querry or
the vba procedure. What might be the coding for realization of the
scope (delete sections, duplicate sections by providing new criterias
in different combo boxes, and to update he value field by using another
text-box on the front-end).

Thanks again for your help again!

Soeren
 
Soeren,

Well, I would definitely recommend doing this by performing your data
manipulations in code. So, assuming the user is entering the criteria
in the comboboxes, and then click a button, or some other event, to make
it happen. So, to use the example of deleting records that meet the
criteria, the code on the applicable event might be something like this...

Dim strSQL As String
strSQL = "DELETE * FROM YourTable WHERE True"
If IsNull(Me.1stCombobox) Then
' proceed
Else
strSQL = strSQL & " AND [NumericalField]=" & Me.1stCombobox
End If
If IsNull(Me.2ndCombobox) Then
' proceed
Else
strSQL = strSQL & " AND [TextField]='" & Me.2ndCombobox & "'"
End If
... etc for all 7 comboboxes
CurrentDb.Execute strSQL, dbFailOnError
 
Steve,

I don't know there seems something to be wrong in the string:

Private Sub Command25_Click()

Dim strSQL As String

strSQL = "DELETE * FROM RAWDATA WHERE True"

If IsNull(Me.cmd_splash_cpy) Then
' proceed
Else
strSQL = strSQL & "[COMP]=" & Me.cmd_splash_cpy
End If

If IsNull(Me.cmd_splash_yr) Then
' proceed
Else
strSQL = strSQL & " AND [YEAR]=" & Me.cmd_splash_yr
End If

If IsNull(Me.cmd_splash_per) Then
' proceed
Else
strSQL = strSQL & " AND [PER]=" & Me.cmd_splash_per
End If

If IsNull(Me.cmd_splash_typ) Then
' proceed
Else
strSQL = strSQL & " AND [DATA]=" & Me.cmd_splash_typ & "'" 'Text
End If
If IsNull(Me.cmd_splash_cur) Then
' proceed
Else
strSQL = strSQL & " AND [CUR]=" & Me.cmd_splash_cur & "'" 'Text
End If
If IsNull(Me.cmd_splash_acc) Then
' proceed
Else
strSQL = strSQL & " AND [ACC]=" & Me.cmd_splash_acc
End If
If IsNull(Me.cmd_splash_cc) Then
' proceed
Else
strSQL = strSQL & " AND [CC]=" & Me.cmd_splash_cc & "'" 'Text
End If
If IsNull(Me.cmd_splash_ord) Then
' proceed
Else
strSQL = strSQL & " AND [ORD]=" & Me.cmd_splash_ord & "'" 'Text
End If


CurrentDb.Execute strSQL, dbFailOnError

End Sub
Steve said:
Soeren,

Well, I would definitely recommend doing this by performing your data
manipulations in code. So, assuming the user is entering the criteria
in the comboboxes, and then click a button, or some other event, to make
it happen. So, to use the example of deleting records that meet the
criteria, the code on the applicable event might be something like this...

Dim strSQL As String
strSQL = "DELETE * FROM YourTable WHERE True"
If IsNull(Me.1stCombobox) Then
' proceed
Else
strSQL = strSQL & " AND [NumericalField]=" & Me.1stCombobox
End If
If IsNull(Me.2ndCombobox) Then
' proceed
Else
strSQL = strSQL & " AND [TextField]='" & Me.2ndCombobox & "'"
End If
... etc for all 7 comboboxes
CurrentDb.Execute strSQL, dbFailOnError

--
Steve Schapel, Microsoft Access MVP

I try to manipulate a cube-like data structure which i keep in a
backend database. I want to enable the user to select the data he might
like to delete, duplicate, or to update by changing either one or up to
seven criterias (Company, Year, Period, Account, Cost Center, Datatype,
Order + Value.) For this reason i thought using combo-boxes in the
frontent might be suitable for choosing the criterias for the querry or
the vba procedure. What might be the coding for realization of the
scope (delete sections, duplicate sections by providing new criterias
in different combo boxes, and to update he value field by using another
text-box on the front-end).
Click to expand...
Click to expand...
 
You need " AND " in front of the condition if Me.cmd_splash_cpy isn't Null:
right now, you're getting:

DELETE * FROM RAWDATA WHERE True[COMP]=5

if it isn't


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Joppel said:
Steve,

I don't know there seems something to be wrong in the string:

Private Sub Command25_Click()

Dim strSQL As String

strSQL = "DELETE * FROM RAWDATA WHERE True"

If IsNull(Me.cmd_splash_cpy) Then
' proceed
Else
strSQL = strSQL & "[COMP]=" & Me.cmd_splash_cpy
End If

If IsNull(Me.cmd_splash_yr) Then
' proceed
Else
strSQL = strSQL & " AND [YEAR]=" & Me.cmd_splash_yr
End If

If IsNull(Me.cmd_splash_per) Then
' proceed
Else
strSQL = strSQL & " AND [PER]=" & Me.cmd_splash_per
End If

If IsNull(Me.cmd_splash_typ) Then
' proceed
Else
strSQL = strSQL & " AND [DATA]=" & Me.cmd_splash_typ & "'" 'Text
End If
If IsNull(Me.cmd_splash_cur) Then
' proceed
Else
strSQL = strSQL & " AND [CUR]=" & Me.cmd_splash_cur & "'" 'Text
End If
If IsNull(Me.cmd_splash_acc) Then
' proceed
Else
strSQL = strSQL & " AND [ACC]=" & Me.cmd_splash_acc
End If
If IsNull(Me.cmd_splash_cc) Then
' proceed
Else
strSQL = strSQL & " AND [CC]=" & Me.cmd_splash_cc & "'" 'Text
End If
If IsNull(Me.cmd_splash_ord) Then
' proceed
Else
strSQL = strSQL & " AND [ORD]=" & Me.cmd_splash_ord & "'" 'Text
End If


CurrentDb.Execute strSQL, dbFailOnError

End Sub
Steve said:
Soeren,

Well, I would definitely recommend doing this by performing your data
manipulations in code. So, assuming the user is entering the criteria
in the comboboxes, and then click a button, or some other event, to make
it happen. So, to use the example of deleting records that meet the
criteria, the code on the applicable event might be something like
this...

Dim strSQL As String
strSQL = "DELETE * FROM YourTable WHERE True"
If IsNull(Me.1stCombobox) Then
' proceed
Else
strSQL = strSQL & " AND [NumericalField]=" & Me.1stCombobox
End If
If IsNull(Me.2ndCombobox) Then
' proceed
Else
strSQL = strSQL & " AND [TextField]='" & Me.2ndCombobox & "'"
End If
... etc for all 7 comboboxes
CurrentDb.Execute strSQL, dbFailOnError

--
Steve Schapel, Microsoft Access MVP

I try to manipulate a cube-like data structure which i keep in a
backend database. I want to enable the user to select the data he might
like to delete, duplicate, or to update by changing either one or up to
seven criterias (Company, Year, Period, Account, Cost Center, Datatype,
Order + Value.) For this reason i thought using combo-boxes in the
frontent might be suitable for choosing the criterias for the querry or
the vba procedure. What might be the coding for realization of the
scope (delete sections, duplicate sections by providing new criterias
in different combo boxes, and to update he value field by using another
text-box on the front-end).
Click to expand...
Click to expand...
Click to expand...
 
Doug, it still fails by creating the string...

Dim strSQL As String

strSQL = "DELETE * FROM RAWDATA WHERE True"

If IsNull(Me.cmd_splash_cpy) Then
' proceed
Else
strSQL = strSQL & " AND [COMP]=" & Me.cmd_splash_cpy
End If

If IsNull(Me.cmd_splash_yr) Then
' proceed
Else
strSQL = strSQL & " AND [YEAR]=" & Me.cmd_splash_yr
End If

If IsNull(Me.cmd_splash_per) Then
' proceed
Else
strSQL = strSQL & " AND [PER]=" & Me.cmd_splash_per
End If

If IsNull(Me.cmd_splash_typ) Then
' proceed
Else
strSQL = strSQL & " AND [DATA]=" & Me.cmd_splash_typ & "'" 'Text
End If
If IsNull(Me.cmd_splash_cur) Then
' proceed
Else
strSQL = strSQL & " AND [CUR]=" & Me.cmd_splash_cur & "'" 'Text
End If
If IsNull(Me.cmd_splash_acc) Then
' proceed
Else
strSQL = strSQL & " AND [ACC]=" & Me.cmd_splash_acc
End If
If IsNull(Me.cmd_splash_cc) Then
' proceed
Else
strSQL = strSQL & " AND [CC]=" & Me.cmd_splash_cc & "'" 'Text
End If
If IsNull(Me.cmd_splash_ord) Then
' proceed
Else
strSQL = strSQL & " AND [ORD]=" & Me.cmd_splash_ord & "'" 'Text
End If


CurrentDb.Execute strSQL, dbFailOnError

End Sub
You need " AND " in front of the condition if Me.cmd_splash_cpy isn't Null:
right now, you're getting:

DELETE * FROM RAWDATA WHERE True[COMP]=5

if it isn't


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Joppel said:
Steve,

I don't know there seems something to be wrong in the string:

Private Sub Command25_Click()

Dim strSQL As String

strSQL = "DELETE * FROM RAWDATA WHERE True"

If IsNull(Me.cmd_splash_cpy) Then
' proceed
Else
strSQL = strSQL & "[COMP]=" & Me.cmd_splash_cpy
End If

If IsNull(Me.cmd_splash_yr) Then
' proceed
Else
strSQL = strSQL & " AND [YEAR]=" & Me.cmd_splash_yr
End If

If IsNull(Me.cmd_splash_per) Then
' proceed
Else
strSQL = strSQL & " AND [PER]=" & Me.cmd_splash_per
End If

If IsNull(Me.cmd_splash_typ) Then
' proceed
Else
strSQL = strSQL & " AND [DATA]=" & Me.cmd_splash_typ & "'" 'Text
End If
If IsNull(Me.cmd_splash_cur) Then
' proceed
Else
strSQL = strSQL & " AND [CUR]=" & Me.cmd_splash_cur & "'" 'Text
End If
If IsNull(Me.cmd_splash_acc) Then
' proceed
Else
strSQL = strSQL & " AND [ACC]=" & Me.cmd_splash_acc
End If
If IsNull(Me.cmd_splash_cc) Then
' proceed
Else
strSQL = strSQL & " AND [CC]=" & Me.cmd_splash_cc & "'" 'Text
End If
If IsNull(Me.cmd_splash_ord) Then
' proceed
Else
strSQL = strSQL & " AND [ORD]=" & Me.cmd_splash_ord & "'" 'Text
End If


CurrentDb.Execute strSQL, dbFailOnError

End Sub
Steve said:
Soeren,

Well, I would definitely recommend doing this by performing your data
manipulations in code. So, assuming the user is entering the criteria
in the comboboxes, and then click a button, or some other event, to make
it happen. So, to use the example of deleting records that meet the
criteria, the code on the applicable event might be something like
this...

Dim strSQL As String
strSQL = "DELETE * FROM YourTable WHERE True"
If IsNull(Me.1stCombobox) Then
' proceed
Else
strSQL = strSQL & " AND [NumericalField]=" & Me.1stCombobox
End If
If IsNull(Me.2ndCombobox) Then
' proceed
Else
strSQL = strSQL & " AND [TextField]='" & Me.2ndCombobox & "'"
End If
... etc for all 7 comboboxes
CurrentDb.Execute strSQL, dbFailOnError

--
Steve Schapel, Microsoft Access MVP

(e-mail address removed) wrote:

I try to manipulate a cube-like data structure which i keep in a
backend database. I want to enable the user to select the data he might
like to delete, duplicate, or to update by changing either one or up to
seven criterias (Company, Year, Period, Account, Cost Center, Datatype,
Order + Value.) For this reason i thought using combo-boxes in the
frontent might be suitable for choosing the criterias for the querry or
the vba procedure. What might be the coding for realization of the
scope (delete sections, duplicate sections by providing new criterias
in different combo boxes, and to update he value field by using another
text-box on the front-end).
Click to expand...
Click to expand...
Click to expand...
 
Thank you everybody I solved the case, obviously it was a problem with
the text field as steve already proposed the Textfield content needs to
put within ' in the string!
So now i am going to take look into copy datasets...

;-)
Doug, it still fails by creating the string...

Dim strSQL As String

strSQL = "DELETE * FROM RAWDATA WHERE True"

If IsNull(Me.cmd_splash_cpy) Then
' proceed
Else
strSQL = strSQL & " AND [COMP]=" & Me.cmd_splash_cpy
End If

If IsNull(Me.cmd_splash_yr) Then
' proceed
Else
strSQL = strSQL & " AND [YEAR]=" & Me.cmd_splash_yr
End If

If IsNull(Me.cmd_splash_per) Then
' proceed
Else
strSQL = strSQL & " AND [PER]=" & Me.cmd_splash_per
End If

If IsNull(Me.cmd_splash_typ) Then
' proceed
Else
strSQL = strSQL & " AND [DATA]=" & Me.cmd_splash_typ & "'" 'Text
End If
If IsNull(Me.cmd_splash_cur) Then
' proceed
Else
strSQL = strSQL & " AND [CUR]=" & Me.cmd_splash_cur & "'" 'Text
End If
If IsNull(Me.cmd_splash_acc) Then
' proceed
Else
strSQL = strSQL & " AND [ACC]=" & Me.cmd_splash_acc
End If
If IsNull(Me.cmd_splash_cc) Then
' proceed
Else
strSQL = strSQL & " AND [CC]=" & Me.cmd_splash_cc & "'" 'Text
End If
If IsNull(Me.cmd_splash_ord) Then
' proceed
Else
strSQL = strSQL & " AND [ORD]=" & Me.cmd_splash_ord & "'" 'Text
End If


CurrentDb.Execute strSQL, dbFailOnError

End Sub
You need " AND " in front of the condition if Me.cmd_splash_cpy isn't Null:
right now, you're getting:

DELETE * FROM RAWDATA WHERE True[COMP]=5

if it isn't


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Joppel said:
Steve,

I don't know there seems something to be wrong in the string:

Private Sub Command25_Click()

Dim strSQL As String

strSQL = "DELETE * FROM RAWDATA WHERE True"

If IsNull(Me.cmd_splash_cpy) Then
' proceed
Else
strSQL = strSQL & "[COMP]=" & Me.cmd_splash_cpy
End If

If IsNull(Me.cmd_splash_yr) Then
' proceed
Else
strSQL = strSQL & " AND [YEAR]=" & Me.cmd_splash_yr
End If

If IsNull(Me.cmd_splash_per) Then
' proceed
Else
strSQL = strSQL & " AND [PER]=" & Me.cmd_splash_per
End If

If IsNull(Me.cmd_splash_typ) Then
' proceed
Else
strSQL = strSQL & " AND [DATA]=" & Me.cmd_splash_typ & "'" 'Text
End If
If IsNull(Me.cmd_splash_cur) Then
' proceed
Else
strSQL = strSQL & " AND [CUR]=" & Me.cmd_splash_cur & "'" 'Text
End If
If IsNull(Me.cmd_splash_acc) Then
' proceed
Else
strSQL = strSQL & " AND [ACC]=" & Me.cmd_splash_acc
End If
If IsNull(Me.cmd_splash_cc) Then
' proceed
Else
strSQL = strSQL & " AND [CC]=" & Me.cmd_splash_cc & "'" 'Text
End If
If IsNull(Me.cmd_splash_ord) Then
' proceed
Else
strSQL = strSQL & " AND [ORD]=" & Me.cmd_splash_ord & "'" 'Text
End If


CurrentDb.Execute strSQL, dbFailOnError

End Sub
Steve Schapel schrieb:

Soeren,

Well, I would definitely recommend doing this by performing your data
manipulations in code. So, assuming the user is entering the criteria
in the comboboxes, and then click a button, or some other event, to make
it happen. So, to use the example of deleting records that meet the
criteria, the code on the applicable event might be something like
this...

Dim strSQL As String
strSQL = "DELETE * FROM YourTable WHERE True"
If IsNull(Me.1stCombobox) Then
' proceed
Else
strSQL = strSQL & " AND [NumericalField]=" & Me.1stCombobox
End If
If IsNull(Me.2ndCombobox) Then
' proceed
Else
strSQL = strSQL & " AND [TextField]='" & Me.2ndCombobox & "'"
End If
... etc for all 7 comboboxes
CurrentDb.Execute strSQL, dbFailOnError

--
Steve Schapel, Microsoft Access MVP

(e-mail address removed) wrote:

I try to manipulate a cube-like data structure which i keep in a
backend database. I want to enable the user to select the data he might
like to delete, duplicate, or to update by changing either one or up to
seven criterias (Company, Year, Period, Account, Cost Center, Datatype,
Order + Value.) For this reason i thought using combo-boxes in the
frontent might be suitable for choosing the criterias for the querry or
the vba procedure. What might be the coding for realization of the
scope (delete sections, duplicate sections by providing new criterias
in different combo boxes, and to update he value field by using another
text-box on the front-end).
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

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

Delete query on Switchboard 5
Is Null 2
Wrong Form opening 5
query on blank or range 1
Pass Query Parameter From Module 2
Running Report from Query using Open Form or Entering Criteria 1
Query based on Check Box 1
query display all records if no criteria entered 3

Back
Top