Year(Date) Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm attempting:

Private Sub Form_AfterInsert()
Dim strSQL As String

strSQL = "INSERT INTO YrTotals (EmpID,Year,VacInclu,FloatInclu) VALUES (" & Forms!Employees!EmployeeID & ", '" & Year(Date) & "', 80,16)"

CurrentDb.Execute (strSQL)

Me.Requery
End Sub

but i keep getting a:

Run-time error '13':
Type mismatch and it looks to be with the Year function.

Any suggestions/solutions would be appreciated.

TIA
 
"Year" is a bad name for a field name, since it is a reserved word in
Access. Be sure to add brackets around it in your query: [Year]. This may
be your problem.

Next, what is the datatype of [Year]? If it is not a Text field, it is
complaining about the apostrophes delimiting it. Then it should be:
Forms!Employees!EmployeeID & ", " & Year(Date) & ", 80,16)"

If it IS a text field, I believe the Year() function returns an integer. It
is probably conmplaining about that. So it should probably be:
Forms!Employees!EmployeeID & ", '" & cstr(Year(Date)) & "', 80,16)"

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

R Weston said:
I'm attempting:

Private Sub Form_AfterInsert()
Dim strSQL As String

strSQL = "INSERT INTO YrTotals (EmpID,Year,VacInclu,FloatInclu) VALUES
Click to expand...
(" & Forms!Employees!EmployeeID & ", '" & Year(Date) & "', 80,16)"
 
Are you sure? My first guess is that you are trying to
pass a text string to the EmpID. Is EmpID a text format?
-----Original Message-----
I'm attempting:

Private Sub Form_AfterInsert()
Dim strSQL As String

strSQL = "INSERT INTO YrTotals
Click to expand...
(EmpID,Year,VacInclu,FloatInclu) VALUES (" & Forms!
Employees!EmployeeID & ", '" & Year(Date) & "', 80,16)"
 
I'm attempting:

Private Sub Form_AfterInsert()
Dim strSQL As String

strSQL = "INSERT INTO YrTotals (EmpID,Year,VacInclu,FloatInclu) VALUES (" & Forms!Employees!EmployeeID & ", '" & Year(Date) & "', 80,16)"

CurrentDb.Execute (strSQL)

Me.Requery
End Sub

but i keep getting a:

Run-time error '13':
Type mismatch and it looks to be with the Year function.

Any suggestions/solutions would be appreciated.

TIA
Click to expand...


1) Year is a reserved word in ccess/VBA and should not be used as a
field name.
Change the field name [Year] to something else, perhaps "VacationYear"

2) The Year() function returns an Integer value, not a string value,
yet your code is written as though it were a string.
After changing the Year field name, try:

strSQL = "INSERT INTO YrTotals (EmpID,YearField,VacInclu,FloatInclu)
VALUES (" & Forms!Employees!EmployeeID & ", " & Year(Date) & ",
80,16)"
 
Even if i do:
Dim strSQL As String, test As Integer ******

test = Year(Date) ******

strSQL = "INSERT INTO YrTotals (EmpID,fldYear,VacInclu,FloatInclu) VALUES (" & Forms!Employees!EmployeeID & ", '2004', 80,16)"

CurrentDb.Execute (strSQL)

Me.Requery

I get:
Run-time error '2645':
Microsfot Office Access cant' find the field 'year'refereed to in your expression, but i have changed the field name to 'fldYear' and it is a text field as well.

Currently, i changed my code to:

strSQL = "INSERT INTO YrTotals (EmpID,fldYear,VacInclu,FloatInclu) VALUES (" & Forms!Employees!EmployeeID & ", '2004', 80,16)"

Which works.

Thanks, All. Will keep trying and am open to any more suggestions/ideas.
 
Back
Top