Text data type to number

[goslincm]

Hi, I am working in the following query but need to find a way to convert the fields [FiscalEndMM] and [FiscalYear] from the text data types that they are, to a number data type so my query will work.

I believe it might be the CAST function that gets used, but not sure how...

SELECT dbo_localUnitAudit.CountyCd, dbo_localUnitAudit.LocalUnitType, dbo_localUnitAudit.LocalUnitCd, dbo_localUnitAudit.FiscalYear, dbo_localUnitAudit.Type, dbo_localUnitAudit.Extension, dbo_localUnitAudit.BiAnnual, dbo_localUnitAudit.AuditReceivedDate, dbo_localUnitAudit.Auditor, dbo_localUnitAudit.Notes, dbo_localUnitAudit.AuditLocation, dbo_LocalUnit.FiscalEndMM, Format(DateSerial([FiscalYear],[FiscalEndMM]+6,0),'mm/dd/yyyy') AS AuditDue, IIf([AuditReceivedDate]>[AuditDue],'LATE',IIf([AuditReceivedDate]<[AuditDue],'TIMELY',IIf([auditdue]<Date() And [auditreceiveddate] Is Null,"NOT YET DUE"))) AS FiledTimely, dbo_localUnitAudit.BiAnnual

FROM dbo_LocalUnit INNER JOIN dbo_localUnitAudit ON (dbo_LocalUnit.LocalUnitCd = dbo_localUnitAudit.LocalUnitCd) AND (dbo_LocalUnit.LocalUnitType = dbo_localUnitAudit.LocalUnitType) AND (dbo_LocalUnit.CountyCd = dbo_localUnitAudit.CountyCd)

WHERE (((dbo_localUnitAudit.FiscalYear) Like "*2006"));

 

[AlexCuse]

I think you want Val(expression) function?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[goslincm]

I don't know how to use that, can you show me?

 

[lespaul]

SELECT Val(SomeFieldWithNumbers) FROM TableName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[goslincm]

I think my problem may be bigger than just a incorrect data type.

When I run my query I'm having trouble defining my expressions in the [Filed timely] field. I'm not catching those records who have a null audit received date.

When they do not have an audit received date, I need it to check to see if the audit due date is greater than the current date, if so, I need it to report "not yet due", otherwise, if audit received date is blank, and audit due date is less than today's date, I need it to indicate "late" as well.

Does this make sense in terms of what I need? I just cannot find a way to deal with my null [audit received date}.

 

[abaldwin]

My suggestion is to build two queries and join the two or use a union query.

Trying to write one "GOD" query is confusing and hard to maintain.

One query for the items that are null and fit the audit date and one for the other.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[SBerthold]

I'd be very careful using VAL() as it is not always sensitive to the local or to the user's decimal seperator format set in the system, when those settings are not in US format.