"Like" operator syntax problem

  • Thread starter Thread starter Roger L
  • Start date Start date
R

Roger L

I'm trying to open a report from a form, and set up a "Where" statement
for that OpenReport command.

If a checkbox is True, then use the exact value from a form field as a
criteria.
If a checkbox is False, then use a Like * operators to return all records
with
the field value, and any others that may qualify.

I can't seem to get the Like * syntax correct. I've tried all types of
syntax,
but can't seem to get it right.
(I've used Like [SomeField] & "*" in queries many times with no
trouble)

Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = Forms!frmYarnPOsDialog!YarnID"
Else
YarnIDArg = "YarnID Like Forms!frmYarnPOsDialog!YarnID" & "*"
'<--problem
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

Any help would be appreciated...
Thanks
Roger
 
I'm trying to open a report from a form, and set up a "Where" statement
for that OpenReport command.

If a checkbox is True, then use the exact value from a form field as a
criteria.
If a checkbox is False, then use a Like * operators to return all records
with
the field value, and any others that may qualify.

I can't seem to get the Like * syntax correct. I've tried all types of
syntax,
but can't seem to get it right.
(I've used Like [SomeField] & "*" in queries many times with no
trouble)

Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = Forms!frmYarnPOsDialog!YarnID"
Else
YarnIDArg = "YarnID Like Forms!frmYarnPOsDialog!YarnID" & "*"
'<--problem
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

Any help would be appreciated...
Thanks
Roger
Click to expand...

What is the datatype of [YarnID]?
If [YarnID] is a Number datatype then:

Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Forms!frmYarnPOsDialog!YarnID
Else
YarnIDArg = "YarnID Like " & Forms!frmYarnPOsDialog!YarnID & "*"

End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

If the above code is being run on the form frmYarnPOsDialog then use
the Me keyword instead:

YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"

However, if [YarnID] is Text datatype, then use:

YarnIDArg = "YarnID = '" & Me!YarnID & "'"
Else
YarnIDArg = "YarnID Like '" & Me!YarnID & "'*"
 
What is the datatype of [YarnID]?
If [YarnID] is a Number datatype then:

Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Forms!frmYarnPOsDialog!YarnID
Else
YarnIDArg = "YarnID Like " & Forms!frmYarnPOsDialog!YarnID & "*"

End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

If the above code is being run on the form frmYarnPOsDialog then use
the Me keyword instead:

YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"

However, if [YarnID] is Text datatype, then use:

YarnIDArg = "YarnID = '" & Me!YarnID & "'"
Else
YarnIDArg = "YarnID Like '" & Me!YarnID & "'*"
Fred
Click to expand...
-----------------------------------------------------
Fred,
Thanks very much for your help.
I copied your code verbatim, but when the check box was False the code
failed with...
Run Time Error '3705'
Extra ) in Query Expression '(YarnID Like 709*)'
YarnID on the form is from an unbound combo box based on the YarnID Numeric
Integer field.

Here's the code... cut and pasted
(ex. YarnID = 709, to yield 709, 7091, 7092 etc.)

Private Sub cmdYarnPOsByYarnID_Click()
Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg
End Sub

I don't see anything wrong with your code!
Even the error message appears to display the correct syntax for the
argument. The True works fine.
A breakpoint on the False indicates (Me!YarnID = 709)
I hope I'm not going whacky here... but I can't see the problem.

Thanks for your help Fred,
Roger
 
LIKE won't work on numbers. It is strictly a text string operator.

One option might be to convert each of your numeric fields to strings (use
something like CStr()) and then use LIKE on these strings. For example,

SELECT CStr([A Numeric Field]) as StringNumber FROM YourTable
WHERE StringNumber LIKE StringNumber & "*";

Good Luck!
--
Chaim


Roger L said:
What is the datatype of [YarnID]?
If [YarnID] is a Number datatype then:

Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Forms!frmYarnPOsDialog!YarnID
Else
YarnIDArg = "YarnID Like " & Forms!frmYarnPOsDialog!YarnID & "*"

End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

If the above code is being run on the form frmYarnPOsDialog then use
the Me keyword instead:

YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"

However, if [YarnID] is Text datatype, then use:

