Data type mismatch

[Radhika]

I have a qry that calculates the difference in time between VPInsertionDate
and VPRemovalDateB in an expression called 'Time'. This difference in dates
leads to '#Error' where I eliminated values that should be present. I want to
isolate all values for 'Time'>180. However, due to '#Error' being present in
the 'Time' column along with the DateDiff I need, I get a message saying
'Data type mismatch'. How can I do isolate values 180?

Thank you
Shukla

 

[Al Campagna]

Radhika,
The first thing to try is to change the name of the Time calculation.
Time is a reserved word in Access, so try another name like VPTime.
That just might be the problem.

If that doesn't cure the problem, it would be helpful to see your
calculation, and an example or two.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Radhika]

I changed the name to the Time calcuation to VPTime, but I still got the
message. I also tried to create another expression that was as
follows:IIf([Time]>180,"Yes") and then tried to filter out field that said
"Yes", but I got the same 'Data type mismatch' error message.

Below is an example:
ID# Date of Visit VPInsertionDate VPRemovalDateB Time Type
253876 01/02/2007 01/02/2007 05/08/2007 184
253876 05/08/2007 05/08/2007 #Error

253876 07/02/2007 07/02/2007 10/10/2007 193
IF
253876 10/10/2007 10/10/2007

This query manipulates the data in the original table ('tbl_TEPRecords) to
make VPInsertionDate = Date of Visit and make the Date of the next visit =
VPRemovalDateB. Moreover, it was designed so the if 'IF' is selected in
'Type', the VPRemovalDateB of the previous visit is null. As such I get the
work'#Error' in the field called 'Time'.

I need all values for 'Time' >180.

I hope this clarifies the situation.

Thank you
Radhika

 

[Al Campagna]

Radhika,
If you changed the name of the caclulation to VPTime then why do you
have...
IIf([Time]>180,"Yes")
You shouldn't use Time as a name for a calculation or a field name.

Also, an IIF statement requires a True response and a False response...
IIf([VPTime]>180,"Yes", "No")

From your sample data, you show an error when VPRemovalDateB is Null.
That is correct... since there's nothing to calculate for that record.
You should filter out any records where the VPRemovalDateB is Null.
In fact, it would be best to filter out Null VPInsertionDates too.
Using the Query design grid...
Field: VPInsertionDate VPRemovalDateB
Criteria: Is Not Null Is Not Null

Using the Query design grid, create a calculated field...
Field: VPTime = [VPRemovalDateB] - [VPInsertionDate]
Criteria: >180

This should return all records (with legitimate dates) where the
differnce is greater than 180.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I changed the name to the Time calcuation to VPTime, but I still got the
message. I also tried to create another expression that was as
follows:IIf([Time]>180,"Yes") and then tried to filter out field that said
"Yes", but I got the same 'Data type mismatch' error message.

Below is an example:
ID# Date of Visit VPInsertionDate VPRemovalDateB Time
Type
253876 01/02/2007 01/02/2007 05/08/2007 184
253876 05/08/2007 05/08/2007 #Error

253876 07/02/2007 07/02/2007 10/10/2007 193
IF
253876 10/10/2007 10/10/2007

This query manipulates the data in the original table ('tbl_TEPRecords) to
make VPInsertionDate = Date of Visit and make the Date of the next visit =
VPRemovalDateB. Moreover, it was designed so the if 'IF' is selected in
'Type', the VPRemovalDateB of the previous visit is null. As such I get
the
work'#Error' in the field called 'Time'.

I need all values for 'Time' >180.

I hope this clarifies the situation.

Thank you
Radhika

Radhika,
The first thing to try is to change the name of the Time calculation.
Time is a reserved word in Access, so try another name like VPTime.
That just might be the problem.

If that doesn't cure the problem, it would be helpful to see your
calculation, and an example or two.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."