Please help with datatype mismatch

[wrightlife11]

Hello, I have a problem with data type mismatch. Can someone assist me
please?

I have some basic date functions below. I keep getting a data type
mismatch. What can I do about it? I need to calcuate the number of days
between dates.
However when I use the Like 1 it will show up as all those with a 1.

I tried this query first:
SELECT MORDDate([SFStartDate]) AS MatOrdDate, Date() AS [Date],
CDbl(Dinq([SFStartDate],Date())) AS DaysPastMord
FROM tblMain
WHERE (((CDbl(Dinq([SFStartDate],Date())))<=1) AND ((tblMain.SFStartDate) Is
Not Null))
ORDER BY tblMain.SFStartDate;

Then I tried a query of a query like this:
SELECT
FROM qryX54DinQ
WHERE (((qryX54DinQ.DaysPastMord) <= 1));

and lastly I tried to use the Dinq function like this:
SELECT Dinq(TargetDate([SFStartDate]),Date()) AS Dinq
FROM qryX54DinQ
WHERE (((qryX54DinQ.DaysPastMord) Like -30) AND
((Dinq(TargetDate([SFStartDate]),Date()))<=1));

Example:
Public Function TargetDate(StartDate As Date) As Date


If StartDate < 0 Then 'Evaluate Argument
Exit Function
Else
TargetDate = StartDate - 7
End If

End Function

and

Public Function Dinq(StartDate As Date, EndDate As Date) As Double

If StartDate < 0 Then 'Evaluate Argument
Exit Function
Else
Dinq = EndDate - StartDate
End If


End Function

 

[John W. Vinson]

Hello, I have a problem with data type mismatch. Can someone assist me
please?

I have some basic date functions below. I keep getting a data type
mismatch. What can I do about it? I need to calcuate the number of days
between dates.

Umm... you're going all around Robin's barn to do something builtin.

DateDiff("d", [startdate], [enddate])

gives an integer number of dates.

However when I use the Like 1 it will show up as all those with a 1.

Of course. That's what the LIKE operator is designed to do - search text
fields with wildcards. you're not searching a text field.

Please explain, in words, what you're trying to accomplish.

 

[wrightlife11]

John, I tried datediff("d",date(),MORDDate([SFStartDate])) and I still get
the same error. Data Type Mismatch. I began doing this because that did not
work.

I also tried this on my home machine and all of this works fine. Any ideas?

Matthew

Hello, I have a problem with data type mismatch. Can someone assist me
please?

I have some basic date functions below. I keep getting a data type
mismatch. What can I do about it? I need to calcuate the number of days
between dates.

Umm... you're going all around Robin's barn to do something builtin.

DateDiff("d", [startdate], [enddate])

gives an integer number of dates.

However when I use the Like 1 it will show up as all those with a 1.

Of course. That's what the LIKE operator is designed to do - search text
fields with wildcards. you're not searching a text field.

Please explain, in words, what you're trying to accomplish.

 

[John W. Vinson]

John, I tried datediff("d",date(),MORDDate([SFStartDate])) and I still get
the same error. Data Type Mismatch. I began doing this because that did not
work.

Again:

What is MORDDate()? It's not an Access builtin function. If it's something you
wrote, what datatype does it return (should be a Date-type Variant)? And
what's the context: where are you calling DateDiff? Please post the code or
the SQL of the query.

 

[wrightlife11]

John, sorry I was in a bit of a hurry when I originally wrote this. I can
view the information when I take out the ">=-3" but it does not like to have
a number in there unless it is prefaced by "like".

I tried the as Variant as you suggested and I get the same results.

MORDDate is my own function:
Public Function MORDDate(StartDate As Date) As Date

If StartDate < 0 Then 'Evaluate Argument
Exit Function
Else
MORDDate = StartDate - 28
End If

End Function

My SQL looks like this:

SELECT tblMain.SFStartDate, MORDDate([SFStartDate]) AS MORDDate,
DateDiff("d",Date(),MORDDate([SFStartDate])) AS DateDiff
FROM tblMain
WHERE (((DateDiff("d",Date(),MORDDate([sfstartdate])))>=-3) AND
((tblMain.SFStartDate) Is Not Null));

John, I tried datediff("d",date(),MORDDate([SFStartDate])) and I still get
the same error. Data Type Mismatch. I began doing this because that did not
work.

Again:

What is MORDDate()? It's not an Access builtin function. If it's something you
wrote, what datatype does it return (should be a Date-type Variant)? And
what's the context: where are you calling DateDiff? Please post the code or
the SQL of the query.

 

[John W. Vinson]

John, sorry I was in a bit of a hurry when I originally wrote this. I can
view the information when I take out the ">=-3" but it does not like to have
a number in there unless it is prefaced by "like".

