how to do data type conversions in access

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Rance (ntg-rhall_at_nctc.net)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 14:51:10 -0800

I have a column in a database called case_count, when the database was setup it was formatted as a charfield with 8 spaces, but the values should have been integers. so, what ever the number of cases the number is padded with enough extra zeros to make 8 chars (eg. '00000034' as opposed to 34)

what I need is the result of "SELECT sum(CASE_COUNT) FROM order_detal WHERE order_number='whatever'"

I know that this sql statement will return an error (because the char fields cant be summed)

inside Access I could so a sql statement and pipe the resulting strings into a recordset, and then do a

While (Not (rs.EOF))
do some stuff
rs.MoveNext
Wend

its the do some stuff part that I need help with

I need to save the string without its leading zeros (and I wont know how many there are)
and then convert the string to a number
then add the number to the counter

when Im done I should have a proper case count from the order

if anyone has a better approach than this, Im all ears.

P.S. if you can tell me how to trigger this VB procedure just prior to printing the report its for I would be even more greatful.

Relevant Pages

  • Re: Error using " in .open with ADODB.Recordset
    ... recordset but I'm getting hung up on the SQL statement. ... query that has a string as a condition. ... Dim mrk As String ...
    (microsoft.public.data.ado)
  • Re: SQL Statement with LIKE %
    ... short string is contained within a bigger field in Access databases. ... But how would I set up the SQL statement if I was trying to find ... the vbscript code that results in the same statement. ... My real problem is that I have a string named AgentString as: ...
    (microsoft.public.inetserver.asp.general)
  • Re: Too few parameters, expected n when executing SQL from VBA
    ... desired SQL statement, either, so I suggest a completely different approach ... Public Sub TestQuery(ByVal strFieldName As String) ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help with SQL statement
    ... > Dim Product As String, TarWgt As Double, FileNum As ... > Exit Sub ... Your SQL statement doesn't work because it is not an UPDATE you need to ... directly from the combo box's Columnto the other text boxes, ...
    (microsoft.public.access.modulesdaovba)
  • RE: Get Values SQL in table
    ... > database engine won't be able to parse the string correctly before executing ... within the SQL statement or the table that is the data source. ... > See http://www.QBuilt.com for all your database needs. ...
    (microsoft.public.access.modulesdaovba)