Number seen as string from form when using in VBA

[twgonder]

I have form with a textbox MyNumber that has as its source a field from MyTable1 called MyNumber that is a byte type (integer less than 255 and >= 0)) in the table.

Deep down in the code I'm getting
"12" > 98 = true
Up above in the code, entry of 12 for the textbox for this statement is showing a data type of 8 (string) in this:

?vartype(aFrm.ActiveControl.Text) (aFrm is the form object Me passed to a sub)
8
?fldnm


MyNumber

Shouldn't, since the data source is MyNumber (byte), VBA treat aFrm.ActiveControl.Text as numeric and not a string?
Is there something to set in the form (other than data source) to tell VBA that MyNumber is a number and not a string?
If I need to convert the string back to a number in the code, is there a property that I can use for the textbox to know it's a number?

I got the idea to use *.text from another thread. Is it better to use *.value and what's the difference other than maybe converting a number to string?
Can I use *.value for numbers, dates and strings?

While I'm at it, I think I saw somewhere a function that returns the data type in English, instead of numbers. Anyone got a link for that (my Google search turned up nothing)?

[Gicu]

Here is a thread that should provide some clues:
https://stackoverflow.com/questions/...-in-vba-access
As for the requested function to provide data types names here is one version from Orange (Jack) on this forum, sorry Jack, hope it's OK:

'--------------------------------------------------------------------------------------------------------------' Procedure : FieldType
' Author 1  : Jack
' Created   : 03/18/2021
' Modified  : 03/23/2021
' Purpose   : To identify fieldtypes in Access
'--------------------------------------------------------------------------------------------------------------


Private Function FieldType(v_fldtype As Integer) As String


    On Error GoTo Error_FieldType


    Select Case v_fldtype
        Case dbDecimal
            FieldType = "Decimal"
        Case dbSingle
            FieldType = "Single"
        Case dbAttachment
            FieldType = "Attachment"
        Case dbBigInt
            FieldType = "BigInteger"
        Case dbBoolean
            FieldType = "Boolean"
        Case dbByte
            FieldType = "Byte"
        Case dbInteger
            FieldType = "Integer"
        Case dbLong
            FieldType = "Long"
        Case dbCurrency
            FieldType = "Currency"
        Case dbSingle
            FieldType = "Single"
        Case dbDouble
            FieldType = "Double"
        Case dbDate
            FieldType = "Date"
        Case dbText
            FieldType = "Text"
        Case dbLongBinary
            FieldType = "LongBinary"
        Case dbMemo
            FieldType = "Memo"
        Case dbGUID
            FieldType = "GUID"
        Case Else
            FieldType = "??Needs definition"    'Not all field types may have been defined
    End Select


Exit_Error_Fieldtype:
    Exit Function


Error_FieldType:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Error_Fieldtype


End Function

Cheers,

[twgonder]

Thanks for the routine and link.

I think the procedure has some errors. Or it's meant for something other than Access VBA.
The documentation I read (VarType Function - Microsoft Support) says that the variable is vbString for 8 but dbDate = 8.
You get Date for something that is a string.

[Gicu]

More reading :
https://learn.microsoft.com/en-us/of...e-property-dao
What you linked is not the same as the table field data type as (I think) requested in the original post.

Cheers,

[twgonder]

More reading :
https://learn.microsoft.com/en-us/of...e-property-dao
What you linked is not the same as the table field data type as (I think) requested in the original post.

Cheers,

I read the link, I don't think it deals with forms and variables in the form.
Here is the problem I'm having:



.value doesn't work because it doesn't return what the user entered in the textbox.
So, I'm using .text

Above is showing the strange result where 12 is greater than 98.
I'm guessing it's because 12 is seen as a string, but that's not certain given the last line in the immediate window.
Is there a better way to do this if statement to get the correct result?
Sometimes the validation procedure will be on strings or dates too.

[twgonder]

I've got to admit, this one really has me stumped:

[twgonder]

Does the space in front of the 98 and not the 12 give a clue as to what's going on?

[Gicu]

Can you upload a sample with the form in question and the full code. You seem to want to validate the entered value "as you type"; I usually do it after everything is entered and just refer to the control name (as Value is the default property). Where do you declare the array and where do you populate it?

Cheers,

[twgonder]

As for the requested function to provide data types names here is one version from Orange (Jack) on this forum, sorry Jack, hope it's OK:

I've now come back to this post.
I'm in the Form_Error of a form,
I can get the activecontrol.name
but activecontrol.type throws an error

Is there a way to find out the field's type while in a form's VBA procedure?
(No help on this from Google)

[CJ_London]

clue is in your question - you want the field type, not the control type so perhaps something like

Recordset(screen.activecontrol.ControlSource).Type

here is a list of field types

https://learn.microsoft.com/en-us/of...e-property-dao

[twgonder]

Dang, lost my message again!
@cj_l Thank you! I'm still sorting out the control from the data and what is which, where and when.
I wouldn't have tried the screen and controlsource, so that got me somewhere.

[twgonder]

clue is in your question - you want the field type, not the control type so perhaps something like

Recordset(screen.activecontrol.ControlSource).Type
...

So, I added this line to my code in Form_Error:
' If Recordset(Screen.ActiveControl.ControlSource).Type = 8 Then 'a date field, look for "."


(still searching what the "screen." part means,
but after this line runs, and the next instance of an entry error occurs caused by clicking on another control, Access with some reliability will crash.

In my sample, the lines after the then don't run, it goes straight to the end if.
I can stop the crashing by commenting out the line, and it comes right back after uncommenting.
I'm guessing that using = 8 instead of = dbDate shouldn't cause a problem (I will test while waiting to see if anyone has an idea as to the crashing).

This is the kind of crash I've been experiencing for months, possibly by some similar line of code.
(I'll have to debug more, now that I know the kind of thing I'm looking for)

This line of code does what I asked for in a previous post, is there any reason why it would crash Access?

[CJ_London]

Can’t work with one line of code and a confusing description of what then happens. What does crash mean? You get an error, it freezes? It closes. What do you have in the way of error handling? Are you using option explicit? The list goes on

re screen, plenty of links out there
https://learn.microsoft.com/en-us/of.../Access.Screen

[twgonder]

[QUOTE=CJ_London;505975]Can’t work with one line of code and a confusing description of what then happens...
/QUOTE]

First, we can start with the syntax. It compiles, but maybe isn't appropriate in the Form_Error procedure?

[CJ_London]

Sorry don’t have time to speculate. Provide proper information or don’t bother. syntax errors typically show as red when you are typing it and complete a line. Compile errors (assuming you are using option explicit) relate to early bound objects - so no error means not a problem there. Run time errors relate to late bound objects and data