NotNull Query

[Drum2001]

I have a form that contains 8 textboxes "Textbox30, Textbox31,
Textbox32, Textbox33, Textbox34, Textbox35, Textbox36, Textbox37".

This form is designed to allow the user to input up to eight Department
Codes. When they hit the submit button, it opens a report based off
the following query:

SELECT ra_report.CCENT ER, ra_report.BANK, ra_report.DIVIS ION,
ra_report.EMPNU M, ra_report.NAME, ra_report.TOKEN , ra_report.ASSIG NED,
ra_report.EXPIR ES, ra_report.USED
FROM ra_report;

Though I do not have it in the query, I am using the following
criteria: [forms]![SearchDept]![Textbox30] OR
[forms]![SearchDept]![Textbox31] OR etc.

Here is the problem. At this point, if all textboxes have values in
them, the report works great. However, if I do not use all eight, the
query returns all values due to the empty textboxes.

So, what I am looking for my be explained in the pseudocode below:

If textbox30 is NotNull, then use in the query... If textbox31 is
Notnull, then use in the query.. If textbox32 is null, do not use in
query...etc

Any suggestions would be welcomed!

Thank you very much!

~Drum2001

[Anthony England]

Re: NotNull Query


"Drum2001" <drum2001@gmail .com> wrote in message
news:1142258019 .933548.160730@ i40g2000cwc.goo glegroups.com.. .[color=blue]
>I have a form that contains 8 textboxes "Textbox30, Textbox31,
> Textbox32, Textbox33, Textbox34, Textbox35, Textbox36, Textbox37".
>
> This form is designed to allow the user to input up to eight Department
> Codes. When they hit the submit button, it opens a report based off
> the following query:
>
> SELECT ra_report.CCENT ER, ra_report.BANK, ra_report.DIVIS ION,
> ra_report.EMPNU M, ra_report.NAME, ra_report.TOKEN , ra_report.ASSIG NED,
> ra_report.EXPIR ES, ra_report.USED
> FROM ra_report;
>
> Though I do not have it in the query, I am using the following
> criteria: [forms]![SearchDept]![Textbox30] OR
> [forms]![SearchDept]![Textbox31] OR etc.
>
> Here is the problem. At this point, if all textboxes have values in
> them, the report works great. However, if I do not use all eight, the
> query returns all values due to the empty textboxes.
>
> So, what I am looking for my be explained in the pseudocode below:
>
> If textbox30 is NotNull, then use in the query... If textbox31 is
> Notnull, then use in the query.. If textbox32 is null, do not use in
> query...etc
>
> Any suggestions would be welcomed!
>
> Thank you very much!
>
> ~Drum2001[/color]


Hi
This was from a previous thread which ran out due to me being away from my
desk since Friday. The easiest way to do this (since you are running a
report) is to use the where condition when opening the report.
In other words, your report is based on all records, but you build up a
special where clause to open it with. Then you call, for example:

DoCmd.OpenRepor t strReport, acViewPreview, , strWhere

The where clause could be some thing like "CCENTER IN ('one','two','t hree')"

[Drum2001]

Re: NotNull Query


