Data Shaping Problem 
Author Message
 Data Shaping Problem

Hello all..

I have the following:

  sConn = "Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & Settings.DatabaseName

  sSQL = "SHAPE {"
  sSQL = sSQL & "SELECT "
  sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
  sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
  sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count, "
  sSQL = sSQL & "dep1_amount, attendant_id.attendant "
  sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate = site.site)
"
  sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
attendant_id.id_code "
  sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
  sSQL = sSQL & "} AS depositdata "

  sSQL = sSQL & "APPEND ({"
  sSQL = sSQL & "SELECT "
  sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site, "
  sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
attendant_id.attendant "
  sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON trans.attendant =
attendant_id.id_code "
  sSQL = sSQL & "WHERE void = -1} "
  sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
  sSQL = sSQL & ") AS transdata"

The database is Access97. All date fields are defined as type Date/Time.
transdata should contain 2 records.

When I execute each query seperately in Access, the Format$() function
returns '#Error'. When this statement is executed in my app, the first
Format$() succeeds, but the one in the SHAPE APPEND clause fails. I am also
executing the child query in another portion of my app as a simple select
and the Format$() function works properly.

Any ideas anyone?

Thanks in advance...

--
Michael White
Programmer/Analyst
Marion County, OR



Tue, 10 May 2005 04:32:26 GMT  
 Data Shaping Problem
Michael,

Could it be NULL value? Not sure but Format$ could fail in case if value is
NULL.

--
Val Mazur
Microsoft MVP


Quote:
> Hello all..

> I have the following:

>   sConn = "Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=" & Settings.DatabaseName

>   sSQL = "SHAPE {"
>   sSQL = sSQL & "SELECT "
>   sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
>   sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
>   sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count, "
>   sSQL = sSQL & "dep1_amount, attendant_id.attendant "
>   sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate =
site.site)
> "
>   sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
> attendant_id.id_code "
>   sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
>   sSQL = sSQL & "} AS depositdata "

>   sSQL = sSQL & "APPEND ({"
>   sSQL = sSQL & "SELECT "
>   sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site, "
>   sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
> attendant_id.attendant "
>   sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON trans.attendant =
> attendant_id.id_code "
>   sSQL = sSQL & "WHERE void = -1} "
>   sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
>   sSQL = sSQL & ") AS transdata"

> The database is Access97. All date fields are defined as type Date/Time.
> transdata should contain 2 records.

> When I execute each query seperately in Access, the Format$() function
> returns '#Error'. When this statement is executed in my app, the first
> Format$() succeeds, but the one in the SHAPE APPEND clause fails. I am
also
> executing the child query in another portion of my app as a simple select
> and the Format$() function works properly.

> Any ideas anyone?

> Thanks in advance...

> --
> Michael White
> Programmer/Analyst
> Marion County, OR




Tue, 10 May 2005 04:36:26 GMT  
 Data Shaping Problem
No, there are valid dates in the field. That is trapped prior to an insert.


Quote:
> Michael,

> Could it be NULL value? Not sure but Format$ could fail in case if value
is
> NULL.

> --
> Val Mazur
> Microsoft MVP



> > Hello all..

> > I have the following:

> >   sConn = "Provider=MSDataShape;Data

Provider=Microsoft.Jet.OLEDB.4.0;Data

- Show quoted text -

Quote:
> > Source=" & Settings.DatabaseName

> >   sSQL = "SHAPE {"
> >   sSQL = sSQL & "SELECT "
> >   sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
> >   sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
> >   sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count, "
> >   sSQL = sSQL & "dep1_amount, attendant_id.attendant "
> >   sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate =
> site.site)
> > "
> >   sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
> > attendant_id.id_code "
> >   sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
> >   sSQL = sSQL & "} AS depositdata "

> >   sSQL = sSQL & "APPEND ({"
> >   sSQL = sSQL & "SELECT "
> >   sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site, "
> >   sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
> > attendant_id.attendant "
> >   sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON trans.attendant =
> > attendant_id.id_code "
> >   sSQL = sSQL & "WHERE void = -1} "
> >   sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
> >   sSQL = sSQL & ") AS transdata"

> > The database is Access97. All date fields are defined as type Date/Time.
> > transdata should contain 2 records.

