Query textboxes on when Not Null

[Drum2001]

SELECT ra_report.CCENTER,

FROM ra_report
WHERE (((ra_report.CCENTER) Like "*" & [forms]![Search Cost
Center]![Text30] & "*" Or (ra_report.CCENTER) Like "*" &
[forms]![Search Cost Center]![Text31] & "*" Or (ra_report.CCENTER) Like
"*" & [forms]![Search Cost Center]![Text32] & "*" Or
(ra_report.CCENTER) Like "*" & [forms]![Search Cost Center]![Text33] &
"*" Or (ra_report.CCENTER) Like "*" & [forms]![Search Cost
Center]![Text34] & "*" Or (ra_report.CCENTER) Like "*" &
[forms]![Search Cost Center]![Text35] & "*" Or (ra_report.CCENTER) Like
"*" & [forms]![Search Cost Center]![Text36] & "*" Or
(ra_report.CCENTER) Like "*" & [forms]![Search Cost Center]![Text37] &
"*"));
This works great, except when one of the textboxes is empty. How would
I code the following pseudo-code:

If textbox is not null, then use criteria OR If textbox is not null,
then use criteria, etc

[Anthony England]

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

SELECT ra_report.CCENTER,

FROM ra_report
WHERE (((ra_report.CCENTER) Like "*" & [forms]![Search Cost
Center]![Text30] & "*" Or (ra_report.CCENTER) Like "*" &
[forms]![Search Cost Center]![Text31] & "*" Or (ra_report.CCENTER) Like
"*" & [forms]![Search Cost Center]![Text32] & "*" Or
(ra_report.CCENTER) Like "*" & [forms]![Search Cost Center]![Text33] &
"*" Or (ra_report.CCENTER) Like "*" & [forms]![Search Cost
Center]![Text34] & "*" Or (ra_report.CCENTER) Like "*" &
[forms]![Search Cost Center]![Text35] & "*" Or (ra_report.CCENTER) Like
"*" & [forms]![Search Cost Center]![Text36] & "*" Or
(ra_report.CCENTER) Like "*" & [forms]![Search Cost Center]![Text37] &
"*"));
This works great, except when one of the textboxes is empty. How would
I code the following pseudo-code:

If textbox is not null, then use criteria OR If textbox is not null,
then use criteria, etc

I'm not sure what I'd do - I suppose it depends on further details. If the
form was to create criteria for a report to run, I might build up a
where-string to open the report with. If it was simply to open a query, I
might re-write the query dynamically. This would allow me to optimize the
query based on what the user really wanted.

For example if we only three boxes were filled in the query would be:
SELECT * FROM ra_report WHERE CCENTER IN ("one","two","three")

but if one was used the query would be
SELECT * FROM ra_report WHERE CCENTER="one"

I realise I'm using exact matches, but the principal is the same. Would you
know how to dynamically re-write the query?

[Drum2001]

Anthony England wrote:

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

SELECT ra_report.CCENTER,

FROM ra_report
WHERE (((ra_report.CCENTER) Like "*" & [forms]![Search Cost
Center]![Text30] & "*" Or (ra_report.CCENTER) Like "*" &
[forms]![Search Cost Center]![Text31] & "*" Or (ra_report.CCENTER) Like
"*" & [forms]![Search Cost Center]![Text32] & "*" Or
(ra_report.CCENTER) Like "*" & [forms]![Search Cost Center]![Text33] &
"*" Or (ra_report.CCENTER) Like "*" & [forms]![Search Cost
Center]![Text34] & "*" Or (ra_report.CCENTER) Like "*" &
[forms]![Search Cost Center]![Text35] & "*" Or (ra_report.CCENTER) Like
"*" & [forms]![Search Cost Center]![Text36] & "*" Or
(ra_report.CCENTER) Like "*" & [forms]![Search Cost Center]![Text37] &
"*"));
This works great, except when one of the textboxes is empty. How would
I code the following pseudo-code:

If textbox is not null, then use criteria OR If textbox is not null,
then use criteria, etc

I'm not sure what I'd do - I suppose it depends on further details. If the
form was to create criteria for a report to run, I might build up a
where-string to open the report with. If it was simply to open a query, I
might re-write the query dynamically. This would allow me to optimize the
query based on what the user really wanted.

For example if we only three boxes were filled in the query would be:
SELECT * FROM ra_report WHERE CCENTER IN ("one","two","three")

but if one was used the query would be
SELECT * FROM ra_report WHERE CCENTER="one"

I realise I'm using exact matches, but the principal is the same. Would you
know how to dynamically re-write the query?

No, I am not sure how to write the dymanic query...

[Anthony England]

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

Anthony England wrote:

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

