VBA Code Type MisMatch.....Where is it!?! HELP (1 Viewer)

[jdlewin1]

Hi all HELP WANTED!!!!

I have the code below which is to populate a calender fromat on a form called reserve. When the PopulateCalendar sub is called i get a Type Mismatch.....can anyone find the issue / location of the Mismatch for me......

Private Sub PopulateCalendar()
On Error GoTo Err_PopulateCalendar

Dim strFirstOfMonth As String, bytFirstWeekdayOfMonth As Byte, bytBlockCounter As Byte
Dim bytBlockDayOfMonth As Byte, lngBlockDate As Long, ctlDayBlock As TextBox
Dim bytDaysInMonth As Byte, bytEventDayOfMonth As Byte, lngFirstOfMonth As Long
Dim lngLastOfMonth As Long, lngFirstOfNextMonth As Long, lngLastOfPreviousMonth As Long
Dim lngEventDate As Long, bytBlankBlocksBefore As Byte, bytBlankBlocksAfter As Byte
Dim astrCalendarBlocks(1 To 42) As String, db As DAO.Database, rstEvents As DAO.Recordset
Dim strEvent As String
Dim lngSystemDate As Long
Dim ctlSystemDateBlock As TextBox, blnSystemDateIsShown As Boolean
Dim strSQL As String
Dim lngFirstDateInRange As Long
Dim lngLastDateInRange As Long
Dim lngEachDateInRange As Long
'Dim strStartTime As String

lngSystemDate = Date
intMonth = objCurrentDate.Month
intYear = objCurrentDate.Year
'lblMonth.Caption = MonthAndYear(intMonth, intYear)
strFirstOfMonth = "/1/" & Str(intMonth) & Str(intYear)

bytFirstWeekdayOfMonth = Weekday(strFirstOfMonth)
lngFirstOfMonth = DateSerial(intYear, intMonth, 1)
lngFirstOfNextMonth = DateSerial(intYear, intMonth + 1, 1)
lngLastOfMonth = lngFirstOfNextMonth - 1
lngLastOfPreviousMonth = lngFirstOfMonth - 1
bytDaysInMonth = lngFirstOfNextMonth - lngFirstOfMonth
bytBlankBlocksBefore = bytFirstWeekdayOfMonth - 1
bytBlankBlocksAfter = 42 - (bytBlankBlocksBefore + bytDaysInMonth)
    
Set db = CurrentDb

'<SQL query to set recordset>
strSQL = "Select AllTransReservCalendar.[Job Number], AllTransReservCalendar.[Initials], AllTransReservCalendar.[Checked Out Date], " & _
         "AllTransReservCalendar.[In Date] From AllTransReservCalendar Where AllTransReservCalendar.[Checked Out Date] " & _
         "Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
         " or AllTransReservCalendar.[In Date] Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
         " or (AllTransReservCalendar.[Checked Out Date] < " & lngFirstOfMonth & _
         " and AllTransReservCalendar.[In Date] > " & lngLastOfMonth & ")" & _
         " ORDER BY AllTransReservCalendar.[Checked Out Date];"

        
Set rstEvents = db.OpenRecordset(strSQL)

Do While Not rstEvents.EOF
  
  lngFirstDateInRange = rstEvents![Checked Out Date]      '<Substitute for [Start Date]>
  If lngFirstDateInRange < lngFirstOfMonth Then
  lngFirstDateInRange = lngFirstOfMonth
  End If
  lngLastDateInRange = rstEvents![In Date]         '<Substitute for [End Date]>
  If lngLastDateInRange > lngLastOfMonth Then
    lngLastDateInRange = lngLastOfMonth
  End If
  
  For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
    bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
    bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
      'If IsNull(rstEvents![Start Time]) Then        ''''<Substitute for [Start Time]>
        'strStartTime = ""
      'Else
        'strStartTime = Format$(rstEvents![Start Time], "Short Time")       ''''<Substitute for [Start Time]>
        ''''strStartTime = Format$(rstEvents![Start Time], "h:mm AM/PM")       ''''<Substitute for [Start Time]>
      'End If
                                              '<Substitute for [Title]>
      If astrCalendarBlocks(bytBlockCounter) = "" Then
        astrCalendarBlocks(bytBlockCounter) = rstEvents![Job Number] & vbNewLine & rstEvents![Initials]
      Else                                    '<Substitute for [Title]>
        astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
                                              rstEvents![Job Number] & vbNewLine & rstEvents![Initials]
      End If
  Next lngEachDateInRange
    
    rstEvents.MoveNext