I'm sorry, this is making no sense at all. You're writing custom functions to
duplicate the capabilities of builtin functions, using LIKE on number
fields... What error are you getting if you don't use LIKE???

I think you can get your desired result with no custom code at all. Could you
explain in words in real-life terms what you want this query to return, and
what the field values might be?

 

[wrightlife11]

Sure, I have a single "Starting Date". I need to in many situations
calculate a different date such as when material is supposed to be ordered or
when any number of other times are. All these dates like MORDDate are
scheduled dates based on the "Starting Date" and the schedule parameters.

So if the Starting date is 1/1/2008 and I need to schedule a time to order
material, supporting JIT practices. The Material Order Date(MORDDate) is
scheduled like this: Starting Date + 32 days.

There are many different dates on the schedule and each one has its own
function.

That is why I used a custom function because the schedule parameters can
change.

I need to now calculate the number of days between today and the scheduled
"MORDDate" and then limit my results to <= or >= number. So I can pull up a
list of items that need to be ordered today based on the schedule.

To clear that all up.....
The functions are the scheduled dates used in many queries/reports and can
change.
This calculation is for a single report. Need to use the function to get
the calculation.
The output of the calculation needs to be a number so I can use "<=" or ">="
to restrict the output to the the items scheduled to be ordered today.

Currently When I use "<=" or ">=" or just a number "5" or "=5" as Criteria
in the query I get a Data Type Mismatch Error. If I use "Like 5" I get a
records returned.

Is there an easier way?

 

[John W. Vinson]

Sure, I have a single "Starting Date". I need to in many situations
calculate a different date such as when material is supposed to be ordered or
when any number of other times are. All these dates like MORDDate are
scheduled dates based on the "Starting Date" and the schedule parameters.

So if the Starting date is 1/1/2008 and I need to schedule a time to order
material, supporting JIT practices. The Material Order Date(MORDDate) is
scheduled like this: Starting Date + 32 days.

There are many different dates on the schedule and each one has its own
function.

That is why I used a custom function because the schedule parameters can
change.

So... you have to go in and edit, recompile and test VBA code whenever a
schedule parameter changes??? OUCH!

I'd *really* recommend a table driven solution. Have a Schedules table with
fields "Event" and "Days":

Material Order 32
Response letter 14
Bill payment 90

etc. etc.

You can then either join this table to your query if the Event is in a table
field, or use DLookUp:

DateAdd("d", [strtDate], DLookup("[Days]", "[Schedules]", "Event = 'Material
Order'")

A criterion of Date() on this field will return all records where that event
is scheduled to happen today; Date()+1 will find all tomorrow's events;
Between Date() AND Date()+7 will find all events in the coming week, etc.

 

[wrightlife11]

Thank you John......I see where you are going with this. Very helpful.....

Matthew

Sure, I have a single "Starting Date". I need to in many situations
calculate a different date such as when material is supposed to be ordered or
when any number of other times are. All these dates like MORDDate are
scheduled dates based on the "Starting Date" and the schedule parameters.

So if the Starting date is 1/1/2008 and I need to schedule a time to order
material, supporting JIT practices. The Material Order Date(MORDDate) is
scheduled like this: Starting Date + 32 days.

There are many different dates on the schedule and each one has its own
function.

That is why I used a custom function because the schedule parameters can
change.

So... you have to go in and edit, recompile and test VBA code whenever a
schedule parameter changes??? OUCH!

I'd *really* recommend a table driven solution. Have a Schedules table with
fields "Event" and "Days":

Material Order 32
Response letter 14
Bill payment 90

etc. etc.

You can then either join this table to your query if the Event is in a table
field, or use DLookUp:

DateAdd("d", [strtDate], DLookup("[Days]", "[Schedules]", "Event = 'Material
Order'")

A criterion of Date() on this field will return all records where that event
is scheduled to happen today; Date()+1 will find all tomorrow's events;
Between Date() AND Date()+7 will find all events in the coming week, etc.

I need to now calculate the number of days between today and the scheduled
"MORDDate" and then limit my results to <= or >= number. So I can pull up a
list of items that need to be ordered today based on the schedule.

To clear that all up.....
The functions are the scheduled dates used in many queries/reports and can
change.
This calculation is for a single report. Need to use the function to get
the calculation.
The output of the calculation needs to be a number so I can use "<=" or ">="
to restrict the output to the the items scheduled to be ordered today.

Currently When I use "<=" or ">=" or just a number "5" or "=5" as Criteria
in the query I get a Data Type Mismatch Error. If I use "Like 5" I get a
records returned.

Is there an easier way?