Correct Syntax

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...
 
D

Douglas J. Steele

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

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

fredg

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'
 
D

Douglas J Steele

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.
 
G

Guest

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...
 
D

Douglas J Steele

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...
 
G

Guest

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...
 
D

Douglas J Steele

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...
 
G

Guest

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...
 

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

Filter Text Syntax 2
Cleanup Code on Form ?? 3
Message Box to Display a count of Records 3
combo Problems 3
Get the sum of Filtered form 0
Build Where Clause in OnOpen of Report 2
CODE HELP! 2
Filtering query results using a ListBox In Access 2000 1

Top