YarnIDArg = "YarnID = '" & Me!YarnID & "'"
Else
YarnIDArg = "YarnID Like '" & Me!YarnID & "'*"
Fred
Click to expand...
-----------------------------------------------------
Fred,
Thanks very much for your help.
I copied your code verbatim, but when the check box was False the code
failed with...
Run Time Error '3705'
Extra ) in Query Expression '(YarnID Like 709*)'
YarnID on the form is from an unbound combo box based on the YarnID Numeric
Integer field.

Here's the code... cut and pasted
(ex. YarnID = 709, to yield 709, 7091, 7092 etc.)

Private Sub cmdYarnPOsByYarnID_Click()
Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg
End Sub

I don't see anything wrong with your code!
Even the error message appears to display the correct syntax for the
argument. The True works fine.
A breakpoint on the False indicates (Me!YarnID = 709)
I hope I'm not going whacky here... but I can't see the problem.

Thanks for your help Fred,
Roger
Click to expand...
 
Thanks Chaim,
After several hours of head banging, I was pretty sure that the ")" error
message was bogus, and that I was getting hung up looking for a "syntax"
problem/solution. As it turns out, just few minutes ago I tried to use
normal (right click) tableview filtering on the table itself, and realized
*'s don't work on numeric fields. So we meet on that point!

I think I'll try to stay with the OpenReport Where argument method,
rather than working within the query. The YarnID criteria has 3 way
logic...
If YID = 4 and ThisYarnIDOnly Then YarnID yield 4 Only
If YID = 4 and Not ThisYarnIDOnly Then yield YarnID 4, 44, 410, etc...
If YarnID = Null Then yield All YarnIDs
So, rather than work that "logic" into an query, I'll just do it from the
calling form.

I'll convert the YarnID criteria to String, as well as YarnID in my
report query, and that should do it.

Thanks a lot for your help,
Roger

Chaim said:
LIKE won't work on numbers. It is strictly a text string operator.

One option might be to convert each of your numeric fields to strings (use
something like CStr()) and then use LIKE on these strings. For example,

SELECT CStr([A Numeric Field]) as StringNumber FROM YourTable
WHERE StringNumber LIKE StringNumber & "*";

Good Luck!
--
Chaim


Roger L said:
fredg said:
On Tue, 19 Jul 2005 20:07:28 -0400, Roger L wrote:
I'm trying to open a report from a form, and set up a "Where"
statement
for that OpenReport command.

If a checkbox is True, then use the exact value from a form field as a
criteria.
If a checkbox is False, then use a Like * operators to return all
records
with
the field value, and any others that may qualify.
Roger
Click to expand...
What is the datatype of [YarnID]?
If [YarnID] is a Number datatype then:

Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Forms!frmYarnPOsDialog!YarnID
Else
YarnIDArg = "YarnID Like " & Forms!frmYarnPOsDialog!YarnID & "*"

End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

If the above code is being run on the form frmYarnPOsDialog then use
the Me keyword instead:

YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"

However, if [YarnID] is Text datatype, then use:

YarnIDArg = "YarnID = '" & Me!YarnID & "'"
Else
YarnIDArg = "YarnID Like '" & Me!YarnID & "'*"
Fred
Click to expand...
-----------------------------------------------------
Fred,
Thanks very much for your help.
I copied your code verbatim, but when the check box was False the code
failed with...
Run Time Error '3705'
Extra ) in Query Expression '(YarnID Like 709*)'
YarnID on the form is from an unbound combo box based on the YarnID
Numeric
Integer field.

Here's the code... cut and pasted
(ex. YarnID = 709, to yield 709, 7091, 7092 etc.)

Private Sub cmdYarnPOsByYarnID_Click()
Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg
End Sub

I don't see anything wrong with your code!
Even the error message appears to display the correct syntax for the
argument. The True works fine.
A breakpoint on the False indicates (Me!YarnID = 709)
I hope I'm not going whacky here... but I can't see the problem.

Thanks for your help Fred,
Roger
Click to expand...
Click to expand...
 
