Solved Need help with number as text (1 Viewer)

[Scarlet_Pimp]

I have following code that gives me error 3464 "type mismatch criteria".

Private Sub cmdSaveFrm1_Click()

 

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    'Dim sql As String

    Dim dubbel As String

 

    'eerst een check of het zoekveld niet leeg is

    If Nz(Me.txtBestelbonNew.Value, "") = "" Then

        MsgBox "Geef een bestelbon in !!", vbExclamation

        Exit Sub

    Else

 

    'sql = "SELECT [Planning]"

    dubbel = "Bestelbon = " & Me.txtBestelbonNew.Value

 

    Set db = CurrentDb

    Set rs = db.OpenRecordset("Planning", dbOpenDynaset, dbSeeChanges)

    'Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges, dbLockOptimistic)

  

    rs.FindFirst dubbel

    'dan een check dat de bestelbonnummer al niet bestaat

    If rs.NoMatch Then

        rs.AddNew

        rs.Fields("Bestelbon") = txtBestelbonNew.Value

        rs.Fields("Productcode") = txtProductcodeNew.Value

        rs.Fields("Hoeveelheid") = txtHoeveelheidNew.Value

        rs.Fields("Leveringsdatum") = txtLeveringsdatumNew.Value

        rs.Fields("Opmerkingen") = txtOpmerkingenNew.Value

        rs.Update

        rs.Close

    Else

 

    Beep

    MsgBox "Bestelbon bestaat al !!", vbCritical

    rs.Close

  

    End If

    End If

      

    Set rs = Nothing

    Set db = Nothing

 

End Sub

I am certain it has the do with this line,

dubbel = "Bestelbon = " & Me.txtBestelbonNew.Value

The value in the textbox is saved as short text in the table. I've tried to put in in single quotes, double quotes. But nothing seems to work.

Any help would be greatly appreciated!

 

[cheekybuddha]

Try:

dubbel = "Bestelbon = '" & Replace(Me.txtBestelbonNew.Value, "'", "''") & "'"

 

[cheekybuddha]

Is your form not already bound to table Planning?

 

[Scarlet_Pimp]

No I usually work with unbound forms.

Someone also suggested this change,

dubbel = "Bestelbon = "' & Me.txtBestelbonNew & '"

How is your suggestion different?

 

[cheekybuddha]

No I usually work with unbound forms.

OK, but you are probably creating a lot more work for yourself, where Access would handle everything natively if you used bound forms.

 

[cheekybuddha]

How is your suggestion different?

It will prevent an error in case anyone types a single apostrophe in txtBestelbonNew.

Someone also suggested this change,

dubbel = "Bestelbon = "' & Me.txtBestelbonNew & '"

Yes, you seem to have already found the answer in this other thread.

Why are you asking again? Did it not work?

 

[cheekybuddha]

dubbel = "Bestelbon = "' & Me.txtBestelbonNew & '"

What you posted is actually incorrect - it is difficult to see because you don't use a monospace font to view it.

dubbel = "Bestelbon = "' & Me.txtBestelbonNew & '"
                      ^ ^                      ^
double quote too soon | |  missing double      |
                               quotes

' It should be:
dubbel = "Bestelbon = '" & Me.txtBestelbonNew & "'"

 

[Ken Sheridan]

You could do this more concisely by executing a single SQL INSERT INTO statement with a VALUES clause. You can then rely on a key violation to determine any attempt at inadvertent duplication. The SQL statement would be executed with the dbFailOnError constant, and the error handled in the code. The main thing you'd need to be careful about is getting the delimiters of each value in the VALUES clause right. Be sure to delimit values of DateTime data type with the # character and format the value in US date/time format, or an otherwise internationally unambiguous format such as the ISO standard for date/time notation of YYYY-MM-DD hh:mm:ss. Note that the standard differentiates months and minutes by case, whereas Access uses m for months and n for minutes.

