Syntax error on where string problem

[Douglas J. Steele]

Jeff: That was due to an incorrect Format string being used. He had
"\#mm\dd\yyyy\#", rather than "\#mm\/dd\/yyyy\#"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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#")'<<

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

Regards

Jeff Boyce
<Office/Access MVP>

Huh ?

 

[Tony Girgenti]

Sorry Jeff. That garbage was a result of a bad format statement.

Tony

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#")'<<

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

Regards

Jeff Boyce
<Office/Access MVP>

Huh ?

 

[Tony Girgenti]

Doug.

The [PENDING ORDERS].[TIMESTAMP] field is a data type of Date/Time and has a
format of General Date.

Tony

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas.

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

Thanks,
Tony

I believe Van had a slight typo there.

Try

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



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

message 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

 

[Douglas J. Steele]

Just noticed that in your code for the improper format, you've still got
quotes around the date.

It should look like:

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

In other words, your code should be

If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & _
"[PENDING ORDERS].[TIMESTAMP] Between " & _
Format(Me.StrDat, "\#mm\/dd\/yyyy\#") & _
" And " & _
Format (Me.EndDat, "\#mm\/dd\/yyyy\#") & _
" AND "
Else 'Start, no end
strWhere = strWhere & _
"[PENDING ORDERS].[TIMESTAMP] >= " &
Format(Me.StrDat, "\#mm\/dd\/yyyy\#") & _
" AND "
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & _
"[PENDING ORDERS].[TIMESTAMP] <= " &
Format(Me.EndDat, "\#mm\/dd\/yyyy/#") & _
" AND "
End If
End If





--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug.

The [PENDING ORDERS].[TIMESTAMP] field is a data type of Date/Time and has
a format of General Date.

Tony

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas.

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

Thanks,
Tony

I believe Van had a slight typo there.

Try

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



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

message 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

 

[Van T. Dinh]

Thank, Doug.

I did have typo in my formatting String. I meant to use the forward slash
like:

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

which should work the same as your:

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

 

[Van T. Dinh]

I noted also that your last SQL did not have the square brackets around
PENDING ORDERS.

Doug has included them in his suggested code. However, if you modify your
code (rather than copy & paste Doug's code), make sure you include the
square brackets.

 

[Douglas J. Steele]

Slight quibble. I believe that Format(Me.EndDat, "\#mm/dd/yyyy\#") will
actually replace the forward slashes with whatever the Date Separator
character has been set to under Regional Settings. "\#mm\/dd\/yyyy\#" will
guarantee that doesn't happen.

 

[Van T. Dinh]

Thanks, Doug.

I wasn't aware of that since "/" is used in my Regional Settings.