Dates

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I have created a query that draws from two two other
queries.
One of the fields used in the combined query is a date
field in the initial query. However, when this date field
is returned in the combined query it is returned as a text
field and not a date field. This field was generated
using an iif statement the inital query date that was
based on a date field.
What do I need to do to have it returned as a date field
and in which query do I need to make this necesary
adjustment.
Please advise
Yours truly,
Mark Bristoll
 
Rob,

where do i use the cdate () function in the inital query
or the combine query.
How does the cdate() function deal will null values.

Some of my date fields have not yet been populated.

Please advise.

Mark
 
Use the CDate() in the query that calls the other queries. From what it
sounds like, this initial query is where you are having the issue of it
returning as text instead of a date.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
I have created a query that draws from two two other
queries.
One of the fields used in the combined query is a date
field in the initial query. However, when this date field
is returned in the combined query it is returned as a text
field and not a date field. This field was generated
using an iif statement the inital query date that was
based on a date field.
What do I need to do to have it returned as a date field
and in which query do I need to make this necesary
adjustment.
Please advise
Yours truly,
Mark Bristoll
Click to expand...

Please post the SQL of the query. I have some hazy guesses at what
might be the problem but since you don't say what you're doing, it's
rather hard to say what you're doing wrong!
 
Attached is a copy of the SQL of the query. Let me know if
you require any additional information.

Yours truly,
Mark
SELECT [Final Gradng Grouping].ProjCode, [Final Gradng
Grouping].Phase, [Final Gradng Grouping].Street, [Final
Gradng Grouping].CityCode, [Final Gradng
Grouping].ActualDateClosed, [Final Gradng Grouping].
[Building Permit], [Final Gradng Grouping].[Occupancy
Permit], [Final Gradng Grouping].[Final Building
Inspection], [Final Gradng Grouping].[Final Grading
Certificate], [Grading Deposits].MajorCode, [Grading
Deposits].MinorCode, Sum([Grading Deposits].Amount) AS
SumOfAmount
FROM [Final Gradng Grouping] LEFT JOIN [Grading Deposits]
ON ([Final Gradng Grouping].ProjID=[Grading
Deposits].ProjID) AND ([Final Gradng Grouping].Phase=
[Grading Deposits].Phase)
GROUP BY [Final Gradng Grouping].ProjCode, [Final Gradng
Grouping].Phase, [Final Gradng Grouping].Street, [Final
Gradng Grouping].CityCode, [Final Gradng
Grouping].ActualDateClosed, [Final Gradng Grouping].
[Building Permit], [Final Gradng Grouping].[Occupancy
Permit], [Final Gradng Grouping].[Final Building
Inspection], [Final Gradng Grouping].[Final Grading
Certificate], [Grading Deposits].MajorCode, [Grading
Deposits].MinorCode;
 
Attached is a copy of the SQL of the query. Let me know if
you require any additional information.

Yours truly,
Mark
SELECT [Final Gradng Grouping]
Click to expand...

Well... that doesn't help, because it doesn't contain the offending
IIF statement. Could you post that IIF statement, or the SQL of the
query that contains it?

I *suspect* that the IIF is returning a date if the condition is true,
and a text string if it's not: if the datatypes of the two returned
values disagree, Access will use the "lowest common denominator", in
this case Text. The reason is that the IIF must return *something* -
and it can't return text in a Date/Time field, so it must return Text.
 
Sorry for not providing you with the proper info.

The iif statement is as follows:
Occupancy Permit: IIf([Final Grading]!TaskNo=109,[Final
Grading]!ActualEndDate,"")

Your analysis is correct. how do i get the iif statement
to return a date if the condition is true and a date
friendly value if the condition is false?

Yours truly,
Mark
-----Original Message-----
Attached is a copy of the SQL of the query. Let me know if
you require any additional information.

Yours truly,
Mark
SELECT [Final Gradng Grouping]
Click to expand...

Well... that doesn't help, because it doesn't contain the offending
IIF statement. Could you post that IIF statement, or the SQL of the
query that contains it?

I *suspect* that the IIF is returning a date if the condition is true,
and a text string if it's not: if the datatypes of the two returned
values disagree, Access will use the "lowest common denominator", in
this case Text. The reason is that the IIF must return *something* -
and it can't return text in a Date/Time field, so it must return Text.


.
Click to expand...
 
The iif statement is as follows:
Occupancy Permit: IIf([Final Grading]!TaskNo=109,[Final
Grading]!ActualEndDate,"")

Your analysis is correct. how do i get the iif statement
to return a date if the condition is true and a date
friendly value if the condition is false?
Click to expand...

Replace the string "" with the typeless variant NULL.
 
John,

Thank you for all your help.

Yours truly,
Mark Bristoll
-----Original Message-----
The iif statement is as follows:
Occupancy Permit: IIf([Final Grading]!TaskNo=109,[Final
Grading]!ActualEndDate,"")

Your analysis is correct. how do i get the iif statement
to return a date if the condition is true and a date
friendly value if the condition is false?
Click to expand...

Replace the string "" with the typeless variant NULL.


.
Click to expand...
 
Back
Top