New to JET SQL

[Jeremiah31]

Yesterday, from reading some references I learned how to create a pass-though queries. I'm having problems converting
field values, for example

The orginial statement in Access query:

SELECT CStr([PO_HEAD_PO_NUMBER]) AS PO,
SSCPGM_MPPMPOHA.PO_HEAD_DEPARTMENT,
SSCPGM_MPPMPOHA.PO_HEAD_LADDER_MONTH,
SSCPGM_MPPMPOHA.PO_HEAD_LADDER_YEAR
FROM SSCPGM_MPPMPOHA;

I'm trying
SELECT SSCPGM.MPPMPOHA.CStr([PO_HEAD_PO_NUMBER]) AS PO,
SSCPGM.MPPMPOHA.PO_HEAD_LADDER_MONTH As LadderMonth,
SSCPGM.MPPMPOHA.PO_HEAD_LADDER_YEAR As LadderYear,
SSCPGM.MPPMPOHA.PO_HEAD_DEPARTMENT As Dept

FROM SSCPGM.MPPMPOHA

I'm not doing a good job because the query error's out when running. I have a lot of questions, but the main question is there a easier way to convert previous Access queries into JET SQL pass-though query? My mind is turning, but it's only producing butter.

 

[AlexCuse]

I think you want something like this?

SELECT cast(SSCPGM.MPPMPOHA.PO_HEAD_PO_NUMBER as Varchar) AS PO,
SSCPGM.MPPMPOHA.PO_HEAD_LADDER_MONTH As LadderMonth,
SSCPGM.MPPMPOHA.PO_HEAD_LADDER_YEAR As LadderYear,
SSCPGM.MPPMPOHA.PO_HEAD_DEPARTMENT As Dept

FROM SSCPGM.MPPMPOHA

The problem was in how you were trying to do string conversion.

A. Most larger RDBMS' that I am aware of do not use the same functions as access.
B. You need to reference your table INSIDE a function call (if CSTR would work , it would've been something like this:

CStr(SSCPGM.MPPMPOHA.[PO_HEAD_PO_NUMBER]) AS PO

But CAST should work for you. What are you trying to pass this query through to?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Jeremiah31]

Going to pass it into Crystal Reports using VB6 as the front door.

I was in a discussion with an I.T guru about linking data tables using ODBC connection. He told me that it puts a strain on the sever and computer. He mentioned that I should be using pass-though queries.

Now I'm trying to figure out the best way to covert all the access queries I have into through-put,, This is my first attempt at it. Feels like learning how to ride a bike again.

 

[AlexCuse]

If you are passing the query INTO access, I suppose it would be this:

SELECT CStr(SSCPGM.MPPMPOHA.[PO_HEAD_PO_NUMBER]) AS PO,
SSCPGM.MPPMPOHA.PO_HEAD_LADDER_MONTH As LadderMonth,
SSCPGM.MPPMPOHA.PO_HEAD_LADDER_YEAR As LadderYear,
SSCPGM.MPPMPOHA.PO_HEAD_DEPARTMENT As Dept

FROM SSCPGM.MPPMPOHA

Your problem really was just in using CStr after specifying DB and Table. THink of it like this. You are not using CStr on the whole table, you are using it on one column. You need to tell CStr where to find this column, so your table info should be INSIDE the parentheses.

THis is oversimplified of course, but hopefully it will help.

Good luck,

Alex


Ignorance of certain subjects is a great part of wisdom