Syntax error on where string problem

T

Tony Girgenti

Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:
Syntax error (missing operator) in query expression '([COMPANY NAME]="BLAIR HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING ORDERS.TIMESTAMP Between "20040405" And "20040405")'<<
Click to expand...
Click to expand...

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0] & """ AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF - WIP]![Combo8] & """ AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between """ & Format(Me.StrDat, "yyyymmdd") & """ And """ & Format (Me.EndDat, "yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ & Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ & Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
 
J

John Spencer

Try adding [] around Pending Orders so that it reads [Pending Orders].TimeStamp
Tony Girgenti wrote:

Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:
Syntax error (missing operator) in query expression '([COMPANY NAME]="BLAIR
Click to expand...
Click to expand...
HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING ORDERS.TIMESTAMP Between
"20040405" And "20040405")'<<

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0] & """
AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF - WIP]![Combo8] &
""" AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between """ &
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format
(Me.EndDat, "yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
Click to expand...
 
V

Van T. Dinh

In addition to John's advice ...

Is your TIMESTAMP a DateTime Field?

If it is, you should use something like:

Format(Me.EndDat, "\#mm\dd\yyyy\#")

so that your constructed SQL becomes:

...([COMPANY NAME]="BLAIR HOUSE 10023")
AND ([SIDEMARK]="MARGAREL")
AND [PENDING ORDERS].[TIMESTAMP]
Between #04/05/2004# And #04/05/2005#)

--
HTH
Van T. Dinh
MVP (Access)



Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:
Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING
ORDERS.TIMESTAMP Between "20040405" And "20040405")'<<
Click to expand...
Click to expand...

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0] & """
AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF - WIP]![Combo8]
& """ AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between """ &
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format
(Me.EndDat, "yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
 
T

Tony Girgenti

Hi John.

If i put the brackets around it, it asks to "Enter Parameter Value - PENDING
ORDERS.TIMESTAMP".

Thanks,
Tony

John Spencer said:
Try adding [] around Pending Orders so that it reads [Pending
Orders].TimeStamp
Tony Girgenti wrote:

Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:
Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR
Click to expand...
HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING ORDERS.TIMESTAMP
Between
"20040405" And "20040405")'<<

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0] &
"""
AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF -
WIP]![Combo8] &
""" AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between """ &
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format
(Me.EndDat, "yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
Click to expand...
Click to expand...
 
J

John Spencer

Did you put brackets this way?
[Pending Orders].[TimeStamp] Correct
or
[Pending Orders.TimeStamp] Incorrect

If you did it correctly, then Access is not recognizing the name.
Are the field and tablename spelled correctly? No extra spaces?
Is the table in the FROM clause of the query?
Is there more than one TimeStamp field in the report's record source? If
not, then drop the reference to the tablename and just use TimeStamp.

Tony Girgenti said:
Hi John.

If i put the brackets around it, it asks to "Enter Parameter Value -
PENDING ORDERS.TIMESTAMP".

Thanks,
Tony

John Spencer said:
Try adding [] around Pending Orders so that it reads [Pending
Orders].TimeStamp
Tony Girgenti wrote:

Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:

Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR
HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING ORDERS.TIMESTAMP
Between
"20040405" And "20040405")'<<

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0] &
"""
AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF -
WIP]![Combo8] &
""" AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between """
&
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format
(Me.EndDat, "yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Girgenti

Hello Van.

Yes it is a DateTime field. When i try your suggestion, i get:
Syntax error (missing operator) in query expression '([COMPANY NAME] =
"BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND PENDING
ORDERS.TIMESTAMP Between "#04d5y0496#" And "#04d5y0496#")'<<
Click to expand...
Click to expand...

Thanks,
Tony

Van T. Dinh said:
In addition to John's advice ...

Is your TIMESTAMP a DateTime Field?

If it is, you should use something like:

Format(Me.EndDat, "\#mm\dd\yyyy\#")

so that your constructed SQL becomes:

...([COMPANY NAME]="BLAIR HOUSE 10023")
AND ([SIDEMARK]="MARGAREL")
AND [PENDING ORDERS].[TIMESTAMP]
Between #04/05/2004# And #04/05/2005#)

--
HTH
Van T. Dinh
MVP (Access)



Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:
Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING
ORDERS.TIMESTAMP Between "20040405" And "20040405")'<<
Click to expand...
Click to expand...

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0] & """
AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF -
WIP]![Combo8] & """ AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between """ &
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format (Me.EndDat,
"yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
Click to expand...
 
T

Tony Girgenti

John.

I tried it your way with "[Pending Orders].[TimeStamp]" and it still asks
for parameters.

Field and table name are spelled correctly.

The table is in the FROM clause of the Query.

If i remove the reference to the table (PENDING ORDERS), I get the message:
The specified field '[TIMESTAMP]' could refer to more than one table
listed in the FROM clause of your SQL statement.<<
Click to expand...
Click to expand...

Thanks,
Tony

John Spencer said:
Did you put brackets this way?
[Pending Orders].[TimeStamp] Correct
or
[Pending Orders.TimeStamp] Incorrect

If you did it correctly, then Access is not recognizing the name.
Are the field and tablename spelled correctly? No extra spaces?
Is the table in the FROM clause of the query?
Is there more than one TimeStamp field in the report's record source? If
not, then drop the reference to the tablename and just use TimeStamp.

Tony Girgenti said:
Hi John.

If i put the brackets around it, it asks to "Enter Parameter Value -
PENDING ORDERS.TIMESTAMP".

Thanks,
Tony

John Spencer said:
Try adding [] around Pending Orders so that it reads [Pending
Orders].TimeStamp

Tony Girgenti wrote:

Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:

Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR
HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING ORDERS.TIMESTAMP
Between
"20040405" And "20040405")'<<

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0] &
"""
AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF -
WIP]![Combo8] &
""" AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between """
&
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format
(Me.EndDat, "yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Perhaps it is something I am not seeing. Could you please copy and post
the SQL of your query? Perhaps I (or someone wiser) will be able to spot
the problem.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

You might try putting
Debug.Print strWhere
STOP

Just before
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

That way when the code halts, you can examine the strWhere in detail in the
immediate window. Perhaps you can detect what the error is then.

Tony Girgenti said:
John.

I tried it your way with "[Pending Orders].[TimeStamp]" and it still asks
for parameters.

Field and table name are spelled correctly.

The table is in the FROM clause of the Query.

If i remove the reference to the table (PENDING ORDERS), I get the
message:
The specified field '[TIMESTAMP]' could refer to more than one table
listed in the FROM clause of your SQL statement.<<
Click to expand...
Click to expand...

Thanks,
Tony

John Spencer said:
Did you put brackets this way?
[Pending Orders].[TimeStamp] Correct
or
[Pending Orders.TimeStamp] Incorrect

If you did it correctly, then Access is not recognizing the name.
Are the field and tablename spelled correctly? No extra spaces?
Is the table in the FROM clause of the query?
Is there more than one TimeStamp field in the report's record source? If
not, then drop the reference to the tablename and just use TimeStamp.

Tony Girgenti said:
Hi John.

If i put the brackets around it, it asks to "Enter Parameter Value -
PENDING ORDERS.TIMESTAMP".

Thanks,
Tony

Try adding [] around Pending Orders so that it reads [Pending
Orders].TimeStamp

Tony Girgenti wrote:

Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:

Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR
HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING ORDERS.TIMESTAMP
Between
"20040405" And "20040405")'<<

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0]
& """
AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF -
WIP]![Combo8] &
""" AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and
end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between
""" &
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format
(Me.EndDat, "yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Girgenti

Hi John.

Thanks,
Tony

SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].[NUMBER OF FABRICS], INSTALL.INSTALLER, INSTALL.[INSTALL DATE],
INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION, INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

