Solved

Format() function problem

Posted on 2013-11-07
13
478 Views
Last Modified: 2013-11-07
Within my application I am writing a label printing routine. The user can design a label specifying the font size and other parameters of various text boxes which appear on the label when the label report is opened. I found that when I applied a value to the Format property of a text box, this was ignored. I assumed this was because I was using unbound controls on my label report. So then I decided to use the format() function to format the value before I assigned it to the text box. Here is a simplified version of my code:

    Dim MyFormat As String
    Dim MyDate As String
    Dim MyControl As Control
   
    MyDate = Date()
   
    MyFormat = "yyyy-mm-dd"
   
    Set MyControl = Me![DateControl]
   
    With MyControl
        .Value = Format(MyDate, MyFormat) 'Error occurs here
    End With
   
    Set MyControl = Nothing

What I find is that I get Error:13 - Type mismatch at the point the text box is assigned. I created a new empty database and put the above code in there. It works just fine. So there must be something external factor which is causing my problem. I've been scratching my head for half a day now. Can anyone help please?
0
Comment
Question by:TownTalk
  • 5
  • 5
  • 3
13 Comments
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

"Control" is a generic class which can hold any control type. So first the question is why don't you use the specific control type like "TextBox" or whatever "DateControl" is?

But you can do this simpler by assigning the format directly to the control at design time or using this to do that at runtime:

With Me.DateControl
    .Format = "yyyy-mm-dd"
    .Value = Date()
End With

Open in new window


So you would not need any variable declarations at all (don't create variables if you do not really need them - in this code none of the variables are needed).

Cheers,

Christian
0
 

Author Comment

by:TownTalk
Comment Utility
I cannot reference individual controls in that manner, because this piece of code loops though every text box required for the label. So I have to instantiate a variable to point to each text box in turn.

And like I said, when I take this piece of code and run it in an empty database, it works just fine.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
There is nothing in that code that should be causing a type-mismatch error from what I can tell.

Try a few things...

1.  Create a new textbox in your problematic database, which will have all of the default properties in place, and try running that code against the new textbox.

2.  If that doesn't work, try creating a new form.

3.  Compact/repair

4.  Make a backup and decompile.  See Jim Dettman's article on the Decompile switch here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2043-Decompile-What-it-is-what-it-does-and-how-to-use-it.html
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
---> Dim MyDate As String

Also, try declaring this as a Date.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

1. why don't you show the real code you use - I see not any loop in here and you assigned the "DateControl" hardcoded in your own code also. When you ask something it is not helpful to write a similar code and post it, please always copy and paste the complete original code which is necessary to explain the problem.

2. please create a new database and import only that form into it and try it again to see if it is needed to repair the database. I personally never needed to use the undocumented "Decompile" option which is also not recommended from Microsoft. You should use that only if there's really no other way.

3. You can use the code above of course also in a loop with a slight change:
Dim MyControl As Access.Control    ' You should always use the full identifier to make sure that you used the right library
For Each MyControl In Me
    If TypeOf(MyControl) Is Access.TextBox Then  ' would address all Textboxes
'   Other variant (would address all controls where you added the mark to the tag property):
'   If MyControl.Tag = "Any mark you have added to your control" Then
'   Other variant:
'   If MyControl.Name = "DateControl" Then
        With MyControl
            .Format = "yyyy-mm-dd"
            .Value = Date()
        End With
    End If
Next

Open in new window


Cheers,

Christian
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
--->  I personally never needed to use the undocumented "Decompile" option

I haven't either, but I have seen enough cases here where it has cured unexplained problems in code that I think it is worth a mention.  Especially if you are having problems with the exact block of code that you have posted (there is no reason for that to cause a mis-match error).  

Of course, try things in order, with decompile last, read Jim's article including his cautions, and take the precaution I suggested earlier of making a backup.


Edit:

In fact one just yesterday:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28285585.html

No error - but also no reason for the code not to work (fixed with Decompile)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi mbizup,

that's right, I also don't see a real reason why it should end in a type mismatch error but as you saw the author didn't post the real code here so it is most likely that the problem is somewhere else in the real code or the object which was assigned to the "Control" variable. When I read that the author is using a loop over all controls it is very likely that he didn't assign the "DateControl" control to the variable, instead the variable was filled with some other control from the same form which caused the type mismatch error.

Cheers,

Christian
0
 

Author Comment

by:TownTalk
Comment Utility
I decompiled the database and it didn't fix anything. So here is my code. I've posted 2 routines.  DrawLabel()  is the routine which contains the loop. It repeatedly calls DrawEntity() for every field which is on the label. One wierd thing I noticed..... If I break my code at the point where the error occurs, and type ?format(Date(), "dd/mm/yy") in the immediate window, I get the same error. If I then reset the debugger and type the same command, it works normally.

Public Sub DrawLabel()
 On Error GoTo ProcError
    Dim Message As String
    Dim ErrorNo As Long
    Dim XFactor As Single
    Dim YFactor As Single
    Dim Entities As New ADODB.Recordset
    Dim Criteria As String
   
    OpenDatabaseConnection
   
    ClearLabel
   
    LabelWidth = NullToZero(Me![PrinterRef].Column(2))
    LabelHeight = NullToZero(Me![PrinterRef].Column(3))
   
    If IsBlank(LabelWidth) Then GoTo ProcExit
    If IsBlank(LabelHeight) Then GoTo ProcExit
           
    XFactor = Int(1500 / LabelWidth) / 10
    YFactor = Int(1500 / LabelHeight) / 10
   
    DisplayFactor = XFactor
    If YFactor < XFactor Then DisplayFactor = YFactor
   
    If DisplayFactor > 1 Then DisplayFactor = 1
   
    DisplayFactor = DisplayFactor
   
    XOffset = (150 - (LabelWidth * DisplayFactor)) / 2
    YOffset = 30 + (150 - (LabelHeight * DisplayFactor)) / 2
   
    With Me![LabelBackground]
        .Left = XOffset * mmTwips
        .Top = YOffset * mmTwips
        .Height = LabelHeight * DisplayFactor * mmTwips
        .Width = LabelWidth * DisplayFactor * mmTwips
        .Visible = True
    End With
   
    Criteria = "Select * From LabelEntities Where LabelRef=" & CStr(Me![LabelID])
   
    With Entities
        .ActiveConnection = DatabaseConnection
        .source = Criteria
        .CursorType = adOpenForwardOnly
        .CursorLocation = adUseClient
        .LockType = adLockReadOnly
        .Open
        Do Until .EOF
            DrawEntity ![EntityID]
            .MoveNext
        Loop
    End With
   
ProcExit:

    If Entities.State = adStateOpen Then Entities.Close
    Set Entities = Nothing

    CloseDatabaseConnection
   
    Exit Sub
   
ProcError:

    ErrorNo = Err
   
     If DebuggingApp() Then
        Debug.Print "Error:" & CStr(ErrorNo) & " - " & Error$(ErrorNo)
        Stop
    Else
        LogError ErrorNo, "Error in DrawLabel()"
       
    End If
       
    Resume ProcExit
   
ProcProblem:

    MsgBox Message, 16, "Problem in DrawLabel()"
    GoTo ProcExit
End Sub

'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Public Sub DrawEntity(EntityID As Long)
 
    On Error GoTo ProcError
    Dim Message As String
    Dim ErrorNo As Long
    Dim Entity As New ADODB.Recordset
    Dim Criteria As String
    Dim I As Integer
    Dim TextBoxNo As Integer
    Dim MyControl As Control
    Dim MyXpos As Integer, MyYpos As Integer
    Dim MyWidth As Integer, MyHeight As Integer
    Dim MyEntityType As Integer
    Dim MyFontName As String
    Dim MyFontSize As Integer
    Dim MyFontWeight As Integer
    Dim MyData As String
    Dim MyDataField As String
    Dim MyAlignment As Integer
    Dim MyItalic As Boolean
    Dim MyBorderWidth As Integer
    Dim MyBarcodeData As String
    Dim MyFormat As String
       
    OpenDatabaseConnection
   
    If Me![DeleteFlag] = "X" Then
        HideCurrentEntity
        GoTo ProcExit
    End If
   
    'If Me![CurrentEntity] is what needs to be drawn, then use the values off the screen
   
    If EntityID = Me![CurrentEntity] Then
        MyEntityType = NullToZero(Me![EntityType])
        MyXpos = NullToZero(Me![Xpos])
        MyYpos = NullToZero(Me![Ypos])
        MyWidth = NullToZero(Me![Width])
        MyHeight = NullToZero(Me![Height])
        MyData = NullToString(Me![Data])
        MyFontName = NullToZero(Me![FontName])
        MyFontSize = NullToZero(Me![FontSize])
        MyFontWeight = NullToZero(Me![FontWeight])
        MyAlignment = NullToZero(Me![Alignment])
        MyItalic = Me![Italic]
        MyDataField = NullToString(Me![Datafield])
        MyBorderWidth = NullToZero(Me![BorderWidth])
        MyEntityType = Me![EntityType]
        MyFormat = Me![Format]
                   
    Else
   
        Criteria = "Select * From CachedLabelEntities where Entityid=" & CStr(EntityID)
   
        With Entity
            .ActiveConnection = CurrentProject.Connection
            .source = Criteria
            .CursorType = adOpenForwardOnly
            .CursorLocation = adUseClient
            .LockType = adLockReadOnly
            .Open
           
            If .EOF Then
                Message = "Could not locate Entity No " & CStr(EntityID)
                GoTo ProcProblem
            End If
           
            MyEntityType = ![EntityType]
            MyXpos = ![Xpos]
            MyYpos = ![Ypos]
            MyWidth = ![Width]
            MyHeight = ![Height]
            MyData = ![Data]
            MyFontName = ![FontName]
            MyFontSize = ![FontSize]
            MyFontWeight = ![FontWeight]
            MyAlignment = ![Alignment]
            MyItalic = ![Italic]
            MyDataField = NullToString(![Datafield])
            MyBorderWidth = ![BorderWidth]
            MyFormat = ![Format]
                               
            'If Not IsBlank(![Datafield]) Then
            '    Criteria = "Select SampleData from LabelDatasourceFields where DataSourceFieldid=" & CStr(MyDataField)
            '    MySampleData = GetSQLString(Criteria)
            'End If
        End With
    End If
   
               
    Select Case MyEntityType
   
        Case 1: 'Static text
                Set MyControl = AssignTextBox(EntityID)
               
                With MyControl
                    .Left = (XOffset + (MyXpos * DisplayFactor)) * mmTwips
                    .Top = (YOffset + (MyYpos * DisplayFactor)) * mmTwips
                    .Width = MyWidth * DisplayFactor * mmTwips
                    .Height = MyHeight * DisplayFactor * mmTwips
                    .FontName = MyFontName
                    .FontSize = MyFontSize
                    .FontWeight = MyFontWeight
                    .TextAlign = MyAlignment
                    .FontItalic = MyItalic
                    .Value = MyData
                    .Visible = True
                End With
               
        Case 2: 'Database Text
                'Criteria = "Select DataSourceFieldID,FieldTitle,FieldName,SampleData from LabelDataSourceFields Where DataSourceRef=" & Me![DataSourceRef]
                'PopulateComboBox Me.Name, "DataField", Criteria
                'DoEvents
               
                Set MyControl = AssignTextBox(EntityID)
               
                With MyControl
                    .Left = (XOffset + (MyXpos * DisplayFactor)) * mmTwips
                    .Top = (YOffset + (MyYpos * DisplayFactor)) * mmTwips
                    .Width = MyWidth * DisplayFactor * mmTwips
                    .Height = MyHeight * DisplayFactor * mmTwips
                    .FontName = MyFontName
                    .FontSize = MyFontSize
                    .FontWeight = MyFontWeight
                    .TextAlign = MyAlignment
                    .FontItalic = MyItalic
                    If MyFormat = "" Then
                        .Value = MyData
                    Else
                        .Value = Format(MyData, MyFormat) '!!! Error occurs here
                    End If
                   
                   
                    .Visible = True
                End With
               
        Case 3: 'Realtime text
       
                Set MyControl = AssignTextBox(EntityID)
               
                With MyControl
                    .Left = (XOffset + (MyXpos * DisplayFactor)) * mmTwips
                    .Top = (YOffset + (MyYpos * DisplayFactor)) * mmTwips
                    .Width = MyWidth * DisplayFactor * mmTwips
                    .Height = MyHeight * DisplayFactor * mmTwips
                    .FontName = MyFontName
                    .FontSize = MyFontSize
                    .FontWeight = MyFontWeight
                    .TextAlign = MyAlignment
                    .FontItalic = MyItalic
                    .Value = MyData
                    .Visible = True
                End With
       
               
        Case 4: 'Logo
                Set MyControl = AssignLogo(EntityID)
       
                With MyControl
                    .Left = (XOffset + (MyXpos * DisplayFactor)) * mmTwips
                    .Top = (YOffset + (MyYpos * DisplayFactor)) * mmTwips
                    .Width = MyWidth * DisplayFactor * mmTwips
                    .Height = MyHeight * DisplayFactor * mmTwips
                    If Not IsBlank(MyData) Then
                        .Picture = MyData
                    End If
                    .Visible = True
               
                End With
               
        Case 5: 'Line
                Set MyControl = AssignLine(EntityID)
                With MyControl
                    .Left = (XOffset + (MyXpos * DisplayFactor)) * mmTwips
                    .Top = (YOffset + (MyYpos * DisplayFactor)) * mmTwips
                    .Width = MyWidth * DisplayFactor * mmTwips
                    .Height = MyHeight * DisplayFactor * mmTwips
                    .BorderWidth = MyBorderWidth * DisplayFactor
                    .Visible = True
               
                End With
                   
        Case 7: 'EAN13 BArcode
               
                ConstructEAN13Barcode "750103131130", "Form", Me.Name, XOffset + (MyXpos * DisplayFactor), _
                    YOffset + (MyYpos * DisplayFactor), MyHeight * DisplayFactor, MyWidth / 1.67
               
        Case 8: 'EAN13Barcode
       
                ConstructEAN8Barcode "7500313", "Form", Me.Name, XOffset + (MyXpos * DisplayFactor), _
                    YOffset + (MyYpos * DisplayFactor), MyHeight * DisplayFactor, MyWidth / 1.67
               
        Case 9  'EAN128Barcode
       
                If MyData = "" Then MyData = "290476909000"
                           
                ConstructEan128Barcode MyData, "Form", Me.Name, XOffset + (MyXpos * DisplayFactor), _
                    YOffset + (MyYpos * DisplayFactor), MyHeight * DisplayFactor, MyWidth / 1.67, MyData
                   
    End Select

   
ProcExit:

    If Entity.State = adStateOpen Then Entity.Close
    Set Entity = Nothing
   
    CloseDatabaseConnection
   
    Exit Sub
   
ProcError:

    ErrorNo = Err
   
     If DebuggingApp() Then
        Debug.Print "Error:" & CStr(ErrorNo) & " - " & Error$(ErrorNo)
        Stop
    Else
        LogError ErrorNo, "Error in DrawEntity()"
       
    End If
       
    Resume ProcExit
   
ProcProblem:

    MsgBox Message, 16, "Problem in DrawEntity()"
    GoTo ProcExit
End Sub
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
Comment Utility
Hi,

when I look at this it seems to me that this code has really nothing to do with what you have told in your initial post...

If I'm not wrong, you're loading the format string and also the data to be displayed from a table so there is no "Date()" and no date format string here. What I see are a lot of variable declarations whith a not so small risk of getting in trouble with existing keywords. You should always introduce a variable name with a short identifier, that makes it easier to read as you know of which type a variable is and also there is no risk of using keywords. That means, i.e. using "strMessage" instead of "Message" and so on.

Additionally, you assign the "MyControl" with a function "AssignTextBox" using an ID coming from the loaded table, so it is not sure what this function does and what it returns. Also the contents of "MyData" and "MyFormat" is not known looking at your code, it comes from a table and could be NULL, you have a function "NullToZero" which is also unknown to the reader of your post.
Some controls doesn't have a "Value" property and as your code is called "DrawLabel" I would assume that you try to assign a value to "Value" of a label which doesn't exist, it must be "Caption" in this case.

Cannot say more from your code.

Cheers,

Christian

BTW: You should look into the naming of all controls also and make sure that you do not use any keyword as name, an often made error which causes such problems is to name a field or control "Date"....
0
 

Author Comment

by:TownTalk
Comment Utility
I disagree with your statement that the code I just posted has got nothing to do with the original post. Actually it is absolutely identical.

 .Value = Format(MyData, MyFormat)

The value in MyData may or may not be a date. That's why I dont use Date variables.

AssignTextBox() looks within the current report for an unused text box which can then be utilised to receive a field of data for the label. At the point the report opens, there are 20 unused hidden text boxes. When AssignTextBox finds an unused TextBox, it returns a pointer to the calling routine.

I choose not to use the Leszynski/Reddick naming convention. I was programming a long time before it was introduced and I find it makes code dificult to read. I know that's  a minority opinion, but i'm comfortable with my coding style and it doesnt give me any problems.

NullToZero() is a function which was included in a sample database supplied by MS in the early versions of Access. (I've been using MS Access for almost 20 years) I didn't look to see if they still supply this utility, I use it all the time. It accepts a variant value and returns zero if the supplied value is null. Otherwise it returns the supplied value.

I'm going to import all my objects into a new database and see if that fixes anything.......
0
 

Author Comment

by:TownTalk
Comment Utility
Damn.... No, importing into a new database didn't fix it. See attached image please.

At the point the error occurs, MyData holds "31/12/200" and MyFormat holds "dd/mm/yy"

If I break my code at this point and type ?Format("31/12/2000","dd/mm/yy") in the immediate window I get error 13, if I then reset the debugger and type the same again, I get 31/12/00
Format-Error.jpg
0
 

Author Comment

by:TownTalk
Comment Utility
I've fixed it. The problem was occuring because there was a field in my table called 'Format'. So Access was attempting to use this instead of the built-in format() function. References to this field can be seen in the code I posted above.

So the problem was self-inflicted. But I had no idea that a table fieldname could override a built in function name like this.

I'll give the points to Bitsqeezer, because he talked about ensuring that variable names did not conflict with keywords. It's not quite what happened, but close enough.

Thanks everyone for your input.

Ian
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi Ian,

yes, it can, I had once a similar thing with a field named "Date" made by another programmer and I was close to think that the Date() function must have a bug...:-)

The same thing can happen with any other object name including variable names so be very careful if you do not want to use prefixes for your names.

Cheers,

Christian
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now