Data type mismatch with last cascading combo box?

[shocktea]

Hi,

I'm slowly learning more Access and VBA, so I apologize in advance if I cannot specifically answer all your questions about this.

Some background: I am making a form at work for a coworker with cascading combo boxes where she can select a Customer, then Platform Description, then Period, then Year. I used VBA code for these and they all seem to be working, except the Year.

Private Sub Form_Load()
On Error Resume Next
 
CustomerCB.SetFocus
PlatformDescriptionL.Enabled = False
PeriodL.Enabled = False
YearCB.Enabled = False
End Sub
 
 
Private Sub CustomerCB_AfterUpdate()
  On Error Resume Next
 
  PlatformDescriptionL.Enabled = True
  PlatformDescriptionL.RowSource = "Select distinct PlatformDescription " & _
           "FROM SD0039DA_T " & _
           "WHERE CustomerName = '" & CustomerCB & "' " & _
           "ORDER BY PlatformDescription"
  PlatformDescriptionL.SetFocus
  PeriodL.Enabled = False
  PeriodL.RowSource = "Select distinct Period " & _
           "FROM SD0039DA_T " & _
           "WHERE CustomerName = '" & CustomerCB & "' " & _
           "ORDER BY Period"
  PeriodL.SetFocus
End Sub
 
Private Sub PlatformDescriptionL_AfterUpdate()
  On Error Resume Next
  PeriodL.Enabled = True
  PeriodL.RowSource = "Select distinct Period " & _
           "FROM SD0039DA_T " & _
           "WHERE CustomerName = '" & CustomerCB & "' AND " & _
           "PlatformDescription = '" & PlatformDescriptionL & "' " & _
           "ORDER BY Period"
  PeriodL.SetFocus
 
End Sub
 
 
  Private Sub PeriodL_AfterUpdate()
  On Error Resume Next
 
  YearCB.Enabled = True
    YearCB.RowSource = "Select distinct BillingYear " & _
           "FROM SD0039DA_T " & _
           "WHERE CustomerName = '" & CustomerCB & "' AND " & _
           "PlatformDescription = '" & PlatformDescriptionL & "' AND " & _
           "Period = '" & PeriodL & "' " & _
           "ORDER BY BillingYear"
  YearCB.SetFocus
 
End Sub

I am pulling a table from Excel into Access (SD0039DA_T2), then I have used a delete and append query to populate a 2nd table (SD0039DA_T). I did this 2nd table because the first table was slowing down all of Access because it's such a large file directly linked to Excel.

The only real difference between the two tables is I added another column to SD0039DA_T called BillingYear. This is the Year in the cascading comboboxes/listboxes I am having trouble with. In the append query, I used ...

BillingYear: Right([SD0039DA_T2]![Billing Date],4)

...appended to BillingYear. I want just the year (YYYY), not the whole billing date from the original table (MM/DD/YYYY).

So I'm thinking the problem is with the YearCB section of the VBA code or something to do with my tables? Please help, I've tried looking at other forums and cannot figure it out! Thank you so much for your help!

-shocktea

p.s. If you see a different way to do this, I'm open to suggestions! I just don't know advanced Access, so step-by-step is appreciated with any recommendations or changes I should make.

 

[pbaldy]

If period is numeric:

"Period = " & PeriodL & " " & _

 

[shocktea]

That worked!! Thank you so much!!

Am I able to check this as solved?

 

[pbaldy]

Happy to help! I marked it for you.