Loop
    
For bytBlockCounter = 1 To 42                       'blank blocks at start of month
  Select Case bytBlockCounter
    Case Is < bytFirstWeekdayOfMonth
      astrCalendarBlocks(bytBlockCounter) = ""
      ReferenceABlock ctlDayBlock, bytBlockCounter
      ctlDayBlock.BackColor = 12632256
      ctlDayBlock = ""
      ctlDayBlock.Enabled = False
      ctlDayBlock.Tag = ""
    Case Is > bytBlankBlocksBefore + bytDaysInMonth 'blank blocks at end of month
      astrCalendarBlocks(bytBlockCounter) = ""
      ReferenceABlock ctlDayBlock, bytBlockCounter
      ctlDayBlock.BackColor = 12632256
      ctlDayBlock = ""
      ctlDayBlock.Enabled = False
      ctlDayBlock.Tag = ""
        ctlDayBlock.Visible = Not (bytBlankBlocksAfter > 6 And bytBlockCounter > 35)
    Case Else   'blocks that hold days of the month
      bytBlockDayOfMonth = bytBlockCounter - bytBlankBlocksBefore
      ReferenceABlock ctlDayBlock, bytBlockCounter
      lngBlockDate = lngLastOfPreviousMonth + bytBlockDayOfMonth 'block's date
        If bytBlockDayOfMonth < 10 Then
          ctlDayBlock = Space(2) & bytBlockDayOfMonth & _
                        vbNewLine & astrCalendarBlocks(bytBlockCounter)
        Else
          ctlDayBlock = bytBlockDayOfMonth & _
                        vbNewLine & astrCalendarBlocks(bytBlockCounter)
        End If
            
        'If this block is the system date, change its color (CFB 1-25-08)
        If lngBlockDate = lngSystemDate Then
          ctlDayBlock.BackColor = RGB(0, 0, 255)
          ctlDayBlock.ForeColor = QBColor(15)
          Set ctlSystemDateBlock = ctlDayBlock
          blnSystemDateIsShown = True
        Else
          ctlDayBlock.BackColor = QBColor(15)
          ctlDayBlock.ForeColor = 8388608
        End If
          ctlDayBlock.Visible = True
          ctlDayBlock.Enabled = True
          ctlDayBlock.Tag = lngBlockDate
  End Select
Next
 
    
Exit_PopulateCalendar:
  Exit Sub
Err_PopulateCalendar:
  MsgBox Err.Description, vbExclamation, "Error inPopulateCalendar()"
    Resume Exit_PopulateCalendar
End Sub

Thanks!!!!!!!!!!!!!!!!

 

[plog]

My guess is its in your strSQL. You have no JOINS, so its in the WHERE.

Spit out your strSQL value, put it into a query object and see what result you get. If you get the same error then I am right and strSQL is the culprit.

A mismatch means you are comparing incompatible datatypes (Number to a string, date to a string, etc.)

 

[jdlewin1]

probably is but i cant determine where it is........i copied it into the query SQL but there is that many &'s in it i cant make head nor tail of it

I am hoping someone can look at it and correct the issue

 

[plog]

No, dont copy the code; copy the value of strSQL and paste that.

 

[jdlewin1]

not sure what you mean

 

[missinglinq]

Right off hand...

You have lngSystemDate Dimmed as Long, which is to say a Long Integer...a Number Datatype... then assigning it using

lngSystemDate = Date

setting it to Date(), which returns a Variant/Date, not a Number, hence the Datatype Mismatch.

Also, the lines

intMonth = objCurrentDate.Month
intYear = objCurrentDate.Year

are suspect. What, exactly, is objCurrentDate?

