By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,012 Members | 988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,012 IT Pros & Developers. It's quick & easy.

NotNull Query

P: n/a
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.CCENTER, ra_report.BANK, ra_report.DIVISION,
ra_report.EMPNUM, ra_report.NAME, ra_report.TOKEN, ra_report.ASSIGNED,
ra_report.EXPIRES, 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

Mar 13 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a

"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
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.CCENTER, ra_report.BANK, ra_report.DIVISION,
ra_report.EMPNUM, ra_report.NAME, ra_report.TOKEN, ra_report.ASSIGNED,
ra_report.EXPIRES, 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

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.OpenReport strReport, acViewPreview, , strWhere

The where clause could be some thing like "CCENTER IN ('one','two','three')"
Mar 13 '06 #2

P: n/a

Anthony England wrote:
"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
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.CCENTER, ra_report.BANK, ra_report.DIVISION,
ra_report.EMPNUM, ra_report.NAME, ra_report.TOKEN, ra_report.ASSIGNED,
ra_report.EXPIRES, 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

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.OpenReport strReport, acViewPreview, , strWhere

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

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"

Mar 13 '06 #3

P: n/a
Drum2001 wrote:
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.CCENTER, ra_report.BANK, ra_report.DIVISION,
ra_report.EMPNUM, ra_report.NAME, ra_report.TOKEN, ra_report.ASSIGNED,
ra_report.EXPIRES, 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
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.

Any suggestions would be welcomed!

Thank you very much!

~Drum2001

Mar 13 '06 #4

P: n/a

"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...

Anthony England wrote:
"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
>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.CCENTER, ra_report.BANK, ra_report.DIVISION,
> ra_report.EMPNUM, ra_report.NAME, ra_report.TOKEN, ra_report.ASSIGNED,
> ra_report.EXPIRES, 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

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.OpenReport strReport, acViewPreview, , strWhere

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

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"


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.Textbox30) Then
' No criteria here - so ignore
Else
strWhere=strWhere & ",'" & Me.Textbox30.Value & "'"
End If

If IsNull(Me.Textbox31) Then
' No criteria here - so ignore
Else
strWhere=strWhere & ",'" & Me.Textbox31.Value & "'"
End If

If IsNull(Me.Textbox32) Then
' No criteria here - so ignore
Else
strWhere=strWhere & ",'" & Me.Textbox32.Value & "'"
End If

' Etc,etc with other 5 textboxes

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

DoCmd.OpenReport strReport, acViewPreview, , strWhere

Mar 13 '06 #5

P: n/a
Got it!

Thank you very much for your assistance and patience!

~Drum2001

Mar 13 '06 #6

P: n/a
How would one do the same thing with a listbox? Is these even possible?

Mar 13 '06 #7

P: n/a

"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
How would one do the same thing with a listbox? Is these even possible?

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(strValue, """", """""")
strWhere = strWhere & ",""" & strValue & """"
Next varSelected

End With

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

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

Exit_Handler:
Exit Sub

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

End Sub
Mar 13 '06 #8

P: n/a
Got it again!

Thank you very much for all your help!

This will end this thread.

Mar 13 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.