Date() throws ADO error 1

[edita]

Hi,
I have database SQL Server (MSDE) and Access project which is interface of this database.
I want to have a view with records that my field "blabla" (of type datetime in source table) is equal to current date.
But when I use criterion "where (blabla = Date())" and I want to save it I get such message:

ADO: 'Date' is not a recognized function name

Have you got any idea why I cannot use it?
Maybe there is any other solution?

Thanks,
bye

 

[PeteJohnston]

You could use blabla = Now() or, if that doesn't work, try

blabla = Format(Now(), "dd mmm yyyy&quot


PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[PaulBricker]

I think you are going to be very frustrated here. I tried asking this question in the AccessProject.adp forumn
thread958-722885

I did not get any answer that helped sort this out. I can't believe that Access can be so easy and SQL Server doesn't have that functionality.

Paul

 

[DseeDm]

Hi,

I believe you'll need to use something like:

"where blabla = '" & format(Date(),"YYYY-MM-DD HH:NN:SS&quot & "'"

you may need to tweak the date format a little.

Good Luck
- DCD

 

[rdodge]

Remember, if you are dealing with writing SQL, you must convert any variable/value to String Data Type and still follow the SQL rules. Also be sure that you use the proper standards based on which ANSI standard you using, ANSI 89 or ANSI 92.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[BobJacksonNCI]

The SQL equivalent of Now() is Getdate()
It will retrieve a timestamp, so you'll need to look up the SQL method to use the date only.

HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.

 

[rdodge]

If you are using VBA to right in SQL, once you have the date and converted to string, surround it with the pound signs like:

#12/11/2003#

This info is found in the Where Clause Help file in Access 2002.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[PaulBricker]

I tried GETDATE() in the View, and if I have a date field formatted mm/dd/yy and put
<GetDate()
It returns all my 12/12/03 records.
I tried Format(GETDATE(),&quot;mm/dd/yy&quot but the View would not accept the Format() function.
But this is progress. I couldn't get anything to work prior to this. I knew that if I put
= '12/12/03' it would return all my current records. But I haven't had any luck with any builtin functions.
Does anyone have any recommendations for reading matter that might help when working in a .adp file. I wandered around Barnes & Noble the other night but didn't see anything.

Paul

 

[marmotje]

Hi,

I have a problem to with something like this. I'am trying to get as default valau YYYY in a tabel but it won't work..

somebody any idea?

thanks anyway


best regards,
R. Harreman

 

[BobJacksonNCI]

This is not addressed to anyone in particular because I don't know who is still working on a solution...
And this is specific to Access projects.

I was away several days because one of my Labrador Retrievers had puppies.

My environment is Access 2000 / SQL 7, and that may be important because significant improvements were made if using Access XP / SQL 2000. The Microsoft project lead stated in a chat that if someone wants to creat Access projects, he/she should use those versions.

General comment: The more you know about Microsoft's SQL implementation, the better off you're going to be. I'm at novice level and only recently talked management into installing SQL on my PC. That's going to help because I'll see what's going on more clearly and have SQL online help at hand.

Back to the original problem of relating Access dates to SQL dates. Bear in mind that Views and Tables in an Access project reside on the SQL server. For my purposes, I wanted date stamps on new records and I didn't want the time included. So, the field definition I used has the following in the default value:

(convert(datetime,convert(char(10),getdate(),101)))

That stores date only, which makes selecting by date using a pass through query much simpler.


HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.

 

[PaulBricker]

Bob, I tried your expression on the criteria line for my date field and it did pull up records from today. I looked for a good SQL book over the weekend but didn't find one. It seems the difference between SQL and Access is significant enough to justify getting some better info.
I did get a message, that I clicked thru quickly, that said something to the effect that the Convert function might not be necessary. But the message never reappeared. I am more of a novice than you. We currently don't use SQL server in the office but there is a good possibility that they will upgrade in the next year +/-. I thought that I could get a jump on it using .adp projects. It is a place to start, but the help files are no help when it comes to SQL commands.
Thanks for taking the time to share what you know.

Paul

 

[BobJacksonNCI]

Paul,

Thank you for the star!

The following link points to the online chat where the program manager makes his statement regarding Access version for creating projects. It also includes a book recommendation.

Good Luck!
Bob

http://msdn.microsoft.com/chats/office/office_092602.asp