John Spencer said:
Perhaps it is something I am not seeing. Could you please copy and post
the SQL of your query? Perhaps I (or someone wiser) will be able to spot
the problem.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

You might try putting
Debug.Print strWhere
STOP

Just before
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

That way when the code halts, you can examine the strWhere in detail in
the immediate window. Perhaps you can detect what the error is then.

Tony Girgenti said:
John.

I tried it your way with "[Pending Orders].[TimeStamp]" and it still asks
for parameters.

Field and table name are spelled correctly.

The table is in the FROM clause of the Query.

If i remove the reference to the table (PENDING ORDERS), I get the
message:
The specified field '[TIMESTAMP]' could refer to more than one table
listed in the FROM clause of your SQL statement.<<
Click to expand...

Thanks,
Tony

John Spencer said:
Did you put brackets this way?
[Pending Orders].[TimeStamp] Correct
or
[Pending Orders.TimeStamp] Incorrect

If you did it correctly, then Access is not recognizing the name.
Are the field and tablename spelled correctly? No extra spaces?
Is the table in the FROM clause of the query?
Is there more than one TimeStamp field in the report's record source?
If not, then drop the reference to the tablename and just use TimeStamp.

Hi John.

If i put the brackets around it, it asks to "Enter Parameter Value -
PENDING ORDERS.TIMESTAMP".