Anthony England wrote:[color=blue]
> "Drum2001" <drum2001@gmail .com> wrote in message
> news:1142258019 .933548.160730@ i40g2000cwc.goo glegroups.com.. .[color=green]
> >I have a form that contains 8 textboxes "Textbox30, Textbox31,
> > Textbox32, Textbox33, Textbox34, Textbox35, Textbox36, Textbox37".
> >
> > This form is designed to allow the user to input up to eight Department
> > Codes. When they hit the submit button, it opens a report based off
> > the following query:
> >
> > SELECT ra_report.CCENT ER, ra_report.BANK, ra_report.DIVIS ION,
> > ra_report.EMPNU M, ra_report.NAME, ra_report.TOKEN , ra_report.ASSIG NED,
> > ra_report.EXPIR ES, ra_report.USED
> > FROM ra_report;
> >
> > Though I do not have it in the query, I am using the following
> > criteria: [forms]![SearchDept]![Textbox30] OR
> > [forms]![SearchDept]![Textbox31] OR etc.
> >
> > Here is the problem. At this point, if all textboxes have values in
> > them, the report works great. However, if I do not use all eight, the
> > query returns all values due to the empty textboxes.
> >
> > So, what I am looking for my be explained in the pseudocode below:
> >
> > If textbox30 is NotNull, then use in the query... If textbox31 is
> > Notnull, then use in the query.. If textbox32 is null, do not use in
> > query...etc
> >
> > Any suggestions would be welcomed!
> >
> > Thank you very much!
> >
> > ~Drum2001[/color]
>
>
> Hi
> This was from a previous thread which ran out due to me being away from my
> desk since Friday. The easiest way to do this (since you are running a
> report) is to use the where condition when opening the report.
> In other words, your report is based on all records, but you build up a
> special where clause to open it with. Then you call, for example:
>
> DoCmd.OpenRepor t strReport, acViewPreview, , strWhere
>
> The where clause could be some thing like "CCENTER IN ('one','two','t hree')"[/color]


What is the significance of 'one', 'two', 'three' ?

I don't understand the logic... Sorry

If you wouldnt mind, what exatly needs to be in the "WHERE CLAUSE"

[salad]

Re: NotNull Query

Drum2001 wrote:[color=blue]
> I have a form that contains 8 textboxes "Textbox30, Textbox31,
> Textbox32, Textbox33, Textbox34, Textbox35, Textbox36, Textbox37".
>
> This form is designed to allow the user to input up to eight Department
> Codes. When they hit the submit button, it opens a report based off
> the following query:
>
> SELECT ra_report.CCENT ER, ra_report.BANK, ra_report.DIVIS ION,
> ra_report.EMPNU M, ra_report.NAME, ra_report.TOKEN , ra_report.ASSIG NED,
> ra_report.EXPIR ES, ra_report.USED
> FROM ra_report;
>
> Though I do not have it in the query, I am using the following
> criteria: [forms]![SearchDept]![Textbox30] OR
> [forms]![SearchDept]![Textbox31] OR etc.
>
> Here is the problem. At this point, if all textboxes have values in
> them, the report works great. However, if I do not use all eight, the
> query returns all values due to the empty textboxes.
>
> So, what I am looking for my be explained in the pseudocode below:
>
> If textbox30 is NotNull, then use in the query... If textbox31 is
> Notnull, then use in the query.. If textbox32 is null, do not use in
> query...etc[/color]

criteria: Is Not Null([forms]![SearchDept]![Textbox30]) OR
Is Not Null([forms]![SearchDept]![Textbox31]) OR etc.

Or it might be if you want matching record values
Table.Field = [forms]![SearchDept]![Textbox30] OR
Table.Field = [forms]![SearchDept]![Textbox31] OR etc.
[color=blue]
>
> Any suggestions would be welcomed!
>
> Thank you very much!
>
> ~Drum2001
>[/color]

[Anthony England]

Re: NotNull Query


"Drum2001" <drum2001@gmail .com> wrote in message
news:1142262218 .750572.213220@ z34g2000cwc.goo glegroups.com.. .[color=blue]
>
> Anthony England wrote:[color=green]
>> "Drum2001" <drum2001@gmail .com> wrote in message
>> news:1142258019 .933548.160730@ i40g2000cwc.goo glegroups.com.. .[color=darkred]
>> >I have a form that contains 8 textboxes "Textbox30, Textbox31,
>> > Textbox32, Textbox33, Textbox34, Textbox35, Textbox36, Textbox37".
>> >
>> > This form is designed to allow the user to input up to eight Department
>> > Codes. When they hit the submit button, it opens a report based off
>> > the following query:
>> >
>> > SELECT ra_report.CCENT ER, ra_report.BANK, ra_report.DIVIS ION,
>> > ra_report.EMPNU M, ra_report.NAME, ra_report.TOKEN , ra_report.ASSIG NED,
>> > ra_report.EXPIR ES, ra_report.USED
>> > FROM ra_report;
>> >
>> > Though I do not have it in the query, I am using the following
>> > criteria: [forms]![SearchDept]![Textbox30] OR
>> > [forms]![SearchDept]![Textbox31] OR etc.
>> >
>> > Here is the problem. At this point, if all textboxes have values in
>> > them, the report works great. However, if I do not use all eight, the
>> > query returns all values due to the empty textboxes.
>> >
>> > So, what I am looking for my be explained in the pseudocode below:
>> >
>> > If textbox30 is NotNull, then use in the query... If textbox31 is
>> > Notnull, then use in the query.. If textbox32 is null, do not use in
>> > query...etc
>> >
>> > Any suggestions would be welcomed!
>> >
>> > Thank you very much!
>> >
>> > ~Drum2001[/color]
>>
>>
>> Hi
>> This was from a previous thread which ran out due to me being away from
>> my
>> desk since Friday. The easiest way to do this (since you are running a
>> report) is to use the where condition when opening the report.
>> In other words, your report is based on all records, but you build up a
>> special where clause to open it with. Then you call, for example:
>>
>> DoCmd.OpenRepor t strReport, acViewPreview, , strWhere
>>
>> The where clause could be some thing like "CCENTER IN
>> ('one','two','t hree')"[/color]
>
>
> What is the significance of 'one', 'two', 'three' ?
>
> I don't understand the logic... Sorry
>
> If you wouldnt mind, what exatly needs to be in the "WHERE CLAUSE"[/color]



The where clause simply tells you which records to select - my example
assumed you had 3 of the eight textboxes filled in and that you were looking
to run a report where the ccenter was 'one' or 'two' or 'three'.

You write code in the OnClick event of the button which is supposed to
generate the report. The first step is to build up the where clause, an
example is shown below.

If you still can't get and your e-mail address is valid I can e-mail you a
sample.



If IsNull(Me.Textb ox30) Then
' No criteria here - so ignore
Else
strWhere=strWhe re & ",'" & Me.Textbox30.Va lue & "'"
End If

If IsNull(Me.Textb ox31) Then
' No criteria here - so ignore
Else
strWhere=strWhe re & ",'" & Me.Textbox31.Va lue & "'"
End If

If IsNull(Me.Textb ox32) Then
' No criteria here - so ignore
Else
strWhere=strWhe re & ",'" & Me.Textbox32.Va lue & "'"
End If

' Etc,etc with other 5 textboxes

If Len(strWhere)>1 Then
strWhere=Mid$(s trWhere,2)
strWhere="CCENT ER IN (" & strWhere & ")"
End If

DoCmd.OpenRepor t strReport, acViewPreview, , strWhere

[Drum2001]

Re: NotNull Query

Got it!

Thank you very much for your assistance and patience!

~Drum2001

[Drum2001]

Re: NotNull Query

How would one do the same thing with a listbox? Is these even possible?

[Anthony England]

Re: NotNull Query


"Drum2001" <drum2001@gmail .com> wrote in message
news:1142277576 .938393.216160@ v46g2000cwv.goo glegroups.com.. .[color=blue]
> How would one do the same thing with a listbox? Is these even possible?[/color]


Yes - this is not only possible, but a sensible progression from what you
are currently doing. The trick to it is to set the multi-select property of
the listbox so you can select multiple items - then you can loop through the
ItemsSelected collection.

The only complication is how many columns you will need to show, which are
text and which are numeric. In this example, I have a single listbox called
lstCenter to show the ccenters which contains a single text column to
identify the center.

Private Sub cmdReport_Click ()

On Error GoTo Err_Handler

Dim strValue As String
Dim strWhere As String
Dim varSelected As Variant

With Me.lstCenter

For Each varSelected In .ItemsSelected
strValue = CStr(.Column(0, varSelected))
strValue = Replace(strValu e, """", """""")
strWhere = strWhere & ",""" & strValue & """"
Next varSelected

End With

If Len(strWhere) > 1 Then
strWhere = Mid$(strWhere, 2)
strWhere = "SetValue IN (" & strWhere & ")"
End If

DoCmd.OpenRepor t "MyReport", acViewPreview, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Drum2001]

Re: NotNull Query

Got it again!

Thank you very much for all your help!

This will end this thread.