sql problem

i have an update querry that should pull number out of a string.the strings
are standardized(same). i thought it would be good to pull out the start
position of the nummber and the end position of the number.

here is the code for start position:

Public Function isPocetakBroja(ByVal IngFactor As String) As Long

Dim pocetakBroja, krajBroja, drugipocetakBroja As Long
'assumes that the FactorSum value is a number, and is a Long Integer.
Select Case Left(IngFactor, 8)
Case Is = "dov. zal"
pocetakBroja = InStr(10, IngFactor, "dana (", vbTextCompare) + 6
drugipocetakBroja = InStr(10, IngFactor, "dana (>", vbTextCompare) + 7
If drugipocetakBroja > pocetakBroja Then
isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
Else
isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
End If
Case Is = "dovoljno"
pocetakBroja = InStr(5, IngFactor, "no (", vbTextCompare) + 4
drugipocetakBroja = InStr(5, IngFactor, "no (>", vbTextCompare) + 5
If drugipocetakBroja > pocetakBroja Then
isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
Else
isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
'...here is type mismatch
End If
Case Is = "sti�e da"
isPocetakBroja = 5
Case Else 'use this if you need a default for records outside of all
isPocetakBroja = 999
End Select

End Function

the strings look like:

dovoljno (4 kom), poslije vi�e ne.
sti�e za 14 dana
dovoljno (11 kom)
dovoljno (>100 kom)

but i get a erron saying :
run time error 13

type mismatch

in line 20

where is the problem

how to solve it?

thx


0
nokia3650
4/7/2005 2:01:08 PM
access.conversion 3038 articles. 0 followers. Follow

3 Replies
751 Views

Similar Articles

[PageSpeed] 14

Your code is much less difficult to read if you indent it properly.
Other comments below refer to the lines marked with <<<<

Public Function isPocetakBroja(ByVal IngFactor As String) As Long
  Dim pocetakBroja, krajBroja, drugipocetakBroja As Long '<<<<1
  'assumes that the FactorSum value is a number, and is a Long Integer.

  Select Case Left(IngFactor, 8)
    Case Is = "dov. zal"
      pocetakBroja = InStr(10, IngFactor, "dana (", vbTextCompare) + 6
      drugipocetakBroja = InStr(10, IngFactor, _
          "dana (>", vbTextCompare) + 7
      If drugipocetakBroja > pocetakBroja Then
        isPocetakBroja = Right(Left(IngFactor, krajBroja), _
            drugipocetakBroja)  '<<<<2
      Else
        isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
      End If
    Case Is = "dovoljno"
      pocetakBroja = InStr(5, IngFactor, "no (", vbTextCompare) + 4
      drugipocetakBroja = InStr(5, IngFactor, _
          "no (>", vbTextCompare) + 5
      If drugipocetakBroja > pocetakBroja Then
        isPocetakBroja = Right(Left(IngFactor, krajBroja), _
           drugipocetakBroja)
      Else
        isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
        '...here is type mismatch
      End If
    Case Is = "sti�e da"
      isPocetakBroja = 5
    Case Else 'use this if you need a default for records outside of all
      isPocetakBroja = 999
  End Select
End Function


1) This line
>Dim pocetakBroja, krajBroja, drugipocetakBroja As Long
declares pocetakBroja and krajBroja as Variants and only
drugipocetakBroja as a Long, which is probably not what you intend. 

2) In this line and several others, the assignment to isPocetakBroja
requires a Long but you are are using Right(), which is a
string-handling function. So the first thing to do is exclude the
possibility that the "Right(Left())" expression is returning a string
that does not represent a number. When the code breaks, click Debug,
open the Immediate pane, type
    ? Right(Left(IngFactor, krajBroja), pocetakBroja)
and hit Enter. If the result is not a valid number, either your code
isn't quite right or your data isn't quite as you expect. 



On Thu, 7 Apr 2005 16:01:08 +0200, "nokia3650" <rpatrcev@globalnet.hr>
wrote:

