Correct Syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have (2) combo boxes and (1) text box set-up to filter out my form. I am
using the following code.

Dim strWhere As String
Dim lngLen As Long


If Not IsNull(Me.txtFilteronDate) Then
strWhere = strWhere & "([Date]= ""#" & Me.FilteronDate & " # "") And "
End If

If Not IsNull(Me.FilterOnShift) Then
strWhere = strWhere & "([ShiftID] = """ & Me.FilterOnShift & """) And "
End If

If Not IsNull(Me.FilterProcessLine) Then
strWhere = strWhere & "([LineID]= """ & Me.FilterProcessLine & """) And"

End If

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

The FilterOnShift and FilterOnLine fields are both numeric fields. I can't
seem to get the syntax correct for these statement and I'm getting the
following error:

Missing),], or Item in query expression '([ShiftID]="2") And ([LineID]="2"".
Can someone please help me with the syntax.

Thanks...
 
If ShiftID and LineID are numeric fields in the table, remove the quotes
from your strings:

strWhere = strWhere & "([ShiftID] = " & Me.FilterOnShift & ") And "
 
I have (2) combo boxes and (1) text box set-up to filter out my form. I am
using the following code.

Dim strWhere As String
Dim lngLen As Long


If Not IsNull(Me.txtFilteronDate) Then
strWhere = strWhere & "([Date]= ""#" & Me.FilteronDate & " # "") And "
End If

If Not IsNull(Me.FilterOnShift) Then
strWhere = strWhere & "([ShiftID] = """ & Me.FilterOnShift & """) And "
End If

If Not IsNull(Me.FilterProcessLine) Then
strWhere = strWhere & "([LineID]= """ & Me.FilterProcessLine & """) And"

End If

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

The FilterOnShift and FilterOnLine fields are both numeric fields. I can't
seem to get the syntax correct for these statement and I'm getting the
following error:

Missing),], or Item in query expression '([ShiftID]="2") And ([LineID]="2"".
Can someone please help me with the syntax.

Thanks...
Click to expand...

In addition to Doug Steeles's comments, if you really do have a field
named "Date", Date is a reserved Access/VBA/Jet word and should not be
used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
 
fredg said:
In addition to Doug Steeles's comments, if you really do have a field
named "Date", Date is a reserved Access/VBA/Jet word and should not be
used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
Click to expand...


Good catch, Fred.

At least Kmurphy enclosed the field name Date in square brackets, which will
minimize problems if he/she cannot (or will not) change the field name.
 
I tried removing the quotes from my strings:


strWhere = strWhere & "([ShiftID] = " & Me.FilterOnShift & ") And "

This gives me the same error message as before: Missing ),], or Item in
query expression '([ShiftID]=2'

Thanks for the tip about the Date field. I know that the word Date is a
reserved word and I am careful to make sure that I place the brackets around
the field name, but I did change it just to be safe..
--
Kmurphy


Douglas J. Steele said:
If ShiftID and LineID are numeric fields in the table, remove the quotes
from your strings:

strWhere = strWhere & "([ShiftID] = " & Me.FilterOnShift & ") And "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Kmurphy said:
I have (2) combo boxes and (1) text box set-up to filter out my form. I am
using the following code.

Dim strWhere As String
Dim lngLen As Long


If Not IsNull(Me.txtFilteronDate) Then
strWhere = strWhere & "([Date]= ""#" & Me.FilteronDate & " # "") And "
End If

If Not IsNull(Me.FilterOnShift) Then
strWhere = strWhere & "([ShiftID] = """ & Me.FilterOnShift & """) And "
End If

If Not IsNull(Me.FilterProcessLine) Then
strWhere = strWhere & "([LineID]= """ & Me.FilterProcessLine & """) And"

End If

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

The FilterOnShift and FilterOnLine fields are both numeric fields. I
can't
seem to get the syntax correct for these statement and I'm getting the
following error:

Missing),], or Item in query expression '([ShiftID]="2") And
([LineID]="2"".
Can someone please help me with the syntax.

Thanks...
Click to expand...
Click to expand...
 
Is that an actual copy-and-paste from your application? I noticed that in
one place you're only adding " And" to the end, so that when you remove the
last 5 characters, you're going to remove a closing parenthesis.

It might be worth putting a Debug.Print strWhere in your code and see what's
actually in the string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kmurphy said:
I tried removing the quotes from my strings:


strWhere = strWhere & "([ShiftID] = " & Me.FilterOnShift & ") And "

This gives me the same error message as before: Missing ),], or Item in
query expression '([ShiftID]=2'

Thanks for the tip about the Date field. I know that the word Date is a
reserved word and I am careful to make sure that I place the brackets around
the field name, but I did change it just to be safe..
--
Kmurphy


Douglas J. Steele said:
If ShiftID and LineID are numeric fields in the table, remove the quotes
from your strings:

strWhere = strWhere & "([ShiftID] = " & Me.FilterOnShift & ") And "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Kmurphy said:
I have (2) combo boxes and (1) text box set-up to filter out my form. I am
using the following code.

Dim strWhere As String
Dim lngLen As Long


If Not IsNull(Me.txtFilteronDate) Then
strWhere = strWhere & "([Date]= ""#" & Me.FilteronDate & " # "") And "
End If

If Not IsNull(Me.FilterOnShift) Then
strWhere = strWhere & "([ShiftID] = """ & Me.FilterOnShift & """) And "
End If

If Not IsNull(Me.FilterProcessLine) Then
strWhere = strWhere & "([LineID]= """ & Me.FilterProcessLine & """) And"

End If

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

The FilterOnShift and FilterOnLine fields are both numeric fields. I
can't
seem to get the syntax correct for these statement and I'm getting the
following error:

Missing),], or Item in query expression '([ShiftID]="2") And
([LineID]="2"".
Can someone please help me with the syntax.

Thanks...
Click to expand...
Click to expand...
Click to expand...
 
Thanks Douglas. That was a good catch. I did get two of the line filters to
work by changing the spacing at the end of one of the lines. Now I'm just
having problems with filtering the date. I'm currently using the following
line in my code:

If Not IsNull(Me.txtFilteronDate) Then
strWhere = strWhere & "([RawDate]= "" # " & Me.txtFilteronDate & " # "")
And "
End If

The problem is when it is executed it doesn't give me an error message it
just gives me a message box that says "The ApplyFilter action was canceled."

Any Suggestions?
--
Kellie Murphy


Douglas J Steele said:
Is that an actual copy-and-paste from your application? I noticed that in
one place you're only adding " And" to the end, so that when you remove the
last 5 characters, you're going to remove a closing parenthesis.

It might be worth putting a Debug.Print strWhere in your code and see what's
actually in the string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kmurphy said:
I tried removing the quotes from my strings:


strWhere = strWhere & "([ShiftID] = " & Me.FilterOnShift & ") And "

This gives me the same error message as before: Missing ),], or Item in
query expression '([ShiftID]=2'

Thanks for the tip about the Date field. I know that the word Date is a
reserved word and I am careful to make sure that I place the brackets around
the field name, but I did change it just to be safe..
--
Kmurphy


Douglas J. Steele said:
If ShiftID and LineID are numeric fields in the table, remove the quotes
from your strings:

strWhere = strWhere & "([ShiftID] = " & Me.FilterOnShift & ") And "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have (2) combo boxes and (1) text box set-up to filter out my form. I am
using the following code.

Dim strWhere As String
Dim lngLen As Long


If Not IsNull(Me.txtFilteronDate) Then
strWhere = strWhere & "([Date]= ""#" & Me.FilteronDate & " # "") And "
End If

If Not IsNull(Me.FilterOnShift) Then
strWhere = strWhere & "([ShiftID] = """ & Me.FilterOnShift & """) And "
End If

If Not IsNull(Me.FilterProcessLine) Then
strWhere = strWhere & "([LineID]= """ & Me.FilterProcessLine & """) And"

End If

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

The FilterOnShift and FilterOnLine fields are both numeric fields. I
can't
seem to get the syntax correct for these statement and I'm getting the
following error:

Missing),], or Item in query expression '([ShiftID]="2") And
([LineID]="2"".
Can someone please help me with the syntax.

Thanks...
Click to expand...
Click to expand...
Click to expand...
 
strWhere = strWhere & "([RawDate]= #" & Me.txtFilteronDate & "# ) And "

Your approach is putting quotes as well as # as delimiters for the dates,
and that's incorrect.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kmurphy said:
Thanks Douglas. That was a good catch. I did get two of the line filters to
work by changing the spacing at the end of one of the lines. Now I'm just
having problems with filtering the date. I'm currently using the following
line in my code:

If Not IsNull(Me.txtFilteronDate) Then
strWhere = strWhere & "([RawDate]= "" # " & Me.txtFilteronDate & " # "")
And "
End If

The problem is when it is executed it doesn't give me an error message it
just gives me a message box that says "The ApplyFilter action was canceled."

Any Suggestions?
--
Kellie Murphy


Douglas J Steele said:
Is that an actual copy-and-paste from your application? I noticed that in
one place you're only adding " And" to the end, so that when you remove the
last 5 characters, you're going to remove a closing parenthesis.

It might be worth putting a Debug.Print strWhere in your code and see what's
actually in the string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kmurphy said:
I tried removing the quotes from my strings:


strWhere = strWhere & "([ShiftID] = " & Me.FilterOnShift & ") And "

This gives me the same error message as before: Missing ),], or Item in
query expression '([ShiftID]=2'

Thanks for the tip about the Date field. I know that the word Date is a
reserved word and I am careful to make sure that I place the brackets around
the field name, but I did change it just to be safe..
--
Kmurphy


:

If ShiftID and LineID are numeric fields in the table, remove the quotes
from your strings:

strWhere = strWhere & "([ShiftID] = " & Me.FilterOnShift & ") And "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have (2) combo boxes and (1) text box set-up to filter out my
Click to expand...
form.
I am
using the following code.

Dim strWhere As String
Dim lngLen As Long


If Not IsNull(Me.txtFilteronDate) Then
strWhere = strWhere & "([Date]= ""#" & Me.FilteronDate & " # "")
Click to expand...
And
"
End If

If Not IsNull(Me.FilterOnShift) Then
strWhere = strWhere & "([ShiftID] = """ & Me.FilterOnShift & """)
Click to expand...
And
"
End If

If Not IsNull(Me.FilterProcessLine) Then
strWhere = strWhere & "([LineID]= """ & Me.FilterProcessLine &
Click to expand...
""")
And"
End If

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

The FilterOnShift and FilterOnLine fields are both numeric fields. I
can't
seem to get the syntax correct for these statement and I'm getting the
following error:

Missing),], or Item in query expression '([ShiftID]="2") And
([LineID]="2"".
Can someone please help me with the syntax.

Thanks...
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help
--
Kellie Murphy


Douglas J Steele said:
strWhere = strWhere & "([RawDate]= #" & Me.txtFilteronDate & "# ) And "

Your approach is putting quotes as well as # as delimiters for the dates,
and that's incorrect.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kmurphy said:
Thanks Douglas. That was a good catch. I did get two of the line filters to
work by changing the spacing at the end of one of the lines. Now I'm just
having problems with filtering the date. I'm currently using the following
line in my code:

If Not IsNull(Me.txtFilteronDate) Then
strWhere = strWhere & "([RawDate]= "" # " & Me.txtFilteronDate & " # "")
And "
End If

The problem is when it is executed it doesn't give me an error message it
just gives me a message box that says "The ApplyFilter action was canceled."

Any Suggestions?
--
Kellie Murphy


Douglas J Steele said:
Is that an actual copy-and-paste from your application? I noticed that in
one place you're only adding " And" to the end, so that when you remove the
last 5 characters, you're going to remove a closing parenthesis.

It might be worth putting a Debug.Print strWhere in your code and see what's
actually in the string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried removing the quotes from my strings:


strWhere = strWhere & "([ShiftID] = " & Me.FilterOnShift & ") And "

This gives me the same error message as before: Missing ),], or Item in
query expression '([ShiftID]=2'

Thanks for the tip about the Date field. I know that the word Date is a
reserved word and I am careful to make sure that I place the brackets
around
the field name, but I did change it just to be safe..
--
Kmurphy


:

If ShiftID and LineID are numeric fields in the table, remove the quotes
from your strings:

strWhere = strWhere & "([ShiftID] = " & Me.FilterOnShift & ") And "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have (2) combo boxes and (1) text box set-up to filter out my form.
I am
using the following code.

Dim strWhere As String
Dim lngLen As Long


If Not IsNull(Me.txtFilteronDate) Then
strWhere = strWhere & "([Date]= ""#" & Me.FilteronDate & " # "") And
"
End If

If Not IsNull(Me.FilterOnShift) Then
strWhere = strWhere & "([ShiftID] = """ & Me.FilterOnShift & """) And
"
End If

If Not IsNull(Me.FilterProcessLine) Then
strWhere = strWhere & "([LineID]= """ & Me.FilterProcessLine & """)
And"

End If

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

The FilterOnShift and FilterOnLine fields are both numeric fields. I
can't
seem to get the syntax correct for these statement and I'm getting the
following error:

Missing),], or Item in query expression '([ShiftID]="2") And
([LineID]="2"".
Can someone please help me with the syntax.

Thanks...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top