Opening report for last month - keep getting Runtime error 13: Type mismatch

[lhgrappler]

I'm trying to make a button that will open a report filtered for last month. I have been all over the internet and tried several different methods, everytime I end up with a type mismatch error. Here's the lastest attempt:

Private Sub cmdMonthlyReports_Click()
Dim defDate1 As Date, defDate2 As Date
defDate1 = Date
defDate2 = Date


defDate1 = DateSerial(Year(DateAdd("m", -1, Date)), Month(DateAdd("m", -1, Date)), 1)
defDate2 = DateAdd("d", -1, DateSerial(Year(Date), Month(Date), 1))

Dim strDateRange As String
strDateRange = "Appointment >= # " & defDate1 & _
" # And Appointment < #" & defDate2 & "#"

DoCmd.OpenReport "Monthly Appointments", acViewReport, , strDateRange
End Sub


When it's not running - the DateSerial() function works as expected in the immediate window, during runtime it throws the type mismatch error.

Any help is appreciated. I have only dabbled in Access, I'm just helping my dad get his database easier for him to use (the guy who set it up is not available)

 

[MajP]

Public Sub PreviousMonth()
Dim FirstDayOfLastMonth As Date
Dim LastDayofLastMonth As Date
'first previous month
LastDayofLastMonth = DateSerial(Year(Date), Month(Date), 0)
FirstDayOfLastMonth = DateSerial(Year(LastDayofLastMonth), Month(LastDayofLastMonth), 1)
Debug.Print LastDayofLastMonth
Debug.Print FirstDayOfLastMonth
End Sub

 

[HiTechCoach]

What is the data type for the field Appointment?

 

[lhgrappler]

Public Sub PreviousMonth()
Dim FirstDayOfLastMonth As Date
Dim LastDayofLastMonth As Date
'first previous month
LastDayofLastMonth = DateSerial(Year(Date), Month(Date), 0)
FirstDayOfLastMonth = DateSerial(Year(LastDayofLastMonth), Month(LastDayofLastMonth), 1)
Debug.Print LastDayofLastMonth
Debug.Print FirstDayOfLastMonth
End Sub

Output is as expected - LastDay 2/29/2020, FirstDay 2/1/2020

 

[lhgrappler]

What is the data type for the field Appointment?

Short Date

 

[lhgrappler]

FYI - hard coding a specific date within Where section of OpenReport function works correctly

 

[cheekybuddha]

Can you add:
Debug.Print strDateRange
before the line opening your retort and pest the output here

 

[lhgrappler]

Can you add:
Debug.Print strDateRange
before the line opening your retort and pest the output here

It breaks before then - on this line:
defDate1 = DateSerial(Year(DateAdd("m", -1, Date)), Month(DateAdd("m", -1, Date)), 1)


I have also tried using DateSerial within the Where section on the OpenReport function - same error "type mismatch"

 

[cheekybuddha]

That's odd that it errors on that line. It works for me.

I used the following function to test it:

Function DateString() As String

  Dim defDate1 As Date, defDate2 As Date
  Dim strDateRange As String
  
  defDate1 = DateSerial(Year(DateAdd("m", -1, Date)), Month(DateAdd("m", -1, Date)), 1)
  defDate2 = DateAdd("d", -1, DateSerial(Year(Date), Month(Date), 1))
  
  strDateRange = "Appointment >= # " & defDate1 & _
  " # And Appointment < #" & defDate2 & "#"
  
  DateString = strDateRange
  
End Function

However, the output might cause your error:

?DateString
Appointment >= # 01/02/2020 # And Appointment < #29/02/2020#

I haven't tested, but the spaces around the date might cause an issue.

 

[cheekybuddha]

>> I haven't tested, but the spaces around the date might cause an issue. <<

Actually, I think that's a red herring. I tried it in an SQL statement and it worded fine. Apologies.

 

[cheekybuddha]

Do you have Option Explicit declared at the top of every code module, and does your code compile?

 

[lhgrappler]

I'm beginning to think this database is just cursed lol. I have tried many variations - using variables or dateserial directly in the OpenReport command. Every time it pitches type mismatch, yet it takes hard coded dates fine.

Do you have Option Explicit declared at the top of every code module, and does your code compile?

Doesn't look like it. Should it be, if so where do I place it?
Compiles with no errors.

 

[cheekybuddha]

>> Doesn't look like it. Should it be, if so where do I place it? <<
Yes, place it above or below Option Compare Database at the top of every code module.

Then compile again.

 

[lhgrappler]

>> Doesn't look like it. Should it be, if so where do I place it? <<
Yes, place it above or below Option Compare Database at the top of every code module.

Then compile again.

Thanks for trying to help - still throws type mismatch

 

[plog]

My gut tells me this is somehow a leap day issue that will resolve itself in 11 days, but I my brain can't find the connection. So, I suggest you skin this cat another way. Compare the month and year of Appointment to the month and year of Date():

strDateRange = "Month(Appointment) = " & Month(DateAdd("m", -1, Date()) & " AND
Year(Appointment) = " & Year(DateAdd("m", -1, Date())

 

[MajP]

Output is as expected - LastDay 2/29/2020, FirstDay 2/1/2020

Out of curiosity if the code I provided works why not use it? My point was your code seems strange to me for the calculation.
Year(DateAdd("m", -1, Date)), Month(DateAdd("m", -1, Date)), 1)

 

[missinglinq]

Short Date

Short Date is how the field is formatted...not the DataType...as HTC asked! In the underlying Table is it defined as a DateTime Datatype?

 

[lhgrappler]

Out of curiosity if the code I provided works why not use it? My point was your code seems strange to me for the calculation.
Year(DateAdd("m", -1, Date)), Month(DateAdd("m", -1, Date)), 1)

I did - when it runs in the monthly report sub I still get 'type mismatch'

 

[lhgrappler]

Short Date is how the field is formatted...not the DataType...as HTC asked! In the underlying Table is it defined as a DateTime Datatype?

Sorry - data type is Date/Time

 

[lhgrappler]

My gut tells me this is somehow a leap day issue that will resolve itself in 11 days, but I my brain can't find the connection. So, I suggest you skin this cat another way. Compare the month and year of Appointment to the month and year of Date():

strDateRange = "Month(Appointment) = " & Month(DateAdd("m", -1, Date()) & " AND
Year(Appointment) = " & Year(DateAdd("m", -1, Date())

Going to give this a try - I'll report back

ETA - Red text & syntax error. There seemed to be a couple ending paranthisis missing, so I tried adding those. That changed the text to black while editing, but back to red with syntax error at runtime.

I so appreciate all of your help!

--
I am in the US, if that makes a difference with how its gets coded.
Also, the date field is sorted & grouped on the report. Maybe that's part of the problem?
--