>i have an update querry that should pull number out of a string.the strings
>are standardized(same). i thought it would be good to pull out the start
>position of the nummber and the end position of the number.
>
>here is the code for start position:
>
>Public Function isPocetakBroja(ByVal IngFactor As String) As Long
>
>Dim pocetakBroja, krajBroja, drugipocetakBroja As Long
>'assumes that the FactorSum value is a number, and is a Long Integer.
>Select Case Left(IngFactor, 8)
>Case Is = "dov. zal"
>pocetakBroja = InStr(10, IngFactor, "dana (", vbTextCompare) + 6
>drugipocetakBroja = InStr(10, IngFactor, "dana (>", vbTextCompare) + 7
>If drugipocetakBroja > pocetakBroja Then
>isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
>Else
>isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
>End If
>Case Is = "dovoljno"
>pocetakBroja = InStr(5, IngFactor, "no (", vbTextCompare) + 4
>drugipocetakBroja = InStr(5, IngFactor, "no (>", vbTextCompare) + 5
>If drugipocetakBroja > pocetakBroja Then
>isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
>Else
>isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
>'...here is type mismatch
>End If
>Case Is = "sti�e da"
>isPocetakBroja = 5
>Case Else 'use this if you need a default for records outside of all
>isPocetakBroja = 999
>End Select
>
>End Function
>
>the strings look like:
>
>dovoljno (4 kom), poslije vi�e ne.
>sti�e za 14 dana
>dovoljno (11 kom)
>dovoljno (>100 kom)
>
>but i get a erron saying :
>run time error 13
>
>type mismatch
>
>in line 20
>
>where is the problem
>
>how to solve it?
>
>thx
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
0
John
4/7/2005 6:28:11 PM
"nokia3650" <rpatrcev@globalnet.hr> д����Ϣ����
:d33e5q$ph9$1@magcargo.vodatel.hr...
> i have an update querry that should pull number out of a string.the
strings
> are standardized(same). i thought it would be good to pull out the start
> position of the nummber and the end position of the number.
>
> here is the code for start position:
>
> Public Function isPocetakBroja(ByVal IngFactor As String) As Long
>
> Dim pocetakBroja, krajBroja, drugipocetakBroja As Long
> 'assumes that the FactorSum value is a number, and is a Long Integer.
> Select Case Left(IngFactor, 8)
> Case Is = "dov. zal"
> pocetakBroja = InStr(10, IngFactor, "dana (", vbTextCompare) + 6
> drugipocetakBroja = InStr(10, IngFactor, "dana (>", vbTextCompare) + 7
> If drugipocetakBroja > pocetakBroja Then
> isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
> Else
> isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
> End If
> Case Is = "dovoljno"
> pocetakBroja = InStr(5, IngFactor, "no (", vbTextCompare) + 4
> drugipocetakBroja = InStr(5, IngFactor, "no (>", vbTextCompare) + 5
> If drugipocetakBroja > pocetakBroja Then
> isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
> Else
> isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
> '...here is type mismatch
> End If
> Case Is = "sti�e da"
> isPocetakBroja = 5
> Case Else 'use this if you need a default for records outside of all
> isPocetakBroja = 999
> End Select
>
> End Function
>
> the strings look like:
>
> dovoljno (4 kom), poslije vi�e ne.
> sti�e za 14 dana
> dovoljno (11 kom)
> dovoljno (>100 kom)
>
> but i get a erron saying :
> run time error 13
>
> type mismatch
>
> in line 20
>
> where is the problem
>
> how to solve it?
>
> thx
>
>


0
yong362
5/24/2005 1:09:30 AM
"nokia3650" <rpatrcev@globalnet.hr> д����Ϣ����
:d33e5q$ph9$1@magcargo.vodatel.hr...
> i have an update querry that should pull number out of a string.the
strings
> are standardized(same). i thought it would be good to pull out the start
> position of the nummber and the end position of the number.
>
> here is the code for start position:
>
> Public Function isPocetakBroja(ByVal IngFactor As String) As Long
>
> Dim pocetakBroja, krajBroja, drugipocetakBroja As Long
> 'assumes that the FactorSum value is a number, and is a Long Integer.
> Select Case Left(IngFactor, 8)
> Case Is = "dov. zal"
> pocetakBroja = InStr(10, IngFactor, "dana (", vbTextCompare) + 6
> drugipocetakBroja = InStr(10, IngFactor, "dana (>", vbTextCompare) + 7
> If drugipocetakBroja > pocetakBroja Then
> isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
> Else
> isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
> End If
> Case Is = "dovoljno"
> pocetakBroja = InStr(5, IngFactor, "no (", vbTextCompare) + 4
> drugipocetakBroja = InStr(5, IngFactor, "no (>", vbTextCompare) + 5
> If drugipocetakBroja > pocetakBroja Then
> isPocetakBroja = Right(Left(IngFactor, krajBroja), drugipocetakBroja)
> Else
> isPocetakBroja = Right(Left(IngFactor, krajBroja), pocetakBroja)
> '...here is type mismatch
> End If
> Case Is = "sti�e da"
> isPocetakBroja = 5
> Case Else 'use this if you need a default for records outside of all
> isPocetakBroja = 999
> End Select
>
> End Function
>
> the strings look like:
>
> dovoljno (4 kom), poslije vi�e ne.
> sti�e za 14 dana
> dovoljno (11 kom)
> dovoljno (>100 kom)
>
> but i get a erron saying :
> run time error 13
>
> type mismatch
>
> in line 20
>
> where is the problem
>
> how to solve it?
>
> thx
>
>


