Hi,
I am using Vb 6 and Access.
I am getting syntax error in the following sql query.
strSql = "SELECT tblInvoice.Proj ID,tblInvoice.C usID,tblInvoice .InvoiceNo,tblI nvoice.InvDate, tblInvoice.Tota l,tblInvoice.Pa id,tblInvoice.B alance,tblCusto mer.CustomerNam e FROM tblCustomer RIGHT JOIN tblInvoice ON tblCustomer.Cus ID=tblInvoice.C usID WHERE tblInvoice.InvD ate >= #" &sDate& "# AND tblInvoice.InvD ate <= #" &eDate& "# AND tblCustomer.Cus tomerName='" & cboCustomer.Tex t & "' AND tblInvoice.Proj ID=" & projectID & "AND Balance >0"
Can anyone one point out whats the mistake I am making here?
When I substitute dates instead of the variables, there is no error and the query executes perfectly.
Thanks for ur time.
1  1857 
I believe you just need to insert some spaces around the variable names. For example... - InvDate >= #" & sDate & "# AND
Also, just for readability, I would recommend chopping up such long lines. That's why VB has a continuation character (_).
For example, see which of these is easier to see... -
strSql = "SELECT tblInvoice.ProjID,tblInvoice.CusID,tblInvoice.InvoiceNo,tblInvoice.InvDate,tblInvoice.Total,tblInvoice.Paid,tblInvoice.Balance,tblCustomer.CustomerNam e FROM tblCustomer RIGHT JOIN tblInvoice ON tblCustomer.CusID=tblInvoice.CusID WHERE tblInvoice.InvDate >= #" & sDate & "# AND tblInvoice.InvDate <= #" & eDate & "# AND tblCustomer.CustomerName='" & cboCustomer.Text & "' AND tblInvoice.ProjID=" & projectID & "AND Balance >0"
-
- or
-
-
strSql = "SELECT tblInvoice.ProjID, tblInvoice.CusID, tblInvoice.InvoiceNo, " _
-
& "tblInvoice.InvDate, tblInvoice.Total, tblInvoice.Paid, " _
-
& "tblInvoice.Balance, tblCustomer.CustomerName " _
-
& "FROM tblCustomer " _
-
& "RIGHT JOIN tblInvoice " _
-
& "ON tblCustomer.CusID = tblInvoice.CusID " _
-
& "WHERE tblInvoice.InvDate >= #" & sDate & "# " _
-
& "AND tblInvoice.InvDate <= #" & eDate & "# " _
-
& "AND tblCustomer.CustomerName='" & cboCustomer.Text & "' " _
-
& "AND tblInvoice.ProjID = " & projectID _
-
& " AND Balance > 0"
Oh, by the way. For your date range, you might want to consider using the BETWEEN operator. It makes for more easily readable code.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Aloof |
last post by:
Using Access 2000
Windows Server 2003
The following code worked fine until we moved hosting companies
StartDate = Request.Form("StartDateMonth") & "/" & Request.Form
("StartDateDay") & "/" & Request.Form("StartDateYear")
EndDate = Request.Form("EndDateMonth") & "/" & Request.Form
("EndDateDay") & "/" & Request.Form("EndDateYear")
|
by: Dalan |
last post by:
Please help - just take a quick look at the function code below. It
probably just needs some minor tweaking.
The function module is based on an intermediate query to provide a
group record total from a subform that is posted to a field on the
main form. "Everything seems to work fine" except that I consistently
receive an error everytime I attempt to create a new record on the
main form. However, there are no errors received when...
|
by: u473 |
last post by:
I am scratching my head with the required quotes and parentheses.
I started with an existing working Query with Running Total
by date.
Now I need to produce a running total by quarter day, so I converted my
dates in serial increments of 6 hours intervals.
I verified that these serial dates have the expected values.
I run into repeated syntax error in the adapted Query.
|
by: Brian Basquille |
last post by:
Hello all,
Bit of a change of pace now. As opposed to the typical questions regarding
my Air Hockey game, am also working on a Photo Album which uses an Access
Database to store information about photos. This information is held inside
the database (photoDB.mdb) in a table called 'photos' - information being
recorded in there are photo information (photoID, location, phDate,
category, caption).
I have inputted my own photos (not via...
|
by: Terry Olsen |
last post by:
I've got 2 different web pages, both updating the same SQL database. One is
for the Technician and one is for the Manager. The technician's update page
works fine but the Manager's update page doesn't. They are both the
same..as far as I can tell...
---------------------
This code works fine, updating the SQL database with the edited data.
Code for Tech Page
| | |
by: kevinjbowman |
last post by:
Update garment_details
SET garment_details.blankprice = '1.50'
Where garment_details.GarmentDetailID
in (Select
garment_details.GarmentDetailID
From garment_details
Inner Join colors ON garment_details.ColorID =
colors.ColorID
Where
colors.ColorType = '1' AND
|
by: ReubenPatterson |
last post by:
Hi All, I'm struggling to get a QBF query working. I have 9 fields and a date range in the form. When I put these fields into the Design View and use the criteria (except the date range which uses BETWEEN) :
"Forms!! OR Forms!! IS NULL"
I get "Query is Too Complex" error. I've tried stripping out the date range and applying it as a filter in a macro, but it doesn't understand the BETWEEN argument. How can I apply the date range to the...
|
by: paul814 |
last post by:
I am having a bit of a problem with some code....can someone give me
some suggestions?
Parse error: syntax error, unexpected '{' in C:\xampp\htdocs\production
\date2.php on line 28
line 28 is this line:
while($row = mysql_fetch_array(mysql_query($sql)) {
It is the first while..
|
by: petter |
last post by:
Hi!
I have two questions: one question that regards the COUNT-function, and one about how to display a month even if I don’t have any data for that month.
I have an Access database where I want to create a query to count all the internal units in my list (the “input”-table), between two dates. I want my list to be grouped by year and month. Here is the SQL-code, as called from VBA:
strSQL_INTU1 = "PARAMETERS DateTime, DateTime;" _
...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