Well, I'm just at the end of my rope with this problem. I tried Fred's
solutions with no luck, and took your advice about going with String only.
The TRUE has always worked fine, and the report proiperly displays ONLY the
YarnID on the form.
The FALSE fails in every way I've tried to work it with the same error:
Perhaps it woulkd help to restate the setup...
1. YarnID is an unbound combo box on frmYarnPOsDialog that gets it value
from [YarnID] a Num/Integer field.
2. YarnIDString : CStr([YarnID]) is calculated field in my report query

If user selects 709 in YarnID combo I need to allow 3 possible criteria
1. If chkThisIDOnly = True then report on ONLY on YarnID = 709
2. If chkThisIDOnly = False then report on YarnID 709, 7091, 7092,
etc...
3. If User leaves YarnID Null then report on ALL YarnIDs

My Code:
Dim YarnIDArg As String
Dim strYarnID As String
strYarnID = CStr(Me.YarnID)
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnIDString = " & strYarnID
ElseIf Me.chkThisIDOnly = False Then
YarnIDArg = "YarnIDString Like " & strYarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

I'm stuck. I can't figure out what the heck is wrong with this code, or
some of the other coding I've tried. I think that is bogus, and there's
something else amiss other than just "syntax".

Thanks for any help,
Al Camp

Chaim said:
LIKE won't work on numbers. It is strictly a text string operator.

One option might be to convert each of your numeric fields to strings (use
something like CStr()) and then use LIKE on these strings. For example,

SELECT CStr([A Numeric Field]) as StringNumber FROM YourTable
WHERE StringNumber LIKE StringNumber & "*";

Good Luck!
--
Chaim


Roger L said:
fredg said:
On Tue, 19 Jul 2005 20:07:28 -0400, Roger L wrote:
I'm trying to open a report from a form, and set up a "Where"
statement
for that OpenReport command.

If a checkbox is True, then use the exact value from a form field as a
criteria.
If a checkbox is False, then use a Like * operators to return all
records
with
the field value, and any others that may qualify.
Roger
Click to expand...
What is the datatype of [YarnID]?
If [YarnID] is a Number datatype then:

Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Forms!frmYarnPOsDialog!YarnID
Else
YarnIDArg = "YarnID Like " & Forms!frmYarnPOsDialog!YarnID & "*"

End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

If the above code is being run on the form frmYarnPOsDialog then use
the Me keyword instead:

YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"

However, if [YarnID] is Text datatype, then use:

YarnIDArg = "YarnID = '" & Me!YarnID & "'"
Else
YarnIDArg = "YarnID Like '" & Me!YarnID & "'*"
Fred
Click to expand...
-----------------------------------------------------
Fred,
Thanks very much for your help.
I copied your code verbatim, but when the check box was False the code
failed with...
Run Time Error '3705'
Extra ) in Query Expression '(YarnID Like 709*)'
YarnID on the form is from an unbound combo box based on the YarnID
Numeric
Integer field.

Here's the code... cut and pasted
(ex. YarnID = 709, to yield 709, 7091, 7092 etc.)

Private Sub cmdYarnPOsByYarnID_Click()
Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg
End Sub

I don't see anything wrong with your code!
Even the error message appears to display the correct syntax for the
argument. The True works fine.
A breakpoint on the False indicates (Me!YarnID = 709)
I hope I'm not going whacky here... but I can't see the problem.

Thanks for your help Fred,
Roger
Click to expand...
Click to expand...
 
Sorry folks. I did a typo on the previous post.
The error message should read:Al

Al Camp said:
Well, I'm just at the end of my rope with this problem. I tried Fred's
solutions with no luck, and took your advice about going with String only.
The TRUE has always worked fine, and the report proiperly displays ONLY
the YarnID on the form.
The FALSE fails in every way I've tried to work it with the same error:
Click to expand...
Perhaps it woulkd help to restate the setup...
1. YarnID is an unbound combo box on frmYarnPOsDialog that gets it
value
from [YarnID] a Num/Integer field.
2. YarnIDString : CStr([YarnID]) is calculated field in my report query

If user selects 709 in YarnID combo I need to allow 3 possible criteria
1. If chkThisIDOnly = True then report on ONLY on YarnID = 709
2. If chkThisIDOnly = False then report on YarnID 709, 7091, 7092,
etc...
3. If User leaves YarnID Null then report on ALL YarnIDs

