Type Mismatch problem (1 Viewer)

[ets960]

I keep getting a type mismatch problem at "Year(Now())" at the PrePressNumber line. Now shows up as a correct date/time, but the year(now()) gives me a type mismatch? What is going on???


Private Sub Form_BeforeUpdate(Cancel As Integer)
'Get the maximum number from the MAIN table for the year
'and update that to the new value

Dim intNewVal As Integer

intNewVal = Nz(DMax("[NextSequence]", "Main", "Year = Year(Now())"), "0")

NextSequence = intNewVal

PrePressNumber = Right(Str(Year(Now())), 2) & "-" & intNewVal

End Sub

 

[pdx_man]

For starters, you are using an Access Keyword, Year as a field name, as you can see from your own code:

Year = Year(Now())

Access, as well as many programming languages gets vary confused. For starters, you need to rename that field to, for instance, Main_Year.

 

[ets960]

Actually, PrePressNumber is a text field of the format

05-3025

And as far as it being a field name, you're probably right about that... But that isn't where the error was coming from.

I updated the code to this and it is working correctly

Private Sub Form_BeforeUpdate(Cancel As Integer)
    'Get the maximum number from the MAIN table for the year
    'and update that to the new value
    
    Dim intNewVal As Integer
    Dim temp    As String
    
    
    intNewVal = Nz(DMax("[NextSequence]", "Main", "Year = Year(Now())"), "0") + 1
    
    NextSequence = intNewVal
    
    temp = Str(Format(Now(), "yyyy"))
    
    PrePressNumber = Right(temp, 2) & "-" & intNewVal
    
End Sub

I have no idea why the other Year(Now()) didn't work, but now all I need to do is figure out a way to have the intNewVal always have 4 digits... so if its 13 it concatonates as 05-0013

Thanks for the help guys.

 

[ets960]

Finally got that to work properly...

Thanks to everyone for the help, I've never designed an Access Database, and I've never written VBA code before, so I'm just sort of plunging into it.

Anyways, to get the integer formatted correctly, I did format(intNewVal, "0000")

So thanks everybody