i am unable to trieve the date in ms-access

hi friends,

I am trying to create alert system in which i can able to retrieve the date while configure the OleDbDataAdapter1 itself the

problem occurs this is the code i tired

select * from table name (username=?) and (date=)

after entering this error occurs

You haven't mentioned about SQL Server version you are using, refer to this http://www.aspfree.com/c/a/Microsoft-Access/Configuring-a-Linked-Microsoft-Access-Server-on-SQL-2005-Server/ link in this case where it explains how you can achieve from SQL Server side.

IN SQL Server 2000 you can use DTS to get (import) the data from any other data provider and you can schedule the DTS package too.

|||

hi

i am not using MS SQL server but i am using ms access

i am unable to trieve the date in ms-access

hi friends,

I am trying to create alert system in which i can able to retrieve the date while configure the OleDbDataAdapter1 itself the

problem occurs this is the code i tired

select * from table name (username=?) and (date=)

after entering this error occurs

You haven't mentioned about SQL Server version you are using, refer to this http://www.aspfree.com/c/a/Microsoft-Access/Configuring-a-Linked-Microsoft-Access-Server-on-SQL-2005-Server/ link in this case where it explains how you can achieve from SQL Server side.

IN SQL Server 2000 you can use DTS to get (import) the data from any other data provider and you can schedule the DTS package too.

|||

hi

i am not using MS SQL server but i am using ms access

i am unable to trieve the date in ms-access

hi friends,

I am trying to create alert system in which i can able to retrieve the date while configure the OleDbDataAdapter1 itself the

problem occurs this is the code i tired

select * from table name (username=?) and (date=)

after entering this error occurs

You haven't mentioned about SQL Server version you are using, refer to this http://www.aspfree.com/c/a/Microsoft-Access/Configuring-a-Linked-Microsoft-Access-Server-on-SQL-2005-Server/ link in this case where it explains how you can achieve from SQL Server side.

IN SQL Server 2000 you can use DTS to get (import) the data from any other data provider and you can schedule the DTS package too.

|||

hi

i am not using MS SQL server but i am using ms access

I am trying to work out datediff for just mon-fri between datefiel

I am trying to find the number of working days between two date fields.
I have datediff(dd, first date, second date) as turnaround time,
But this includes saturdays and sundays in the output. I need to find the
number of days, only including monday to friday, between the two date fields.
It helps to have a Calendar table in your database:
CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
Populate it with as many years as you'll ever need:
INSERT INTO Calendar (caldate) VALUES ('20000101')
WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Set the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
You'll probably want to record any public holidays in the same way.
Now you can easily compute the number of working days between two dates:
SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @.first_date AND @.second_date
AND workingday = 'Y' ;
David Portas
SQL Server MVP
|||Hi Shaun,
Check if this helps...
SELECT DATEDIFF(dd, 'startdate', 'enddate')
- DATEDIFF(ww, 'startdate', 'enddate') *2
Thanks
Yogish

I am trying to work out datediff for just mon-fri between datefiel

I am trying to find the number of working days between two date fields.
I have datediff(dd, first date, second date) as turnaround time,
But this includes saturdays and sundays in the output. I need to find the
number of days, only including monday to friday, between the two date fields
.It helps to have a Calendar table in your database:
CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
Populate it with as many years as you'll ever need:
INSERT INTO Calendar (caldate) VALUES ('20000101')
WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate)
,
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Set the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
You'll probably want to record any public holidays in the same way.
Now you can easily compute the number of working days between two dates:
SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @.first_date AND @.second_date
AND workingday = 'Y' ;
David Portas
SQL Server MVP
--|||Hi Shaun,
Check if this helps...
SELECT DATEDIFF(dd, 'startdate', 'enddate')
- DATEDIFF(ww, 'startdate', 'enddate') *2
Thanks
Yogish

I am trying to work out datediff for just mon-fri between datefiel

I am trying to find the number of working days between two date fields.
I have datediff(dd, first date, second date) as turnaround time,
But this includes saturdays and sundays in the output. I need to find the
number of days, only including monday to friday, between the two date fields.It helps to have a Calendar table in your database:
CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
Populate it with as many years as you'll ever need:
INSERT INTO Calendar (caldate) VALUES ('20000101')
WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Set the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
You'll probably want to record any public holidays in the same way.
Now you can easily compute the number of working days between two dates:
SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @.first_date AND @.second_date
AND workingday = 'Y' ;
--
David Portas
SQL Server MVP
--|||Hi Shaun,
Check if this helps...
SELECT DATEDIFF(dd, 'startdate', 'enddate')
- DATEDIFF(ww, 'startdate', 'enddate') *2
--
Thanks
Yogish

I am not getting calender in front of the Date textbox.Pls Help!!!

I created a reoport using wizard.In my quesry i took two date parameters.But when i run my report i get two texboxes StartDate and EndDate.How do i get calender button in front of the text boxes.Below is my query.

SELECT dbo.tblReferralTransferStatus.RefId
FROM dbo.tblReferralTransferStatus INNER JOIN
dbo.tblDownloadTracking ON dbo.tblReferralTransferStatus.VendorId = dbo.tblDownloadTracking.VendorId AND
dbo.tblReferralTransferStatus.DateModified < dbo.tblDownloadTracking.DateModified INNER JOIN
dbo.tblVENDOR ON dbo.tblReferralTransferStatus.VendorId = dbo.tblVENDOR.VendorId
WHERE (dbo.tblVENDOR.VendorName = @.vendorname)AND (dbo.tblReferralTransferStatus.DateModified >= CAST(@.StartDate AS varchar)) AND (dbo.tblReferralTransferStatus.DateModified < CAST(@.EndDate AS varchar))

Hello,

Can you verify that your parameters are set to the DateTime data type? Open Report Parameters from the Report menu, select StartDate and EndDate and make sure the Data type is set to DateTime.

Jarret