By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,991 Members | 1,737 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,991 IT Pros & Developers. It's quick & easy.

Help needed with query syntax

P: n/a
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
Nov 30 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Nov 30 '05 #2

P: n/a
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
Nov 30 '05 #3

P: n/a
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

Nov 30 '05 #4

P: n/a
> 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.


For me the dathergestuurd looks to be an empty string.

If you're using MS Access put a breakpoint and see the value of that
variable. Or write a line before:
MSGBOX "dathergestuurd value is " & dathergestuurd

I forgot in Access if it's + sign or & sign to use in the MSGBOX.
You should be able to test various ways your problem.
Try another simple thing replace the variable with the variable you
know has a value:

"AND " & HTdatumMinAantalDagen & " > " & HTdatumMinAantalDagen

I haven't used Access code in a couple of years so I am a little rusty. But
it seems if you're sending this SQL string to SQL Server then you should
enclose the dates inside single quotes:

"AND '" & HTdatumMinAantalDagen & "' > '" & HTdatumMinAantalDagen
& "'"

And if you're not using SQL Server, rather MS Access SQL then the single
quotes should be replaced by # sign.

If you still need help please post what you tried.

Dec 1 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.