SQL/Access time fields differences

[tziviak]

I'm working with a access database that has a table in sql. so in my module when I'm trying to run an sql statement to get records from my sql table("seesions&quot I have to use this sql statment-because of the differences in the time fields between access/sql-the cvDate format

sqll = "Select OSIS, STime, DateOfService FROM qrySessionsOverlaps Where [OSIS] = '" & StudentID & "'" & _
&quot; And DateOfService = #&quot; & DATE1 & &quot;# And ((#&quot; & CVDate(Format(BeginTime, &quot;hh:nn AM/PM&quot) & &quot;# <= #&quot; & [STime] & &quot;# And #&quot; & _
CVDate(Format(EndTime, &quot;hh:nn AM/PM&quot) & &quot;# > &quot; & [STime] & &quot; Or (# &quot; & _
CVDate(Format(BeginTime, &quot;hh:nn AM/PM&quot) & &quot;# >=&quot; & [STime] & &quot; And #&quot; & CVDate(Format(BeginTime, &quot;hh:nn AM/PM&quot) & &quot;# < &quot; & [STime] & &quot; ))&quot;


and the qrySesssionsOverlaps contains this:

SELECT Sessions.OSIS, Sessions.EmployeeID, Sessions.DateOfService, Sessions.WereServiceRendered, CVDate(Format([TimeSessionStarted],&quot;hh:nn:ss AM/PM&quot) AS STime, Sessions.ErrorCodes
FROM Sessions
WHERE (((Sessions.WereServiceRendered)=&quot;Y&quot AND ((Sessions.ErrorCodes) Is Null Or (Sessions.ErrorCodes)<>&quot;del&quot);


now, the problem is that the code in the module (the first code that I posted doesn't return anything for STime-it stays empty-even though I converted it to the right format in the qryOverlapsSessions query

any ideas why?

 

[cmmrfrds]

Please show the data types of the fields you are working with and an example of typical data in the fields.

 

[vbajock]

When you &quot;in sql&quot; do you mean this is an MS SQL database? I don't know of any date formats native to MSSQL that Access can't handle automatically.

 

[tziviak]

I'm using the date/time field in both access (I'm using the 97 version) and SQL-yes, I am using MS SQL database. and regarding the time/date format check out this link from microsoft:

http://support.microsoft.com/defaul...port/kb/articles/q173/0/97.asp&NoWebContent=1

thanks

 

[vbajock]

Sounds like your best solution is to upgrade Access.

 

[tziviak]

not an option for now.
does anyone have any clue?

 

[cmmrfrds]

I am not sure what you mean by this &quot;I have to use this sql statment-because of the differences in the time fields between access/sql-the cvDate format&quot;.

Is this because you are formatting a statement for sql server? Please describe the requirements a little more.

 

[tziviak]

well basically I have a table in SQL with session notes-which includes date,studentID,therapistID,timeSessionStarted, and timeSessionEnded
now, I need to run a module from access-to find if the therapist overlaped during the day (if she entered a session that overlaps with another session that she entered)
so in order for access to recognize the timeSessionStarted and timeSessionEnded fields which are in the MS SQL database I have to convert/format it-because of the bug which I described above.
am I making myself clear?

 

[cmmrfrds]

There are a number of ways to pass the sql directly to sql server without Access fooling with the sql statement. The easiest is a pass-through query. Create a pass-through query that contains ANSI sql. This can be the record source for a Form or Report. Another way is to make an ADO recordset which can also be ANSI sql. Date handling in sql server is typically done with the convert function but there are other date functions that you can use.

Example of Convert function.

Select Convert(varchar(10), yourdate,101) as yourdate from yourtab where Convert(varchar(10), yourdate,101) = '11/07/2003'

In the Convert function above the 101 is the style and is used to tell sql server how to format the date.