Criteria in calculated field - unexpected prompt for input

[Peter]

So powerful but sooooo frustrating.

In a Select query I have a calculated field that contains
LOS: DateDiff("d",[A_Date],[D_Date])

A_Date and D_Date are, in turn, calculated fields (they use left(), mid()
and right() to manipulate a table field from yyyymmdd (text data type) to
dd/mm/yyyy (still as text I assume).

When I run the query all looks fine. Then back in query design view I add a
criteria for example >1 or “1†in the criteria row in the LOS field. Now when
I run the query I get two “Enter Parameter Value†boxes prompting for A_Date
first and then D_Date. No matter what I enter the resultant query has just
one all blank record.

What is going on? Why the prompt for input?

I have tried replacing the two aliases A_Date & D_Date in the above
expression with the text manipulation functions. Still the query runs okay
but now I get a Data type mismatch error when I enter a criteria either text
eg "1" or a number eg 1.

Please accept my apologies if this has been answered before – I did look
quite hard.

I bet I’ve got something very basic wrong. Thanks for your help.

Peter

 

[KARL DEWEY]

I bet I’ve got something very basic wrong.
Do not use a text field for a date.
[A_Date] and [D_Date] are aliases. They can be used in follow-on queries
but not in the same query that created them. You must use the calculation
instead.

--
KARL DEWEY
Build a little - Test a little

So powerful but sooooo frustrating.

In a Select query I have a calculated field that contains
LOS: DateDiff("d",[A_Date],[D_Date])

A_Date and D_Date are, in turn, calculated fields (they use left(), mid()
and right() to manipulate a table field from yyyymmdd (text data type) to
dd/mm/yyyy (still as text I assume).

When I run the query all looks fine. Then back in query design view I add a
criteria for example >1 or “1†in the criteria row in the LOS field. Now when
I run the query I get two “Enter Parameter Value†boxes prompting for A_Date
first and then D_Date. No matter what I enter the resultant query has just
one all blank record.

What is going on? Why the prompt for input?

I have tried replacing the two aliases A_Date & D_Date in the above
expression with the text manipulation functions. Still the query runs okay
but now I get a Data type mismatch error when I enter a criteria either text
eg "1" or a number eg 1.

Please accept my apologies if this has been answered before – I did look
quite hard.

I bet I’ve got something very basic wrong. Thanks for your help.

Peter

 

[Peter]

Many thanks for you reply.

I have inherited the database so have no choice about table design.

I need the numer of days difference between these two date fields that have
a data type of text. Given what you have already said I will use the
calculation not aliases but that still leaves me with a data type mismatch
problem when I enter a criteria.

Can you point me to anything online that I can read to help me understand
what I am doing here?

Maybe I sould mention that there are about half a million records.

Thanks again for you time. Peter.

Do not use a text field for a date.
[A_Date] and [D_Date] are aliases. They can be used in follow-on queries
but not in the same query that created them. You must use the calculation
instead.

--
KARL DEWEY
Build a little - Test a little

So powerful but sooooo frustrating.

In a Select query I have a calculated field that contains
LOS: DateDiff("d",[A_Date],[D_Date])

A_Date and D_Date are, in turn, calculated fields (they use left(), mid()
and right() to manipulate a table field from yyyymmdd (text data type) to
dd/mm/yyyy (still as text I assume).

When I run the query all looks fine. Then back in query design view I add a
criteria for example >1 or “1†in the criteria row in the LOS field. Now when
I run the query I get two “Enter Parameter Value†boxes prompting for A_Date
first and then D_Date. No matter what I enter the resultant query has just
one all blank record.

What is going on? Why the prompt for input?

I have tried replacing the two aliases A_Date & D_Date in the above
expression with the text manipulation functions. Still the query runs okay
but now I get a Data type mismatch error when I enter a criteria either text
eg "1" or a number eg 1.

Please accept my apologies if this has been answered before – I did look
quite hard.

I bet I’ve got something very basic wrong. Thanks for your help.

Peter

 

[Peter]

A little more info.....

My data type mismatch error is occuring when I enter a criteria in the
calculated field that uses the function Datediff(). So I am assuming the
data type of the calculated field (which looks like it is a number - r/hand
aligned) and the data entered for the criteria ( either eg 1 or "1" - I've
get the same error for both) don't match. Am I right so far?

On the properties form for the calculated column in the format box there is
no entry. I set the format to "General Number". Now I get no records in my
query whether I enter a criteria on not - *and* I cannot set the format back
to blank. So I am worse off than before.

I have very little hair left to spare so all help much appreciated.

Peter.

Many thanks for you reply.

I have inherited the database so have no choice about table design.

I need the numer of days difference between these two date fields that have
a data type of text. Given what you have already said I will use the
calculation not aliases but that still leaves me with a data type mismatch
problem when I enter a criteria.

Can you point me to anything online that I can read to help me understand
what I am doing here?

Maybe I sould mention that there are about half a million records.

Thanks again for you time. Peter.

I bet I’ve got something very basic wrong.

Do not use a text field for a date.

I get two “Enter Parameter Value†boxes prompting for A_Date first and then D_Date.

[A_Date] and [D_Date] are aliases. They can be used in follow-on queries
but not in the same query that created them. You must use the calculation
instead.

--
KARL DEWEY
Build a little - Test a little

So powerful but sooooo frustrating.

In a Select query I have a calculated field that contains
LOS: DateDiff("d",[A_Date],[D_Date])

A_Date and D_Date are, in turn, calculated fields (they use left(), mid()
and right() to manipulate a table field from yyyymmdd (text data type) to
dd/mm/yyyy (still as text I assume).

When I run the query all looks fine. Then back in query design view I add a
criteria for example >1 or “1†in the criteria row in the LOS field. Now when
I run the query I get two “Enter Parameter Value†boxes prompting for A_Date
first and then D_Date. No matter what I enter the resultant query has just
one all blank record.

What is going on? Why the prompt for input?

I have tried replacing the two aliases A_Date & D_Date in the above
expression with the text manipulation functions. Still the query runs okay
but now I get a Data type mismatch error when I enter a criteria either text
eg "1" or a number eg 1.

Please accept my apologies if this has been answered before – I did look
quite hard.

I bet I’ve got something very basic wrong. Thanks for your help.

Peter

 

[KARL DEWEY]

You said your text field date is yyyymmdd. Just re-arranging the data does
not make it a DATE that you can use DateDiff with.
Use this to convert your text to a date ---
DateSerial(Left([yy],4),Right([yy],2),Right(Left([yy],4),2))
Substitute your field name with brackets for the [yy] in the formula.
You can add a DateTime fields to the table design and run an update query
that has the above formula. Make sure you backup the database first.

--
KARL DEWEY
Build a little - Test a little

A little more info.....

My data type mismatch error is occuring when I enter a criteria in the
calculated field that uses the function Datediff(). So I am assuming the
data type of the calculated field (which looks like it is a number - r/hand
aligned) and the data entered for the criteria ( either eg 1 or "1" - I've
get the same error for both) don't match. Am I right so far?

On the properties form for the calculated column in the format box there is
no entry. I set the format to "General Number". Now I get no records in my
query whether I enter a criteria on not - *and* I cannot set the format back
to blank. So I am worse off than before.

I have very little hair left to spare so all help much appreciated.

Peter.

Many thanks for you reply.

I have inherited the database so have no choice about table design.

I need the numer of days difference between these two date fields that have
a data type of text. Given what you have already said I will use the
calculation not aliases but that still leaves me with a data type mismatch
problem when I enter a criteria.

Can you point me to anything online that I can read to help me understand
what I am doing here?

Maybe I sould mention that there are about half a million records.

Thanks again for you time. Peter.

I bet I’ve got something very basic wrong.
Do not use a text field for a date.

I get two “Enter Parameter Value†boxes prompting for A_Date first and then D_Date.
[A_Date] and [D_Date] are aliases. They can be used in follow-on queries
but not in the same query that created them. You must use the calculation
instead.

--
KARL DEWEY
Build a little - Test a little


:

So powerful but sooooo frustrating.

In a Select query I have a calculated field that contains
LOS: DateDiff("d",[A_Date],[D_Date])

A_Date and D_Date are, in turn, calculated fields (they use left(), mid()
and right() to manipulate a table field from yyyymmdd (text data type) to
dd/mm/yyyy (still as text I assume).

When I run the query all looks fine. Then back in query design view I add a
criteria for example >1 or “1†in the criteria row in the LOS field. Now when
I run the query I get two “Enter Parameter Value†boxes prompting for A_Date
first and then D_Date. No matter what I enter the resultant query has just
one all blank record.

What is going on? Why the prompt for input?

I have tried replacing the two aliases A_Date & D_Date in the above
expression with the text manipulation functions. Still the query runs okay
but now I get a Data type mismatch error when I enter a criteria either text
eg "1" or a number eg 1.

Please accept my apologies if this has been answered before – I did look
quite hard.

I bet I’ve got something very basic wrong. Thanks for your help.

Peter

 

[Peter]

Karl

Thanks so much for your time and I will do as you say and use Datserial()
and run an Update query but I would like to try to understand what is going
on.

The Datediff() function gives the correct result even though the rearranged
data is not in Date type. The Datediff function returns a variant (long)
type according to the reference manual. Even when I enter a criteria with
type casting eg CVar(1) I still get the data type mismatch. Do you know why
that is?

Peter

You said your text field date is yyyymmdd. Just re-arranging the data does
not make it a DATE that you can use DateDiff with.
Use this to convert your text to a date ---
DateSerial(Left([yy],4),Right([yy],2),Right(Left([yy],4),2))
Substitute your field name with brackets for the [yy] in the formula.
You can add a DateTime fields to the table design and run an update query
that has the above formula. Make sure you backup the database first.

--
KARL DEWEY
Build a little - Test a little

A little more info.....

My data type mismatch error is occuring when I enter a criteria in the
calculated field that uses the function Datediff(). So I am assuming the
data type of the calculated field (which looks like it is a number - r/hand
aligned) and the data entered for the criteria ( either eg 1 or "1" - I've
get the same error for both) don't match. Am I right so far?

On the properties form for the calculated column in the format box there is
no entry. I set the format to "General Number". Now I get no records in my
query whether I enter a criteria on not - *and* I cannot set the format back
to blank. So I am worse off than before.

I have very little hair left to spare so all help much appreciated.

Peter.

Many thanks for you reply.

I have inherited the database so have no choice about table design.

I need the numer of days difference between these two date fields that have
a data type of text. Given what you have already said I will use the
calculation not aliases but that still leaves me with a data type mismatch
problem when I enter a criteria.

Can you point me to anything online that I can read to help me understand
what I am doing here?

Maybe I sould mention that there are about half a million records.

Thanks again for you time. Peter.

:

I bet I’ve got something very basic wrong.
Do not use a text field for a date.

I get two “Enter Parameter Value†boxes prompting for A_Date first and then D_Date.
[A_Date] and [D_Date] are aliases. They can be used in follow-on queries
but not in the same query that created them. You must use the calculation
instead.

--
KARL DEWEY
Build a little - Test a little


:

So powerful but sooooo frustrating.

In a Select query I have a calculated field that contains
LOS: DateDiff("d",[A_Date],[D_Date])

A_Date and D_Date are, in turn, calculated fields (they use left(), mid()
and right() to manipulate a table field from yyyymmdd (text data type) to
dd/mm/yyyy (still as text I assume).

When I run the query all looks fine. Then back in query design view I add a
criteria for example >1 or “1†in the criteria row in the LOS field. Now when
I run the query I get two “Enter Parameter Value†boxes prompting for A_Date
first and then D_Date. No matter what I enter the resultant query has just
one all blank record.

What is going on? Why the prompt for input?

I have tried replacing the two aliases A_Date & D_Date in the above
expression with the text manipulation functions. Still the query runs okay
but now I get a Data type mismatch error when I enter a criteria either text
eg "1" or a number eg 1.

Please accept my apologies if this has been answered before – I did look
quite hard.

I bet I’ve got something very basic wrong. Thanks for your help.

Peter

 

[Chuck]

We must be working on a very similar problem - I am calculating Length of Stay in the same manner and while the Datediff functions provides what appears to be correct integer answers I also get a 'type mismatch' when I apply a criteria such as <2 to see only records where the LOS < 2. I have tried a dozen different methods of CInt, CLong, CVar, CStr, Str and several others trying to get the data on both sides of the comparitor to be the same type. I've even used TypeName() to display the datatypes but I cannot seem to convert the result of the Datediff()to a type that I can select using a integer criteria. I'm sure the solution is simple.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Peter]

Chuck,

As you see from my posts I never found the answer to this problem. As you
say it must be simple. I was able to work round the problem by running a
"Make Table" query including all the fields from the original table and
including the Datediff() calculated field as an additional filed in the new
table. I was then able to query the new table in the expected manner. That
is a criteria of say <2 worked in what was the calculated field of the
original table. Hope you understand what I mean.

I realise that this is not very satisfactory but it was ok for me as I am
just analysing data (that is not being added to) that is in Access and not
try to build any kind of application.

If you would like to talk more about this I suggest we take it off-line.
Please feel free to email me at petereverett@@btinternet.com (remove the
surplus @).

Peter