Access Crashes Filtering a Linked SQL Table on a date field

I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.

This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.

Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.

I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.

Has anyone run across this before?

Thanks!

-BenWhat is the operating system on the new PC?
Is is connecting directly to the network?
What is the SQL Server ODBC driver version #?

The other thing I would do is find the developer that designed the application in Access and smack him in the back of the head for trying to develop a multi-user application in Access.

There are many many other alternatives that would work better and be much much faster.

Access accessing SQL {}

Im using Access to access a SQL database. For one of the queries i see the following under column:

{ fn month(dbo.TableName.Date) }

Ive never used this structure before but im confused to what it is? Can anyone explain please?

Thanks

The curly braces and 'fn' server to denote a OBDC level function.

The T-SQL equilivent would be:

month( YourDateColumn )

|||

Hello

Thanks for that. Could you explain a bit more on that please? As far as im concerned i didnt think there would be an ODBC link as it was connecting straight to a SQL database (checking ODBC theres no link to a SQL database either). Maybe an example if you can on what this user could have been trying to achieve would be great?

Thanks

ABS () and dates

What would the Sql syntax look like if I wanted to select +/- 10 days of a d
elivery date.
For instance; Table has Actual Delivery Date & Expected Delivery date, and u
sing the ABS Sql function, I want to select only the rows from the table tha
t were delivered with +/- 10 days.
And even if it's not the best way to do it, I would really like the info on
ABS.
Any ideas?
Thanx!Use DATEADD function like:
BETWEEN DATEADD ( d, -10, dt ) AND DATEADD ( d, 10, dt )
Anith|||I don't know your table structure or sample data, but here's a guess:
WHERE ABS(DATEDIFF(DAY, ActualDate, ExpectedDate)) <= 10
"Jude" <judes@.email.uophx.edu> wrote in message
news:%23NQBlSwQGHA.1868@.TK2MSFTNGP09.phx.gbl...
What would the Sql syntax look like if I wanted to select +/- 10 days of a
delivery date.
For instance; Table has Actual Delivery Date & Expected Delivery date, and
using the ABS Sql function, I want to select only the rows from the table
that were delivered with +/- 10 days.
And even if it's not the best way to do it, I would really like the info on
ABS.
Any ideas?
Thanx!|||Trust me I would, but someone taking a class asked me this question & I said
I had never used this function, but knew where to go to ask the experts!
Thanx!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u1RNgfwQGHA.4960@.TK2MSFTNGP12.phx.gbl...
> Use DATEADD function like:
> BETWEEN DATEADD ( d, -10, dt ) AND DATEADD ( d, 10, dt )
> --
> Anith
>|||Fantastic, Thanx so much Aaron! This is a favor for a friend who is taking
a class, and since I haven't used this function & would just use DateDiff
functions, I didn't have an answer. I knew the experts in here would
though!
Thanx again!!!!!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23zWkBxQGHA.1160@.TK2MSFTNGP09.phx.gbl...
>I don't know your table structure or sample data, but here's a guess:
> WHERE ABS(DATEDIFF(DAY, ActualDate, ExpectedDate)) <= 10
>
> "Jude" <judes@.email.uophx.edu> wrote in message
> news:%23NQBlSwQGHA.1868@.TK2MSFTNGP09.phx.gbl...
> What would the Sql syntax look like if I wanted to select +/- 10 days of a
> delivery date.
> For instance; Table has Actual Delivery Date & Expected Delivery date, and
> using the ABS Sql function, I want to select only the rows from the table
> that were delivered with +/- 10 days.
> And even if it's not the best way to do it, I would really like the info
> on ABS.
> Any ideas?
> Thanx!
>
>

About Sql Statement

I am writing Procedure in SQL Server 2000, and i am giving three inputparameters ie: Account number and from date and to date.but in will give input to procedure as account number or from date and todate.

So in select command how can i write, ie i will give input any one ie accno or ftomdate and to date. i will write sql query which i write but it is giving error

Select
* From Mf_Tran_Reg
Where
mft_fundcd='RMF' and mft_purred='P'
if @.Folio = ''
begin
and mft_procdate between @.Fdate and @.tdate
end
else
begin
and mft_accno= @.Folio
end

Hi Majid,

CREATE PROCEDURE SomeProcedure
(
@.mft_accno INT = NULL,
@.Fdate DATETIME = NULL,
@.tdate DATETIME = NULL
)
AS
Select
* From Mf_Tran_Reg
Where
mft_fundcd='RMF' and mft_purred='P'
and
( mft_procdate between @.Fdate and @.tdate
OR mft_accno= @.Folio
)

As from your description both will not be passed, onyl either the Date or the number. If AccountNumber or dates can be NULL you will have to add the AND columnn is NULL to not give back the Nulled values.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

about select between help pls

well i have 2 input date form and to.

"date_from" varchar variable

and "date_to" varchar variable

where in i want to select only the dates between the two variables only.

example

date_from -date_to contains= "200702"-"200705"

and everything that starts from 200702 to 200705 will be output.

can someone help me or give some similar examples regarding this?

thanks...

Select * from Transaction Where Date_From >= (Cast(Year(TransactionDate) as varchar) + Cast(Month(TransactionDate) as varchar)) AND Date_To <= (Cast(Year(TransactionDate) as varchar) + Cast(Month(TransactionDate) as varchar))|||

My suggestion is not to use the BETWEEN operator, but instead use the >= and < operators. This, along with adding 1 day to the upper bounds of the "to" date, will help to avoid the pitfalls you can encounter when trying to work around the time portion of dates.

