By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,770 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Data type mismatch in criteria expression - Code Posted

P: 5
When the Command Button "Notify" is clicked, I am wanting to send out an email to the user with appropriate information in the email (works great), check the "Notified" check box (does not work), and disable the "Notify" button (works).

After the email is sent off, i get a Data type mismatch in criteria expression error. I am completely stumped. Any help would be greatly appreciated! Here is my code (I removed the email portion as it is working fine)... Thanks in advance!

I am working with Access 2003

Expand|Select|Wrap|Line Numbers
  1. Private Sub Notify_Click()
  2.     On Error GoTo Err_Notify_Click
  3.  
  4.     Dim stWhere As String       '-- Criteria for DLookup
  5.     Dim varTo As Variant        '-- Address for SendObject
  6.     Dim stText As String        '-- E-mail text
  7.     Dim RecDate As Variant      '-- Rec date for e-mail text
  8.     Dim stSubject As String     '-- Subject line of e-mail
  9.     Dim stTicketID As String    '-- The ticket ID from form
  10.     Dim Method As String        '-- Method of Delivery Information
  11.     Dim stWho As String         '-- Reference to tblUsers
  12.     Dim strSQL As String        '-- Create SQL update statement
  13.     Dim errLoop As Error
  14.  
  15.     'Set the update statement to disable command button
  16.     'once e-mail is sent
  17.     strSQL = "UPDATE Requests SET Requests.Notified = -1 " & _
  18.              "Where Requests.CauseNo = " & Me.CauseNo & ";"
  19.  
  20.     Debug.Print strSQL
  21.     On Error GoTo Err_Execute
  22.     CurrentDb.Execute (strSQL)
  23.     On Error GoTo 0
  24.  
  25.     'Requery checkbox to show checked
  26.     'after update statement has ran
  27.     'and disable send mail command button
  28.     Me.Notified.Requery
  29.     Me.Notified.SetFocus
  30.     Me.Notify.Enabled = False
  31.  
  32.     Exit Sub
  33.  
  34. Err_Execute:
  35.  
  36.     ' Notify user of any errors that result from
  37.     ' executing the query.
  38.     If DBEngine.Errors.Count > 0 Then
  39.         For Each errLoop In DBEngine.Errors
  40.             MsgBox "Error number: " & errLoop.Number & vbCr & _
  41.                    errLoop.Description
  42.         Next errLoop
  43.     End If
  44.  
  45.     Resume Next
  46.  
  47. Exit_Notify_Click:
  48.     Exit Sub
  49.  
  50. Err_Notify_Click:
  51.     MsgBox Err.Description
  52.     Resume Exit_Notify_Click
  53.  
  54. End Sub
  55.  
Sep 22 '09 #1

✓ answered by NeoPa

@ChipR
If CauseNo is a text field then you probably need to change your lines #17 to #18 to :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE Requests SET [Notified]=TRUE " & _
  2.          "Where [CauseNo]='" & Me.CauseNo & "';"
PS. Good call Chip. I should have remembered to add the link too.

Share this Question
Share on Google+
9 Replies


Expert 100+
P: 1,287
You should be able to use Notified.Refresh rather than .Requery, since you haven't added or deleted any records. Have you commented out the error handling to see on what line your error occurs?
Sep 22 '09 #2

P: 5
@ChipR
When commenting out the error handling, the error occurs on line 22

CurrentDB.Execute (strSQL)
Sep 22 '09 #3

Expert 100+
P: 1,287
Well, that narrows it down well. What types are the table fields for Notified and CauseNo?
Sep 22 '09 #4

P: 5
@ChipR
Notified is a yes/no check box

CauseNo is a text field
Sep 22 '09 #5

Expert 100+
P: 1,287
Here's a very handy reference article on
Quotes (') and Double-Quotes (") - Where and When to use them.

Short story is, since CauseNo is a text field, you need something like:
Expand|Select|Wrap|Line Numbers
  1.   ...
  2.   "Where Requests.CauseNo = '" & Me.CauseNo & "';"
Sep 22 '09 #6

NeoPa
Expert Mod 15k+
P: 31,277
@ChipR
If CauseNo is a text field then you probably need to change your lines #17 to #18 to :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE Requests SET [Notified]=TRUE " & _
  2.          "Where [CauseNo]='" & Me.CauseNo & "';"
PS. Good call Chip. I should have remembered to add the link too.
Sep 22 '09 #7

P: 5
ChipR and NeoPa,

Thanks for all the good info. On campus right now, but will be giving all this a whirl in a little bit. I'll chime back in when I get the chance to review the info in the link and try the code modifications. Thanks again.
Sep 22 '09 #8

P: 5
@NeoPa
Did the trick! Thanks for the help! I guess its time to get back to relearning the small things!
Sep 22 '09 #9

NeoPa
Expert Mod 15k+
P: 31,277
I'm very pleased to hear that we were able to help.

Welcome to Bytes!
Sep 22 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.