My Code:
Dim YarnIDArg As String
Dim strYarnID As String
strYarnID = CStr(Me.YarnID)
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnIDString = " & strYarnID
ElseIf Me.chkThisIDOnly = False Then
YarnIDArg = "YarnIDString Like " & strYarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

I'm stuck. I can't figure out what the heck is wrong with this code, or
some of the other coding I've tried. I think that is bogus, and there's
something else amiss other than just "syntax".

Thanks for any help,
Al Camp

Chaim said:
LIKE won't work on numbers. It is strictly a text string operator.

One option might be to convert each of your numeric fields to strings
(use
something like CStr()) and then use LIKE on these strings. For example,

SELECT CStr([A Numeric Field]) as StringNumber FROM YourTable
WHERE StringNumber LIKE StringNumber & "*";

Good Luck!
--
Chaim


Roger L said:
On Tue, 19 Jul 2005 20:07:28 -0400, Roger L wrote:
I'm trying to open a report from a form, and set up a "Where"
statement
for that OpenReport command.

If a checkbox is True, then use the exact value from a form field as
a
criteria.
If a checkbox is False, then use a Like * operators to return all
records
with
the field value, and any others that may qualify.
Roger

What is the datatype of [YarnID]?
If [YarnID] is a Number datatype then:

Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Forms!frmYarnPOsDialog!YarnID
Else
YarnIDArg = "YarnID Like " & Forms!frmYarnPOsDialog!YarnID & "*"

End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

If the above code is being run on the form frmYarnPOsDialog then use
the Me keyword instead:

YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"

However, if [YarnID] is Text datatype, then use:

YarnIDArg = "YarnID = '" & Me!YarnID & "'"
Else
YarnIDArg = "YarnID Like '" & Me!YarnID & "'*"
Fred
-----------------------------------------------------
Fred,
Thanks very much for your help.
I copied your code verbatim, but when the check box was False the code
failed with...
Run Time Error '3705'
Extra ) in Query Expression '(YarnID Like 709*)'
YarnID on the form is from an unbound combo box based on the YarnID
Numeric
Integer field.

Here's the code... cut and pasted
(ex. YarnID = 709, to yield 709, 7091, 7092 etc.)

Private Sub cmdYarnPOsByYarnID_Click()
Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg
End Sub

I don't see anything wrong with your code!
Even the error message appears to display the correct syntax for the
argument. The True works fine.
A breakpoint on the False indicates (Me!YarnID = 709)
I hope I'm not going whacky here... but I can't see the problem.

Thanks for your help Fred,
Roger
Click to expand...
Click to expand...
Click to expand...
 
When your field is Text, you need to have quotes around your search items.

You say, however, that the True side is working without an error, which
implies that YarnIDString is numeric, not text.

If YarnIDString is numeric, you can't use Like: it only works with text.

If YarnIDString is text, then you need:

If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnIDString = '" & strYarnID & "'"
ElseIf Me.chkThisIDOnly = False Then
YarnIDArg = "YarnIDString Like '" & strYarnID & "*'"
End If

Exagerated for clarity, that's

If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnIDString = ' " & strYarnID & " ' "
ElseIf Me.chkThisIDOnly = False Then
YarnIDArg = "YarnIDString Like ' " & strYarnID & " * ' "
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Al Camp said:
Well, I'm just at the end of my rope with this problem. I tried Fred's
solutions with no luck, and took your advice about going with String only.
The TRUE has always worked fine, and the report proiperly displays ONLY
the YarnID on the form.
The FALSE fails in every way I've tried to work it with the same error:
Perhaps it woulkd help to restate the setup...
1. YarnID is an unbound combo box on frmYarnPOsDialog that gets it
value
from [YarnID] a Num/Integer field.
2. YarnIDString : CStr([YarnID]) is calculated field in my report query

If user selects 709 in YarnID combo I need to allow 3 possible criteria
1. If chkThisIDOnly = True then report on ONLY on YarnID = 709
2. If chkThisIDOnly = False then report on YarnID 709, 7091, 7092,
etc...
3. If User leaves YarnID Null then report on ALL YarnIDs