Month and Year are not Properties, to my knowledge, of anything.

Ditto, what, exactly, are intMonth and intYear? They're not dimmed as Variables, which means, I believe, that they're considered Variants, by default

I haven't really slogged through the rest of the code, but you may have other Variables dimmed inappropriately. intMonth and intYear, for instance, aren't dimmed at all, which means, I believe, if they are in fact Variables, as it appears, that they're considered Variants, by default.

Linq ;0)>

 

[jdlewin1]

Objcirrentdate is dim at the top of the code page for the form (not shown there) and is used to store the current date I believe. the intmonth and intyear are created in a public sub in a seperate module

 

[JHB]

At least one error here:

strFirstOfMonth = "[B][COLOR=Red]/[/COLOR][/B]1/" & Str(intMonth) & Str(intYear)

You're also missing a "/" between Str(intMonth) & Str(intYear)
Couldn't you post a stripped down version of your database with some sample data + description of how to reproduce the error.
Else be more specific in which code line the error shows.

 

[jdlewin1]

yes i have changed that back to:

strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)

 

[jdlewin1]

i am now getting an error that says "Too few parameters, Expected 1"

I put a stop on the PopulateCalendar sub and F8'd to follow it through and it runs through highlighting everything ok and then after highlighting "Set rstEvents = db.OpenRecordset(strSQL)" jumps to the error.

I put a Debug.Print on the strSQL and it printed the below:

Select AllTransReservCalendar.[Job Number], AllTransReservCalendar.[Initials], AllTransReservCalendar.[Checked Out Date], AllTransReservCalendar.[In Date] From AllTransReservCalendar Where AllTransReservCalendar.[Checked Out Date] Between 42826 And 42855 or AllTransReservCalendar.[In Date] Between 42826 And 42855 or (AllTransReservCalendar.[Checked Out Date] < 42826 and AllTransReservCalendar.[In Date] > 42855) ORDER BY AllTransReservCalendar.[Checked Out Date];

 

[JHB]

The paste the output from the Debug.Print into a new query, then it is easier to find out what the problem is.
I think it is the dates = 42826, 42855 it doesn't looks right.

 

[jdlewin1]

i have put the strSQL code into a new query and saved it....opened the form (as it needs the ID on the form to filter the correct records) and it shows what i would expect. The recordset shows the Job Number, Initials (of the person), Check Out Date and Due Date of the reservations that are in the current (selected) month.

Anyone got any ideas on the "Too few parameters, Expected 1" that is being generated? and where things are not going correctly!?

 

[JHB]

i have put the strSQL code into a new query and saved it....

Have you viewed the query?

..opened the form (as it needs the ID on the form to filter the correct records)

Nothing in the query seems to need an ID!
I see you've an error handler in the sub, comment it out until the code run as expected.

 

[The_Doc_Man]

JHB suggested it first, but I will add my two cents' worth.

The comparison of dates to integers would in theory work for VBA but SQL is less forgiving. It is true that dates can be resolved to a number, and further, it is true that if all you wanted was the date and not the time, a LONG would hold dates that stretch farther into the future than any of our life expectancies. But this kind of conversion is automatic in VBA; it is less automatic in SQL.

I suspect that based on some of the other manipulations in that code you posted, the date formats are giving you trouble. The question I would ask in this context is, what is the data type of the dates in your table? Are they stored as type DATE or as type STRING? Because if they are strings, having "4/4/2017" isn't a date. #4/4/2017# IS a date when in query context. Or having CDate("4/4/2017") is a date. But a string is not a date regardless of what it contains. It isn't only about content. It is sometimes also about presentation of content. (Which is why the "Cxxxx" conversion functions exist.)

 

[jdlewin1]

The table fields are date format, the sql is running off a query which I have created to get me my initial recordset. Then the code should create the mini record set that I want to show on the calendar so just the Jon number and Initials and then the start and end date of the record to out that info on each of the days

 

[The_Doc_Man]

If the fields are in date format, then instead of using

[date-field] > ambiguous-integer

try

[date-field] > CDate( ambiguous-integer )

Should be a cheap enough experiment.