> > When I execute each query seperately in Access, the Format$() function
> > returns '#Error'. When this statement is executed in my app, the first
> > Format$() succeeds, but the one in the SHAPE APPEND clause fails. I am
> also
> > executing the child query in another portion of my app as a simple
select
> > and the Format$() function works properly.

> > Any ideas anyone?

> > Thanks in advance...

> > --
> > Michael White
> > Programmer/Analyst
> > Marion County, OR




Tue, 10 May 2005 04:57:57 GMT  
 Data Shaping Problem
Michael,

Is it working without Format$ function? Did you try?

--
Val Mazur
Microsoft MVP


Quote:
> No, there are valid dates in the field. That is trapped prior to an
insert.



> > Michael,

> > Could it be NULL value? Not sure but Format$ could fail in case if value
> is
> > NULL.

> > --
> > Val Mazur
> > Microsoft MVP



> > > Hello all..

> > > I have the following:

> > >   sConn = "Provider=MSDataShape;Data
> Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > Source=" & Settings.DatabaseName

> > >   sSQL = "SHAPE {"
> > >   sSQL = sSQL & "SELECT "
> > >   sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
> > >   sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
> > >   sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count, "
> > >   sSQL = sSQL & "dep1_amount, attendant_id.attendant "
> > >   sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate =
> > site.site)
> > > "
> > >   sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
> > > attendant_id.id_code "
> > >   sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
> > >   sSQL = sSQL & "} AS depositdata "

> > >   sSQL = sSQL & "APPEND ({"
> > >   sSQL = sSQL & "SELECT "
> > >   sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site, "
> > >   sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
> > > attendant_id.attendant "
> > >   sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON trans.attendant
=
> > > attendant_id.id_code "
> > >   sSQL = sSQL & "WHERE void = -1} "
> > >   sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
> > >   sSQL = sSQL & ") AS transdata"

> > > The database is Access97. All date fields are defined as type
Date/Time.
> > > transdata should contain 2 records.

> > > When I execute each query seperately in Access, the Format$() function
> > > returns '#Error'. When this statement is executed in my app, the first
> > > Format$() succeeds, but the one in the SHAPE APPEND clause fails. I am
> > also
> > > executing the child query in another portion of my app as a simple
> select
> > > and the Format$() function works properly.

> > > Any ideas anyone?

> > > Thanks in advance...

> > > --
> > > Michael White
> > > Programmer/Analyst
> > > Marion County, OR




Tue, 10 May 2005 09:25:42 GMT  
 Data Shaping Problem
Yes, if I just retrieve the value as is without format$ it works.

I just tried the query in Access2K and I'm getting "undefined function
'format$' in expression.

Michael


Quote:
> Michael,

> Is it working without Format$ function? Did you try?

> --
> Val Mazur
> Microsoft MVP



> > No, there are valid dates in the field. That is trapped prior to an
> insert.



> > > Michael,

> > > Could it be NULL value? Not sure but Format$ could fail in case if
value
> > is
> > > NULL.

> > > --
> > > Val Mazur
> > > Microsoft MVP



> > > > Hello all..

> > > > I have the following:

> > > >   sConn = "Provider=MSDataShape;Data
> > Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > > Source=" & Settings.DatabaseName

> > > >   sSQL = "SHAPE {"
> > > >   sSQL = sSQL & "SELECT "
> > > >   sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
> > > >   sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
> > > >   sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count, "
> > > >   sSQL = sSQL & "dep1_amount, attendant_id.attendant "
> > > >   sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate =
> > > site.site)
> > > > "
> > > >   sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
> > > > attendant_id.id_code "
> > > >   sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
> > > >   sSQL = sSQL & "} AS depositdata "

> > > >   sSQL = sSQL & "APPEND ({"
> > > >   sSQL = sSQL & "SELECT "
> > > >   sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site, "
> > > >   sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
> > > > attendant_id.attendant "
> > > >   sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON
trans.attendant
> =
> > > > attendant_id.id_code "
> > > >   sSQL = sSQL & "WHERE void = -1} "
> > > >   sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
> > > >   sSQL = sSQL & ") AS transdata"

> > > > The database is Access97. All date fields are defined as type
> Date/Time.
> > > > transdata should contain 2 records.

> > > > When I execute each query seperately in Access, the Format$()
function
> > > > returns '#Error'. When this statement is executed in my app, the
first
> > > > Format$() succeeds, but the one in the SHAPE APPEND clause fails. I
am
> > > also
> > > > executing the child query in another portion of my app as a simple
> > select
> > > > and the Format$() function works properly.

> > > > Any ideas anyone?

> > > > Thanks in advance...

> > > > --
> > > > Michael White
> > > > Programmer/Analyst
> > > > Marion County, OR




Wed, 11 May 2005 00:44:34 GMT  
 Data Shaping Problem
Michael,

Just check which values you have in that column in child recordset. Maybe
there is something which cannot be converted into specified format,
otherwise it could be a bug

--
Val Mazur
Microsoft MVP


Quote:
> Yes, if I just retrieve the value as is without format$ it works.

> I just tried the query in Access2K and I'm getting "undefined function
> 'format$' in expression.

> Michael



> > Michael,

> > Is it working without Format$ function? Did you try?

> > --
> > Val Mazur
> > Microsoft MVP



> > > No, there are valid dates in the field. That is trapped prior to an
> > insert.



> > > > Michael,

> > > > Could it be NULL value? Not sure but Format$ could fail in case if
> value
> > > is
> > > > NULL.

> > > > --
> > > > Val Mazur
> > > > Microsoft MVP



> > > > > Hello all..

> > > > > I have the following:

> > > > >   sConn = "Provider=MSDataShape;Data
> > > Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > > > Source=" & Settings.DatabaseName

> > > > >   sSQL = "SHAPE {"
> > > > >   sSQL = sSQL & "SELECT "
> > > > >   sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
> > > > >   sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
> > > > >   sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count,
"
> > > > >   sSQL = sSQL & "dep1_amount, attendant_id.attendant "
> > > > >   sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate =
> > > > site.site)
> > > > > "
> > > > >   sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
> > > > > attendant_id.id_code "
> > > > >   sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
> > > > >   sSQL = sSQL & "} AS depositdata "

> > > > >   sSQL = sSQL & "APPEND ({"
> > > > >   sSQL = sSQL & "SELECT "
> > > > >   sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site,
"
> > > > >   sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
> > > > > attendant_id.attendant "
> > > > >   sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON
> trans.attendant
> > =
> > > > > attendant_id.id_code "
> > > > >   sSQL = sSQL & "WHERE void = -1} "
> > > > >   sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
> > > > >   sSQL = sSQL & ") AS transdata"

> > > > > The database is Access97. All date fields are defined as type
> > Date/Time.
> > > > > transdata should contain 2 records.

> > > > > When I execute each query seperately in Access, the Format$()
> function
> > > > > returns '#Error'. When this statement is executed in my app, the
> first
> > > > > Format$() succeeds, but the one in the SHAPE APPEND clause fails.
I
> am
> > > > also
> > > > > executing the child query in another portion of my app as a simple
> > > select
> > > > > and the Format$() function works properly.

> > > > > Any ideas anyone?

> > > > > Thanks in advance...

> > > > > --
> > > > > Michael White
> > > > > Programmer/Analyst
> > > > > Marion County, OR




Wed, 11 May 2005 00:52:48 GMT  
 Data Shaping Problem


Fri, 19 Jun 1992 00:00:00 GMT  
 Data Shaping Problem
Hi,
try to remove the $.

Moreover, what did you mean with "...but the one in the SHAPE APPEND clause
fails."?

Bye

Giuseppe



Mon, 23 May 2005 06:13:11 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. ABOUT DEFINE DATA SHAPE PROBLEM

2. VB6 Question re: Data Shaping relations and Shape query

3. VB6 Question re: Data Shaping relations and Shape query

4. VB6 Question re: Data Shaping relations and Shape query

5. Problem with data shaping APPEND command against SQL Server 2000

6. Shapes shapes shapes

7. Using MS Data Shaping with OLE DB - data from more than one table in mpp file

8. how to put data in to data shape child object

9. Using the Shape command to multiple grouping of data for data reports

10. Data Environment and Data shapes

11. How to Put Data to data Shape?

12. Using the Shape command to multiple grouping of data for data reports

 

 
Powered by phpBB® Forum Software