I need to pull some reports from an older ERP system in the US via ODBC
I have the report in play, but want to limit the criteria to the items
shipped today
I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
TODAY_FIELD
matches that. (ie 8/30/2006 12:00:00 AM)
So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
TODAY()) which will not generate and ORA-00904 error.
Can anyone please help?getdate() is the system date to use. Or am I understanding what you want
wrong?
"VB" <vodkablokey@.gmail.com> wrote in message
news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
>I need to pull some reports from an older ERP system in the US via ODBC
> I have the report in play, but want to limit the criteria to the items
> shipped today
> I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> TODAY_FIELD
> matches that. (ie 8/30/2006 12:00:00 AM)
> So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> TODAY()) which will not generate and ORA-00904 error.
> Can anyone please help?
>|||Ben Watts wrote:
> getdate() is the system date to use. Or am I understanding what you want
> wrong?
> "VB" <vodkablokey@.gmail.com> wrote in message
> news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> >I need to pull some reports from an older ERP system in the US via ODBC
> >
> > I have the report in play, but want to limit the criteria to the items
> > shipped today
> >
> > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> >
> > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > TODAY_FIELD
> > matches that. (ie 8/30/2006 12:00:00 AM)
> >
> > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > TODAY()) which will not generate and ORA-00904 error.
> >
> > Can anyone please help?
> >
I am looking to report all shipping transactions (TRANSACTION_CODE ='OESHIP') which have happened since Midnight.
I know = TODAY() will give you 12:00am today, how do I pass this as a
criteria to a ODBC database call?
I had tried =TODAY() but this is rejected, it seems to suggest I need
to use TO_DATE, but I don't know how to do this either.
Thanks for getting back though :)|||Try:
WHERE REAL_SHIP_DATE > CAST( CONVERT( VARCHAR(8), GETDATE(), 112) AS DATETIME)
This takes the the curent system time, as suggested by Ben, chops of the
time and returns a DateTime value at the very start of the day, similar to
the vb Today() function.
HTH,
Magendo_man
"VB" wrote:
> Ben Watts wrote:
> > getdate() is the system date to use. Or am I understanding what you want
> > wrong?
> > "VB" <vodkablokey@.gmail.com> wrote in message
> > news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> > >I need to pull some reports from an older ERP system in the US via ODBC
> > >
> > > I have the report in play, but want to limit the criteria to the items
> > > shipped today
> > >
> > > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> > >
> > > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > > TODAY_FIELD
> > > matches that. (ie 8/30/2006 12:00:00 AM)
> > >
> > > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > > TODAY()) which will not generate and ORA-00904 error.
> > >
> > > Can anyone please help?
> > >
> I am looking to report all shipping transactions (TRANSACTION_CODE => 'OESHIP') which have happened since Midnight.
> I know = TODAY() will give you 12:00am today, how do I pass this as a
> criteria to a ODBC database call?
> I had tried =TODAY() but this is rejected, it seems to suggest I need
> to use TO_DATE, but I don't know how to do this either.
> Thanks for getting back though :)
>|||magendo_man wrote:
> Try:
> WHERE REAL_SHIP_DATE > CAST( CONVERT( VARCHAR(8), GETDATE(), 112) AS DATETIME)
> This takes the the curent system time, as suggested by Ben, chops of the
> time and returns a DateTime value at the very start of the day, similar to
> the vb Today() function.
> HTH,
> Magendo_man
> "VB" wrote:
> >
> > Ben Watts wrote:
> > > getdate() is the system date to use. Or am I understanding what you want
> > > wrong?
> > > "VB" <vodkablokey@.gmail.com> wrote in message
> > > news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> > > >I need to pull some reports from an older ERP system in the US via ODBC
> > > >
> > > > I have the report in play, but want to limit the criteria to the items
> > > > shipped today
> > > >
> > > > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> > > >
> > > > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > > > TODAY_FIELD
> > > > matches that. (ie 8/30/2006 12:00:00 AM)
> > > >
> > > > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > > > TODAY()) which will not generate and ORA-00904 error.
> > > >
> > > > Can anyone please help?
> > > >
> >
> > I am looking to report all shipping transactions (TRANSACTION_CODE => > 'OESHIP') which have happened since Midnight.
> >
> > I know = TODAY() will give you 12:00am today, how do I pass this as a
> > criteria to a ODBC database call?
> >
> > I had tried =TODAY() but this is rejected, it seems to suggest I need
> > to use TO_DATE, but I don't know how to do this either.
> >
> > Thanks for getting back though :)
> >
> >
THanks, that would do the trick.
When I try it though I get a new error, INVALID or MISSING EXPRESSION
Any ideas?|||VB wrote:
> magendo_man wrote:
> > Try:
> >
> > WHERE REAL_SHIP_DATE > CAST( CONVERT( VARCHAR(8), GETDATE(), 112) AS DATETIME)
> >
> > This takes the the curent system time, as suggested by Ben, chops of the
> > time and returns a DateTime value at the very start of the day, similar to
> > the vb Today() function.
> >
> > HTH,
> > Magendo_man
> >
> > "VB" wrote:
> >
> > >
> > > Ben Watts wrote:
> > > > getdate() is the system date to use. Or am I understanding what you want
> > > > wrong?
> > > > "VB" <vodkablokey@.gmail.com> wrote in message
> > > > news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> > > > >I need to pull some reports from an older ERP system in the US via ODBC
> > > > >
> > > > > I have the report in play, but want to limit the criteria to the items
> > > > > shipped today
> > > > >
> > > > > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> > > > >
> > > > > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > > > > TODAY_FIELD
> > > > > matches that. (ie 8/30/2006 12:00:00 AM)
> > > > >
> > > > > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > > > > TODAY()) which will not generate and ORA-00904 error.
> > > > >
> > > > > Can anyone please help?
> > > > >
> > >
> > > I am looking to report all shipping transactions (TRANSACTION_CODE => > > 'OESHIP') which have happened since Midnight.
> > >
> > > I know = TODAY() will give you 12:00am today, how do I pass this as a
> > > criteria to a ODBC database call?
> > >
> > > I had tried =TODAY() but this is rejected, it seems to suggest I need
> > > to use TO_DATE, but I don't know how to do this either.
> > >
> > > Thanks for getting back though :)
> > >
> > >
> THanks, that would do the trick.
> When I try it though I get a new error, INVALID or MISSING EXPRESSION
> Any ideas?
SOrry Folks, I realised (eventually) instead of trying to re-invent the
wheel I should just create a view in the database with the sysdate -1
call and just report the view.
Showing posts with label criteria. Show all posts
Showing posts with label criteria. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
Newbie Query Problem
I want to use the same field in one table and return multiple columns for
different criteria. In other words...
First column
SUM(Sales.NetSales) as 'Total Sales').
Then I want a second column as
SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
Is this reasonable? I am sure that it is a simple thing that I am just
ignorant of.
Thanks.
ChuckChuck,
Try:
--rows
SELECT CategoryID, SUM(Sales) AS 'Total Sales'
FROM NetSales
GROUP BY CategoryID
--or
--columns
SELECT 'Category 1' = (SELECT SUM(Sales) AS 'Total Sales'FROM NetSales WHERE
CategoryID = 1),
'Category 2' = (SELECT SUM(Sales) AS 'Total Sales'FROM NetSales WHERE
CategoryID = 2)
HTH
Jerry
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:94FD5CDE-C7BE-43CF-857C-2A847675A1EF@.microsoft.com...
>I want to use the same field in one table and return multiple columns for
> different criteria. In other words...
> First column
> SUM(Sales.NetSales) as 'Total Sales').
> Then I want a second column as
> SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
> Is this reasonable? I am sure that it is a simple thing that I am just
> ignorant of.
> Thanks.
> Chuck|||SELECT
(SELECT SUM(NetSales) FROM Sales) as TotalSales,
(SELECT SUM(NetSales) FROM Sales WHERE categoryid = '02') as Category02
Chuck wrote:
> I want to use the same field in one table and return multiple columns for
> different criteria. In other words...
> First column
> SUM(Sales.NetSales) as 'Total Sales').
> Then I want a second column as
> SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
> Is this reasonable? I am sure that it is a simple thing that I am just
> ignorant of.
> Thanks.
> Chuck|||On Mon, 19 Sep 2005 11:58:06 -0700, Chuck wrote:
>I want to use the same field in one table and return multiple columns for
>different criteria. In other words...
>First column
>SUM(Sales.NetSales) as 'Total Sales').
>Then I want a second column as
>SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
>Is this reasonable? I am sure that it is a simple thing that I am just
>ignorant of.
>Thanks.
>Chuck
Hi Chuck,
Here's a way that requires only one pass over the table:
SELECT SUM(NetSales) AS 'Total Sales',
SUM(CASE WHEN categoryid = '02' THEN NetSales ELSE NULL END) AS
'Category 02'
FROM YourTable
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
different criteria. In other words...
First column
SUM(Sales.NetSales) as 'Total Sales').
Then I want a second column as
SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
Is this reasonable? I am sure that it is a simple thing that I am just
ignorant of.
Thanks.
ChuckChuck,
Try:
--rows
SELECT CategoryID, SUM(Sales) AS 'Total Sales'
FROM NetSales
GROUP BY CategoryID
--or
--columns
SELECT 'Category 1' = (SELECT SUM(Sales) AS 'Total Sales'FROM NetSales WHERE
CategoryID = 1),
'Category 2' = (SELECT SUM(Sales) AS 'Total Sales'FROM NetSales WHERE
CategoryID = 2)
HTH
Jerry
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:94FD5CDE-C7BE-43CF-857C-2A847675A1EF@.microsoft.com...
>I want to use the same field in one table and return multiple columns for
> different criteria. In other words...
> First column
> SUM(Sales.NetSales) as 'Total Sales').
> Then I want a second column as
> SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
> Is this reasonable? I am sure that it is a simple thing that I am just
> ignorant of.
> Thanks.
> Chuck|||SELECT
(SELECT SUM(NetSales) FROM Sales) as TotalSales,
(SELECT SUM(NetSales) FROM Sales WHERE categoryid = '02') as Category02
Chuck wrote:
> I want to use the same field in one table and return multiple columns for
> different criteria. In other words...
> First column
> SUM(Sales.NetSales) as 'Total Sales').
> Then I want a second column as
> SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
> Is this reasonable? I am sure that it is a simple thing that I am just
> ignorant of.
> Thanks.
> Chuck|||On Mon, 19 Sep 2005 11:58:06 -0700, Chuck wrote:
>I want to use the same field in one table and return multiple columns for
>different criteria. In other words...
>First column
>SUM(Sales.NetSales) as 'Total Sales').
>Then I want a second column as
>SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
>Is this reasonable? I am sure that it is a simple thing that I am just
>ignorant of.
>Thanks.
>Chuck
Hi Chuck,
Here's a way that requires only one pass over the table:
SELECT SUM(NetSales) AS 'Total Sales',
SUM(CASE WHEN categoryid = '02' THEN NetSales ELSE NULL END) AS
'Category 02'
FROM YourTable
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Monday, March 19, 2012
newbie need help
I need to know how to write a sql statement for VB to Microsoft Access for the following criteria.
TblEmployees only Field to be concerned with is EmployeeID -Its PK
TblEmpAttendane has these fields:
EntryID - Pk
EmployeeID - Fk
Date- A text form date validated through vb to avoid hassle
Value-A single digit value that can be alpha OR numeric
To get the Date range for the current month I use variables to store the First and last day of the month(in a CUSTOM calendar control).
I have been trying to use this Sql statement(that wont work):
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date BETWEEN '" & FirstDay & "' AND '" & LastDay & "'"
I need to:
1.)using the current EmployeeID (from TblEmployees)
2.) find the same EmployeeID in TblEmpAttendance
3.)Get the date range for the current month and year(valid days currnt mo.)
-these date are supplied by the variables "FirstDay" AND "LastDay" as seen in above SqlString
4.) Find the Values associated with the dates and EmployeeIDAre you using Jet or MS-SQL (aka MSDE) as your database engine? They have differences in how they handle dates, and the example you gave would not fly very well in Jet.
-PatP|||Im using jet to connect(through code) to my db.
Can u assist me in how to improve my table format..or whatever I would have to do in order to be able to do this correctly?
All that I did was set up a string format in vb to make sure that the correct amount of characters and the proper syntax was used:
eg... 00/00/0000
In my code I nvr explicity refer to them as a date. Only the user would think that it was a date!
Thanks-Greg S|||I'd try using:SqlString = "SELECT * FROM TblEmpAttendance WHERE #" _
& FirstDay & "# <= Date AND Date <= #" & LastDay & "#"This is only a swag, but I think that it should work.
-PatP|||Thanks I will try that and get back to you. Much appreciated!|||I have a lot more to do than I thought. I did do a date conversion function from within access so it is only displayed as dd/mm/yy format.
Then from vb I used:
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date Bewteen '" & FirstDay & "' AND '" & LastDay & "'"
It seemd to work fine as I can refer to a field but for some reason when I refer to the recordcount property,I always get -1...Seems weird how I can read the info but not get the recordcount!!|||Check the BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp). If you use ADOpenStatic you won't have -1 as a recordcount.
-PatP|||I dont know what you mean by "BOL" but as I am using ADO as a connection it does support bookmarks(not that I believe I need on with the move.bof statement) If this is not what you were getting at could you be more specific?
OK WAIT..I added adOpenStatic to my statement!
now it seems to be working
But I had to put in in the form of:
AdoRecordset.Open SqlString, AdoConnection, adOpenStatic
instead of AdoRecordset.Open (SqlString, AdoConnection), adOpenStatic
like the link you posted...Im still not sure why the perverbial correct way wouldnt work but my way worked anyhow..
THANKS MUCH!|||adOpenStatic, adOpenDynamic, adOpenKeyset, and adOpenForwardOnly are qualifications of the cursor that is being open either on the server or client side (depends on the CursorLocation property). The default (adOpenForwardOnly) will have -1 for RecordCount property.
TblEmployees only Field to be concerned with is EmployeeID -Its PK
TblEmpAttendane has these fields:
EntryID - Pk
EmployeeID - Fk
Date- A text form date validated through vb to avoid hassle
Value-A single digit value that can be alpha OR numeric
To get the Date range for the current month I use variables to store the First and last day of the month(in a CUSTOM calendar control).
I have been trying to use this Sql statement(that wont work):
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date BETWEEN '" & FirstDay & "' AND '" & LastDay & "'"
I need to:
1.)using the current EmployeeID (from TblEmployees)
2.) find the same EmployeeID in TblEmpAttendance
3.)Get the date range for the current month and year(valid days currnt mo.)
-these date are supplied by the variables "FirstDay" AND "LastDay" as seen in above SqlString
4.) Find the Values associated with the dates and EmployeeIDAre you using Jet or MS-SQL (aka MSDE) as your database engine? They have differences in how they handle dates, and the example you gave would not fly very well in Jet.
-PatP|||Im using jet to connect(through code) to my db.
Can u assist me in how to improve my table format..or whatever I would have to do in order to be able to do this correctly?
All that I did was set up a string format in vb to make sure that the correct amount of characters and the proper syntax was used:
eg... 00/00/0000
In my code I nvr explicity refer to them as a date. Only the user would think that it was a date!
Thanks-Greg S|||I'd try using:SqlString = "SELECT * FROM TblEmpAttendance WHERE #" _
& FirstDay & "# <= Date AND Date <= #" & LastDay & "#"This is only a swag, but I think that it should work.
-PatP|||Thanks I will try that and get back to you. Much appreciated!|||I have a lot more to do than I thought. I did do a date conversion function from within access so it is only displayed as dd/mm/yy format.
Then from vb I used:
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date Bewteen '" & FirstDay & "' AND '" & LastDay & "'"
It seemd to work fine as I can refer to a field but for some reason when I refer to the recordcount property,I always get -1...Seems weird how I can read the info but not get the recordcount!!|||Check the BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp). If you use ADOpenStatic you won't have -1 as a recordcount.
-PatP|||I dont know what you mean by "BOL" but as I am using ADO as a connection it does support bookmarks(not that I believe I need on with the move.bof statement) If this is not what you were getting at could you be more specific?
OK WAIT..I added adOpenStatic to my statement!
now it seems to be working
But I had to put in in the form of:
AdoRecordset.Open SqlString, AdoConnection, adOpenStatic
instead of AdoRecordset.Open (SqlString, AdoConnection), adOpenStatic
like the link you posted...Im still not sure why the perverbial correct way wouldnt work but my way worked anyhow..
THANKS MUCH!|||adOpenStatic, adOpenDynamic, adOpenKeyset, and adOpenForwardOnly are qualifications of the cursor that is being open either on the server or client side (depends on the CursorLocation property). The default (adOpenForwardOnly) will have -1 for RecordCount property.
Subscribe to:
Posts (Atom)