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

--
Steve Schapel, Microsoft Access MVP

(E-Mail Removed) wrote:
> 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])
>

Thanks, you just saved my day!!!




Steve Schapel wrote:
> Janetzky,
>
> [Forms]![switchboard]![cmd_splash_cpy] Or
> [Forms]![switchboard]![cmd_splash_cpy] Is Null
>
> --
> Steve Schapel, Microsoft Access MVP
>
> (E-Mail Removed) wrote:
> > 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])
> >

On 20 Dec 2006 12:50:11 -0800, (E-Mail Removed) wrote:

>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])

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 Schapel wrote:
> Janetzky,
>
> [Forms]![switchboard]![cmd_splash_cpy] Or
> [Forms]![switchboard]![cmd_splash_cpy] Is Null
>
> --
> Steve Schapel, Microsoft Access MVP
>
> (E-Mail Removed) wrote:
> > 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])
> >

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

(E-Mail Removed) wrote:
> Thanks, you just saved my day!!!
>
>
>
>
> Steve Schapel wrote:
> > Janetzky,
> >
> > [Forms]![switchboard]![cmd_splash_cpy] Or
> > [Forms]![switchboard]![cmd_splash_cpy] Is Null
> >
> > --
> > Steve Schapel, Microsoft Access MVP
> >
> > (E-Mail Removed) wrote:
> > > 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,

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?

--
Steve Schapel, Microsoft Access MVP

(E-Mail Removed) wrote:
> 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

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

Steve Schapel schrieb:

> 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?
>
> --
> Steve Schapel, Microsoft Access MVP
>
> (E-Mail Removed) wrote:
> > 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

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 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).

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 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).