By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,056 Members | 1,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,056 IT Pros & Developers. It's quick & easy.

Mathimatical function on a derived date

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
"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
Nov 12 '05 #4

P: n/a
"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?
Nov 12 '05 #5

P: n/a
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()));

Nov 12 '05 #6

P: n/a
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#
Nov 12 '05 #7

P: n/a
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?

Nov 12 '05 #8

P: n/a
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.

Nov 12 '05 #9

P: n/a
"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 ...
Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.