Help needed with query syntax

[T. Wintershoven]

Hi

Can someone please tell me whats wrong with the last line of the query
below. The first three lines work fine but when i add the fourth line i get
an error message (see text at ERROR MESSAGE)

sql_HTTermijnRecords = "select * from Orders where FaktuurGeprint =
'J'" & _
"AND dathergestuurd Is Not Null " & _
"AND PerBankKas Is Null " & _
"AND " & HTdatumMinAantalDagen & " > " & dathergestuurd

The last line is a comparison between two dates.

ERROR MESSAGE
============================================
Run-time error 3075

Syntax error (missing operator) in query expression 'FaktuurGeprint =
'J' AND dathergestuurd Is Not Null AND PerBankKas Is Null AND
22-11-2005 >'

========= end error message==============================

The first date (22-11-2005) is visible in the error message but the second
date is missing.
As you can see there is nothing after the > but there should be date
information from a DB-cell named dathergestuurd.
I tried a lot of things but i keep getting the same message.

What is wrong with the syntax of the last line ???

T.i.a.

Regards

Tino Wintershoven
The Netherlands

[GH]

Is dathergestuurd both a date field in the table and a variable in your
code? If not, you need to make sure that dathergestuurd is part of
the query enclosed in the double quotes, like:

sql_HTTermijnRecords = "select * from Orders where FaktuurGeprint =
'J'" & _
"AND dathergestuurd Is Not Null " & _
"AND PerBankKas Is Null " & _
"AND dathergestuurd < #" & HTdatumMinAantalDagen & "#'

If it is a variable value, then you might want to verify it contains a
valid date value prior to executing your query. If neither suggestion
resolves your issue, please let me know.

- GH

[Allen Browne]

The message indicates that dathergestuurd was Null, so the last part of the
WHERE clause was invalid.

Your code concatenates the values of HTdatumMinAantalDagen and
dathergestuurd into the string. Presumably these are the names of controls
on the form. If so the literal date concatenated into the string needs to be
delimted with #. It also won't work in the d/m/y format, so you will need to
format it as mm/dd/yyyy.

However, I don't understand the logic of concatenating the values from the
form into the string, since the result would either be True for all records
(making no difference), or False for all records (returning no results at
all.) I wonder if you meant:

sql_HTTermijnRecords = "select * from Orders " &
"where (FaktuurGeprint = 'J') " & _
"AND (dathergestuurd Is Not Null) " & _
"AND (PerBankKas Is Null) " & _
"AND (HTdatumMinAantalDagen > dathergestuurd);"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"T. Wintershoven" <wi**********@quicknet.nl> wrote in message
news:50***************************@cache40.multika bel.net...

Can someone please tell me whats wrong with the last line of the query
below. The first three lines work fine but when i add the fourth line i
get
an error message (see text at ERROR MESSAGE)

sql_HTTermijnRecords = "select * from Orders where FaktuurGeprint =
'J'" & _
"AND dathergestuurd Is Not Null " & _
"AND PerBankKas Is Null " & _
"AND " & HTdatumMinAantalDagen & " > " & dathergestuurd

The last line is a comparison between two dates.

ERROR MESSAGE
============================================
Run-time error 3075

Syntax error (missing operator) in query expression 'FaktuurGeprint =
'J' AND dathergestuurd Is Not Null AND PerBankKas Is Null AND
22-11-2005 >'

========= end error message==============================

The first date (22-11-2005) is visible in the error message but the second
date is missing.
As you can see there is nothing after the > but there should be date
information from a DB-cell named dathergestuurd.
I tried a lot of things but i keep getting the same message.

What is wrong with the syntax of the last line ???

[Erland Sommarskog]

T. Wintershoven (wi**********@quicknet.nl) writes:

Can someone please tell me whats wrong with the last line of the query
below. The first three lines work fine but when i add the fourth line i
get an error message (see text at ERROR MESSAGE)

sql_HTTermijnRecords = "select * from Orders where FaktuurGeprint
= 'J'" & _
"AND dathergestuurd Is Not Null " & _
"AND PerBankKas Is Null " & _
"AND " & HTdatumMinAantalDagen & " > " & dathergestuurd

The last line is a comparison between two dates.

ERROR MESSAGE
============================================
Run-time error 3075

Syntax error (missing operator) in query expression 'FaktuurGeprint =
'J' AND dathergestuurd Is Not Null AND PerBankKas Is Null AND
22-11-2005 >'

========= end error message==============================

