Query SQL date field with hh:mm:ss AM

[CarpalT]

I am linked to a SQL table with a date field, via a ODBC connection. Some of the values stored in it are like this:
03/23/2005 5:58:07 AM
A few are like this:
03/23/2005

I have a very simple query. All I want is everything with yesterday's date, but as selection criteria
now()-1 doesn't work.
I want to ignore the hours, minutes, seconds, and am or pm but don't know how to do this (Access 2000)
Thanks,


The world is full of good people.

 

[LittleSmudge]

You need something along the lines of

WHERE Int(fieldInSQL) = (Date() - 1)



'ope-that'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[CarpalT]

Thank you for your suggestion LittleSmudge. Since I am not a programmer, I tried to guess what I should put in the query criteria grid. This is what I tried:
Int([trans_date])-1
This is the error I get: ODBC-call failed. Implicit conversion from datatype datetime to float not allowed. Use CONVERT function to run this query (#257). I read the Access help on CONVERT and to be truthful, didn't understand it. It refers to a spreadsheet function. Whenever I've tried to use anything that the help refers to as "spreadsheet", it doesnt work.
No doubt I'm doing something wrong?

The world is full of good people.

 

[LittleSmudge]

Okay so if you are doing this in the QBE grid

In the top row of the grid you have the name of the field in the SQL Db ( fieldInSQL )

Edit this to read

DayfieldInSQL: Int(fieldInSQL)

Replace the string 'fieldInSQL' above with your field name and put the rest in exactly as shown. You might find that Access adds square brackets automatically inside the curved brackets. If so leave them there.

Then in the Criteria line below it put

Date() - 1


This has the effect of Renaming you DateTime field from fieldInSQL to a Date field called DayfieldInSQL.
So you might need to do something in the target Form or Report to cope with the change of Name.


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[CarpalT]

Good Morning LittleSmudge, thank you for sticking with me.
I tried that this morning, and get this error message:
ODBC-call failed [Microsoft][ODBCSQL Server Driver][SQLServer]Implicit conversion from data type datetime to float is not allowed. Use CONVERT function to run this query (#257)

When I take it a step at a time, it works when I leave the Date() - 1 part out. Without that it runs, and converts the dates to (I'm guessing) the number of days since 1/1/1900. It is the Date() - 1 that Access is balking about.

The world is full of good people.

 

[LittleSmudge]

Okay then.

Edit top line to

DayfieldInSQL: Int(fieldInSQL) + 1


and leave criteria as

Date()


My copy of Access ( 2k2 ) doesn't even acknowledge CONVERT in the Help files at all !

Is this any kind of PassThrough query ?

Is the Date() being implemented on the SQL server or in Access ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[RoyVidar]

Seems strange it should choke on the Date function, could you try something like this in the criteria row for the field (and just the field (trans_date), no conversion in the field row):

Between date()-1 and date() -1 & " 23:59:59"

Roy-Vidar

 

[CarpalT]

Finally, this worked:
Expression in the field row:
TestDate: DateValue([dbo_tablename.recorddate])

Criteria: Date() - 1

Thank you both for your help. I don't know the answers to your questions. All I know is the database admins recently decided for me to link to their SQL tables than to download a little subset to me once in a while. They said it's easier!

The world is full of good people.

 

[PHV]

Is by chance this query a passthru query ?
If yes, you have to use T-SQL syntax.

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

 

[CarpalT]

Thanks for the input. I just had to read the online help to find out about passthrough queries. No, it isn't a passthrough. It is a make table query.

The world is full of good people.