Error Data Type Mismatch - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Error Data Type Mismatch

Jerry Fowler

3 years ago

I have been staring at this for 3 days now, and I'm breaking down and asking for help.

I'm usually pretty good at finding Data Mismatch errors, but this one is stumping me.

I'm using a form very similar to the Email sending using Gmail that Rick teaches about in the seminar.

I created a Sendanemail Subroutine and I'm passing the MembersID and the MembersEmail to the subroutine. That is where I'm getting the Data type mismatch error.  I has dim them both as string, which is what they are typed in the table.  So just to test it I sent to dummy values in quotes to the subroutine;

Dim a as string
Dim b as string

a = "ab12345"
b = "[email protected]"

and I get the error

The error I'm getting is ERROR: Data type mismatch in criteria expression.

So I began to think it was a Query that might be causing the error.  Checked all the criteria and the links between tables and they are fine and all run on their own.

Looking for ideas on where to look next.  Let me know if any screenshots would help.  Thanks in advance as you all are so very helpful.

	Jerry Fowler OP @Reply 3 years ago
Sorry forgot to include the line just after where I set the values of A & B Sendanemail A, B

Jerry Fowler OP @Reply

3 years ago

Ok After more testing I narrowed it down by using breaks in the VBA to this Code;

CurrentDb.Execute "UPDATE BillingT SET """ & NoticeSent & """ = #" & Date & "# WHERE MemID=""" & MemID & """ AND DuesYear=" & GetUseYear & ";"

Now I'm first to say that I am not very competent in quotes and Double double quotes...lol

I have an entry in BillingT that I want to update the record where MemID = MemID and DuesYear = 2023. and the field is one of three fields either FirstNotice, SecondNotice, or ThirdNotice with the current date. So I set a [TempVars] ! [TempNoticeSent to equal one of the three values.

In the BillingT table, the MemID is type Short Text, DuesYear is type Number (Long Integer), and the 3 fields are type as Date/Time. Example of data are MemID = "02345678", DuesYear = 2023, and the noticesent ones = #5/4/2023#

	Kevin Robertson @Reply 3 years ago
Try this: `CurrentDb.Execute "UPDATE BillingT SET NoticeSent=#" & Date & "# WHERE MemID=""" & MemID & """ AND DuesYear=" & GetUseYear & ";"`

Jerry Fowler OP @Reply

3 years ago

Thanks, Kevin I used your code and got the ERROR: Too few parameters. Expected 1.

I came up with this and it worked:

    NoticeSent = [TempVars]![TempNoticeDateSent]

    sql = "UPDATE BillingT SET " _
        & "[" & NoticeSent & "]" & " = #" & Date _
        & "# WHERE MemID=""" & MemID _
        & """ AND DuesYear=" & GetUseYear & ""

    CurrentDb.Execute sql

I'm wondering since NoticeSent is a variable that holds the name of the field to be filled in (FirstNotice, SecondNotice, or ThirdNotice). that might be the issue with your code.

Still, I'm very appreciative of your quick reply. and this can be closed

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View