My Code:
Dim YarnIDArg As String
Dim strYarnID As String
strYarnID = CStr(Me.YarnID)
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnIDString = " & strYarnID
ElseIf Me.chkThisIDOnly = False Then
YarnIDArg = "YarnIDString Like " & strYarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

I'm stuck. I can't figure out what the heck is wrong with this code, or
some of the other coding I've tried. I think that is bogus, and there's
something else amiss other than just "syntax".

Thanks for any help,
Al Camp

Chaim said:
LIKE won't work on numbers. It is strictly a text string operator.

One option might be to convert each of your numeric fields to strings
(use
something like CStr()) and then use LIKE on these strings. For example,

SELECT CStr([A Numeric Field]) as StringNumber FROM YourTable
WHERE StringNumber LIKE StringNumber & "*";

Good Luck!
--
Chaim


Roger L said:
On Tue, 19 Jul 2005 20:07:28 -0400, Roger L wrote:
I'm trying to open a report from a form, and set up a "Where"
statement
for that OpenReport command.

If a checkbox is True, then use the exact value from a form field as
a
criteria.
If a checkbox is False, then use a Like * operators to return all
records
with
the field value, and any others that may qualify.
Roger

What is the datatype of [YarnID]?
If [YarnID] is a Number datatype then:

Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Forms!frmYarnPOsDialog!YarnID
Else
YarnIDArg = "YarnID Like " & Forms!frmYarnPOsDialog!YarnID & "*"

End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

If the above code is being run on the form frmYarnPOsDialog then use
the Me keyword instead:

YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"

However, if [YarnID] is Text datatype, then use:

YarnIDArg = "YarnID = '" & Me!YarnID & "'"
Else
YarnIDArg = "YarnID Like '" & Me!YarnID & "'*"
Fred
-----------------------------------------------------
Fred,
Thanks very much for your help.
I copied your code verbatim, but when the check box was False the code
failed with...
Run Time Error '3705'
Extra ) in Query Expression '(YarnID Like 709*)'
YarnID on the form is from an unbound combo box based on the YarnID
Numeric
Integer field.

Here's the code... cut and pasted
(ex. YarnID = 709, to yield 709, 7091, 7092 etc.)

Private Sub cmdYarnPOsByYarnID_Click()
Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg
End Sub

I don't see anything wrong with your code!
Even the error message appears to display the correct syntax for the
argument. The True works fine.
A breakpoint on the False indicates (Me!YarnID = 709)
I hope I'm not going whacky here... but I can't see the problem.

Thanks for your help Fred,
Roger
Click to expand...
Click to expand...
Click to expand...
 
Doug,
Guess I'm a bit confused. I take the value of YarnID on the form, and
convert it to a string before I run the IF code.
Dim strYarnID As String
strYarnID = CStr(Me.YarnID) 'from YarnID on the form
Click to expand...

So I still need to enclose the variable strYarnID in quotes in the IF
statement? Hmmm... got me on that one.

I can see what your doing with the quotes too... so that's very helpful.

Will try... thanks for the help!
Al Camp

Douglas J. Steele said:
When your field is Text, you need to have quotes around your search items.

You say, however, that the True side is working without an error, which
implies that YarnIDString is numeric, not text.

If YarnIDString is numeric, you can't use Like: it only works with text.

If YarnIDString is text, then you need:

If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnIDString = '" & strYarnID & "'"
ElseIf Me.chkThisIDOnly = False Then
YarnIDArg = "YarnIDString Like '" & strYarnID & "*'"
End If

Exagerated for clarity, that's

If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnIDString = ' " & strYarnID & " ' "
ElseIf Me.chkThisIDOnly = False Then
YarnIDArg = "YarnIDString Like ' " & strYarnID & " * ' "
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Al Camp said:
Well, I'm just at the end of my rope with this problem. I tried Fred's
solutions with no luck, and took your advice about going with String
only.
The TRUE has always worked fine, and the report proiperly displays ONLY
the YarnID on the form.
The FALSE fails in every way I've tried to work it with the same error:
Run Time Error '3705'
Extra ) in Query Expression '(YarnID Like 709*)'
Click to expand...

Perhaps it woulkd help to restate the setup...
1. YarnID is an unbound combo box on frmYarnPOsDialog that gets it
value
from [YarnID] a Num/Integer field.
2. YarnIDString : CStr([YarnID]) is calculated field in my report
query

