Textbox to search Memo Field Will Not Work Properly

[Guest]

The program has 3 option buttons to choose from, you can choose 1, 2, or 3,
or any combination of 1 and 2, or 2 and 3, or 1,2,3 etc...they all work
except for the last option button, it wont work on its own, but if u choose
option 1 and 3, or 2 and 3, or 1, 2, 3 then opt 3 works, but if u choose opt
3 by itself it wont work... (can anybody explain why it wont work?)


Dim strwhere As String
strwhere = ""

If Not IsNull(Me.FY1) Then
strwhere = strwhere & "[queryfile.date1]Between " & Me.BegYR & " And " &
Me.EndYR
End If

If Not IsNull(Me.FI2) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strwhere & "[queryfile.fileno]= " & Chr$(34) & Me.combo2
& Chr$(34)
End If

If Not IsNull(Me.SB5) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strwhere & "[queryfile.subject]LIKE ""*" & Me.textbox & "*"""
end if

DoCmd.OpenReport "database1", acPreview, , strwhere

 

[kingston via AccessMonster.com]

This may or may not be a simple formatting issue:

Dim strwhere As String
strwhere = ""

If IsNull(Me.FY1)=False Then
strwhere = "([date1] Between #" & Me.BegYR & "# And #" & Me.EndYR & "#)
"
End If

If IsNull(Me.FI2)=False Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strwhere & "([fileno]= " & Chr$(34) & Me.combo2 & Chr$(34)
&")"
End If

If IsNull(Me.SB5)=False Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strwhere & "([subject] LIKE '*" & Me.textbox & "*')"
End If

DoCmd.OpenReport "database1", acPreview, , strwhere

The program has 3 option buttons to choose from, you can choose 1, 2, or 3,
or any combination of 1 and 2, or 2 and 3, or 1,2,3 etc...they all work
except for the last option button, it wont work on its own, but if u choose
option 1 and 3, or 2 and 3, or 1, 2, 3 then opt 3 works, but if u choose opt
3 by itself it wont work... (can anybody explain why it wont work?)

Dim strwhere As String
strwhere = ""

If Not IsNull(Me.FY1) Then
strwhere = strwhere & "[queryfile.date1]Between " & Me.BegYR & " And " &
Me.EndYR
End If

If Not IsNull(Me.FI2) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strwhere & "[queryfile.fileno]= " & Chr$(34) & Me.combo2
& Chr$(34)
End If

If Not IsNull(Me.SB5) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strwhere & "[queryfile.subject]LIKE ""*" & Me.textbox & "*"""
end if

DoCmd.OpenReport "database1", acPreview, , strwhere

 

[Guest]

ty... does not seem to be... I know Excel, Word, Powerpoint, so unfortunately
I assumed Access was going to be just as easy until I had to create a
database SYSTEM here at work... that included screens to make it more
userfriendly for people who are unfamiliar with Access and only want to pull
things from the database system. If it wasnt for that last filter screen I
would have been done months ago... it actually took me 3 months to figure it
out... (would have been longer without this MS Access help line), because
prior to this job, I was the user and not the programmer.

I "patched" it up, but adding beginning and ending date constants so that
when opt 3 is selected by itself then the date would automatically be
selected, so that helped... so I'm figuring when ever another field doesnt
show up, I will just ad a "patch" (attaching that field as a constant) to
make it show... I will also see if adding the parenthesis that you added help
as well...

ty

This may or may not be a simple formatting issue:

Dim strwhere As String
strwhere = ""

If IsNull(Me.FY1)=False Then
strwhere = "([date1] Between #" & Me.BegYR & "# And #" & Me.EndYR & "#)
"
End If

If IsNull(Me.FI2)=False Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strwhere & "([fileno]= " & Chr$(34) & Me.combo2 & Chr$(34)
&")"
End If

If IsNull(Me.SB5)=False Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strwhere & "([subject] LIKE '*" & Me.textbox & "*')"
End If

DoCmd.OpenReport "database1", acPreview, , strwhere

The program has 3 option buttons to choose from, you can choose 1, 2, or 3,
or any combination of 1 and 2, or 2 and 3, or 1,2,3 etc...they all work
except for the last option button, it wont work on its own, but if u choose
option 1 and 3, or 2 and 3, or 1, 2, 3 then opt 3 works, but if u choose opt
3 by itself it wont work... (can anybody explain why it wont work?)

Dim strwhere As String
strwhere = ""

If Not IsNull(Me.FY1) Then
strwhere = strwhere & "[queryfile.date1]Between " & Me.BegYR & " And " &
Me.EndYR
End If

If Not IsNull(Me.FI2) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strwhere & "[queryfile.fileno]= " & Chr$(34) & Me.combo2
& Chr$(34)
End If

If Not IsNull(Me.SB5) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strwhere & "[queryfile.subject]LIKE ""*" & Me.textbox & "*"""
end if

DoCmd.OpenReport "database1", acPreview, , strwhere

 

[kingston via AccessMonster.com]

Put a debug stop in the procedure. Check that the procedure is triggered
when only option 3 is selected and display strwhere before the OpenReport
command (MsgBox strwhere). Try running the DoCmd in the results pane to see
if it gives you the proper results. Does any of this give you an error
message?

