SQL problem

[Sw0rdfish149]

Hi there,

I've inserted an SQL statement that will enable me to pull off data out of two seperate tables and show them on a datareport.

The tables are:
Stock Invoice
Stock Invoice Items

This is my statement:

manuid = dbcCompanyID.Text
sqlselect = "SELECT 'Stock Invoice Items'.*, 'Stock Invoice'.* FROM 'Stock Invoice Items', 'Stock Invoice' WHERE 'Stock Invoice Items'.'Stock Invoice ID' = 'Stock Invoice'.'Stock Invoice ID'" ' AND ('Stock Invoice Items'.Status = 'Uncomplete') AND ('Stock Invoice Items'.Stockists ID = '" & manuid & "')"
DataEnvironment.rsSQLReorder.Close
DataEnvironment.rsSQLReorder.Source = sqlselect
DataEnvironment.rsSQLReorder.Open

DataReport1.Show

I think I'm missing something as I get the error message:

Syntax error in query.Incomplete query clause.

Anyone got any idea what Im missing?

Sw0rdfish149

 

[vb5prgrmr]

[tt]
sqlselect = "SELECT 'Stock Invoice Items'.*, 'Stock Invoice'.* FROM 'Stock Invoice Items', 'Stock Invoice' WHERE 'Stock Invoice Items'.'Stock Invoice ID' = 'Stock Invoice'.'Stock Invoice ID'" ' AND ('Stock Invoice Items'.Status = 'Uncomplete') AND ('Stock Invoice Items'.Stockists ID = '" & manuid & "')"
[/tt]

Good Luck

 

[LNBruno]

Looks like you're trying to select everything from both tables, but you only have criteria for 'Stock Invoice Items.' I would expect some kind of join criteria for the two tables; something like this:

SELECT 
'Stock Invoice Items'.*, 
'Stock Invoice'.* 
FROM 'Stock Invoice Items'

LEFT JOIN  'Stock Invoice' on 'Stock Invoice'.InvoiceNumber = 'Stock Invoice Items'.InvoiceNumber

WHERE 'Stock Invoice Items'.'Stock Invoice ID' = 'Stock Invoice'.'Stock Invoice ID'" ' 
AND ('Stock Invoice Items'.Status = 'Uncomplete') 
AND ('Stock Invoice Items'.Stockists ID = '" & manuid & "')"

< M!ke >

 

[MarkSweetland]

Surround the table and column names with brackets and string arguments with the single quotes:

sqlselect = &quot;SELECT [Stock Invoice Items].*, [Stock Invoice].* FROM [Stock Invoice Items], [Stock Invoice] WHERE [Stock Invoice Items].[Stock Invoice ID] = [Stock Invoice].[Invoice ID] AND ([Stock Invoice Items].Status = 'Uncomplete') AND ([Stock Invoice Items].Stockists ID = '&quot; & manuid & &quot;')&quot;


Mark

&quot;You guys pair up in groups of three, then line up in a circle.&quot;
- Bill Peterson, a Florida State football coach

 

[Sw0rdfish149]

Hey Mike,

Urs is the only one that worked after I tampered around with it. I kept getting syntax errors so had to amalgamate everything into one line.

This is what I ended up with:

sqlselect = &quot;SELECT 'Stock Invoice Items'.*,'Stock Invoice'.*From 'Stock Invoice Items' Left Join 'Stock Invoice' on 'Stock Invoice'.InvoiceNumber = 'Stock Invoice Items'.InvoiceNumber WHERE 'Stock Invoice Items'.'Stock Invoice ID' = 'Stock Invoice'.'Stock Invoice ID'&quot; And &quot;('Stock Invoice Items'.Status = 'Uncomplete') AND ('Stock Invoice Items'.Stockists ID = '&quot; & manuid & &quot;')&quot;

It works to an extent, I now get Type Mismatches, which is probably because the field types are different.

Thanx for you help

Sw0rdfish149

 

[Sw0rdfish149]

Okay...

after changing my code to

sqlselect = &quot;SELECT 'Stock Invoice Items'.*,'Stock Invoice'.* From 'Stock Invoice Items' Left Join 'Stock Invoice' on 'Stock Invoice'.'Stock Invoice ID' = 'Stock Invoice Items'.'Stock Invoice ID' WHERE 'Stock Invoice Items'.'Stock Invoice ID' = 'Stock Invoice'.'Stock Invoice ID'&quot; And &quot;('Stock Invoice Items'.Status = 'Uncomplete') AND ('Stock Invoice Items'.'Stockists ID' = '&quot; & manuid & &quot;')&quot;

and modifying my field types to texts I still get a type Mismatch error.

Could this be because the report textboxes aren't linked to the fields that are selected in the SQL statement?

I don't know enough about SQL but does the table and field names need to be the same names as the ones in the dataenvironment or the names in the database? And whats the difference between the ' and &quot; marks?

Cheers guys

 

[Schroeder]

The first part of your WHERE clause implies that you'd rather use an INNER JOIN between your two tables. If you only want records where the two &quot;Stock Invoice ID&quot; fields are equal, just use an INNER JOIN, keep your &quot;on 'Stock Invoice'.'Stock Invoice ID' = 'Stock Invoice Items'.'Stock Invoice ID'&quot; and remove the first part of the WHERE clause (since it will be redundant).
As for the Type Mismatch, Is 'Stock Invoice Items'.Status a Text field? is 'Stock Invoice Items'.'Stockists ID' also a text field? If they're not, take the single quotes out (i.e. ... = &quot; & manuid & &quot&quot;
Also, in
'Stock Invoice'.'Stock Invoice ID'&quot; And &quot;('Stock Invoice Items'.Status
why is And surrounded by double quotes?

 

[Glasgow]

I think you may need the square brackets that Mark suggested throughout but keep the single quotes for

= 'Uncomplete'

and

= '&quot; & manuid & &quot;')&quot;

 

[Schroeder]

I concur regarding the single quotes vs square brackets. I'd never tried single quotes for that purpose before.. just tried it.. didn't work.

 

[Sw0rdfish149]

Hey everyone

Thanx for help. I now know what I've been doing wrong, I don't have a field in the database for Manuid Duh!

Anyhow after doing some major changes I've decided to set up from scratch something else between two tables:

Items table which has
Item ID
Stockists ID
Item Name
etc

Stock Invoice Items table
Stock Invoice ID
Item ID
Quantity
Price
Status

And their is a join created in the SQL builder between the two Item ID's.

What I want to do is select all the records in the stock Invoice Items table that has the

Status= Uncomplete
Stock Invoice ID = a variable

then select the Item Name from the Items table that matches the Item ID in the selected records in the Stock Invoice Items table. Got it? Then display the records in a datareport.

The problem is for some reason in the dataenvironment the SQL isn't showing up as a recordset? Anyone know how to fix this? Cos it was working before.

Also can someone check my code? I think its right but i'm not sure about how to select only the Item Names from the Items table that have the Item ID that matches with the ones in the Stock Invoice Items table that have Uncomplete and the Stock Invoice ID.


sqlselect = &quot;SELECT [Stock Invoice Items].[Item ID], [Stock Invoice Items].[Price],[Stock Invoice Items].[Quantity], [Items].[Item Name] FROM [Items], [Stock Invoice Items] WHERE (([Stock Invoice Items].[Stock Invoice ID]='StockInvoiceID')and ([Stock Invoice Items].[Status]='Uncomplete') and ([Items].[Item ID] = [Stock Invoice Items].[Item ID]))&quot;



I know its a lot but hopefully someone out their can help.

Cheers