Date queries 
July 24th, 2008, 07:50 AM
| | Member | | Join Date: Mar 2008 Location: Bangalore, India Age: 26
Posts: 47
| | Date queries
Hi,
Sorry to be reopening this thread. but i am still unable to get this solved. The code i have used is - DoCmd.OpenReport "NameOfTheReport", acViewPreview, , Date - 3
I get an error message saying "Compile Error : Can't find project or library"
If this is closed out, i can deliver my project. Please help!!!
Thanks
Neo
| 
July 25th, 2008, 04:11 AM
|  | Expert | | Join Date: May 2007 Location: Florida Age: 60
Posts: 1,877
| |
I think your problem is with Date - 3. If you are referring to the system date, the syntax should be as follows: Date() - 3
| 
July 25th, 2008, 04:41 AM
| | Member | | Join Date: Mar 2008 Location: Bangalore, India Age: 26
Posts: 47
| | Quote: |
Originally Posted by puppydogbuddy I think your problem is with Date - 3. If you are referring to the system date, the syntax should be as follows: Date() - 3 |
When i tried doing that, the paranthesis () just disappeared. and it ended up being the way it is now. Am unable to change that.
| 
July 25th, 2008, 05:30 AM
| | Member | | Join Date: Mar 2008
Posts: 69
| |
Since Access is looking for an SQL string clause without the "WHERE", I think you should try something like: - "DateFieldInQuery = #" & Date() - 3 & "#"
"DateFieldInQuery" is whatever is the name that you have assigned in the query to the date you want to test. The # before and after tells Access that the number returned by Date() - 3 should be teated as a date.
| 
July 25th, 2008, 06:01 AM
| | Member | | Join Date: Mar 2008 Location: Bangalore, India Age: 26
Posts: 47
| | Quote: |
Originally Posted by youmike Since Access is looking for an SQL string clause without the "WHERE", I think you should try something like: - "DateFieldInQuery = #" & Date() - 3 & "#"
"DateFieldInQuery" is whatever is the name that you have assigned in the query to the date you want to test. The # before and after tells Access that the number returned by Date() - 3 should be teated as a date. | But would this not be a query? how do i insert this in the where clause in the code?
| 
July 25th, 2008, 06:42 AM
| | Expert | | Join Date: Jan 2008 Age: 44
Posts: 365
| | Quote: |
Originally Posted by neosam But would this not be a query? how do i insert this in the where clause in the code? | -
"SELECT * FROM Tablename WHERE Datefieldname = # " & Date() - 3 & "#"
-
| 
July 25th, 2008, 06:44 AM
| | Expert | | Join Date: Jan 2008 Age: 44
Posts: 365
| |
The way that you would put that into your openreport command is as follows: -
DoCmd.OpenReport "NameOfTheReport", acViewPreview, , "Datefieldname = # " & Date() - 3 & "#"
-
| 
July 25th, 2008, 08:11 AM
| | Member | | Join Date: Mar 2008 Location: Bangalore, India Age: 26
Posts: 47
| | Quote: |
Originally Posted by PianoMan64 The way that you would put that into your openreport command is as follows: -
DoCmd.OpenReport "NameOfTheReport", acViewPreview, , "Datefieldname = # " & Date() - 3 & "#"
-
| I still get the same error message. "Cannot find project or library" and the 'date' in the code is highlighted.
| 
July 25th, 2008, 11:59 AM
|  | Expert | | Join Date: May 2007 Location: Florida Age: 60
Posts: 1,877
| |
try:
Format(Date(),"mm/dd/yyyy") - 3
| 
July 25th, 2008, 12:26 PM
| | Expert | | Join Date: Jun 2007 Location: Derbyshire, UK Age: 62
Posts: 311
| |
Hi
I have had this problem a few times over the years. It seems to be a referencing problem (perhaps when using A2000 on a DB created in A2003 but in A2000 format), and yes the parentheses do disappear when Date() is used. These only seem necessary in Query designer. I have never found a satisfactory answer to this, but in the past, in desperation, I have resorted to Int(Now()) in stead of Date !!
MTB
| 
July 25th, 2008, 03:56 PM
| | Forum Leader | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,538
| |
Hi. When Date() is no longer recognised as a valid function the problem seems to arise as a result of missing or incorrect project references for that installation, as MTB said. To trace which one it may be best to compare the project references for the 'problem' installation to those for a 'known good' PC running Access with no problems.
I experienced this on a laptop installation, and all the development databases I was working on failed on normal built-in functions - Date was just one of many that caused compile errors and other completely daft symptoms. There was a missing reference in my case, but in others I know of it was a conflicting reference (to the wrong version of a DLL). It's really annoying because there is actually nothing wrong with the underlying DBs or their code, and trying to trace non-existent errors is such a fruitless task.
-Stewart
| 
July 28th, 2008, 05:28 AM
| | Member | | Join Date: Mar 2008 Location: Bangalore, India Age: 26
Posts: 47
| | Quote: |
Originally Posted by Stewart Ross Inverness Hi. When Date() is no longer recognised as a valid function the problem seems to arise as a result of missing or incorrect project references for that installation, as MTB said. To trace which one it may be best to compare the project references for the 'problem' installation to those for a 'known good' PC running Access with no problems.
I experienced this on a laptop installation, and all the development databases I was working on failed on normal built-in functions - Date was just one of many that caused compile errors and other completely daft symptoms. There was a missing reference in my case, but in others I know of it was a conflicting reference (to the wrong version of a DLL). It's really annoying because there is actually nothing wrong with the underlying DBs or their code, and trying to trace non-existent errors is such a fruitless task.
-Stewart | Well the int(Now()) command helps. Thanks guys......
|  | | Thread Tools | Search this Thread | | | | | | | What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 220,662 network members.
|