When delimiting text data I prefer to use two contiguous quotes characters to represent a literal quotes characters, rather than the single quotes characters, when building a string expression, e.g "VALUES(""" & txtFirstName & """,""" & txtLastName & """)". This allows for apostrophes in the values, as in my own name in its original non-anglicized form: Cináed O'Siridean. Generally speaking a value is more likely to contain an apostrophe than a double quotes character.

 

[cheekybuddha]

You could do this more concisely by executing a single SQL INSERT INTO statement with a VALUES clause.

Or more concisely still by using a bound form and placing code in the Form's Error event to catch the key violation. You also then no longer need to worry about requerying the form and returning to the same recordset position, delimiting/escaping your inputs etc. etc.

This allows for apostrophes in the values

But doesn't allow for double quotes. You know you will eventually come up against the situation where someone is trying to enter six foot two inches!

 

[Ken Sheridan]

Or more concisely still by using a bound form and placing code in the Form's Error event to catch the key violation. You also then no longer need to worry about requerying the form and returning to the same recordset position etc. etc.


But doesn't allow for double quotes. You know you will eventually come up against the situation where someone is trying to enter six foot two inches!

I entirely agree that the use of a bound form is a no brainer. As regards feet and inches, I do have to put up with them from my American partner. I don't think she'll ever get used to metric units! When entering dimensions or quantities I'd generally include a Unit column, which might have values such as m, mm, mg, mcg etc. with the dimension or quantity as a number in a separate column. One number is all that's required of course because metric units are easily scaled up or down if necessary, but how do Americans store dimensions? Do they have a Feet column and an Inches column? I'd be interested to know.

I can see that something like 6'2" might be used in a Long Text column of narrative text. If returning rows on the basis of a substring, the simple use of single or double quotes as a delimiter would be problematic. I have on occasions had to to deal with columns which could contain either or both quotes characters, in which case I've used the Replace function to return the double quotes characters as a high ascii character such as the tilde. I guess that inserting a value from a variable which includes both quotes characters, I'd do the same in reverse. Assigning a literal string would no problem of course, e.g.

strHeight = "6'2"""
? strHeight
6'2"

 

[cheekybuddha]

When building strings for SQL you will always have to escape whatever character is used as the string delimiter.

Personally I find a pair of single quotes easier to parse mentally than a bank of three or four double quotes!

Of course, if you are doing any amount of it (building SQL strings) then you would use a helper function that you can code once and forget, or use other string builder classes/tools to minimise your contact with delimiters.

6'2" was a facetious example and you rightly point out that you would likely use a unit field along with the dimension, but there will always be free text somewhere that the user will unknowingly abuse!

 

[Pat Hartman]

I use a constant named QUOTE. That makes the strings much easier to understand.

strSQL = "Select * From MyTable Where Fld1 = " & QUOTE & Me.Fld1 & QUOTE & " AND Fld2 = " QUOTE & Me.fld2 & QUOTE

 

[cheekybuddha]

I use a constant named QUOTE. That makes the strings much easier to understand.

strSQL = "Select * From MyTable Where Fld1 = " & QUOTE & Me.Fld1 & QUOTE & " AND Fld2 = " QUOTE & Me.fld2 & QUOTE

(Though you still will need to escape and double up QUOTE within your input if either Me.Fld1 or Me.Fld2 might contain a QUOTE)

 

[Pat Hartman]

(Though you still will need to escape and double up QUOTE within your input if either Me.Fld1 or Me.Fld2 might contain a QUOTE)

That's a different problem

 

[RonPaii]

I entirely agree that the use of a bound form is a no brainer. As regards feet and inches, I do have to put up with them from my American partner. I don't think she'll ever get used to metric units! When entering dimensions or quantities I'd generally include a Unit column, which might have values such as m, mm, mg, mcg etc. with the dimension or quantity as a number in a separate column. One number is all that's required of course because metric units are easily scaled up or down if necessary, but how do Americans store dimensions? Do they have a Feet column and an Inches column? I'd be interested to know.