> SELECT ra_report.CCENTER,
>
> FROM ra_report
> WHERE (((ra_report.CCENTER) Like "*" & [forms]![Search Cost
> Center]![Text30] & "*" Or (ra_report.CCENTER) Like "*" &
> [forms]![Search Cost Center]![Text31] & "*" Or (ra_report.CCENTER) Like
> "*" & [forms]![Search Cost Center]![Text32] & "*" Or
> (ra_report.CCENTER) Like "*" & [forms]![Search Cost Center]![Text33] &
> "*" Or (ra_report.CCENTER) Like "*" & [forms]![Search Cost
> Center]![Text34] & "*" Or (ra_report.CCENTER) Like "*" &
> [forms]![Search Cost Center]![Text35] & "*" Or (ra_report.CCENTER) Like
> "*" & [forms]![Search Cost Center]![Text36] & "*" Or
> (ra_report.CCENTER) Like "*" & [forms]![Search Cost Center]![Text37] &
> "*"));
>
>
> This works great, except when one of the textboxes is empty. How would
> I code the following pseudo-code:
>
> If textbox is not null, then use criteria OR If textbox is not null,
> then use criteria, etc

I'm not sure what I'd do - I suppose it depends on further details. If
the
form was to create criteria for a report to run, I might build up a
where-string to open the report with. If it was simply to open a query,
I
might re-write the query dynamically. This would allow me to optimize
the
query based on what the user really wanted.

For example if we only three boxes were filled in the query would be:
SELECT * FROM ra_report WHERE CCENTER IN ("one","two","three")

but if one was used the query would be
SELECT * FROM ra_report WHERE CCENTER="one"

I realise I'm using exact matches, but the principal is the same. Would
you
know how to dynamically re-write the query?

No, I am not sure how to write the dymanic query...

If you put the DefineQuery function (shown below) into a separate module,
then you could generate the SQL based on which textboxes had been completed
and write something like
strSQL="SELECT Blah, blah, blah"
If Not DefineQuery("MyQuery",strSQL) Then
Exit Sub
End If
' If I'm here, then I've re-defined the query so go on
' and open the report, form or whatever

Public Function DefineQuery(strQueryName As String, _
strSQL As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(strQueryName)

qdf.SQL = strSQL

DefineQuery = True

Exit_Handler:

If Not qdf Is Nothing Then
Set qdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

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

End Function

[Drum2001]

Anthony England wrote:

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

Anthony England wrote:

"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
> SELECT ra_report.CCENTER,
>
> FROM ra_report
> WHERE (((ra_report.CCENTER) Like "*" & [forms]![Search Cost
> Center]![Text30] & "*" Or (ra_report.CCENTER) Like "*" &
> [forms]![Search Cost Center]![Text31] & "*" Or (ra_report.CCENTER) Like
> "*" & [forms]![Search Cost Center]![Text32] & "*" Or
> (ra_report.CCENTER) Like "*" & [forms]![Search Cost Center]![Text33] &
> "*" Or (ra_report.CCENTER) Like "*" & [forms]![Search Cost
> Center]![Text34] & "*" Or (ra_report.CCENTER) Like "*" &
> [forms]![Search Cost Center]![Text35] & "*" Or (ra_report.CCENTER) Like
> "*" & [forms]![Search Cost Center]![Text36] & "*" Or
> (ra_report.CCENTER) Like "*" & [forms]![Search Cost Center]![Text37] &
> "*"));
>
>
> This works great, except when one of the textboxes is empty. How would
> I code the following pseudo-code:
>
> If textbox is not null, then use criteria OR If textbox is not null,
> then use criteria, etc

I'm not sure what I'd do - I suppose it depends on further details. If
the
form was to create criteria for a report to run, I might build up a
where-string to open the report with. If it was simply to open a query,
I
might re-write the query dynamically. This would allow me to optimize
the
query based on what the user really wanted.

For example if we only three boxes were filled in the query would be:
SELECT * FROM ra_report WHERE CCENTER IN ("one","two","three")

but if one was used the query would be
SELECT * FROM ra_report WHERE CCENTER="one"

I realise I'm using exact matches, but the principal is the same. Would
you
know how to dynamically re-write the query?

No, I am not sure how to write the dymanic query...

If you put the DefineQuery function (shown below) into a separate module,
then you could generate the SQL based on which textboxes had been completed
and write something like
strSQL="SELECT Blah, blah, blah"
If Not DefineQuery("MyQuery",strSQL) Then
Exit Sub
End If
' If I'm here, then I've re-defined the query so go on
' and open the report, form or whatever

Public Function DefineQuery(strQueryName As String, _
strSQL As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(strQueryName)

qdf.SQL = strSQL

DefineQuery = True

Exit_Handler:

If Not qdf Is Nothing Then
Set qdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

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

End Function

Anthony,

My apologies to make this baby steps, but what would the query for SQL
be. I am kind of new to this...