Help needed with IIF in my query

  • Thread starter Thread starter jannie
  • Start date Start date
J

jannie

I have several fields in my database that are date fields. The data type in
my table for these is date/time and formatted as short date. In my query
(I'll use one field as my example here). The result I want is if there is a
date in the field then say Y for yes, or N for no. Pretty simple. I don't
know why I keep getting a data type mismatch in criteria expression error.
The fields in my table are date fields and formatted as short date. Is there
something wrong with my IIF or can you suggest a better syntax to use. Please
let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I also tried
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N"). Neither one works.
 
Jannie

Are you saying that if a field holds a date, you want to say "Y"?

What about:

IIF(IsDate([YourField])=True, "Y", "N")

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
If any date is good enough, try this:

IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]) = True,"N","Y")
 
I tried exactly what you put here and I still get the data type mismatch
error?????? What do I need to check?

Jerry Whittle said:
If any date is good enough, try this:

IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]) = True,"N","Y")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


jannie said:
I have several fields in my database that are date fields. The data type in
my table for these is date/time and formatted as short date. In my query
(I'll use one field as my example here). The result I want is if there is a
date in the field then say Y for yes, or N for no. Pretty simple. I don't
know why I keep getting a data type mismatch in criteria expression error.
The fields in my table are date fields and formatted as short date. Is there
something wrong with my IIF or can you suggest a better syntax to use. Please
let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I also tried
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N"). Neither one works.
Click to expand...
Click to expand...
 
Jeff I get the data type mismatch error with your formula as well. I must
have something else wrong but I don't know what else to look for.

Jeff Boyce said:
Jannie

Are you saying that if a field holds a date, you want to say "Y"?

What about:

IIF(IsDate([YourField])=True, "Y", "N")

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

jannie said:
I have several fields in my database that are date fields. The data type in
my table for these is date/time and formatted as short date. In my query
(I'll use one field as my example here). The result I want is if there is
a
date in the field then say Y for yes, or N for no. Pretty simple. I don't
know why I keep getting a data type mismatch in criteria expression error.
The fields in my table are date fields and formatted as short date. Is
there
something wrong with my IIF or can you suggest a better syntax to use.
Please
let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I also
tried
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N"). Neither one works.
Click to expand...


.
Click to expand...
 
Is it possible the mismatch is arising from another part of your query?
Assuming you've confirmed that's not the case, then you need to remove
everything from your query that has nothing to do with this problem,
switch to SQL View, and show us the sql statement that causes the error.
I tried exactly what you put here and I still get the data type
mismatch error?????? What do I need to check?

Jerry Whittle said:
If any date is good enough, try this:

IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]) = True,"N","Y")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