I can see that something like 6'2" might be used in a Long Text column of narrative text. If returning rows on the basis of a substring, the simple use of single or double quotes as a delimiter would be problematic. I have on occasions had to to deal with columns which could contain either or both quotes characters, in which case I've used the Replace function to return the double quotes characters as a high ascii character such as the tilde. I guess that inserting a value from a variable which includes both quotes characters, I'd do the same in reverse. Assigning a literal string would no problem of course, e.g.

strHeight = "6'2"""
? strHeight
6'2"

Dimension can unitless, then you have a unit column or a default unit. If the unit is Inch all dimension are decimal multiples or fractions of an inch. CAD software for example is unitless, users setup the default in templates or configurations with units saved with each model, imported models are converted from that model's units to your model's units. Dimensioning of drawings can be in any unit with the dimension converted between units.

 

[Ken Sheridan]

6'2" was a facetious example and you rightly point out that you would likely use a unit field along with the dimension, but there will always be free text somewhere that the user will unknowingly abuse!

I wouldn't be too worried about a value like that in a narrative long text column. My partner tells me we did in fact do so in a database of enslaved persons we built for a museum in the US. I'd still like to know how Americans (and whoever else still uses imperial units) enter dimensions as data, and how they do arithmetic on that data.

 

[RonPaii]

I wouldn't be too worried about a value like that in a narrative long text column. My partner tells me we did in fact do so in a database of enslaved persons we built for a museum in the US. I'd still like to know how Americans (and whoever else still uses imperial units) enter dimensions as data, and how they do arithmetic on that data.

Store dimensions as double or decimal in what ever unit you want, Millimeter, Centimeter, Inch, Foot, Foot-Inch-Fractions etc. Convert user input to that unit before storing. Do normal arithmetic, accounting for loss of precision. Present it to the user in your stored units or convert to the user's units for presentation.

 

[Ken Sheridan]

I had a look at how I might enter dimensions in feet and inches if the need ever arose. Data entry is simple of course, as parsing feet, inches, and fractions of inches into a single decimal value in inches is easily done. Reversing the process to convert a decimal value in whole feet and inches is also trivial. Fractions of inches from a decimal number is a little trickier. This is what I came up with:

Public Function DecToFraction(DecFraction As Double) As String

    Dim Numerator As Long
    Dim Denominator As Long
    Dim h As Long ' higher of two numbers
    Dim l As Long ' lower of two numbers
    Dim r As Long ' result of subtraction of two numbers
   
    ' express the decimal fraction as a vulgar fraction
    ' whose deniminator is a power of 10
    Numerator = Mid(CStr(DecFraction), InStr(CStr(DecFraction), ".") + 1)
    Denominator = 10 ^ Len(Mid(CStr(DecFraction), InStr(CStr(DecFraction), ".") + 1))
   
    ' using Euclid's algorithm get the highest common factor (HCF)
    ' of the numerator and denominator
    h = Denominator
    l = Numerator
   
    Do
        r = h - l
        If r < l Then
            l = r
        Else
            h = r
        End If
       
        If h = l Then
            ' h and l, when equal, are the HCF of the numerator and denominator value,
            ' to return the decimal fraction as a simple fraction the
            ' numerator and denominator values are divided by the HCF
            DecToFraction = (Numerator / h) & "/" & (Denominator / h)
            Exit Do
        End If
    Loop
   
End Function

As an example in the immediate window:

Firstly convert the vulgar fraction to a decimal fraction:

? 11/64
0.171875

Now convert in back:

? DecToFraction(0.171875)
11/64

 

[KitaYama]

Since CAD has been mentioned, just as a caution from someone who is constantly in touch with CAD, converting dimensions is very risky, and I've never seen anybody who works in CAD, converts.
I suggest sticking with the system your country mainly use (Imperial/metric), and stay away of converting dimensions, specially mass property of your design, while entering your data in a database.

 

[GPGeorge]

Since CAD has been mentioned, just as a caution from someone who is constantly in touch with CAD, converting dimensions is very risky, and I've never seen anybody who works in CAD, converts.
I suggest sticking with the system your country mainly use (Imperial/metric), and stay away of converting dimensions, specially mass property of your design, while entering your data in a database.

Cases in point.