Try something like this:

DECLARE @.date_from datetime

DECLARE @.date_to datetime

SELECT @.date_from = '20070201', @.date_to = '20070530' -- note the the time portion of the date will default to midnight 00:00:00.0000

SELECT @.date_to = DATEADD(d, 1, @.date_to) -- add 1 day to the ending date

SELECT

someColumns

FROM

someTable

WHERE

myDate >= @.date_from AND myDate < @.date_to

|||

how about this one?

Public Sub read_records(ByVal nyutancd As String, ByVal date_from_new As String, ByVal date_to_new As String)

'配送依頼のデータ取得
'SQL作成
db = New TDataCenter.db.TDataCenter("venus", "venus", "threesupport", "postgres", "postgres")

date_from = ""
date_to = ""
syain_name = ""
product = ""

Dim objReader As PgSqlDataReader

'objReader = db.GetSQLReader(String.Format("Select distinct urr_urinenget,urr_etancd,urr_scd,urr_urikbn,urr_zurgsuu,urr_zurgkin,urr_zsirgkin,urr_curgsuu,urr_curgkin,urr_csirgkin,urr_gurgsuu,urr_gurgkinn,urr_gsirgkin,urr_turgkin,urr_tsirgkin,urr_kousindate from urr")))
objReader = db.GetSQLReader(String.Format("Select distincturr_urinenget,sn_syainnm,sy_snm,urr_urikbn,kt.kt_nm fromurr inner join sy on sy.scd = urr.urr_scd inner join sn on sn.syaincd = urr_etancd inner join kt on kt.kt_cd = urr.urr_urikbn and kt.kt_kbn='cm07'where urr_urinenget between {0} and {1} group by urr_urinenget,sn_syainnm,sy_snm,urr_urikbn, kt.kt_nm ", TDataCenter.db.TDataCenter.SingleQuatedStr(date_from_new), TDataCenter.db.TDataCenter.SingleQuatedStr(date_to_new)))

do you think it will work?

|||

natasha_arriell:

do you think it will work?

That approach is insecure. Do not use placeholders and string substitution; always use parameters.

I have no idea what processing SingleQuatedStr does to those string values. I have no idea what those string values contain. And I have no idea what sort of data is stored in the urr_urineget column. Your best bet is to use some edge case data and try it yourself to see if you are getting the desired result.

about indexes

Hi Grus,
How can i know that on what date a index was created and how many times
it was reindexed.
Or how offen it being reindexed.
from
DollerSQL Server doesn't keep such information. So you would have to keep track of
it yourself, possibly
using a Profiler trace.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"doller" <sufianarif@.gmail.com> wrote in message
news:1132808347.911840.163270@.g44g2000cwa.googlegroups.com...
> Hi Grus,
> How can i know that on what date a index was created and how many times
> it was reindexed.
> Or how offen it being reindexed.
> from
> Doller
>

about indexes

Hi Grus,
How can i know that on what date a index was created and how many times
it was reindexed.
Or how offen it being reindexed.
from
Doller
SQL Server doesn't keep such information. So you would have to keep track of it yourself, possibly
using a Profiler trace.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"doller" <sufianarif@.gmail.com> wrote in message
news:1132808347.911840.163270@.g44g2000cwa.googlegr oups.com...
> Hi Grus,
> How can i know that on what date a index was created and how many times
> it was reindexed.
> Or how offen it being reindexed.
> from
> Doller
>

about indexes

Hi Grus,
How can i know that on what date a index was created and how many times
it was reindexed.
Or how offen it being reindexed.
from
DollerSQL Server doesn't keep such information. So you would have to keep track of it yourself, possibly
using a Profiler trace.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"doller" <sufianarif@.gmail.com> wrote in message
news:1132808347.911840.163270@.g44g2000cwa.googlegroups.com...
> Hi Grus,
> How can i know that on what date a index was created and how many times
> it was reindexed.
> Or how offen it being reindexed.
> from
> Doller
>

About DateTime in SQL

Hi,
I am making a report in SQL Reporting with the following format.
Does any have any idea how can I get date in this format "Monday, March 26, 2007" and how can I get time of each tranction in Reporting.I tried Formatdatetime function but it doesn't work..Plzz help me Thanks

Monday, March 26, 2007
10am 100
10am 110
1pm 100
Total: 310
Tuesday,March 27,2007
11am 500
6 pm 500
Total: 1000
Grad Total 1310

Quote:

Originally Posted by ri58776

Hi,
I am making a report in SQL Reporting with the following format.
Does any have any idea how can I get date in this format "Monday, March 26, 2007" and how can I get time of each tranction in Reporting.I tried Formatdatetime function but it doesn't work..Plzz help me Thanks

Monday, March 26, 2007
10am 100
10am 110
1pm 100
Total: 310
Tuesday,March 27,2007
11am 500
6 pm 500
Total: 1000
Grad Total 1310

----
For Date format you can use....Example,

SELECT substring(CONVERT(CHAR(20),'March 26, 2007 05:32:08 PM',109),1,14)

For Time part... Example
SELECT CONVERT(CHAR(15),'05:32:08 PM',114)

about datetime

I have to convert date such as '2006-09-21 13:27:25.077' to '20060921'

any ideas?

thanks in advance

I attempted to convert(char(8),getdate()),but result is 'Sep 21...'

I have no idea about that

|||

Problem solved!

datatime have many styles

just like convert(char(8),getdate(),112) can perform what i want