0
LINQIONG
5/24/2005 1:37:32 AM
Reply:

Similar Artilces:

Calling Dex Procedures from SQL
Is there any way to call dex procs from SQL or VB code? Jed There is an undocumented and unsupported method of calling Dex code from VBA. However, I know of no method of calling Dexterity from SQL. Please email me if you want an example, just remove the online. from the address below. David Musgrave [MSFT] Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions policy. This p...

is SQL 2000 architecture different SQL Server 2005 / 2008 architecture?
Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I have been told is a good book. I am going backwards... reading about the sql server architecture in chapter 3 of the book. As I'm reading... I was wondering is it different SQL Server 2000 vs SQL Server 2005/2008? Also where can I find a book/or link that can give me more details about the internals on architecture? Thank you norm (normanchan@gmail.com) writes: > Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I > have been told is a good book. I am g...

Internet Free/Busy problem
Hi, I'm trying to use the MS Internet Free/Busy Service. I have registered successfully at the website and added a couple of people. Unfortunately Outloook 2003 has the following two Internet Free/Busy options greyed out: Publish and search using Microsoft Office Internet Free/Busy Service (Manage button is also disabled) Request free/busy information in meeting informations ( greyed out but on) Any ideas? They discontinued the IFB service as few were using it. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert lat...

Why not answer printing problems
I have been waiting for an answer of how to print register, other people are trying to print home page, etc. Is this a problem that MS Money has ? In microsoft.public.money, fordfitz wrote: >I have been waiting for an answer of how to print register, other people >are trying to print home page, etc. Is this a problem that MS Money has ? A really neat way to find prior postings in this group is to use the Google Usenet Groups archive. http://groups.google.com/advanced_group_search enter words that you think would be in relevant posts into the "all of the words" box. Enter...

Problem when running the sample data macro
In order to install PA I´m trying to install sample data from the Project Accounting Sample Data.mac for the Great Plains sample company. So I can learn how to use it myself. But when the macro is running we have the following message and the macro process stops: "An unexpected modal dialog appeared. (Line# 5480 )" Could someone help me about this? It is quite important for me to implement PA module. So I will appreciate if you can help me to run this macro properly. Regards There are some important things to do prior to running the macro. 1. Make sure the tax tables...

File Size problem #2
I have a simple spreadsheet that started out 1.5 MB....then I starte adding working sheets and VLOOKUP's.,....next thing I know it come back saying 7 MB. I went back and copied, paste special, values of th results of the VLOOKUP, but still the file size did not go down. I should since I am virtually going back to the way it was in its 1.5 M form. How can I reduce file size? Thanks for any advice...... Richar -- Message posted from http://www.ExcelForum.com "gacollege >" <<gacollege.10a6rk@excelforum-nospam.com> wrote in message news:gacollege.10a6rk@excelforum-no...

protection problem
I am protecting a worksheet with UserInterfaceOnly set to true so VBA can change cells. I am not adding any objects. When I try to run the macro I get a runtime error 1004. Cannot change protected cells. I am also using on action to make a custom toolbar. Would that cause the error? Any suggestions around it instead of turning off protection before the cell is overridden .. See one response at your first post. Robert Lee wrote: > > I am protecting a worksheet with UserInterfaceOnly set to > true so VBA can change cells. I am not adding any objects. > When I try to run t...

Date field problem
I created an Access 97 database a year ago and it always worked fine. Since this morning, it does not work anymore. I received the error : This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. After a year, How can it become to complex to be evaluated? I troubled shoot a bit to find out the source of the problem. It does not accept my date format. Here an exemple : YearWeek: Year([MinOfDateReceived]) &am...

