Data type mismatch error

[mays0515]

Can someone tell me what is wrong with this line:

SQL = "update tblGroupAndMembers set group_id = '" & !group_id & "' where member_id = '" & memberId & "'"

I keep getting 'Data type mismatch in criteria expression.' If i hard code memberId then I dont get the error. I dont understand what I am doing wrong. i defined memberID as Integer. The data Type for member_id in the tblGroupAndMembers table is number with field size integer. Can someone please help?

This line is in the following code:

With rstMemberId
SQL = "insert into tblGroupAndMembers(member_id) values('" & !member_id & "')"
db.Execute SQL
memberId = !member_id
.Close
End With

Set qdfTemp = db.CreateQueryDef("", _
"SELECT group_id FROM tblGroupNames WHERE group_name = '" & cmbGroupName & "' ")
Set rstGroupId = qdfTemp.OpenRecordset()
With rstGroupId
SQL = "update tblGroupAndMembers set group_id = '" & !group_id & "' where member_id = '" & memberId & "'"
db.Execute SQL
.Close
End With

 

[Jon K]

A numeric field doesn't need any delimiters. Remove the single quotes that surround its value i.e.

SQL = "update tblGroupAndMembers set group_id = '" & !group_id & "' where member_id = " & memberId


If the group_id field in the table is also a numeric field, remove the two single quotes that surround its value, too.

 

[mays0515]

Thanks!! That worked.

 

[Richardio]

data type mismatch and date

Hello:

I am also having a data type mismatch error. Both data field are date/time with medium date format. In my query, I want access to replace all blank spaces in a column with values in another column. I don't know why I am getting the error, please help. Find the SQL below:

WHERE ((([Invoice StateJan 14-April30].[Transaction in State Since])=IIf([Invoice StateJan 14-April30]![Transaction in State Since]=" ",[Invoice StateJan 14-April30]![Logged Date],[Invoice StateJan 14-April30]![Transaction in State Since])));


Thanks


Richardio

A numeric field doesn't need any delimiters. Remove the single quotes that surround its value i.e.

SQL = "update tblGroupAndMembers set group_id = '" & !group_id & "' where member_id = " & memberId


If the group_id field in the table is also a numeric field, remove the two single quotes that surround its value, too.

 

[Richardio]

Thanks a lot, that worked!

But I noticed without the iif statement, I had about 13,919 records, after I used the iff statement
(=IIf(IsNull([Invoice StateJan 14-April30]![Transaction in State Since]),[Invoice StateJan 14-April30]![Logged Date],[Invoice StateJan 14-April30]![Transaction in State Since]);

I had about 9197 records, any idea why my data is shrinking?

Thanks again.


Richardio

You can't compare a date to a zero-length string. Numeric fields cannot hold zero-length strings. Use the IsNull() function when testing for nulls.

WHERE ((([Invoice StateJan 14-April30].[Transaction in State Since])=IIf(IsNull([Invoice StateJan 14-April30]![Transaction in State Since]),[Invoice StateJan 14-April30]![Logged Date],[Invoice StateJan 14-April30]![Transaction in State Since])));

 

[Richardio]

Hi,
Thanks again.

That's true, I might have fields where both the Logged Date and Transaction In State Since Date are null. But I thought the iff statement took care of that in that if the transaction date is null, put the logged date, otherwise leave what ever is in the transaction date (i.e.blank). Do I have to rewrite the iff statement? How would it change from this:
(=IIf(IsNull([Invoice StateJan 14-April30]![Transaction in State Since]),[Invoice StateJan 14-April30]![Logged Date],[Invoice StateJan 14-April30]![Transaction in State Since]);


Thanks for your patience.

You have some records where both fields are null or neither date matches the date you are looking for.

 

[Richardio]

Thanks,

In my query, I am pulling some fields from an existing table. The fields I am pulling include: Transaction In State Since date and Logged Date. I want access to provide me with Transaction in State Since Date, but if Transaction in State Since date is null or blank I want access to replace those cells with the Logged Date, if the logged date is also blank when the transaction date is blank, then it should just give me whatever is in the Transaction in State Since column (in this case, a blank or null cell).

Please help me with the statement I need to write if it is not an iff statement.

I really appreciate your patience.


Richardio

Why not tell us your condition in words rather than in the IIf(). I don't think you should be even using an IIf()