jannie said:
I have several fields in my database that are date fields. The data
type in my table for these is date/time and formatted as short
date. In my query (I'll use one field as my example here). The
result I want is if there is a date in the field then say Y for
yes, or N for no. Pretty simple. I don't know why I keep getting a
data type mismatch in criteria expression error. The fields in my
table are date fields and formatted as short date. Is there
something wrong with my IIF or can you suggest a better syntax to
use. Please let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I
also tried IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N").
Neither one works.
Click to expand...
Click to expand...
Click to expand...
 
If this IIF() statement, including the IsDate() test, is returning "data
type mismatch", that implies that the field that's being used is NOT an
Access Date/Time field.

What is the data type in the table for this field?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

jannie said:
Jeff I get the data type mismatch error with your formula as well. I must
have something else wrong but I don't know what else to look for.

Jeff Boyce said:
Jannie

Are you saying that if a field holds a date, you want to say "Y"?

What about:

IIF(IsDate([YourField])=True, "Y", "N")

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

jannie said:
I have several fields in my database that are date fields. The data type
in
my table for these is date/time and formatted as short date. In my
query
(I'll use one field as my example here). The result I want is if there
is
a
date in the field then say Y for yes, or N for no. Pretty simple. I
don't
know why I keep getting a data type mismatch in criteria expression
error.
The fields in my table are date fields and formatted as short date. Is
there
something wrong with my IIF or can you suggest a better syntax to use.
Please
let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I also
tried
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N"). Neither one
works.
Click to expand...


.
Click to expand...
Click to expand...
 
SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG
FROM MANUAL_INPUT_DRILL_DATES
GROUP BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG
HAVING
(((MANUAL_INPUT_DRILL_DATES.SURF_CSG)=IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T")) AND ((MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW)>0))
ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME;


Bob Barrows said:
Is it possible the mismatch is arising from another part of your query?
Assuming you've confirmed that's not the case, then you need to remove
everything from your query that has nothing to do with this problem,
switch to SQL View, and show us the sql statement that causes the error.
I tried exactly what you put here and I still get the data type
mismatch error?????? What do I need to check?

Jerry Whittle said:
If any date is good enough, try this:

IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]) = True,"N","Y")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have several fields in my database that are date fields. The data
type in my table for these is date/time and formatted as short
date. In my query (I'll use one field as my example here). The
result I want is if there is a date in the field then say Y for
yes, or N for no. Pretty simple. I don't know why I keep getting a
data type mismatch in criteria expression error. The fields in my
table are date fields and formatted as short date. Is there
something wrong with my IIF or can you suggest a better syntax to
use. Please let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I
also tried IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N").
Neither one works.
Click to expand...
Click to expand...

--
HTH,
Bob Barrows


.
Click to expand...
 
HAVING(((MANUAL_INPUT_DRILL_DATES.SURF_CSG)=IIf(IsNull([MANUAL_INPUT_DRILL_DAT
ES]![SURF_CSG])=True,"N","T"))


This is the only relevant part.
What are the only two possible values that will be returned by the Iif()
expression?
Answer: either "N" or "T"
Does either of those two values appear to be a date?
Answer: No
How could MANUAL_INPUT_DRILL_DATES.SURF_CSG, a Date/Time field, possibly
contain either of those two values?
Answer: It couldn't, hence, the datatype mismatch

So, I think we need to get back to where you properly state your
requirement. Based on this SQL statement, it seems that you actually
want to limit the rows returned to those where SURF_CSG contains a date.
However, in your original post, you said
if there is a date in the field then say Y for yes, or N for no
Click to expand...

There is a definite contradiction here. We've been going under the
assumption that you wanted a new column to appear in the results. If so,
you have put this Iif() expression in the wrong place: in the criteria
row of the SURG_CSG column in the query grid. That is the wrong place
for it. It needs to be in the top row of a new column in your query
grid, with an "alias" to provide a column name, like this:

DateExists:IIf(IsNull([MANUAL_INPUT_DRILL_DATES].[SURF_CSG])=True,"N","T
"))

So that the sql statement looks like this:
SELECT ...,
IIf(IsNull([MANUAL_INPUT_DRILL_DATES].[SURF_CSG])=True,"N","T")) As
DateExists
FROM MANUAL_INPUT_DRILL_DATES
 
In my table it says:
Field Name Data Type General Box at the bottom
SURF_CSG Date/Time the format at the bottom says Short Date
Jannie

Jeff Boyce said:
If this IIF() statement, including the IsDate() test, is returning "data
type mismatch", that implies that the field that's being used is NOT an
Access Date/Time field.

What is the data type in the table for this field?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

jannie said:
Jeff I get the data type mismatch error with your formula as well. I must
have something else wrong but I don't know what else to look for.

Jeff Boyce said:
Jannie

Are you saying that if a field holds a date, you want to say "Y"?

What about:

IIF(IsDate([YourField])=True, "Y", "N")

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have several fields in my database that are date fields. The data type
in
my table for these is date/time and formatted as short date. In my
query
(I'll use one field as my example here). The result I want is if there
is
a
date in the field then say Y for yes, or N for no. Pretty simple. I
don't
know why I keep getting a data type mismatch in criteria expression
error.
The fields in my table are date fields and formatted as short date. Is
there
something wrong with my IIF or can you suggest a better syntax to use.
Please
let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I also
tried
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N"). Neither one
works.


.
Click to expand...
Click to expand...


.
Click to expand...
 
Try this for starters:

SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG,
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T") as SC
FROM MANUAL_INPUT_DRILL_DATES
WHERE MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW > 0
ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME;

Your statement below has the HAVING clause checking the same field against
itself. Also that field is a Date, but you are checking it against T and N
text.

The Group By really isn't needed unless you are trying to get rid of
duplicates. A SELECT DISTINCT is cleaner if needed.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


jannie said:
SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG
FROM MANUAL_INPUT_DRILL_DATES
GROUP BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG
HAVING
(((MANUAL_INPUT_DRILL_DATES.SURF_CSG)=IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T")) AND ((MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW)>0))
ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME;


Bob Barrows said:
Is it possible the mismatch is arising from another part of your query?
Assuming you've confirmed that's not the case, then you need to remove
everything from your query that has nothing to do with this problem,
switch to SQL View, and show us the sql statement that causes the error.
I tried exactly what you put here and I still get the data type
mismatch error?????? What do I need to check?

:

If any date is good enough, try this:

IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]) = True,"N","Y")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have several fields in my database that are date fields. The data
type in my table for these is date/time and formatted as short
date. In my query (I'll use one field as my example here). The
result I want is if there is a date in the field then say Y for
yes, or N for no. Pretty simple. I don't know why I keep getting a
data type mismatch in criteria expression error. The fields in my
table are date fields and formatted as short date. Is there
something wrong with my IIF or can you suggest a better syntax to
use. Please let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I
also tried IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N").
Neither one works.
Click to expand...

--
HTH,
Bob Barrows


.
Click to expand...
Click to expand...
 
Exactly right!!!! This works. Now all I have to do is get the other date
fields to do the same thing. So do I repeat the IIf underneath the one you
wrote with SC for the rest of the fields? Like keep adding IIf's until I get
all of them in the SQL?

Jerry Whittle said:
Try this for starters:

SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG,
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T") as SC
FROM MANUAL_INPUT_DRILL_DATES
WHERE MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW > 0
ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME;

Your statement below has the HAVING clause checking the same field against
itself. Also that field is a Date, but you are checking it against T and N
text.

The Group By really isn't needed unless you are trying to get rid of
duplicates. A SELECT DISTINCT is cleaner if needed.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


jannie said:
SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG
FROM MANUAL_INPUT_DRILL_DATES
GROUP BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG
HAVING
(((MANUAL_INPUT_DRILL_DATES.SURF_CSG)=IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T")) AND ((MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW)>0))
ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME;


Bob Barrows said:
Is it possible the mismatch is arising from another part of your query?
Assuming you've confirmed that's not the case, then you need to remove
everything from your query that has nothing to do with this problem,
switch to SQL View, and show us the sql statement that causes the error.

jannie wrote:
I tried exactly what you put here and I still get the data type
mismatch error?????? What do I need to check?

:

If any date is good enough, try this:

IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]) = True,"N","Y")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have several fields in my database that are date fields. The data
type in my table for these is date/time and formatted as short
date. In my query (I'll use one field as my example here). The
result I want is if there is a date in the field then say Y for
yes, or N for no. Pretty simple. I don't know why I keep getting a
data type mismatch in criteria expression error. The fields in my
table are date fields and formatted as short date. Is there
something wrong with my IIF or can you suggest a better syntax to
use. Please let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I
also tried IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N").
Neither one works.

--
HTH,
Bob Barrows


.
Click to expand...
Click to expand...
Click to expand...
 
Thanks. That rules that out!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

jannie said:
In my table it says:
Field Name Data Type General Box at the bottom
SURF_CSG Date/Time the format at the bottom says Short Date
Jannie

Jeff Boyce said:
If this IIF() statement, including the IsDate() test, is returning "data
type mismatch", that implies that the field that's being used is NOT an
Access Date/Time field.

What is the data type in the table for this field?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

jannie said:
Jeff I get the data type mismatch error with your formula as well. I
must
have something else wrong but I don't know what else to look for.

:

Jannie

Are you saying that if a field holds a date, you want to say "Y"?

What about:

IIF(IsDate([YourField])=True, "Y", "N")

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have several fields in my database that are date fields. The data
type
in
my table for these is date/time and formatted as short date. In my
query
(I'll use one field as my example here). The result I want is if
there
is
a
date in the field then say Y for yes, or N for no. Pretty simple. I
don't
know why I keep getting a data type mismatch in criteria expression
error.
The fields in my table are date fields and formatted as short date.
Is
there
something wrong with my IIF or can you suggest a better syntax to
use.
Please
let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I
also
tried
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N"). Neither one
works.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Yes and No. ;-)

For the data structure that you have in the table now, yes. Just put
something like that in each field name.

But in the long term, your table structure is wrong. You should not have a
different date field for each piece of information. What happens to your
queries, forms, and reports when someone wants to add a new date to track?
You have to rebuild them all.

Instead you should have a table with something like below:

WELL_ID Event_Date Event_Type
123 1/1/2010 SURF_CSG
123 1/9/2019 PROD_CSG_SET

And so on. This way you won't have empty fields. You add the date and event
when needed.

This table should be linked to a table of Wells. Then the Wells table should
be linked to a table of Platforms. I'm assuming that a Platform can have more
than one Well.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding much
further on this database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


jannie said:
Exactly right!!!! This works. Now all I have to do is get the other date
fields to do the same thing. So do I repeat the IIf underneath the one you
wrote with SC for the rest of the fields? Like keep adding IIf's until I get
all of them in the SQL?

Jerry Whittle said:
Try this for starters:

SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG,
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T") as SC
FROM MANUAL_INPUT_DRILL_DATES
WHERE MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW > 0
ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME;

Your statement below has the HAVING clause checking the same field against
itself. Also that field is a Date, but you are checking it against T and N
text.

The Group By really isn't needed unless you are trying to get rid of
duplicates. A SELECT DISTINCT is cleaner if needed.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


jannie said:
SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG
FROM MANUAL_INPUT_DRILL_DATES
GROUP BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG
HAVING
(((MANUAL_INPUT_DRILL_DATES.SURF_CSG)=IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T")) AND ((MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW)>0))
ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME;


:

Is it possible the mismatch is arising from another part of your query?
Assuming you've confirmed that's not the case, then you need to remove
everything from your query that has nothing to do with this problem,
switch to SQL View, and show us the sql statement that causes the error.

jannie wrote:
I tried exactly what you put here and I still get the data type
mismatch error?????? What do I need to check?

:

If any date is good enough, try this:

IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]) = True,"N","Y")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have several fields in my database that are date fields. The data
type in my table for these is date/time and formatted as short
date. In my query (I'll use one field as my example here). The
result I want is if there is a date in the field then say Y for
yes, or N for no. Pretty simple. I don't know why I keep getting a
data type mismatch in criteria expression error. The fields in my
table are date fields and formatted as short date. Is there
something wrong with my IIF or can you suggest a better syntax to
use. Please let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I
also tried IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N").
Neither one works.

--
HTH,
Bob Barrows


.
Click to expand...
Click to expand...
Click to expand...
 
With the help you gave me with my SQL yesterday I was able to get the columns
to line up properly and give me the results I needed. I even was able to add
all the other ones as well. The query runs exactly like I need it to. What is
up with this database is it is actually written in DSS and dynamically pulls
data from Landmark TOW and Aries programs. (Tables are from there). This is a
manual table that I made on the back side of DSS which uses Access. (Maybe
that's an add in or something like that). I'm not that guru. But all I know
is if I go to queries and select access I end up in Access and can write
queries much easier than in DSS. DSS is it's own animal. I am learning SQL
and I'm really green on this but I'm trying hard to learn and hopefully
attend a class soon to help me more. For now you guys are my help and I'm so
thankful that you are available to help me when I can't figure something out.
I print tons of these posts out to help me learn. I'm very glad ya'll are
here to help me!!! I put a new post today 2/2 to build on this query a little
more. I need to finish up my report for everyone and I need total counts on
all my Y's and N's for each column. So hopefully you can help me with the
correct syntax for that. I tried count and sum but I keep getting some kind
of paramater PLATFORM_NAME error. Probably cause I don't know exactly where
to put my statement or how to say it right.

Jerry Whittle said:
Yes and No. ;-)

For the data structure that you have in the table now, yes. Just put
something like that in each field name.

But in the long term, your table structure is wrong. You should not have a
different date field for each piece of information. What happens to your
queries, forms, and reports when someone wants to add a new date to track?
You have to rebuild them all.

Instead you should have a table with something like below:

WELL_ID Event_Date Event_Type
123 1/1/2010 SURF_CSG
123 1/9/2019 PROD_CSG_SET

And so on. This way you won't have empty fields. You add the date and event
when needed.

This table should be linked to a table of Wells. Then the Wells table should
be linked to a table of Platforms. I'm assuming that a Platform can have more
than one Well.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding much
further on this database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


jannie said:
Exactly right!!!! This works. Now all I have to do is get the other date
fields to do the same thing. So do I repeat the IIf underneath the one you
wrote with SC for the rest of the fields? Like keep adding IIf's until I get
all of them in the SQL?

Jerry Whittle said:
Try this for starters:

SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG,
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T") as SC
FROM MANUAL_INPUT_DRILL_DATES
WHERE MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW > 0
ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME;

Your statement below has the HAVING clause checking the same field against
itself. Also that field is a Date, but you are checking it against T and N
text.

The Group By really isn't needed unless you are trying to get rid of
duplicates. A SELECT DISTINCT is cleaner if needed.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG
FROM MANUAL_INPUT_DRILL_DATES
GROUP BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.PROD_CSG_SET,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG
HAVING
(((MANUAL_INPUT_DRILL_DATES.SURF_CSG)=IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T")) AND ((MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW)>0))
ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME;


:

Is it possible the mismatch is arising from another part of your query?
Assuming you've confirmed that's not the case, then you need to remove
everything from your query that has nothing to do with this problem,
switch to SQL View, and show us the sql statement that causes the error.

jannie wrote:
I tried exactly what you put here and I still get the data type
mismatch error?????? What do I need to check?

:

If any date is good enough, try this:

IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]) = True,"N","Y")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have several fields in my database that are date fields. The data
type in my table for these is date/time and formatted as short
date. In my query (I'll use one field as my example here). The
result I want is if there is a date in the field then say Y for
yes, or N for no. Pretty simple. I don't know why I keep getting a
data type mismatch in criteria expression error. The fields in my
table are date fields and formatted as short date. Is there
something wrong with my IIF or can you suggest a better syntax to
use. Please let me know. Thanks. Here is my formula:
IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>#12/1/1900#,"Y","N"). I
also tried IIf([MANUAL_INPUT_DRILL_DATES]![SURF_CSG]>0,"Y","N").
Neither one works.

--
HTH,
Bob Barrows


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top