Moving customizations from SQL 2k to SQL 2k5 CRM
This may be a non-issue but I wanted to check to see if anyone has done this and ran into issues. I have the XML customization files from our previous install of CRM 3.0 with SQL 2k database. I now want to import and publish these same customizations to CRM 3.0 on SQL 2k5. Has anyone done this or can point me to documentation that tells about the success or issues for doing this? Thanks. On 7 Feb., 06:35, Chris Treanor <ChrisTrea...@discussions.microsoft.com> wrote: > This may be a non-issue but I wanted to check to see if anyone has done this > and ran into issues. > &...

CRM 3.0 Install Error with SQL Reporting Services and SQL 2005
Hi, Doing a CRM 3.0 Server installation. During the last page of the install wizard (or what I assume is the last page) when I goes through and does verification I get a red X next to SQL Reporting Services. The message I receive when I look at the details is: ***** The specified path is not a metabase path. Parameter name: path ***** Now SRS is installed and working. I put in the URL: http://<servername>/reportserver and every other variation on that I can find. Thoughts? Alan try http://servername/reports also assuming you did not use ssl... =======================...

Problem with Turkish Excel File
Hi to all, I'm some excel files generated by a report servers. These reports are generated in Turkish language. If I open them by using English Microsoft Excel (Microsoft Office Excel 2007 (12.0.6535.5002) SP1 MSO (12.0.6535.5002)) I can see them properly with table, images and format properties. If I open them by using Turkish Microsoft Excel (TRK Microsoft Office Excel 2007 (12.0.6545.5000) SP2 MSO (12.0.6545.5004)) I'm not able to see them properly..in particular no table is showed nor the images and so on. I found here http://groups.google.com/group/microsoft.public.excel/browse_...

excel chart x/y variable problem
I am trying to plot variables on my x and y axis and have the field b standard... such as site = 1 2 3 4 5 value 1 = 10 1 6 8 3 value 2 = 2 6 3 2 9 with value 1 being on the x axis, and value 2 being on the y axis an the site # being compared between the 2. I assume you can do it using x y scatter but how??? Any help -- Message posted from http://www.ExcelForum.com If I understand you correctly, plot the value-1 and value-2 in a XY scatter chart. Then, use the site #s as data labels. To simplify the job, use either XY Chartlabeler (www.appspro.com) or Chart Tools (www.j-walk.com)....

OleMainThreadWndName Problem
We have an MFC app. On some PC's running NT4.0 SP6a, we find that although the app appears to have "terminated", in fact it is still invisibly running according TaskManager. We have no problems terminating the apps from TaskManager, no "End Task" recquired. But if we forget to do this, NT 4.0 on shutting done cannot terminate the app and windows pops up saying that for app "OleMainThreadWndName" it cannot shut it down and do we wish to "End Task"? My thoughts 1. I think these are the same thing. Some residue of our app does not terminate. We don...

problem with NavigateUrl
hi asp.net 3.5 On my site I have a menu control where I've put all the menu controls. The problem is that when staying on one page and navigating to another page, that page is not found: If I'm on ~/Pages/MyPage.aspx and wants to navigate to ~/Admin/SqlExceptions.aspx Then I get an error saying that ~/Pages/SqlExceptions.aspx don't exists - of course that webpage doesn't exists, it hasa the wrong directory. But I did specify the correct directory in the mennu control, that makes it more confusing.. here is markup of the menu controls in trouble: <as...

SQL 'for xml' and C# example??
I'm missing some piece of the puzzle. I'm using 'for xml' type queries from SQL2K - and now trying to change the front-end from ADO to C#, I can't quite figure it out.. It seems that I could use an XmlReader.. and somehow use that instead of a DataReader.. but that didn't work.. I tried using a DataReader and get the byte array - but that wasn't right. SQL returns formatted XML, I need to load that XML into an XmlDocument somehow without ANY molestation of that dom. Any ideas? Examples? Missing links???? thanks THANKS!! I've been looking for the answer to ...

