Trouble with DateDiff

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that needs a DateDiff function. This is what I have so far...
Month: DateDiff("m",[Entrance Date],[EvalDate])

Entrance Date is coming from tblStudents
EvalDate is coming from tblMain

I keep getting an error box that says Type mismatch in expression. What do
I need to do to get this thing working?
 
Are both fields are Date type?
Do you have Null sometimes in one of the fields?

If you do have null, then you either don't run the DateDiff or set defaults
to the dates incase of a null using the NZ function to replace null with
another date

NZ([Entrance Date],#1/1/2000#)
 
Both feilds are date feilds and they don't contain and nulls. There are some
students that don't have any entries in tblMain.



Ofer said:
Are both fields are Date type?
Do you have Null sometimes in one of the fields?

If you do have null, then you either don't run the DateDiff or set defaults
to the dates incase of a null using the NZ function to replace null with
another date

NZ([Entrance Date],#1/1/2000#)
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



RTimberlake said:
I have a query that needs a DateDiff function. This is what I have so far...
Month: DateDiff("m",[Entrance Date],[EvalDate])

Entrance Date is coming from tblStudents
EvalDate is coming from tblMain

I keep getting an error box that says Type mismatch in expression. What do
I need to do to get this thing working?
Click to expand...
Click to expand...
 
Your definition of "date fields" and Access' definition of date fields may
not coincide. Can you confirm that the underlying fields in the tables are
defined as Access Date/Time fieldtypes?

It may also be that the query is corrupted somehow. Can you create a new
query and do the DateDiff() calculation again?

Regards

Jeff Boyce
<Office/Access MVP>

RTimberlake said:
Both feilds are date feilds and they don't contain and nulls. There are
some
students that don't have any entries in tblMain.



Ofer said:
Are both fields are Date type?
Do you have Null sometimes in one of the fields?

If you do have null, then you either don't run the DateDiff or set
defaults
to the dates incase of a null using the NZ function to replace null with
another date

NZ([Entrance Date],#1/1/2000#)
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck



RTimberlake said:
I have a query that needs a DateDiff function. This is what I have so
far...
Month: DateDiff("m",[Entrance Date],[EvalDate])

Entrance Date is coming from tblStudents
EvalDate is coming from tblMain

I keep getting an error box that says Type mismatch in expression.
What do
I need to do to get this thing working?
Click to expand...
Click to expand...
Click to expand...
 
Thank you for your expertise.
The underlying fields in the tables are both defined as Access Date/Time
fieldtypes. They are both set to Simple in format.
I have recreated the query multiple times and can't get it to work.

Jeff Boyce said:
Your definition of "date fields" and Access' definition of date fields may
not coincide. Can you confirm that the underlying fields in the tables are
defined as Access Date/Time fieldtypes?

It may also be that the query is corrupted somehow. Can you create a new
query and do the DateDiff() calculation again?

Regards

Jeff Boyce
<Office/Access MVP>

RTimberlake said:
Both feilds are date feilds and they don't contain and nulls. There are
some
students that don't have any entries in tblMain.



Ofer said:
Are both fields are Date type?
Do you have Null sometimes in one of the fields?

If you do have null, then you either don't run the DateDiff or set
defaults
to the dates incase of a null using the NZ function to replace null with
another date

NZ([Entrance Date],#1/1/2000#)
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I have a query that needs a DateDiff function. This is what I have so
far...
Month: DateDiff("m",[Entrance Date],[EvalDate])

Entrance Date is coming from tblStudents
EvalDate is coming from tblMain

I keep getting an error box that says Type mismatch in expression.
What do
I need to do to get this thing working?
Click to expand...
Click to expand...
Click to expand...
 
I'm puzzled. Which version of Access are you using? Have you searched
on-line using some combination of date, mismatch, DateDiff()?

Regards

Jeff Boyce
<Office/Access MVP>

RTimberlake said:
Thank you for your expertise.
The underlying fields in the tables are both defined as Access Date/Time
fieldtypes. They are both set to Simple in format.
I have recreated the query multiple times and can't get it to work.

Jeff Boyce said:
Your definition of "date fields" and Access' definition of date fields
may
not coincide. Can you confirm that the underlying fields in the tables
are
defined as Access Date/Time fieldtypes?

It may also be that the query is corrupted somehow. Can you create a new
query and do the DateDiff() calculation again?

Regards

Jeff Boyce
<Office/Access MVP>

RTimberlake said:
Both feilds are date feilds and they don't contain and nulls. There are
some
students that don't have any entries in tblMain.



:

Are both fields are Date type?
Do you have Null sometimes in one of the fields?

If you do have null, then you either don't run the DateDiff or set
defaults
to the dates incase of a null using the NZ function to replace null
with
another date

NZ([Entrance Date],#1/1/2000#)
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I have a query that needs a DateDiff function. This is what I have
so
far...
Month: DateDiff("m",[Entrance Date],[EvalDate])

Entrance Date is coming from tblStudents
EvalDate is coming from tblMain

I keep getting an error box that says Type mismatch in expression.
What do
I need to do to get this thing working?
Click to expand...
Click to expand...
Click to expand...
 
Puzzled seems to be a very good word for this one.
I am using Access 2003. I have looked every where. Unfortunately I am still
a novice when it comes to Access. I would like to have proper training, but
until then I am having to fumble my way through.

Jeff Boyce said:
I'm puzzled. Which version of Access are you using? Have you searched
on-line using some combination of date, mismatch, DateDiff()?

Regards

Jeff Boyce
<Office/Access MVP>

RTimberlake said:
Thank you for your expertise.
The underlying fields in the tables are both defined as Access Date/Time
fieldtypes. They are both set to Simple in format.
I have recreated the query multiple times and can't get it to work.

Jeff Boyce said:
Your definition of "date fields" and Access' definition of date fields
may
not coincide. Can you confirm that the underlying fields in the tables
are
defined as Access Date/Time fieldtypes?

It may also be that the query is corrupted somehow. Can you create a new
query and do the DateDiff() calculation again?

Regards

Jeff Boyce
<Office/Access MVP>

Both feilds are date feilds and they don't contain and nulls. There are
some
students that don't have any entries in tblMain.



:

Are both fields are Date type?
Do you have Null sometimes in one of the fields?

If you do have null, then you either don't run the DateDiff or set
defaults
to the dates incase of a null using the NZ function to replace null
with
another date

NZ([Entrance Date],#1/1/2000#)
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I have a query that needs a DateDiff function. This is what I have
so
far...
Month: DateDiff("m",[Entrance Date],[EvalDate])

Entrance Date is coming from tblStudents
EvalDate is coming from tblMain

I keep getting an error box that says Type mismatch in expression.
What do
I need to do to get this thing working?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top