sage line50 database access

[Rshah]

Anybody know syntax of sage line 50 query?

how can I write sage line50 database conidtion sql query?

when i write this syntax, get error msg of column not found.
'SELECT c.stock_cat_name, a.stock_code, c.description, a.date,
IIF(MID(a.type,2,1)="I",SUM(a.quantity),0) AS iqty,
IIF(MID(a.type,2,1)="O",SUM(a.quantity),0) AS oqty
FROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_code
WHERE a.date>=[from_date] And a.date<=[to_date]
GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,
MID(a.type,2,1)
ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1)'

rashesh

[Larry Linson]

What's a "sage line50 database"? What does it have to do with Microsoft
Access?

Larry Linson

"Rshah" <rs********@gmail.comwrote in message
news:11**********************@q2g2000cwa.googlegro ups.com...

Anybody know syntax of sage line 50 query?

how can I write sage line50 database conidtion sql query?

when i write this syntax, get error msg of column not found.
'SELECT c.stock_cat_name, a.stock_code, c.description, a.date,
IIF(MID(a.type,2,1)="I",SUM(a.quantity),0) AS iqty,
IIF(MID(a.type,2,1)="O",SUM(a.quantity),0) AS oqty
FROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_code
WHERE a.date>=[from_date] And a.date<=[to_date]
GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,
MID(a.type,2,1)
ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1)'

rashesh

[Baz]

Sage Line50 is a technically garbage but very popular accounting package for
small businesses, at least it is here in the UK. It's a few years since I
had anything to do with this heap of junk, but it used to come with
unbelievably slow read-only ODBC drivers, so it is possible that the OP is
querying it from Access, although it would have helped if he'd made this
clear.

As to which column he has a problem with, given the lack of information he
has provided, that is of course anyone's guess.
"Larry Linson" <bo*****@localhost.notwrote in message
news:D8jsh.420$uL6.165@trnddc03...

What's a "sage line50 database"? What does it have to do with Microsoft
Access?

Larry Linson

"Rshah" <rs********@gmail.comwrote in message
news:11**********************@q2g2000cwa.googlegro ups.com...

Anybody know syntax of sage line 50 query?

how can I write sage line50 database conidtion sql query?

when i write this syntax, get error msg of column not found.
'SELECT c.stock_cat_name, a.stock_code, c.description, a.date,
IIF(MID(a.type,2,1)="I",SUM(a.quantity),0) AS iqty,
IIF(MID(a.type,2,1)="O",SUM(a.quantity),0) AS oqty
FROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_code
WHERE a.date>=[from_date] And a.date<=[to_date]
GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,
MID(a.type,2,1)
ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1)'

rashesh

[salad]

Rshah wrote:

Anybody know syntax of sage line 50 query?

how can I write sage line50 database conidtion sql query?

when i write this syntax, get error msg of column not found.
'SELECT c.stock_cat_name, a.stock_code, c.description, a.date,
IIF(MID(a.type,2,1)="I",SUM(a.quantity),0) AS iqty,
IIF(MID(a.type,2,1)="O",SUM(a.quantity),0) AS oqty
FROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_code
WHERE a.date>=[from_date] And a.date<=[to_date]
GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,
MID(a.type,2,1)
ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1)'

rashesh

Make a copy of this query. Using the copy...

Delete the Where line. Run it? Any error? If no error, then
wipe out a field at a time. Sooner or later the problem field will be
removed and the query will run.