sql summarizing help
Hi All, Given: table1 --------- id, cdate, weeksago, amount, typeid, timeclose 496, 2010-02-11, 0, 450, 3, 1 490, 2010-02-01, 1, 200, 4, 1 491, 2010-02-01, 1, 350, 2, 2 493, 2010-02-01, 1, 500, 5, 1 489, 2010-01-21, 3, 150, 2, 1 136, 2010-01-12, 4, 500, 3, 2 137, 2010-01-12, 4, 100, 3, 1 138, 2010-01-12, 4, 500, 2, 1 1. i'm trying to create a sql that will give me a summary resultset grouped by weeksago. 2. i want to sum the columns and get counts based on typeid. 3. avg of timeclose here's the sql i have: select weeksago, , (sum(timeClose)/(count(timeclo...

table problem
I have a table with three columns of data. Column A contains a part number such as "Part XX", or "Part ZZ". Column B has a date in the 01/01/05 format. Column C has a numeric value. There are about 20,000 rows of data. The dates cover three years. For example: PartXX 1/1/05 123 Part XX 3/7/05 456 PartZZ 9/10/07 789 PartXX 1/1/06 159 PartXX 1/1/07 234 Eart part only has one entry for each date during the year. In the fourth column I'll add a column with all 365 days for the year. The date will be in the 1/1, 1/2, 1/3 format. I want to create an output which searche...

Vlookup and IF function problem
Hi, I would like to do a kind of double Vlookup. I have 2 different worksheets with following information: Worksheet1 is the master file with all modells: 3 columns A B C B segment Honda Civic B J segment Honda Civic Coup� D segment Toyota Avensis D ... Worksheet2 is the data file: 3 columns, of which 2 are filled A B C B segment Honda Civic ... J s...

2006 Password Problems
I have been using MS Money for several years. The last version that I used, and was having no problems with, was MS Money 2005. I used the program on both my desktop and my laptop. I regularly backed up the data to a flash drive and transferred the file back and forth between the 2 machines. Then I upgraded to MS Money 2006 Deluxe. I restored the file from my 2005 version with no problems. I installed the 2006 to my laptop and transferred the backup file with no problems. I took my laptop on a 2 month trip keeping 2006 up-to-date and backing it up to my flash drive. Came home and tried to re...

Update multivalue field in table from form with SQL
Access 2007 on Vista I am using a form to establish the variables for a report before it is created. One field on the underlying table is a multivalue field, and the form includes a multi-select combo box control. After selections are made and the OK button on the multi-select combo drop down is clicked, focus moves to the next control. I have discovered though, that the underlying table does not update immediately, but does so correctly when I close the form. This sequence does not deliver the proper report data, so I need a way to immediately update the underlying table (m...

Problems on shut down or restart
Each time I shut down or restart my computer I keep getting pop up window. The window states Outlook is not responding and asks if I wish to end now. If I click on end now 3 times it will go ahead and shut down. Is anyone else having this problem? Doesn't seem to make any difference if I close Outlook prior to shut down or restart. I do have another email application that may, or may not, be running. Maybe this is the problem. I am seeing the same problem. I close the main Outlook window, but it is still running in my system tray. I can kill it by bringing up the Task Manager and...

Refer to SQL statements globally
I have two large SQL statements that I need to use across several forms, reports, etc. Rather than repeat them in each form, I'm trying to store and reference them globally. For example, I'd like to store strSQL1 and strSQL2 in a global module, so I can use either like this in a form: Random form: Me!lstResults.RowSource = strSQL1 Random report: Me.Report.RecordSource = strSQL2 Would I just store the SQL statements in a global module like: Public Function SQLSource() As String Dim strSQL1 As String Dim strSQL2 As String strSQL1 = "SELECT blah blah...

a language exportation problem
Hello, I have made some customization in an english CRM version then when I tried to export this modification to a french version , it failed. I am asking if there is some modification to the XML File to make it valid during the import from a french CRM. Thanks in advance Ahmed, This is tricky business you try do. But there is a tag language in the xml file english = 1033. Change it to the correct french language tag. HTH Ahmed Maaloul schreef: > Hello, > I have made some customization in an english CRM version then when I tried > to export this modification to a french v...

Hyperlink problem 2003 to 2002 and 2000
We have multi-sheet workbooks with hyperlinks between worksheets we create in 2003. When we send to a 2000 or 2002 user, they cannot use the hyperlink. Anyone know how to resolve? Save it as a htm? "Bob" wrote: > We have multi-sheet workbooks with hyperlinks between worksheets we create > in 2003. When we send to a 2000 or 2002 user, they cannot use the hyperlink. > Anyone know how to resolve? > > > Saving as an HTM will not help as users share the workbook. The hyperlinks are to move users between worsheets in the workbook. "Bob" <bob@...

To SQL or not to SQL?
I have a very vertical product developed 100% in M/S Access. I developed it myself in 1993 in the initial release of Access and been with it ever since. It is currently very stable and running in Access 2003. My big question is how or even *if* we should move it to SQL. My customers are getting bigger all the time with more users. Typically our customers run one or two workstations generally never more than 5. Performance, however, is starting to slow with database sizes passing 500 meg. We also have a LOT of code behind forms so are bound tightly to the Access development environ...