473,324 Members | 2,214 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Help needed with query syntax

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
4 1867
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
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
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
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Julien - Marseille | last post by:
Hello, I need help for php syntax when i call Mysql database I have wrote that and my sql connection is working I just have a problem with this command line : $query = "SELECT * FROM...
15
by: Jack | last post by:
I have a text file of data in a file (add2db.txt) where the entries are already entered on separate lines in the following form: INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great...
28
by: stu_gots | last post by:
I have been losing sleep over this puzzle, and I'm convinced my train of thought is heading in the wrong direction. It is difficult to explain my circumstances, so I will present an identical...
6
by: mo | last post by:
I need to bring the ssn's into UniqueSups (supervisors) from tblNonNormalized. My inherited DB is not normalized and I find it extremely irritating due to the workarounds needed. I created...
4
by: trint | last post by:
Ok, This script is something I wrote for bringing up a report in reporting services and it is really slow...Is their any problems with it or is their better syntax to speed it up and still provide...
8
by: baustin75 | last post by:
Posted: Mon Oct 03, 2005 1:41 pm Post subject: cannot mail() in ie only when debugging in php designer 2005 -------------------------------------------------------------------------------- ...
4
by: MLH | last post by:
I apologize in advance to forum readers noticing this somewhat redundant post. I fear that my Subject Heading was ill-chosen in earlier post I made on this topic. Am hoping that a clearer Subject...
6
by: T. Wintershoven | last post by:
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...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.