Query textboxes on when Not Null

**Anthony England** · Mar 7 '06, 10:25 PM

Re: Query textboxes on when Not Null

"Drum2001" <drum2001@gmail .com> wrote in message
news:1141766937 .611785.219190@ j33g2000cwa.goo glegroups.com.. .[color=blue]
> SELECT ra_report.CCENT ER,
>
> FROM ra_report
> WHERE (((ra_report.CC ENTER) Like "*" & [forms]![Search Cost
> Center]![Text30] & "*" Or (ra_report.CCEN TER) Like "*" &
> [forms]![Search Cost Center]![Text31] & "*" Or (ra_report.CCEN TER) Like
> "*" & [forms]![Search Cost Center]![Text32] & "*" Or
> (ra_report.CCEN TER) Like "*" & [forms]![Search Cost Center]![Text33] &
> "*" Or (ra_report.CCEN TER) Like "*" & [forms]![Search Cost
> Center]![Text34] & "*" Or (ra_report.CCEN TER) Like "*" &
> [forms]![Search Cost Center]![Text35] & "*" Or (ra_report.CCEN TER) Like
> "*" & [forms]![Search Cost Center]![Text36] & "*" Or
> (ra_report.CCEN TER) 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[/color]

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","t hree")

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** · Mar 9 '06, 01:55 PM

Re: Query textboxes on when Not Null

Anthony England wrote:[color=blue]
> "Drum2001" <drum2001@gmail .com> wrote in message
> news:1141766937 .611785.219190@ j33g2000cwa.goo glegroups.com.. .[color=green]
> > SELECT ra_report.CCENT ER,
> >
> > FROM ra_report
> > WHERE (((ra_report.CC ENTER) Like "*" & [forms]![Search Cost
> > Center]![Text30] & "*" Or (ra_report.CCEN TER) Like "*" &
> > [forms]![Search Cost Center]![Text31] & "*" Or (ra_report.CCEN TER) Like
> > "*" & [forms]![Search Cost Center]![Text32] & "*" Or
> > (ra_report.CCEN TER) Like "*" & [forms]![Search Cost Center]![Text33] &
> > "*" Or (ra_report.CCEN TER) Like "*" & [forms]![Search Cost
> > Center]![Text34] & "*" Or (ra_report.CCEN TER) Like "*" &
> > [forms]![Search Cost Center]![Text35] & "*" Or (ra_report.CCEN TER) Like
> > "*" & [forms]![Search Cost Center]![Text36] & "*" Or
> > (ra_report.CCEN TER) 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[/color]
>
> 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","t hree")
>
> 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?[/color]

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

**Anthony England** · Mar 9 '06, 02:15 PM

Re: Query textboxes on when Not Null

"Drum2001" <drum2001@gmail .com> wrote in message
news:1141911981 .789444.26150@z 34g2000cwc.goog legroups.com...[color=blue]
>
> Anthony England wrote:[color=green]
>> "Drum2001" <drum2001@gmail .com> wrote in message
>> news:1141766937 .611785.219190@ j33g2000cwa.goo glegroups.com.. .[color=darkred]
>> > SELECT ra_report.CCENT ER,
>> >
>> > FROM ra_report
>> > WHERE (((ra_report.CC ENTER) Like "*" & [forms]![Search Cost
>> > Center]![Text30] & "*" Or (ra_report.CCEN TER) Like "*" &
>> > [forms]![Search Cost Center]![Text31] & "*" Or (ra_report.CCEN TER) Like
>> > "*" & [forms]![Search Cost Center]![Text32] & "*" Or
>> > (ra_report.CCEN TER) Like "*" & [forms]![Search Cost Center]![Text33] &
>> > "*" Or (ra_report.CCEN TER) Like "*" & [forms]![Search Cost
>> > Center]![Text34] & "*" Or (ra_report.CCEN TER) Like "*" &
>> > [forms]![Search Cost Center]![Text35] & "*" Or (ra_report.CCEN TER) Like
>> > "*" & [forms]![Search Cost Center]![Text36] & "*" Or
>> > (ra_report.CCEN TER) 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[/color]
>>
>> 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","t hree")
>>
>> 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?[/color]
>
>
> No, I am not sure how to write the dymanic query...[/color]

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("My Query",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(str QueryName 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(s trQueryName)

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** · Mar 9 '06, 07:15 PM

Re: Query textboxes on when Not Null

Anthony England wrote:[color=blue]
> "Drum2001" <drum2001@gmail .com> wrote in message
> news:1141911981 .789444.26150@z 34g2000cwc.goog legroups.com...[color=green]
> >
> > Anthony England wrote:[color=darkred]
> >> "Drum2001" <drum2001@gmail .com> wrote in message
> >> news:1141766937 .611785.219190@ j33g2000cwa.goo glegroups.com.. .
> >> > SELECT ra_report.CCENT ER,
> >> >
> >> > FROM ra_report
> >> > WHERE (((ra_report.CC ENTER) Like "*" & [forms]![Search Cost
> >> > Center]![Text30] & "*" Or (ra_report.CCEN TER) Like "*" &
> >> > [forms]![Search Cost Center]![Text31] & "*" Or (ra_report.CCEN TER) Like
> >> > "*" & [forms]![Search Cost Center]![Text32] & "*" Or
> >> > (ra_report.CCEN TER) Like "*" & [forms]![Search Cost Center]![Text33] &
> >> > "*" Or (ra_report.CCEN TER) Like "*" & [forms]![Search Cost
> >> > Center]![Text34] & "*" Or (ra_report.CCEN TER) Like "*" &
> >> > [forms]![Search Cost Center]![Text35] & "*" Or (ra_report.CCEN TER) Like
> >> > "*" & [forms]![Search Cost Center]![Text36] & "*" Or
> >> > (ra_report.CCEN TER) 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","t hree")
> >>
> >> 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?[/color]
> >
> >
> > No, I am not sure how to write the dymanic query...[/color]
>
> 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("My Query",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(str QueryName 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(s trQueryName)
>
> 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[/color]

Anthony,

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

Query textboxes on when Not Null

Query textboxes on when Not Null

Comment

Comment

Comment

Comment