If user selects 709 in YarnID combo I need to allow 3 possible criteria
1. If chkThisIDOnly = True then report on ONLY on YarnID = 709
2. If chkThisIDOnly = False then report on YarnID 709, 7091, 7092,
etc...
3. If User leaves YarnID Null then report on ALL YarnIDs

My Code:
Dim YarnIDArg As String
Dim strYarnID As String
strYarnID = CStr(Me.YarnID)
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnIDString = " & strYarnID
ElseIf Me.chkThisIDOnly = False Then
YarnIDArg = "YarnIDString Like " & strYarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

I'm stuck. I can't figure out what the heck is wrong with this code, or
some of the other coding I've tried. I think that is bogus, and there's
something else amiss other than just "syntax".

Thanks for any help,
Al Camp

Chaim said:
LIKE won't work on numbers. It is strictly a text string operator.

One option might be to convert each of your numeric fields to strings
(use
something like CStr()) and then use LIKE on these strings. For example,

SELECT CStr([A Numeric Field]) as StringNumber FROM YourTable
WHERE StringNumber LIKE StringNumber & "*";

Good Luck!
--
Chaim


:

On Tue, 19 Jul 2005 20:07:28 -0400, Roger L wrote:
I'm trying to open a report from a form, and set up a "Where"
statement
for that OpenReport command.

If a checkbox is True, then use the exact value from a form field as
a
criteria.
If a checkbox is False, then use a Like * operators to return all
records
with
the field value, and any others that may qualify.
Roger

What is the datatype of [YarnID]?
If [YarnID] is a Number datatype then:

Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Forms!frmYarnPOsDialog!YarnID
Else
YarnIDArg = "YarnID Like " & Forms!frmYarnPOsDialog!YarnID & "*"

End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

If the above code is being run on the form frmYarnPOsDialog then use
the Me keyword instead:

YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"

However, if [YarnID] is Text datatype, then use:

YarnIDArg = "YarnID = '" & Me!YarnID & "'"
Else
YarnIDArg = "YarnID Like '" & Me!YarnID & "'*"
Fred
-----------------------------------------------------
Fred,
Thanks very much for your help.
I copied your code verbatim, but when the check box was False the code
failed with...
Run Time Error '3705'
Extra ) in Query Expression '(YarnID Like 709*)'
YarnID on the form is from an unbound combo box based on the YarnID
Numeric
Integer field.

Here's the code... cut and pasted
(ex. YarnID = 709, to yield 709, 7091, 7092 etc.)

Private Sub cmdYarnPOsByYarnID_Click()
Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg
End Sub

I don't see anything wrong with your code!
Even the error message appears to display the correct syntax for the
argument. The True works fine.
A breakpoint on the False indicates (Me!YarnID = 709)
I hope I'm not going whacky here... but I can't see the problem.

Thanks for your help Fred,
Roger
Click to expand...
Click to expand...
Click to expand...
 
Doug,
Your code worked just fine.
I couldn't figure out why FredG's code didn't work.
He had:
YarnIDArg = "YarnID = '" & Me!YarnID & "'"
Else
YarnIDArg = "YarnID Like '" & Me!YarnID & "'*"
Notice that the last ' is before the *, causing the asterick to fall out
of the string... so now it all makes sense. (this comment is not to blame
Fred, but rather to finally understand why I couldn't seem to get any
traction with this problem.)
The real culprit in this problem was getting the same error with all the
permutations I tried. That Error 3705 must be just some "general" fall
out...

Well, looks like I'm all set now. Thanks Doug, and to all who helped.
Al Camp

Douglas J. Steele said:
When your field is Text, you need to have quotes around your search items.

You say, however, that the True side is working without an error, which
implies that YarnIDString is numeric, not text.

If YarnIDString is numeric, you can't use Like: it only works with text.

If YarnIDString is text, then you need:

If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnIDString = '" & strYarnID & "'"
ElseIf Me.chkThisIDOnly = False Then
YarnIDArg = "YarnIDString Like '" & strYarnID & "*'"
End If

Exagerated for clarity, that's