The first date (22-11-2005) is visible in the error message but the second
date is missing.
As you can see there is nothing after the > but there should be date
information from a DB-cell named dathergestuurd.

Juding from the error message, this is not SQL Server so being outside
my realm, I will have to guess. But I note that the first instance of
dathergestuurd is within a string literal, and the second is not. Whatever
the second refers to, it is not a database column.

I also suspect that you need to put the date within some delimiters. If
you are using Access, that's #.

And the next time you have a question to ask, please be a little more
considerate when you choose a newsgroup. Particularly, there is no point
to post to a newsgroup about SQL Server, if you are not using SQL Server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[GH]

I think I erroneously assumed this was an Access question due to its
posting to the Access newsgroup, among others, so that impression could
be my fault. However, this query could just as well be an inline SQL
Server query called from a VB application, since its syntax is rather
ambiguous on that front. Regardless of whether or not it is Access or
SQL Server query, it is missing the appropriate date delimiter, # in
Access or ' in SQL Server.

Also, as I alluded to in my first post, unless dathergestuurd is a VB
object, it is, as you mention, not included in the string literal, so
the SQL engine is not interpreting dathergestuurd as part of the query.
This could be captured in the VB code by adding Option Explicit at the
top of the code module, thus enforcing declaration of variables and,
more importantly, identifying undeclared variables.

- GH

[Michael B. Johnson]

On Wed, 30 Nov 2005 15:46:11 +0100, "T. Wintershoven" <wi**********@quicknet.nl>
wrote:

Can someone please tell me whats wrong with the last line of the query
below. The first three lines work fine but when i add the fourth line i get
an error message (see text at ERROR MESSAGE)

sql_HTTermijnRecords = "select * from Orders where FaktuurGeprint =
'J'" & _
"AND dathergestuurd Is Not Null " & _
"AND PerBankKas Is Null " & _
"AND " & HTdatumMinAantalDagen & " > " & dathergestuurd

The last line is a comparison between two dates.

ERROR MESSAGE
============================================
Run-time error 3075

Syntax error (missing operator) in query expression 'FaktuurGeprint =
'J' AND dathergestuurd Is Not Null AND PerBankKas Is Null AND
22-11-2005 >'

========= end error message============================== What is wrong with the syntax of the last line ???

If you are sure the Date in [HTdatumMinAantalDagen] and the Date in
[dathergestuurd] are not NULL, then you need to use single quotes. -

SELECT *
FROM Orders
WHERE FaktuurGeprint = 'J'
AND dathergestuurd Is Not Null
AND PerBankKas Is Null

Last line needs to be like this:

.... & " AND '" & HTdatumMinAantalDagen & "' > '" & dathergestuurd & "'"
-- Please note the additional ' within the "" quotes!

Hope this helps!
_______________________
Michael B. Johnson

[Gert-Jan Strik]

Tino,

22-11-2005 is not a date. At most, it is an expression (a calculation)
that resolves to -1994. If you want it to be a date, then use quotes to
make it a string that can be converted to a date. It is also best to use
a safe date format, for example YYYYMMDD. This will correctly convert to
a datetime regardless of server or language settings. So for example
'20051122'.

Also note, that the line
"AND " & HTdatumMinAantalDagen & " > " & dathergestuurd
will cause all selected rows to be returned or none. This means that you
could simply write:

If HTdatumMinAantalDagen > dathergestuurd Then
' your original query without the last line
sql_HTTermijnRecords = ...
Else
' no rows to return
End If

HTH,
Gert-Jan
"T. Wintershoven" wrote:

Hi

Can someone please tell me whats wrong with the last line of the query
below. The first three lines work fine but when i add the fourth line i get
an error message (see text at ERROR MESSAGE)

sql_HTTermijnRecords = "select * from Orders where FaktuurGeprint =
'J'" & _
"AND dathergestuurd Is Not Null " & _
"AND PerBankKas Is Null " & _
"AND " & HTdatumMinAantalDagen & " > " & dathergestuurd

The last line is a comparison between two dates.

ERROR MESSAGE
============================================
Run-time error 3075

Syntax error (missing operator) in query expression 'FaktuurGeprint =
'J' AND dathergestuurd Is Not Null AND PerBankKas Is Null AND
22-11-2005 >'

========= end error message==============================

The first date (22-11-2005) is visible in the error message but the second
date is missing.
As you can see there is nothing after the > but there should be date
information from a DB-cell named dathergestuurd.
I tried a lot of things but i keep getting the same message.

What is wrong with the syntax of the last line ???

T.i.a.

Regards

Tino Wintershoven
The Netherlands