I am using the following query to calculate a date for a report ...
Cal Due: DateAdd("m",[tblEquipment]![CalCycle],[tblEquipment]!
[LastCalibrated])
I now need to select all records whose Cal Due date is less than the date
of the report. I have tried to use Date() as the critera but I get a "Data
Type Mismatch" error. If I input "10/*/03" as the criteria I can pull up
all of the records for October but I can't figure out how to select records
before a specific date.
Any help will be geratly appreciated. 9 3604
Especially where there may be nulls, you need to explicitly typecast the
results of a calculated query field:
Cal Due: CVDate(DateAdd("m", [tblEquipment]![CalCycle],
[tblEquipment]![LastCalibrated]))
The Criteria involving dates should then match the data type.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Bob Sanderson" <xn***@LUVSPAMsandmansoftware.com> wrote in message
news:Xn**********************************@167.206. 3.3... I am using the following query to calculate a date for a report ...
Cal Due: DateAdd("m",[tblEquipment]![CalCycle],[tblEquipment]! [LastCalibrated])
I now need to select all records whose Cal Due date is less than the date of the report. I have tried to use Date() as the critera but I get a
"Data Type Mismatch" error. If I input "10/*/03" as the criteria I can pull up all of the records for October but I can't figure out how to select
records before a specific date.
Any help will be geratly appreciated.
Bob Sanderson <xn***@LUVSPAMsandmansoftware.com> wrote in
news:Xn**********************************@167.206. 3.3: I am using the following query to calculate a date for a report ...
Cal Due: DateAdd("m",[tblEquipment]![CalCycle],[tblEquipment]! [LastCalibrated])
I now need to select all records whose Cal Due date is less than the date of the report. I have tried to use Date() as the critera but I get a "Data Type Mismatch" error. If I input "10/*/03" as the criteria I can pull up all of the records for October but I can't figure out how to select records before a specific date.
Any help will be geratly appreciated.
I don't know how you are creating the query. If you are creating
the SQL yourself, the criteria would have to be
Where datediff("d",
DateAdd("m",
[tblEquipment]![CalCycle],
[tblEquipment]![LastCalibrated]),
date()) <0
'hope I got the sign right. If the report returns all equipment
currently in cal, < becomes >.
If you use the query builder, put that in a new field, and the
criteria should be Yes.
If you are putting the criteria in code somewhere, post the code.
Bob
"Allen Browne" <al*********@SeeSig.invalid> wrote in news:kEeib.147916
$b********@news-server.bigpond.net.au: Cal Due: CVDate(DateAdd("m", [tblEquipment]![CalCycle], [tblEquipment]![LastCalibrated]))
The Criteria involving dates should then match the data type.
I've tried various criteria forms but still get the "data type" error. My
query is shown at the URL below. http://bobsanderson.com/images/caldue.gif
Can you tell me what to put in the criteria field to list all records with
dates earlier than 10/1/03?
tia
"Allen Browne" <al*********@SeeSig.invalid> wrote in news:kEeib.147916
$b********@news-server.bigpond.net.au: Cal Due: CVDate(DateAdd("m", [tblEquipment]![CalCycle], [tblEquipment]![LastCalibrated]))
Shouldn't that be CDate, not CVDate?
Bob Sanderson <xn***@LUVSPAMsandmansoftware.com> wrote in
news:Xn*********************************@167.206.3 .3: "Allen Browne" <al*********@SeeSig.invalid> wrote in news:kEeib.147916 $b********@news-server.bigpond.net.au:
Cal Due: CVDate(DateAdd("m", [tblEquipment]![CalCycle], [tblEquipment]![LastCalibrated]))
The Criteria involving dates should then match the data type.
I've tried various criteria forms but still get the "data type" error. My query is shown at the URL below.
http://bobsanderson.com/images/caldue.gif
Can you tell me what to put in the criteria field to list all records with dates earlier than 10/1/03?
tia
<DATE() worked fine for me.
If you select SQL View on the query, you should see
SELECT DateAdd("m",[TblEquipment].[calcycle],
[TblEquipment].[LastCalibrateD]) AS [CAL DUE]
FROM TblEquipment
WHERE
(((DateAdd("m",[TblEquipment].[calcycle],[TblEquipment].[LastCalibr
ateD]))<Date()));
Bob Sanderson <xn***@LUVSPAMsandmansoftware.com> wrote in message news:<Xn*********************************@167.206. 3.3>... "Allen Browne" <al*********@SeeSig.invalid> wrote in news:kEeib.147916 $b********@news-server.bigpond.net.au:
Cal Due: CVDate(DateAdd("m", [tblEquipment]![CalCycle], [tblEquipment]![LastCalibrated]))
The Criteria involving dates should then match the data type.
I've tried various criteria forms but still get the "data type" error. My query is shown at the URL below.
http://bobsanderson.com/images/caldue.gif
Can you tell me what to put in the criteria field to list all records with dates earlier than 10/1/03?
tia
WHERE tblEquipment.LastCalibrated<#10/1/2003#
CDate() converts to a date.
CVDate() is converts to a variant of subtype date.
CVDate() is the older function, from the days when Access Basic had no Date
type, and therefore no CDate(). Help says that it exists for compatibility
only. However, I find it really useful, as it handles Null values correctly,
where CDate() cannot. The output type works correctly in all cases.
To achieve the same thing with CDate(), one would have to do something such
as:
CDate(Nz([MyDate], #1/1/9999#))
and then sort out the results.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Ross Presser" <rp******@NOSPAM.imtek.com.invalid> wrote in message
news:Xn**********************@129.250.170.91... "Allen Browne" <al*********@SeeSig.invalid> wrote in news:kEeib.147916 $b********@news-server.bigpond.net.au:
Cal Due: CVDate(DateAdd("m", [tblEquipment]![CalCycle], [tblEquipment]![LastCalibrated]))
Shouldn't that be CDate, not CVDate?
If 10/*/03 works in your query, then obviously your dates are stored as
text, not dates.
Convert them to dates, and the techniques the others are showing should
work.
Try:
Cal Due: DateAdd("m",
[tblEquipment]![CalCycle],CVDate([tblEquipment]![LastCalibrated]))
--
Doug Steele, Microsoft Access MVP http://I.Am/DougSteele
(No private e-mails, please)
"Bob Sanderson" <xn***@LUVSPAMsandmansoftware.com> wrote in message
news:Xn**********************************@167.206. 3.3... I am using the following query to calculate a date for a report ...
Cal Due: DateAdd("m",[tblEquipment]![CalCycle],[tblEquipment]! [LastCalibrated])
I now need to select all records whose Cal Due date is less than the date of the report. I have tried to use Date() as the critera but I get a
"Data Type Mismatch" error. If I input "10/*/03" as the criteria I can pull up all of the records for October but I can't figure out how to select
records before a specific date.
Any help will be geratly appreciated.
"Allen Browne" <al*********@SeeSig.invalid> wrote in
news:lW*********************@news-server.bigpond.net.au: CDate() converts to a date. CVDate() is converts to a variant of subtype date.
CVDate() is the older function, from the days when Access Basic had no Date type, and therefore no CDate(). Help says that it exists for compatibility only. However, I find it really useful, as it handles Null values correctly, where CDate() cannot. The output type works correctly in all cases.
Thank you. I didn't know about CVDate(); it sounds very useful.
--
Ross Presser -- rpresser AT imtek DOT com
.... seeking a new quote ... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Johno |
last post by:
I have written the two associated base classes below (Digital_camera and
Review) to manage digital camera and review objects. They are base classes
for which other derived classes can be written to...
|
by: WittyGuy |
last post by:
Hi all,
Though I know the concepts of both abstract class & virtual
function (like derived class pointer pointing to base class...then
calling the function with the pointer...), what is the real...
|
by: Alan |
last post by:
I have a class, "event_list", derived from the template class "vector."
I extended it with a method, "write" for this new class. Here's its
definition:
class event_list : public vector<Event>
{...
|
by: intl04 |
last post by:
I'm trying to set up a query that will include a new field ('Days
until completion') whose value is derived from the DateDiff function.
I think I have the syntax correct but am not sure.
Days...
|
by: jimfortune |
last post by:
From:
http://groups-beta.google.com/group/comp.databases.ms-access/msg/769e67e3d0f97a90?hl=en&
Errata:
19 solar years = 2939.6018 days should be
19 solar years = 6939.6018 days
Easter...
|
by: William Stacey |
last post by:
I know the following is not allowed, but shouldn't it be? sharedObject is
part of Derived and should be able to be set in the constructor - no? tia
public abstract class Base1
{
protected...
|
by: marco_segurini |
last post by:
Hi,
the following sample code shows a compiler error I get trying to build
some old code with the last CL compiler (vers 13.10.3077):
//----- begin
#include <iostream>
namespace ns
{
|
by: cindy |
last post by:
Get data into datatable, add to dataset dsSearch "
Get data into datatable, add to dataset dsSearch
Using In-Memory SQL Engine join the tables and select the filenames from the
join, add to...
|
by: aamircheema |
last post by:
Hi,
Say I have a table Job with columns name, date, salary . I want to get
the name ,date and salary for the date when that person earned maximum
salary. I am using something like
SELECT...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |