Cast TEXT to NUMBER

[JKwan]

Wondering how to cast a TEXT ("1234") to NUMBER (1234) in a SQL Select. My access database have few fields defined as NUMBERS. However, when I performed a SELECT from EXCEL, those fields are no longer numbers, they are text. I've tried CDBL and CSNG, both were accepted, but don't work. Googled it, they all suggests using CDBL or CSNG??!!

[SamT]

Show us your SQL String/Code

[JKwan]

Here is my SQL, nothing special:

Select [CaseNumber], [DeliveryUnit], [FieldOffice], [Field], [FunctionArea], [PSIClassification], [OGPTier], [PSELevel], [Quarter],
[Title], [DescriptionFacts], [CreatedDate], [EventDate], [LOPCSet], [LOPCEquipmentInvolved], [LOPCEquipmentDetail],
[LOPCExtent], [LOPCProduct], [LOPCProductClassification], [LOPCVolume], [LOPCUnit], [LOPCPerHour_kg], [LOPCEmulsionProduct],
[LOPCEmulsionWaterVolume], [LOPCKPISpill], [LOPCSaltCrusting], [Duration], [Outdoor], [SUSet], [SUEquipmentInvolved],
[SUBypassedManually], [SUFailueMechanism], [SUFailueMechanismDetail], [SUFailureCause], [SUFailureCauseDetail], [PESet],
[PEEquipmentInvolved], [PETypeOfExcursion], [PEExtentExcursion], [CISSet], [CISEquipmentInvolved], [CISFailureMechanism],
[CISFailureMechanismDetail], [CISFailureCause], [CISFailureCauseDetail], [DetectionMethod], [ActualSeverity], [PotentialSeverity],
[EventYear], [EventMonth], [Completed], [ExcludeFromReport], [Deleted], [WeeklyHighlight], [FollowUp], [FollowUpMemo],
[AssignedDate], [CompletedDate], [CompletionComment], [AssignedTo], [LSD], [Section], [Township], [Range], [Meridian], [UWI],
[CaseComment], [Description]
From PSI
Where ((UCase([Deleted]) <> 'Y') or ([Deleted] is Null)) And
([DeliveryUnit] = 'Canada')
Order By [DeliveryUnit], [FieldOffice], [Field], [FunctionArea], [OGPTier], [Quarter], [CaseNumber] DESC


If I run this SQL in Access, these fields ([LOPCVolume], [LOPCPerHour_kg], [LOPCEmulsionWaterVolume], [EventYear]) are ok, as numeric. However, If I do this in Excel, they are TEXT?? I put CDBL and CSNG around them, still TEXT. What is worse is that some of them have 11 decimals??!!! My data only contains one decimal, also, those extra long decimals, they are not zeroes, they look like "random" numbers, if they are zeroes, then at least it is ok.

[ranman256]

CDBL.
its the biggest

[SamT]

Query "Relined" for clarity


Select [CaseNumber], [DeliveryUnit], [FieldOffice], [Field], [FunctionArea], [PSIClassification],
[OGPTier], [PSELevel], [Quarter], [Title], [DescriptionFacts], [CreatedDate], [EventDate], _
[LOPCSet], [LOPCEquipmentInvolved], [LOPCEquipmentDetail], [LOPCExtent], [LOPCProduct], [LOPCProductClassification], [LOPCVolume], [LOPCUnit], [LOPCPerHour_kg], [LOPCEmulsionProduct], [LOPCEmulsionWaterVolume], [LOPCKPISpill], [LOPCSaltCrusting], _
[Duration], [Outdoor], _
[SUSet], [SUEquipmentInvolved], [SUBypassedManually], [SUFailueMechanism], [SUFailueMechanismDetail], [SUFailureCause], [SUFailureCauseDetail], _
[PESet], [PEEquipmentInvolved], [PETypeOfExcursion], [PEExtentExcursion], _
[CISSet], [CISEquipmentInvolved], [CISFailureMechanism], [CISFailureMechanismDetail], [CISFailureCause], [CISFailureCauseDetail], _
[DetectionMethod], [ActualSeverity], [PotentialSeverity], _
[EventYear], [EventMonth], _
[Completed], [ExcludeFromReport], [Deleted], [WeeklyHighlight], [FollowUp], [FollowUpMemo], [AssignedDate], [CompletedDate], [CompletionComment], _
[AssignedTo], _
[LSD], [Section], [Township], [Range], [Meridian], [UWI], _
[CaseComment], [Description]

From PSI
Where ((UCase([Deleted]) <> 'Y') or ([Deleted] is Null)) And ([DeliveryUnit] = 'Canada')
Order By [DeliveryUnit], [FieldOffice], [Field], [FunctionArea], [OGPTier], [Quarter], [CaseNumber] DESC

I don't see where you are assigning a Case Number, so why does it matter what Type it is?

[jonh]

what does "when I performed a SELECT from EXCEL" mean?

[JKwan]

@jonh: What it means is that I construct the SQL within Excel and send that command to Access (something like thru ODBC)
@SamT: I don't understand your comment of "I don't see where you are assigning a Case Number, so why does it matter what Type it is". I am selecting what I need from Access...

[jonh]

[QUOTE=JKwan;344309]@jonh: What it means is that I construct the SQL within Excel and send that command to Access (something like thru ODBC)

and the result is inserted into excel how?

[SamT]

@ Jonh,

I believe to OP is having difficulties constructing the Query in a manner that includes Numericals.

Anyway, now that you're on the scene, I will bow out.

[JKwan]

@jonh: after the SQL execution, the record set is copied via .copyrecordset
@Sam: no I don't have problem constructing the SQL to "include numerals". My query executes fine, however, where my database fields defined as numbers, get posted to Excel as TEXT. This data type, as you know, cannot perform math functions on it, only (so to speak) string functions. Even basic comparisons don't work quite the same, because it is text.

[jonh]

Access might not set the datatypes correctly if all values in a field can't be set to that datatype. If a fields datatype is numeric then obviously values will be numeric (unless your database is corrupt or you've altered the values by query) so values should transfer over as such.

If you are writing values to an existing sheet, what are the cell formats on that sheet? Delete the cells, reset the formats, or copy to a new sheet.

Try using MS Query in Excel, if it's installed, and see if that works.

[JKwan]

The is "no format" of the cells, it is set as default, general? I will give ms query a try. Thank you all.

[SamT]

My query executes fine, however, where my database fields defined as numbers, get posted to Excel as TEXT
Thank you. That is the critical information that we needed to know.

First try Jonh's suggestions.

As a cludge, you might have to use VBA to format the Numerical Columns as Numbers after each Query.

It might be as simple as

Dim Cols As Variant
Dim Cel As Range

Cols =Array("A:A", "B:B", "DG:DG", Etc)
For i = LBound(Cols) to Ubound(Cols)
For Each Cel in Sheets("Sheet1").Range(Cols(i))
If Cel.Row > 1 Then Cel.Value = CDbl(Cel.Value)
Next
Next

[JKwan]

Thank you all for help and suggestions. Runs fine from MSQuery, I think it is something the data got "lost in translation".... I think that I will take Sam's advice and change the format from Excel, after my query.