Time/Date Works with SQL, not with Access! 2

[Stevehewitt]

Hi Guys,

If I use the query builder on SQL Server Enterprise Manager to lookup all records that have a 'TimeIn' value after 5:00pm it creates the following SQL for me:

> '17:59:00'

SQL Server returns all of the results - all good!

However, when I try to use the query builder in Access 2003 I don't get any results back at all. And I'm entering in:

>#17:59:00#

Nothing at all.

Anyone help me out with this? Access is my front-end and SQL Server MSDE 2000 is the backend.

Thanks,




Steve.

"They have the internet on computers now!" - Homer Simpson

 

[Crowley16]

are you sure your data in the backend is set to date?

the ' ' delimiters on your sql query suggest they are saved as text...

try putting >"17:59:00" in your front end, if it works, then you know your backend is not set as date

--------------------
Procrastinate Now!

 

[Stevehewitt]

Thanks Crowley16, I've just given that a go and I get a 'Data type mismatch in criteria expression' error when I try to run the query from Access using "".

Not too hot on the SQL side of things, but the SQL code it pumps out on SQL Server when I type the criteria into the query builder is:

SELECT     *, TimeIn AS Expr1
FROM         tblInboxInput
WHERE     (TimeIn > CONVERT(DATETIME, '1899-12-30 17:59:00', 102))

Not sure if that makes any difference of clues..!

Any other suggestions are more than welcome!

Cheers,



Steve.

"They have the internet on computers now!" - Homer Simpson

 

[Crowley16]

ahh, you've set it as datetime... try putting something like

where format(SQLResult, "Long Time") > #17:59:00#

--------------------
Procrastinate Now!

 

[Stevehewitt]

Whey! Spot on!!! I just upsized the database to SQL recently and obviously Access doesn't have a Time field - only date / time!!!

Thanks for that.

One thing though -

"format(SQLResult, "Long Time") > #17:59:00#"

Doesn't show 'greater than' - only all times!

Cheers,




Steve.

"They have the internet on computers now!" - Homer Simpson

 

[Crowley16]

hmm, that means it's trying to compaire the full time value...

try putting a CStr around the format, and chainging the ## to ''

frankly I'm a little surprised it's doing this, fairly sure the format would have been enough...

--------------------
Procrastinate Now!

 

[Stevehewitt]

Thanks again for your help Crowley16 - it's appreciated!

Still can't get it to work - although I don't understand the CStr function (Access help is of no use whatso ever!)

Is this correct?:

CStr(Format("SQLResult","Long Time")>'17:59:00')

Cheers again,



Steve.

"They have the internet on computers now!" - Homer Simpson

 

[PHV]

WHERE Format([your Datetime field],'hh:nn:ss')>'17:59:00'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Stevehewitt]

PHV - Your a legend!
Works great - that you so much.

Cheers for all you help,



Steve.

"They have the internet on computers now!" - Homer Simpson