ty... does not seem to be... I know Excel, Word, Powerpoint, so unfortunately
I assumed Access was going to be just as easy until I had to create a
database SYSTEM here at work... that included screens to make it more
userfriendly for people who are unfamiliar with Access and only want to pull
things from the database system. If it wasnt for that last filter screen I
would have been done months ago... it actually took me 3 months to figure it
out... (would have been longer without this MS Access help line), because
prior to this job, I was the user and not the programmer.

I "patched" it up, but adding beginning and ending date constants so that
when opt 3 is selected by itself then the date would automatically be
selected, so that helped... so I'm figuring when ever another field doesnt
show up, I will just ad a "patch" (attaching that field as a constant) to
make it show... I will also see if adding the parenthesis that you added help
as well...

ty

This may or may not be a simple formatting issue:

[quoted text clipped - 53 lines]

 

[Guest]

after being here on this helpline, i have been seriously considering changing
my display name to "apprenticeACCESSdesigner" lol.... I have no idea as to
how to do any of what i said, which of course i know i have to learn because
after this system is done, i have to do another one (YIKES)... all my focus
was on one step at a time, "if this works, cool, i'll go to the next step,
towards finishing the system", (debugging, wasnt one of the steps because it
was not directly related towards the final product)... i brought ACCESS
BIBLE, extremely helpful... except its not on a higher level as far as these
programming statements are concerned... what books do you suggest as it
relates to designing and programming Access, that actually goes step by step,
and/or contains ALL of the programming functions and how they work?

Put a debug stop in the procedure. Check that the procedure is triggered
when only option 3 is selected and display strwhere before the OpenReport
command (MsgBox strwhere). Try running the DoCmd in the results pane to see
if it gives you the proper results. Does any of this give you an error
message?

ty... does not seem to be... I know Excel, Word, Powerpoint, so unfortunately
I assumed Access was going to be just as easy until I had to create a
database SYSTEM here at work... that included screens to make it more
userfriendly for people who are unfamiliar with Access and only want to pull
things from the database system. If it wasnt for that last filter screen I
would have been done months ago... it actually took me 3 months to figure it
out... (would have been longer without this MS Access help line), because
prior to this job, I was the user and not the programmer.

I "patched" it up, but adding beginning and ending date constants so that
when opt 3 is selected by itself then the date would automatically be
selected, so that helped... so I'm figuring when ever another field doesnt
show up, I will just ad a "patch" (attaching that field as a constant) to
make it show... I will also see if adding the parenthesis that you added help
as well...

ty

This may or may not be a simple formatting issue:

[quoted text clipped - 53 lines]

DoCmd.OpenReport "database1", acPreview, , strwhere

 

[Guest]

correction: I have no idea as to

how to do any of what YOU said,(regarding debugging)

after being here on this helpline, i have been seriously considering changing
my display name to "apprenticeACCESSdesigner" lol.... I have no idea as to
how to do any of what i said, which of course i know i have to learn because
after this system is done, i have to do another one (YIKES)... all my focus
was on one step at a time, "if this works, cool, i'll go to the next step,
towards finishing the system", (debugging, wasnt one of the steps because it
was not directly related towards the final product)... i brought ACCESS
BIBLE, extremely helpful... except its not on a higher level as far as these
programming statements are concerned... what books do you suggest as it
relates to designing and programming Access, that actually goes step by step,
and/or contains ALL of the programming functions and how they work?

Put a debug stop in the procedure. Check that the procedure is triggered
when only option 3 is selected and display strwhere before the OpenReport
command (MsgBox strwhere). Try running the DoCmd in the results pane to see
if it gives you the proper results. Does any of this give you an error
message?

ty... does not seem to be... I know Excel, Word, Powerpoint, so unfortunately
I assumed Access was going to be just as easy until I had to create a
database SYSTEM here at work... that included screens to make it more
userfriendly for people who are unfamiliar with Access and only want to pull
things from the database system. If it wasnt for that last filter screen I
would have been done months ago... it actually took me 3 months to figure it
out... (would have been longer without this MS Access help line), because
prior to this job, I was the user and not the programmer.

I "patched" it up, but adding beginning and ending date constants so that
when opt 3 is selected by itself then the date would automatically be
selected, so that helped... so I'm figuring when ever another field doesnt
show up, I will just ad a "patch" (attaching that field as a constant) to
make it show... I will also see if adding the parenthesis that you added help
as well...

ty

This may or may not be a simple formatting issue:

[quoted text clipped - 53 lines]

DoCmd.OpenReport "database1", acPreview, , strwhere

 

[kingston via AccessMonster.com]

In the procedure, enter this before the DoCmd line to display the where
clause:

MsgBox strwhere

How does this procedure get activated? What event is it linked to? I assume
that it is triggered by an AfterUpdate event or OnClick event.

You can put a debug stop by clicking in the border to the left of a line in
the procedure. The line will turn red and when the procedure is run, it will
stop there.

The results pane is the bottom pane in the VB window. Simply type DoCmd.
OpenReport... there to see if the command works properly; the report should
open with the filtered data.

correction: I have no idea as to

how to do any of what YOU said,(regarding debugging)

after being here on this helpline, i have been seriously considering changing
my display name to "apprenticeACCESSdesigner" lol.... I have no idea as to

[quoted text clipped - 37 lines]