Thanks,
Tony

Try adding [] around Pending Orders so that it reads [Pending
Orders].TimeStamp

Tony Girgenti wrote:

Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:

Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR
HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING ORDERS.TIMESTAMP
Between
"20040405" And "20040405")'<<

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0]
& """
AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF -
WIP]![Combo8] &
""" AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and
end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between
""" &
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format
(Me.EndDat, "yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

Tony

What is:

04d5y0496

I'm not familiar with that as a way of writing a date.

And I believe one of the other responders on this post recommended that you
surround "PENDING ORDERS" with square brackets -- SQL statements don't
"scan" if there are spaces in the names of tables or fields.

Regards

Jeff Boyce
<Office/Access MVP>
 
D

Douglas J. Steele

I believe Van had a slight typo there.

Try

Format(Me.EndDat, "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tony Girgenti said:
Hello Van.

Yes it is a DateTime field. When i try your suggestion, i get:
Syntax error (missing operator) in query expression '([COMPANY NAME] =
"BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND PENDING
ORDERS.TIMESTAMP Between "#04d5y0496#" And "#04d5y0496#")'<<
Click to expand...
Click to expand...

Thanks,
Tony

Van T. Dinh said:
In addition to John's advice ...

Is your TIMESTAMP a DateTime Field?

If it is, you should use something like:

Format(Me.EndDat, "\#mm\dd\yyyy\#")

so that your constructed SQL becomes:

...([COMPANY NAME]="BLAIR HOUSE 10023")
AND ([SIDEMARK]="MARGAREL")
AND [PENDING ORDERS].[TIMESTAMP]
Between #04/05/2004# And #04/05/2005#)

--
HTH
Van T. Dinh
MVP (Access)



Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:
Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING
ORDERS.TIMESTAMP Between "20040405" And "20040405")'<<
Click to expand...

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0] &
""" AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF -
WIP]![Combo8] & """ AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between """ &
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format (Me.EndDat,
"yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
Click to expand...
Click to expand...
 
J

John Spencer

Well, unless my eyes deceive me there is no [Pending Orders].TimeStamp in
the SELECT clause of that query. Since there is no field with that name
being returned by the query, it is not possible to refer to the field with
the add-on where clause. You need to add the field to the underlying query.

SELECT DISTINCT TempStatus.WORKSTATION,
TempStatus.TIMESTAMP,
TempStatus.JOBNO,
[PENDING ORDERS].DESCRIPTION,
[PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME],
[PENDING ORDERS].SIDEMARK,
[PENDING ORDERS].[NUMBER OF FABRICS],
INSTALL.INSTALLER,
INSTALL.[INSTALL DATE],
INSTALL.[INSTALL NOTES],
INSTALL.WORKSTATION,
INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON
[PENDING ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN
INSTALL ON [PENDING ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

Tony Girgenti said:
Hi John.

Thanks,
Tony

SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].[NUMBER OF FABRICS], INSTALL.INSTALLER, INSTALL.[INSTALL DATE],
INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION, INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

John Spencer said:
Perhaps it is something I am not seeing. Could you please copy and post
the SQL of your query? Perhaps I (or someone wiser) will be able to spot
the problem.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

You might try putting
Debug.Print strWhere
STOP

Just before
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

That way when the code halts, you can examine the strWhere in detail in
the immediate window. Perhaps you can detect what the error is then.

Tony Girgenti said:
John.

I tried it your way with "[Pending Orders].[TimeStamp]" and it still
asks for parameters.

Field and table name are spelled correctly.

The table is in the FROM clause of the Query.

If i remove the reference to the table (PENDING ORDERS), I get the
message:
The specified field '[TIMESTAMP]' could refer to more than one table
listed in the FROM clause of your SQL statement.<<

Thanks,
Tony

Did you put brackets this way?
[Pending Orders].[TimeStamp] Correct
or
[Pending Orders.TimeStamp] Incorrect

If you did it correctly, then Access is not recognizing the name.
Are the field and tablename spelled correctly? No extra spaces?
Is the table in the FROM clause of the query?
Is there more than one TimeStamp field in the report's record source?
If not, then drop the reference to the tablename and just use
TimeStamp.

Hi John.

If i put the brackets around it, it asks to "Enter Parameter Value -
PENDING ORDERS.TIMESTAMP".

Thanks,
Tony

Try adding [] around Pending Orders so that it reads [Pending
Orders].TimeStamp

Tony Girgenti wrote:

Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:

Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR
HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING ORDERS.TIMESTAMP
Between
"20040405" And "20040405")'<<

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF -
WIP]![Combo0] & """
AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF -
WIP]![Combo8] &
""" AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and
end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between
""" &
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format
(Me.EndDat, "yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Girgenti

I'm sorry John.

Your original question was "Is the table in the FROM clause of the query?"

So now i made that change, and i get "Data type mismatch in criteria
expression".

Thanks,
Tony


John Spencer said:
Well, unless my eyes deceive me there is no [Pending Orders].TimeStamp in
the SELECT clause of that query. Since there is no field with that name
being returned by the query, it is not possible to refer to the field with
the add-on where clause. You need to add the field to the underlying
query.

SELECT DISTINCT TempStatus.WORKSTATION,
TempStatus.TIMESTAMP,
TempStatus.JOBNO,
[PENDING ORDERS].DESCRIPTION,
[PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME],
[PENDING ORDERS].SIDEMARK,
[PENDING ORDERS].[NUMBER OF FABRICS],
INSTALL.INSTALLER,
INSTALL.[INSTALL DATE],
INSTALL.[INSTALL NOTES],
INSTALL.WORKSTATION,
INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON
[PENDING ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN
INSTALL ON [PENDING ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

Tony Girgenti said:
Hi John.

Thanks,
Tony

SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].[NUMBER OF FABRICS], INSTALL.INSTALLER, INSTALL.[INSTALL DATE],
INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION, INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

John Spencer said:
Perhaps it is something I am not seeing. Could you please copy and
post the SQL of your query? Perhaps I (or someone wiser) will be able
to spot the problem.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

You might try putting
Debug.Print strWhere
STOP

Just before
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

That way when the code halts, you can examine the strWhere in detail in
the immediate window. Perhaps you can detect what the error is then.

John.

I tried it your way with "[Pending Orders].[TimeStamp]" and it still
asks for parameters.

Field and table name are spelled correctly.

The table is in the FROM clause of the Query.

If i remove the reference to the table (PENDING ORDERS), I get the
message:
The specified field '[TIMESTAMP]' could refer to more than one table
listed in the FROM clause of your SQL statement.<<

Thanks,
Tony

Did you put brackets this way?
[Pending Orders].[TimeStamp] Correct
or
[Pending Orders.TimeStamp] Incorrect

If you did it correctly, then Access is not recognizing the name.
Are the field and tablename spelled correctly? No extra spaces?
Is the table in the FROM clause of the query?
Is there more than one TimeStamp field in the report's record source?
If not, then drop the reference to the tablename and just use
TimeStamp.

Hi John.

If i put the brackets around it, it asks to "Enter Parameter Value -
PENDING ORDERS.TIMESTAMP".

Thanks,
Tony

Try adding [] around Pending Orders so that it reads [Pending
Orders].TimeStamp

Tony Girgenti wrote:

Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:

Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR
HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING ORDERS.TIMESTAMP
Between
"20040405" And "20040405")'<<

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF -
WIP]![Combo0] & """
AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF -
WIP]![Combo8] &
""" AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and
end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between
""" &
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format
(Me.EndDat, "yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """
&
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """
&
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Ok, this is progress.

Is TIMESTAMP a datetime field? If it is, you need to change your code that
is building strWhere to reflect that. Right now you are trying to compare a
datetime field to a string. You need to delimit the dates with # characters
and you need to insert separators between the date parts. At this point, I
don't know if you have Pending Orders.TimeStamp in your query, but I will
assume that is the case.

strWhere = strWhere & "[PENDING ORDERS].TIMESTAMP Between #" & _
Format(Me.StrDat, "yyyy-mm-dd") & "# And #" & _
Format(Me.EndDat, "yyyy-mm-dd") & "# AND "

As I suggested earlier, you might want to insert
Debug.Print strWhere
just before you actually use the strWhere string. Then you can look at the
string and see exactly what you are generating and whether or not it looks
like a valid WHERE clause (without the word "Where").



Tony Girgenti said:
I'm sorry John.

Your original question was "Is the table in the FROM clause of the query?"

So now i made that change, and i get "Data type mismatch in criteria
expression".

Thanks,
Tony


John Spencer said:
Well, unless my eyes deceive me there is no [Pending Orders].TimeStamp in
the SELECT clause of that query. Since there is no field with that name
being returned by the query, it is not possible to refer to the field
with the add-on where clause. You need to add the field to the
underlying query.

SELECT DISTINCT TempStatus.WORKSTATION,
TempStatus.TIMESTAMP,
TempStatus.JOBNO,
[PENDING ORDERS].DESCRIPTION,
[PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME],
[PENDING ORDERS].SIDEMARK,
[PENDING ORDERS].[NUMBER OF FABRICS],
INSTALL.INSTALLER,
INSTALL.[INSTALL DATE],
INSTALL.[INSTALL NOTES],
INSTALL.WORKSTATION,
INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON
[PENDING ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN
INSTALL ON [PENDING ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

Tony Girgenti said:
Hi John.

Thanks,
Tony
Click to expand...
Click to expand...
Click to expand...
S N I P
 
T

Tony Girgenti

Hello Douglas.

Assuming "\/" in the Format statement = a backslash and forwardslash, i now
get a Datatype mismatch in criteria expression".

Thanks,
Tony

Douglas J. Steele said:
I believe Van had a slight typo there.

Try

Format(Me.EndDat, "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tony Girgenti said:
Hello Van.

Yes it is a DateTime field. When i try your suggestion, i get:
Syntax error (missing operator) in query expression '([COMPANY NAME] =
"BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND PENDING
ORDERS.TIMESTAMP Between "#04d5y0496#" And "#04d5y0496#")'<<
Click to expand...

Thanks,
Tony

Van T. Dinh said:
In addition to John's advice ...

Is your TIMESTAMP a DateTime Field?

If it is, you should use something like:

Format(Me.EndDat, "\#mm\dd\yyyy\#")

so that your constructed SQL becomes:

...([COMPANY NAME]="BLAIR HOUSE 10023")
AND ([SIDEMARK]="MARGAREL")
AND [PENDING ORDERS].[TIMESTAMP]
Between #04/05/2004# And #04/05/2005#)

--
HTH
Van T. Dinh
MVP (Access)



Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:

Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING
ORDERS.TIMESTAMP Between "20040405" And "20040405")'<<

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0] &
""" AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF -
WIP]![Combo8] & """ AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between """ &
Format(Me.StrDat, "yyyymmdd") & """ And """ & Format (Me.EndDat,
"yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Girgenti

John.

No errors, but it didn't find any records. My Debug.Print of StrWhere is:
[COMPANY NAME] = "BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND
[PENDING ORDERS].TIMESTAMP Between #2004-04-05# And #2004-04-05#

The data in [PENDING ORDERS].TIMESTAMP is "4/5/2004 8:38:36 AM"

Thanks,
Tony

John Spencer said:
Ok, this is progress.

Is TIMESTAMP a datetime field? If it is, you need to change your code
that is building strWhere to reflect that. Right now you are trying to
compare a datetime field to a string. You need to delimit the dates with
# characters and you need to insert separators between the date parts. At
this point, I don't know if you have Pending Orders.TimeStamp in your
query, but I will assume that is the case.

strWhere = strWhere & "[PENDING ORDERS].TIMESTAMP Between #" & _
Format(Me.StrDat, "yyyy-mm-dd") & "# And #" & _
Format(Me.EndDat, "yyyy-mm-dd") & "# AND "

As I suggested earlier, you might want to insert
Debug.Print strWhere
just before you actually use the strWhere string. Then you can look at
the string and see exactly what you are generating and whether or not it
looks like a valid WHERE clause (without the word "Where").



Tony Girgenti said:
I'm sorry John.

Your original question was "Is the table in the FROM clause of the
query?"

So now i made that change, and i get "Data type mismatch in criteria
expression".

Thanks,
Tony


John Spencer said:
Well, unless my eyes deceive me there is no [Pending Orders].TimeStamp
in the SELECT clause of that query. Since there is no field with that
name being returned by the query, it is not possible to refer to the
field with the add-on where clause. You need to add the field to the
underlying query.

SELECT DISTINCT TempStatus.WORKSTATION,
TempStatus.TIMESTAMP,
TempStatus.JOBNO,
[PENDING ORDERS].DESCRIPTION,
[PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME],
[PENDING ORDERS].SIDEMARK,
[PENDING ORDERS].[NUMBER OF FABRICS],
INSTALL.INSTALLER,
INSTALL.[INSTALL DATE],
INSTALL.[INSTALL NOTES],
INSTALL.WORKSTATION,
INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON
[PENDING ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN
INSTALL ON [PENDING ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

Hi John.

Thanks,
Tony
Click to expand...
Click to expand...
S N I P
Click to expand...
 
G

Guest

Tony Girgenti said:
John.

No errors, but it didn't find any records. My Debug.Print of StrWhere is:
[COMPANY NAME] = "BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND
[PENDING ORDERS].TIMESTAMP Between #2004-04-05# And #2004-04-05#

The data in [PENDING ORDERS].TIMESTAMP is "4/5/2004 8:38:36 AM"
Click to expand...

Well, your query is asking for all records where TIMESTAMP is between
midnight, April 5 and midnight, April 5 - and 8:38:36 is not within that
particular microsecond <g>.

Try

[PENDING ORDERS].Timestamp > [Enter date:] AND [PENDING ORDERS].Timestamp <
DateAdd("d", 1, [Enter date:]

to get all times during that day.
 
J

Jeff Boyce

Tony

I was commenting on what I saw in the newsgroup, i.e.,
Syntax error (missing operator) in query expression '([COMPANY NAME] =
"BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND PENDING
ORDERS.TIMESTAMP Between "#04d5y0496#" And "#04d5y0496#")'<<
Click to expand...
Click to expand...

The #04d5y0496# above does not match any date format I know about.

Regards

Jeff Boyce
<Office/Access MVP>
 
D

Douglas J. Steele

The implication, then, is that Timestamp is a Text field, not a Date/Time
field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tony Girgenti said:
Hello Douglas.

Assuming "\/" in the Format statement = a backslash and forwardslash, i
now get a Datatype mismatch in criteria expression".

Thanks,
Tony

Douglas J. Steele said:
I believe Van had a slight typo there.

Try

Format(Me.EndDat, "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tony Girgenti said:
Hello Van.

Yes it is a DateTime field. When i try your suggestion, i get:

Syntax error (missing operator) in query expression '([COMPANY NAME] =
"BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND PENDING
ORDERS.TIMESTAMP Between "#04d5y0496#" And "#04d5y0496#")'<<

Thanks,
Tony

In addition to John's advice ...

Is your TIMESTAMP a DateTime Field?

If it is, you should use something like:

Format(Me.EndDat, "\#mm\dd\yyyy\#")

so that your constructed SQL becomes:

...([COMPANY NAME]="BLAIR HOUSE 10023")
AND ([SIDEMARK]="MARGAREL")
AND [PENDING ORDERS].[TIMESTAMP]
Between #04/05/2004# And #04/05/2005#)

--
HTH
Van T. Dinh
MVP (Access)



Hello.

Using Access 2003 on WIN XPpro.

Why am i getting this error when executing a report:

Syntax error (missing operator) in query expression '([COMPANY
NAME]="BLAIR HOUSE 10023" AND [SIDEMARK]="MARGAREL" AND PENDING
ORDERS.TIMESTAMP Between "20040405" And "20040405")'<<

Any help would be greatly appreciated.

Here is my coding:
=============================================================================
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String
Dim strWhere2 As String
Dim lngLen As Long

UpdateTempStatus

stDocName = "CHF STATUS 3"

strWhere = " "

If Not IsNull(Forms![CHF - WIP]![Combo0]) Then
strWhere = "[COMPANY NAME] = """ & Forms![CHF - WIP]![Combo0] &
""" AND "
End If

If Not IsNull(Forms![CHF - WIP]![Combo8]) Then
strWhere = strWhere & "[SIDEMARK] = """ & Forms![CHF -
WIP]![Combo8] & """ AND "
End If

'***********************************************
'Starting and ending DATES: tixhisthdr.postingdate is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP Between """
& Format(Me.StrDat, "yyyymmdd") & """ And """ & Format (Me.EndDat,
"yyyymmdd") & """ AND "
Else 'Start, no end
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP >= """ &
Format(Me.StrDat, "yyyymmdd") & """ AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & "PENDING ORDERS.TIMESTAMP <= """ &
Format(Me.EndDat, "yyyymmdd") & """ AND "
End If
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = "" & Left$(strWhere, lngLen) & vbCrLf
End If

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:

If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If

On Error GoTo 0

Resume Exit_Command2_Click

End Sub
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

type mismatch error 13 1
StrWhere with multi search results forms 8
Filters not responding 2
ADP list box filter 3
Populate distinct one combo box depending on selection in another combo box 5
Datasheet view alignment after filtering on subform 3
StrWhere error runtime 2448 5
Error 3075 w/ query 5

Top