If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnIDString = ' " & strYarnID & " ' "
ElseIf Me.chkThisIDOnly = False Then
YarnIDArg = "YarnIDString Like ' " & strYarnID & " * ' "
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Al Camp said:
Well, I'm just at the end of my rope with this problem. I tried Fred's
solutions with no luck, and took your advice about going with String
only.
The TRUE has always worked fine, and the report proiperly displays ONLY
the YarnID on the form.
The FALSE fails in every way I've tried to work it with the same error:
Run Time Error '3705'
Extra ) in Query Expression '(YarnID Like 709*)'
Click to expand...

Perhaps it woulkd help to restate the setup...
1. YarnID is an unbound combo box on frmYarnPOsDialog that gets it
value
from [YarnID] a Num/Integer field.
2. YarnIDString : CStr([YarnID]) is calculated field in my report
query

If user selects 709 in YarnID combo I need to allow 3 possible criteria
1. If chkThisIDOnly = True then report on ONLY on YarnID = 709
2. If chkThisIDOnly = False then report on YarnID 709, 7091, 7092,
etc...
3. If User leaves YarnID Null then report on ALL YarnIDs

My Code:
Dim YarnIDArg As String
Dim strYarnID As String
strYarnID = CStr(Me.YarnID)
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnIDString = " & strYarnID
ElseIf Me.chkThisIDOnly = False Then
YarnIDArg = "YarnIDString Like " & strYarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

I'm stuck. I can't figure out what the heck is wrong with this code, or
some of the other coding I've tried. I think that is bogus, and there's
something else amiss other than just "syntax".

Thanks for any help,
Al Camp

Chaim said:
LIKE won't work on numbers. It is strictly a text string operator.

One option might be to convert each of your numeric fields to strings
(use
something like CStr()) and then use LIKE on these strings. For example,

SELECT CStr([A Numeric Field]) as StringNumber FROM YourTable
WHERE StringNumber LIKE StringNumber & "*";

Good Luck!
--
Chaim


:

On Tue, 19 Jul 2005 20:07:28 -0400, Roger L wrote:
I'm trying to open a report from a form, and set up a "Where"
statement
for that OpenReport command.

If a checkbox is True, then use the exact value from a form field as
a
criteria.
If a checkbox is False, then use a Like * operators to return all
records
with
the field value, and any others that may qualify.
Roger

What is the datatype of [YarnID]?
If [YarnID] is a Number datatype then:

Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Forms!frmYarnPOsDialog!YarnID
Else
YarnIDArg = "YarnID Like " & Forms!frmYarnPOsDialog!YarnID & "*"

End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg

If the above code is being run on the form frmYarnPOsDialog then use
the Me keyword instead:

YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"

However, if [YarnID] is Text datatype, then use:

YarnIDArg = "YarnID = '" & Me!YarnID & "'"
Else
YarnIDArg = "YarnID Like '" & Me!YarnID & "'*"
Fred
-----------------------------------------------------
Fred,
Thanks very much for your help.
I copied your code verbatim, but when the check box was False the code
failed with...
Run Time Error '3705'
Extra ) in Query Expression '(YarnID Like 709*)'
YarnID on the form is from an unbound combo box based on the YarnID
Numeric
Integer field.

Here's the code... cut and pasted
(ex. YarnID = 709, to yield 709, 7091, 7092 etc.)

Private Sub cmdYarnPOsByYarnID_Click()
Dim YarnIDArg As String
If Me.chkThisIDOnly = True Then
YarnIDArg = "YarnID = " & Me!YarnID
Else
YarnIDArg = "YarnID Like " & Me!YarnID & "*"
End If
DoCmd.OpenReport "rptYarnPosByYarnID", acViewPreview, , YarnIDArg
End Sub

I don't see anything wrong with your code!
Even the error message appears to display the correct syntax for the
argument. The True works fine.
A breakpoint on the False indicates (Me!YarnID = 709)
I hope I'm not going whacky here... but I can't see the problem.

Thanks for your help Fred,
Roger
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ALTER COLUMN syntax 2
Problem with IF statement. 5
VBA Syntax 3
Syntax error (missing operator) 4
VBA Syntax 2
Syntax for number of copies to print? 1
Syntax problem with a DLookup function call 2
OpenReport + Current Record Only 2

Back
Top